MSSQL Server Reporting Services : Mastering OLAP Reporting: Ad Hoc TopCount and BottomCount Parameters - Page 11May 16, 2005 "Parameterizing" the Core Dataset We began the design of our report with a simple, unparameterized core Dataset. We can now logically approach the parameterization of the core Dataset, with everything in place that will be required to make it work. While, as we have mentioned in previous articles, the handling of parameterization for an MDX query takes some getting used to, the important thing to remember is that the objective is simply to supply a precise MDX string, to pass to MSAS from Reporting Services. Reporting from an OLAP cube, with anything but the simplest, non-parameterized MDX, requires a bit more effort than simply creating an MDX query to create a Dataset, as we did with the original in our example. The clumsiness comes with the introduction of the string by which we pass the MDX / parameter combination. In essence, we implant the parameter, and then turn the existing MDX into a string. While it works fine in passing the code to MSAS, converting the query to a string disables Dataset refreshment on the Data tab - hence the unwieldiness that some developers find challenging. Let's revisit our initial query, through which we will make the soon-to-be-handicapped Report Designer aware of all the fields we expect to require within the Dataset. Precision is important in the next steps, particularly with regard to the absence of carriage returns (pressing the Enter key to "push down" a line), and other stray actions. 1. On the Data tab, select the ProductData Dataset in the Dataset selector, as depicted in Illustration 48.
2. Replace the original ProductData Dataset query with the following (cut and paste, if helpful):
SELECT
{ [Measures].[Store Sales], [Measures].[Store Cost] } ON AXIS(0),
{TopCount([Product].[Product Name].Members, 10, ([Measures].[Store Sales]))} ON
AXIS(1),
{[Time].[1997].[Q1]} ON AXIS(2)
FROM
[SALES]
3. Click the Run button in the toolbar to execute the query, 4. Click the Refresh Fields button to refresh the data fields with the data retrieved within the new Dataset, as shown in Illustration 49.
The new Dataset appears in the Results pane, below the query, as depicted in Illustration 50.
Running the query with "placeholder", hardcoded TopCount() and Time specifications, as above, allows us to populate the data set with a representative of each of the data fields, as well as giving us an opportunity to see the function work within a simple, non-parameterized query. Having refreshed the data fields to match the new query results, we will now insert parameterization. Once this is done, the query must be combined into a single string, as we shall see. 5. Modify exactly the existing Dataset query by substituting parameter references, shown in the "With" column of Table 4, for the portion of the query designated under "Replace"
Table 4: Expression Components Summary The Query pane, containing our modified input, appears as shown in Illustration 51.
6. Combine the query into a single string, enclosing it in quotes, with an equals (=) sign in front of the query, precisely as shown in the following string (cut and paste, if helpful):
="SELECT { [Measures].[Store Sales], [Measures].[Store Cost] } ON AXIS(0), {"
+Parameters!pX_OutlierType.Value +"([Product].[Product Name].Members, "
NOTE: The query must exist as a single line, without "artificially" creating line breaks via the Enter key, etc. It often helps to do so within Notepad or another editor. Although line breaks may appear to exist in the query above, this is simply the way the text is presented in this document; the query is a single string. The Run icon is disabled once the query is enclosed in double quotes. The importance of constructing the query correctly becomes obvious, in that there is no way to execute it within the Data tab at this stage. An explanation of the components of the above expression appears in Table 5.
Table 5: Parameterized Expression Components Summary Having completed the parameterization of the report, we have a final pair of modifications to make to the report's properties to render the report fully functional from the perspective of the new business requirements with which we began our practice exercise. We will perform this alteration in the following section. |
|
|
The Network for Technology Professionals
Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers
+Parameters!pX_TopBotNum.Value
+", ([Measures].[Store Sales], " +Parameters!pX_Date_Hier.Value +"))} ON AXIS(1), {"
+Parameters!pX_Date_Hier.Value +"} ON AXIS(2) FROM [SALES]"