Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Report from a Cube in Reporting Services

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 menuclip_image002

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

clip_image004

17. Under Date>Calendar, Drag over the Month, Quarter, and Year into the bottom section

clip_image006

18. Under Geography drag over the State-Province and Country

clip_image008

19. Drag over the Calendar Date Hierarchy into the top section

clip_image010

20. Change the range operator to Range (Inclusive)

21. Place a check in the two checkboxes under Parameters

clip_image012

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

clip_image014

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

clip_image016

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

clip_image018

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.


Comments

Leave a comment on the original post [mikedavissql.com, opens in a new window]

Loading comments...