Recently, we had a business requirement in which one of our clients needed some modifications in report. So as we all know, SSRS reporting tool is most widely used to make customizations in the report.
Requirement:
User should be able to see the updated report header based on, the value selected for the field in filter criteria of report. For e.g.
Suppose, there are multiple opportunity records in Dynamics 365 and each of the opportunity record contains “Lost /Open/Won” options in “Status” field. And there is a custom report created which shows the opportunities created with “Lost /Open/Won” status in last 7 days.
Now user usually run the report and in report header, it shows “Lost – Opportunities created in last 7 days” which was a static label.
And client requirement is when the user made modification in filter criteria and run report at that time user should be able to see the dynamic label.
For e.g.
If the user selects Status = “Won” in filter criteria and Run Report.
Then in the report, the header needs to be changed to “Won – Opportunities created in last 7 days” i.e. dynamics report header.
Solution:
To achieve this we have made following changes in expression of header text box:
- We created a parameter which is generally used to get the filter value in report i.e. “CRM_FilterText”.
- Once we get the filter value in report parameter as a string we would need to divide this parameter and to get Status value.
So we have used the following code in the expression:
=Replace(Split(Mid(Parameters!CRM_Fi lterText.Value.ToString().Trim,(InStrRev(Parameters!CRM_FilterText.Value.ToString().Trim(),"Status “)),Parameters!CRM_FilterText.Value.ToString().Trim().Length)," ").GetValue(2),";",", ") + " - Opportunities created in last 7 days"
Note: Here we have used the “CRM_FilterText” OOB parameter to get the filter criteria in report.
By using the above expression in header text we can set dynamics status for the report.
Note: This code will only work for status options without spaces.
Conclusion:
We can get the specific text used in filter criteria to filter report and set it in the report as text in Dynamics 365 CRM.
