Data warehousing has become a staple of businesses to help make business decisions. From these data warehouses you can develop an Analysis Services cube which allows for fast reporting. Creating reports in Reporting Services 2008 from a cube is very different from a normal SQL query. We are going to walk through creating a report from the adventure works 2008 analysis database cube. Adventure works is freely available from codeplex. You will need to download and deploy the cube to have it available in reporting services as a data source. After you have the adventure works cube deployed you are ready to start building a report in BIDS (Business Intelligence Development Studio).
1. In BIDS Click File> New > Report Server Project
2. Set the name to CubeReport
3. In solution explorer Right click on the reports folder and select New Item > Report
4. Change the name of the RDL file to CubeReport
5. Click Add
6. In the Report Data Window on the left select New >Data Source
7. Set the Name to AWCube
8. Change the Connection type to Microsoft SQL Server Analysis Services
9. Click Edit
10. Enter in your server name(Example: Localhost)
11. Select the Adventure Works DW 2008 from the database drop down menu
12. Click ok twice
13. Right click on the AWCube data source and select add Dataset
14. Change the data set name to Sales
15. Click the Query Designer button
This window is the query designer for MDX. MDX is the language used to pull data from a cube. MDX stands for Multi Dimensional Expressions. When you think of pulling data from a cube you need to think about a three dimensional cube where the data is in the cube and the edges represent how you can view that data. For example, date, geography, product could be the edges, and sales amount as the measure in the cube. You are going to use date and geography from this cube.
Notice there are two sections in the Query Designer. The top section is for dimensions that you want to use as a filter or parameter for your report. The lower section is for the measures and dimension you want to show on the report. You are going to drag measures, date, and geography dimensions into the bottom section. You are also going to drag the date dimension into the top section to be used as two parameters on the report.
16. Under Measure > Internet Sales, drag in the Internet Sales Amount to the bottom section
17. Under Date>Calendar, Drag over the Month, Quarter, and Year into the bottom section
18. Under Geography drag over the State-Province and Country
19. Drag over the Calendar Date Hierarchy into the top section
20. Change the range operator to Range (Inclusive)
21. Place a check in the two checkboxes under Parameters
22. Click OK in the two open windows
Now you will see two parameters created automatically for you under the parameters folder in the Report Data window. In most reports you want the parameters to have available values. The MDX query has already created two hidden queries which are used in the available values for both parameters. Since both queries are identical you can delete one of them and use the just one query in each parameter.
23. Right click on the AWcube datasource
24. Place a check next to Show Hidden Datasets
25. Right click on the ToDateCalendar hidden query and delete it.
26. Double Click on the ToDateCalendar Parameter to open the parameter editor
27. Click on available Values
28. Set the dataset to FromDateCalendar
29. Set the Value field to ParameterValue
30. Set the Label Field to ParameterCaptionIndented
31. Click OK
32. Drag a Matrix from the toolbox to the report
33. Drag in each field to match the figure below
The Internet Sales Amount is in the Data section of the matrix
34. Change the background color of the headers to light blue and bold text
35. Change the format property of the Internet Sales Amount to “C”(Currency)
36. Preview the report
37. Set both parameters to “CY 2001”
38. The report should look like the figure below
There is a lot more clean-up you can do on this report. Adding items like collapsible groups on the columns and rows make it easier to see the data desired. Removing the words “Internet Sales Amount” from every column and making just a single header with that term would be easier to read. Now you know how to use a cube to create a report. The parameters work differently than in a normal SQL query report. It creates the hidden queries automatically for you. In the case of the above example the two hidden queries were identical. In these cases you can increase performance be eliminating one of the queries and using the same query in both parameters. Notice the months are not in order also. This is because they are in alphabetical order. This can be sorted using the month number in the group sorting options.
