How to Add Drilldown Functionality to an SCRM Report
The following procedures add drilldown functionality to the reported program name column in the SCRM 2006 Add or Remove Programs Inventory Summary report.
The first procedure moves the version field to become a header and extends the Program Name column to include the language drilldown. The second procedure creates a custom report stored procedure to return the language name in the report. It also updates the report to use the custom stored procedure.
To perform these procedures, you must first add a dataset column in an SCRM project. For more information, see How to Add a Dataset Column in an SCRM Report.
To add a drilldown field in an SCRM report
Open the SQL Server Business Intelligence Development Studio project you used in How to Add a Dataset Column in an SCRM Report.
In Solution Explorer, double-click the report to open it in Layout view.
In the report matrix, click the Program Name column handle, right-click it, and then click Insert Column to the Right.
Move the mouse to highlight the column handle next to Program Name.
Ctrl-click the header cell for the new column and the Program Name column header cell, right-click one of the header cells, and then click Merge Cells.
Click the View menu option, and then click Datasets to display the Datasets window.
In the Datasets window, right-click the dataset AllApplicationInventorySummary and then click Add to display the Add New Field dialog box.
In the Name edit box, type Language.
In the Database field, type LanguageName and then click OK.
In the report matrix, select and then copy the cell with the expression value =Fields!ProductName.Value.
Paste the cell into the bottom row cell of the new column.
Click the bottom row handle to select the entire row, right-click it, and then click Insert Row Below.
Repeat the previous step to add a second row.
Paste the copied expression into the bottom row of the new column.
Right-click the expression value you just pasted, and change the expression to =Fields!Language.Value.
Copy the cell Version and paste it into the cell above the language value expression you previously pasted.
Click the pasted cell, and change the text to Language.
Using the version row, copy the three metrics cells (Total, Physical, and Virtual) and paste them on the same row as the language expression cell and in the same columns they were copied from.
In the language header cell, right-click it, and then click Properties.
In the Properties window, expand Visibility and set Hidden to True.
Repeat steps 19 and 20 for the language expression cell, and then save the report.
To update the stored procedure used by the SCRM report
Using SCRS_SWIApplicationsInventorySummary, create a custom stored procedure named SCRS_SWIApplicationsInventorySummary_CUSTOM to return the LanguageName in its result set. See Code Sample: SCRS_SWIApplicationsInventorySummary_CUSTOM SCRM Stored Procedurefor a complete example.
In Solution Explorer, right-click the report, and then click View Code.
Search for
<CommandText>SCRS_SWIApplicationsInventorySummary</CommandText>, and replace it with<CommandText>SCRS_SWIApplicationsInventorySummary_CUSTOM</CommandText>.Preview, save, and then publish the report.
See Also
Tasks
Code Sample: SCRS_SWIApplicationsInventorySummary_CUSTOM SCRM Stored Procedure
How to Create an SCRM Report Server Project
How to Create an SCRM Stored Procedure
How to Obtain the Report Definition Language File for an SCRM Report
How to Publish an SCRM Report
Concepts
About SCRM 2006 Report Customization