How to Add a New Filter to an SCRM Report

System Center

The following procedure adds the SMS site code as a filter on the SCRM 2006 Software Update Status for a Specific Software Update report.

To complete this procedure, you must first add a dataset column in an SCRM report. For more information, see How to Add a Dataset Column in an SCRM Report.

How to add a new filter to an SCRM report

  1. Open the SQL Server Business Intelligence Development Studio project you used in How to Add a Dataset Column in an SCRM Report.

  2. In Solution Explorer, right-click the report and then select View Code.

  3. In the code, search for the node <DataSet Name="LocationParameter">.

  4. Copy the node and paste it directly below the source node.

  5. In the copied node, change the <DataSet> node name to <DataSet Name="SMSSiteCode">.

  6. Change the child element <CommandText> to <CommandText>SCRS_GetSMSSiteCode</CommandText>.

  7. Delete the child node <QueryParameter Name = "@Location">.

  8. In the child node <Fields>, change the child node <Field Name="LocationParameter"> to <Field Name="SMSSiteCode">.

  9. Change the child element <DataField>LocationParameter</DataField> to <DataField>SiteCode</DataField>.

  10. In the child node <Fields>, change the child node <Field Name="LocationParameterLabel"> to <Field Name="SMSSiteCodeLabel">.

  11. Change the child element <DataField>LocationParameterLabel</DataField> to <DataField>SiteCodeLabel</DataField>, and then save the report.

  12. In the code, search for the node <ReportParameter Name="LocationParameter">.

  13. Copy the node and paste it directly below the source node.

  14. In the copied node, change the <ReportParameter> node name to <ReportParameter Name="SMSSiteCode">.

  15. Delete the child node <DefaultValue>.

  16. Change the child element <prompt> to <prompt>Site Code:</prompt>.

  17. In the node <ValidValues>, change the child element <DataSetName> to <DataSetName>SMSSiteCode</DataSetName>.

  18. Change the child element <ValueField> to <ValueField>SMSSiteCode</ValueField>.

  19. Change the child element <LabelField> to <LabelField>SMSSiteCodeLabel</ValueField>.

  20. Save and preview the report.

  21. In Solution Explorer, right-click the report, and then select View Code.

  22. In the code, search for the node <commandText>SCRS_SWUStatusSpecificUpdate_CUSTOM</CommandText>.

  23. Copy the child node <QueryParameter>Name="@Location Parameter"</QueryParameter> and then paste it below the source node.

  24. Change the copied node to <QueryParameter>Name@SMSSiteCode"</QueryParameter>.

  25. Change the child element <Value> to <Value>=Parameters!SMSSiteCode.Value</Value>.

  26. Change the stored procedure SCRS_SWUStatusSpecificUpdate_CUSTOM to accept the new parameter @SMSSiteCode NVARCHAR(255)='<ALL>'. For a completed example, see Code Sample: SCRS_SWUStatusSpecificUpdate_CUSTOM SCRM Stored Procedure.

  27. Verify and publish the report.

See Also

Show: