SQL Server Analysis Services 2005/2008 articles,blogs,scripts,FAQs,tools,books

In this article, we will continue to get hands-on exposure to parameterizing LastPeriods() within the sample OLAP report that we cloned in Part I. Beginning with the general concepts, in Part I, we began our practice session by setting up a scenario within which to work with a basic OLAP report in exposing the steps involved in parameterizing the LastPeriods() function specifically. We began by opening the sample Report Server project, AdventureWorks Sample Reports, and ascertaining connectivity of its shared Analysis Services data source. Next, we created a clone of an existing sample report, containing a matrix data region, with which to perform our practice exercise.

Once we had made structural modifications to the clone report, to further prepare for our practice exercise session, we performed a brief overview of the MDX LastPeriods() function, discussing details of the use we intended for the function to perform in support of the stated reporting needs of a hypothetical client, as well as touching upon general concepts surrounding the parameterization of MDX functions in general, and the LastPeriods() function specifically.

In this, the second part of our article, we will:

• Ensure the adequacy of the datasets, created automatically when we added query parameters as a last step in Part I, to support report parameters and meet business requirements;
• Add syntax to the Month dataset query to enforce cascading, based upon the selection made for the Year parameter by an information consumer at runtime;
• Leverage the MDX LastPeriods() function, containing index and Month parameter placeholders;
• Discuss the interaction of the various components in supporting the runtime parameter that the end consumer sees;
• Discuss the results obtained with the development techniques that we exploit.

Read more...