The Baker's Dozen: 13 Tips for Querying OLAP Databases with MDX : Page 6
MDX is a SQL-like query language used to retrieve data from OLAP databases and to define Key Performance Indicators (KPI) in SQL Server 2005 Analysis Services.
by Kevin S. Goff
Mar 17, 2008
Page 6 of 6
Tip 13: The Baker's Dozen Spotlight—Using MDX in an SSRS2005 Report
Now that you've seen plenty of MDX examples, I'm now going to show an example of how to use MDX in a reporting situation. Tip 7 shows an example of a result set for the Top five sales states, and within those states, the top ten product subcategories sold. Suppose the end user wants to change either or both parameters before seeing the results on a report. How would you accomplish this?
I'll assume that you already know how to create a new SQL Server Reporting Services 2005 report project, and that you know how to create a data source connection to Analysis Services and to the AdventureWorks DW OLAP database.
Be VERY careful when using design tools that generate MDX code and allow you to edit the results. If you return to design mode, the designer tool will overwrite your custom changes.
The key point here is to incorporate the MDX query into the SSRS2005 DataSet designer, and to add the two parameters (you'll call them TopStateCount and TopProductCount.). Here are the steps:
Enter the code from Tip 7 into the MDX query editor (in the SSRS DataSet designer), as shown in Figure 2.
Note that in Figure 2, I've highlighted the Query Parameters button. Click on that button and add two parameters for the State Count and Product SubCategory Count, and also set default values (see Figure 3).
Go back to the MDX query in Figure 2, and replace the literals of 5 and 10 with @TopStateCount and @TopProductCount, respectively. Then click on the Execute Query button in the toolbar (the icon with the explanation point), and make sure the query executes correctly.
Design the report in the report layout designer (I added a table from the SSRS report toolbox, created a report group on State, dragged and dropped the columns from the report DataSet, and then added a subtotal for sales by state).
Note that once you added the Query Parameters in Step 3, SSRS2005 automatically adds report parameters for you (see Figure 4). You can modify the prompt as needed.
Finally, you can preview the report. Note that SSRS2005 prompts for the two parameters, and then displays the report (see Figure 5).
Finally, I also recommend two books. "Fast Track to MDX," by Mark Whitehorn, Robert Zare, and Mosha Pasumansky is a good introductory book. "MDX Solutions: With Microsoft SQL Server Analysis Services 2005 and Hyperion Essbase," by George Spofford, Sivakumar Harinath, Christopher Webb, Dylan Hai Huang, and Francesco Civardi, is a more intermediate/advanced book with a large amount of sample MDX code.
Coming Up Next, Creating Business Intelligence Dashboards
This article is a prelude to my next article, where I present 13 tips for building business intelligence (BI) dashboards using Microsoft's newest BI product offering, PerformancePoint Server. I'll show you how to leverage MDX query techniques from this article so that you can build and customize effective and attractive dashboards using SharePoint, Microsoft Excel Services, and PerformancePoint Server.
Have you ever submitted something (an article, a paper, some code, etc.) and thought of some good ideas after the fact? Well, I'm the king of thinking of things afterwards. Fortunately, that's the type of thing that makes blogs valuable. Check my blog for follow-up tips and notes on Baker's Dozen articles and maybe a few additional treats!
Kevin S. Goff is the founder and principal consultant of Common Ground Solutions, a consulting group that provides custom web and desktop software solutions in .NET, Visual FoxPro, SQL Server, and Crystal Reports. Kevin has been building software applications for 17 years. He has received several awards from the U.S. Department of Agriculture for systems automation. He has also received special citations from Fortune 500 companies for solutions that yielded six-figure returns on investment. He has worked in such industries as insurance, accounting, public health, real estate, publishing, advertising, manufacturing, finance, consumer packaged goods, and trade promotion. In addition, Kevin provides many forms of custom training.