Support Parameterization from Analysis Services - Page 3

Preparation: Modify the OLAP Report for Use within Our Practice Session

We will next make a few modifications to prepare the report for our practice session. Our objective will be to begin the session with a simple OLAP report that contains basic parameterized filters as required by our client colleagues (just as we did in preparing the report to support its own parameters within the articles we cited earlier). This time, however, we will only be creating the parameters within Reporting Services to serve as skeletal placeholders from which we will reference support objects that we will add to our Analysis Services data source in the respective procedural section below. Some of the support structure that is automatically created within Reporting Services as part of this process will be discarded / ignored. While there are other ways of approaching the creation of the parameters in Reporting Services, we are simply taking a short cut to finalize preparation and get to our focus subject matter, the creation and alignment of the parameter support objects in Analysis Services, more rapidly.

Let’s open the report (for those of us not already there) and make the necessary settings to place it into a state from which we can commence our “from scratch” practice steps.

We will begin by adding additional parameters to our sample report. These parameters will initially be of a standard, basic picklist variety. We will then work with these parameters to align them with support objects we later construct within the report’s underlying Analysis Services data source.

Add Additional Parameters through the Query Builder Interface and Review the Automatic Creation of Support Objects

As we stated in Mastering OLAP Reports: Parameters for Analysis Services Reporting, Pt. I, and elsewhere within this, and other of my, series’, the Design Mode button in the toolbar of the Data tab allows us to easily shift between Design view and Query view, when working with our OLAP queries. The idea, obviously, is to provide those who are not at ease with direct MDX a means of creating queries within a graphical interface.

We have begun with a basic report with core datasets, and are ready to add three rudimentary, date-related prompts that will meet our objectives. We will create these prompts within Design view, by taking the following steps:

We enter the Data view, which defaults to the presentation of the first of two pre-existing datasets, ProductData. To state what is probably obvious, we can tell that we are in Design view, primarily because we see the column headings for our query components in the Data grid, and we do not see MDX syntax. We also see that the Design Mode button is depressed, as depicted in Illustration 6.

We have accessed the Data tab to add three date-related filters (each of which we shall parameterize): one each for Calendar Year, Calendar Quarter and Calendar Month. Let’s first run the existing query to ascertain that all is in order with regard to the retrieval of data.

Data populates the Data pane, and appears similar to that depicted in Illustration 8.

Having ascertained that the core query is in working order, we will now add the date-related filters, as well as the “vanilla” parameters that we intend for them to support.

The Date dimension populates the Dimension column, within a newly created row of the Filter pane. The Calendar Year attribute hierarchy appears within the Hierarchy column, as well.

Our completed filter-with-parameter entry appears in the new row of the Filter pane, as depicted in Illustration 10.

We have successfully added a new parameter, in conjunction with a filter, within the primary dataset underlying our report. The addition of the parameter within the graphical interface, as we have seen in previous articles, has already begun a sequence of events that will trigger the automatic creation of a corresponding Report Parameter. A supporting dataset will also be created, as soon as we move to the Layout tab. But first, we need to add entries within the Filter pane for the additional Date filters we intend to add.

We will now create the final parameterized filter within the Filter pane.

The Filter pane, with all four entries, appears as shown in Illustration 11.

The Report Parameters dialog opens, this time revealing four Report Parameters, as depicted in Illustration 12.

We will make adjustments to each of the new Report Parameter entries in later steps, where it will be most efficient to accomplish all at one time.

Based upon our discussions in earlier articles, we see the behavior we have expected. All five Datasets, including the originally existing two Datasets and the three we have added via the enactment of parameterized filters within the Filter pane (and subsequent departure from the Data tab), now appear, as shown in Illustration 13.

We have successfully added three rudimentary parameters, based upon filters we added within the Filter pane of the primary Dataset underlying our report. The addition of the parameters within the graphical interface, as we have verified, triggered the automatic creation of corresponding Report Parameters. Supporting datasets were also created, as soon as we moved away from the Data tab. The timing of the automatic creation of objects can thus be understood; the sequencing becomes very important in the event that we need to modify or remove any of the objects after they are initially created and aligned.

Because we will be referencing parameter support objects outside Reporting Services, which we will create later in Analysis Services, we will not need the automatically created parameter support datasets. We will remove these later – we can thus reference their structures, in the meantime, which are already aligned to the existing Report Parameters, as a part of quick construction of the Analysis Services replacements.

We are now ready to transit to the Analysis Services Cube Designer to add parameter support to the Adventure Works cube.