MSSQL Server Reporting Services : Black Belt Components: Manage Nulls in OLAP Reports - Page 8
November 17, 2004
19. Click the Edit button to the right of the highlighted group.
The Grouping and Sorting Properties dialog - General tab appears.
20. Replace the contents of the Name box atop the tab, BrandSales_Brand_Name, with the following:
21. In the Group On: list, use the selector button to the right of the top line in the list, to modify the existing expression, =Fields!Brand_Name.Value, to the following expression:
Illustration 26 depicts scrolling down on the selector to select the replacement expression.
Click for larger image
The Grouping and Sorting Properties dialog - General tab appears, as shown in Illustration 27.
We are returned to Matrix Properties dialog - Group tab, where we note that the bottom row group now reflects the name change we made in the Grouping and Sorting Properties dialog.
22. Click OK to accept modifications, and to close the dialog.
23. Click OK to close the Matrix Properties dialog.
We return to the Layout tab. We now need to make another modification to continue with the drill-down-related changes requested by the information consumer group.
24. Right-click the textbox fourth column from the left / right (currently showing "= Fields!Brand_Name.Value" in the Layout view.
25. Select Properties from the context menu that appears.
The Textbox Properties dialog appears.
26. Replace "Brand_Name" in the Name box with the following:
27. Select the following by clicking the selector for Value, just below Name, to replace the current "= Fields!Brand_Name.Value" :
The Textbox Properties dialog appears (modifications circled) as depicted in Illustration 28.
28. Click OK to close the Textbox Properties dialog.
We return to the Layout tab. We have one more modification to make to complete the structural changes that the intended audience has requested: to remove the capability to drill down from Year to Quarters.
29. Click the Quarter textbox, containing the expression =Fields!Quarter.Value, which appears below the Year textbox (containing expression =Fields!Year.Value), as shown in Illustration 29.
30. Press the Delete key.
The Quarter textbox vanishes. This removes the capability to drill down to Quarters from the Year, as requested by the information consumers for this report.
We are now ready to test the report to preview the results of our layout changes.
31. Click the Preview tab.
32. Select Food from the parameter picklist that appears atop the report on the Preview tab.
33. Click the View Report button to execute the report.
The report runs and returns the data associated with the Food Product Family that resides in the MSAS Sales cube. We will drill down again, to ascertain the effectiveness of our layout modifications.
34. Drill down on Baking Goods (click the "+" sign to its left), which appears near the top left of the report, to expose its underlying groups.
35. Drill down on the Jams and Jellies level that appears to the right of Baking Goods.
36. Drill down on Jelly, which appears to the right of Jams and Jellies.
At this point we see the new lowest level of the row axis, Product Name, appear. We note, too, the appearance of the nulls we identified earlier. A portion of our view of the report at this stage appears in Illustration 30.
Therefore, we confirm that we have met the structural portion of the business requirements of the intended audience. We now need to restrict the report to present only those products with sales (or, in other words, to filter out any products whose Sales values are null). We will concentrate upon the construction of the filters to restrict the presentation as requested in the following section - and then explore the effects of null fields on filtering in general, together with approaches for overcoming those effects to deliver effective filters within our reports.
37. Select File -> Save All to save our work so far.