You’re offline. This is a read only version of the page.
170
Hi Everyone,
I am working on FetchXML based SSRS reports and stuck with how to proceed with the issue. I have an entity called opportunity and it is 1:N relationship with forecasts. 1 opportunity can have multiple forecast records. I need to do a SSRS report to display some fields from opportunity and SUM from related forecast records based on dates.
So the report columns looks like this and it is grouped by opportunityid to show only 1 for each opportunity.
Opportunity ID, Estimated Revenue, Created On, Forecast This Month, Forecast next 3 months, Forecast Next 6 Months and so on
I have written a fetch XML query to retrieve opportunity and its related forecast records.
I did to find a SUM of forecast records between two dates but it shows 0 as a result. Please see below. I have tried different expressions but it does not work. Please see the expressions I am using as below
=SUM(IIF(((Fields!ad_new_forecastdate.Value)>(DateAdd("d",-(Day(today)-1), Today))And((Fields!ad_new_forecastdate.Value)<((DateAdd("d",-(Day(today)-1), Today)).AddMonths(3))),0)),"opportunityid")
=IIF(((Fields!ad_new_forecastdate.Value)>(today))And((Fields!ad_new_forecastdate.Value)<(today.AddMonths(3))),Sum(Fields!ad_new_actualtotalValue.Value),0)
Can someone point to correct way of doing this and correct filter expression?
Thanks and Regards
Nemath
The general approach should be as per the first expression - i.e. the Sum function should be in the outside. From what I can see, the first expression is summing a 0, rather than any other field
If may help to break the different parts of the expression into separate calculated fields, and display each while you test it
Share
Report
All responses (
Answers (
