MSSQL Server Reporting Services: Mastering OLAP Reporting: Cascading Prompts - Page 8July 27, 2004 Our next action is to tie together the datasets of the cascading chain. We will designate ds_pX_Country as the "lead" dataset - it will not require modification, as we shall see. However, each of the remaining ds_pX datasets will require a modification, which we will accomplish in the next steps. 13. Click the Data tab once again, as required. 14. Select ds_pX_State from the dataset selector, as depicted in Illustration 31.
Click for larger image The ds_pX_State dataset opens. At this stage, we will need to focus upon precision in the modifications we make to our MDX query. In essence, we will be converting it to a string, which will pass parameters to MSAS. First, we will add the parameter reference, and then we will shape the query into a string. 15. Remove the comment line at the top of the query. 16. In the query, change the ON AXIS(1) - the ON ROWS line - as follows: Original Line: {[Store].[Store State].Members} ON AXIS(1)
Change to: {DESCENDANTS({" & Parameters!pX_Country.Value & " }, [Store].[Store State])} on AXIS(1)
The modified dataset appears as shown in Illustration 32.
17. Pull the query together into a single string, adding a =" (an equals sign and open double quotes combination) to the beginning, and a " (close double quotes symbol), as shown in Illustration 33.
NOTE: It is critical to remember that the query must be joined together in a single string - it would stretch to a single, long line if the query view were opened wide enough to contain it. What we must avoid specifically is the presence of artificial line breaks / "carriage returns," created by the Enter key. In addition, absence of the equals ("=" sign at the beginning of the query, which notifies Reporting Services that a string containing an expression follows, or either or both of the opening and closing quotes, will result in failure when we next execute the query. Once we take the above modification actions, the Run button is disabled. This is a bit cumbersome, as converting the query to a string results in our inability to actually see the query in action going forward, at least with regard to its populating the results pane underneath it. This is why precision is critical - we may not know we have an error until we run the query in the context of the full report. (A consolation, however, is that Reporting Services will inform us of the offending dataset(s) in the event of a failure, helping us to isolate and repair syntactical errors). We will perform a similar process with the queries returning the ds_pX_City and ds_pX_Store datasets. But first, let's bind the ds_pX_State query to a report parameter, linking it, in the process, to the first member of the "cascading chain," ds_pX_Country. 18. Select Report --> Report Parameters from the main menu, as depicted in Illustration 34.
The Report Parameters dialog appears. 19. Click Add to begin a new report parameter. A new parameter with default name appears. 20. For the dialog boxes shown in Table 5, type the corresponding items (replacing defaults where required):
The completed Report Parameters dialog appears as shown in Illustration 35.
21. Click OK to accept input and close the dialog. Having created the first of four report parameters, we will follow the same process with the three remaining parameters. 22. Create the following report parameters:
using the corresponding details contained within Tables 6, 7 and 8 below.
|
|
|
The Network for Technology Professionals
Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers
