Home > SSW Standards > Rules > Making reports from an OLAP data source in Yukon
Making reports from an OLAP data source in Yukon
In this demo we will show you how to create a report using an OLAP data source. We will be basing our Report off the AdventureWorksDW sample database that is supplied with Yukon.
We will be creating a report that shows The Adventure Works Internet Sales
(by Product Category) and that will allow us to drill down into the Subcategory and Product levels.
Note: Before trying to create a report on the AdventureWorksDW it is important to deploy and process the Adventure Works sample. To do this you will need to open the
Adventure Works solution in VS2005 and deploy it to your Analysis Services
Server. In a typical installation the Adventure Works solution can be found at this location: "C:\Program
Files\Microsoft SQL Server\90\Tools\Samples\AdventureWorks Analysis
Services Project\".
Getting Started
- Open up the Business Intelligence Studio
- Create new Report Server project
- Enter name and location for the project
- Click OK
Figure:
Creating a New Project.
Connecting to Analysis Services
Under your project, in the solution explorer, you will now have two folders:
- Shared Data Sources
- Reports
- Right click Shared Data Sources
- Click Add New Data Source
- Change the Type of data source to Microsoft SQL Server Analysis Services
- Click Edit to create the connection string
Figure:
Select Microsoft SQL Server Analysis Services.
- In the Server Name field type in the Name of your Server (eg localhost)
- Select AdventureWorksDW
from the Database dropdown list
Note: If the sample has not been deployed properly, the AdventureWorksDW database will not be accessible.
Figure:
Building the Connection String.
- Click Test Connection
- If the Connection String is good click OK
- Name your Data Source after the Database (AdventureWorksDW)
- Click OK
You will return to the IDE and you should now have an AdventureWorksDW.rds file listed under the Shared Data Sources folder.
Create the Report
- Right click the Reports folder
- Click Add New Report
- Click Next on the splash screen
- Click Next to Select the AdventureWorksDW as our Data Source
- Click Query Builder to start building our Dataset
Lets make our Dataset
On the left of the Query Builder window, you will notice a section with a tab labeled Metadata. In this section you will see all the Measures, KPIs and Dimensions associated with your cube.
- Expand the Measures group by clicking the plus
- Expand the Direct Sales folder
- Drag Internet Sales Amount over to the empty pane on the right (This is the preview window)
- Scroll down to and expand the Products dimension
- Drag the Product Category heirarchy over to the preview window
Figure:
Dragging dimension attributes across.
We will also be adding a parameter so that we can report on different years.
- Expand the Date Dimension
- Expand the Calendar Folder
- Drag the Calendar Year attribute over to the Filter window on the top right of the Query Builder
- Tick the Parameter box
- Select All Periods from the Filter Expression dropdown list
The Query Builder window should be looking something like this...
Figure:
The dataset is complete.
- Click OK
Finishing Up the Wizard
After clicking OK on the Query Builder window, we will return to the Report wizard where you will see the MDX query that
we just created. Try not to look at it for too long, you may hurt your eyes.
- Click Next
- Select the Tabular report type and click Next
- Click Group> three times to move the Category, Subcategory and Product Name over to the Groups section
Note: The order in which you add the groups affects the way they will drill on the report.
- Click Details> once to move the Internet Sales Amount across to the Details section
- Click Next
- Check the Enable Drill Down and Include Subtotals boxes
- Select the Corporate Style and Click Next (last time I promise)
Note: Selecting a style does not apply a style sheet to your report. Rather, it just formats the initial report components to match this scheme. If you delete any report items, and add new ones they will be without this formatting.
- Name the Report (something like Internet Sales by Product Category)
- Click
Finish
The bulk of our work is now done! You may preview your report by clicking on the Preview tab, but I must warn you, it needs some tidying up.
Tidying Things Up
If you preview your report now,
it will look pretty ugly, but the functionality will be there. We will
adjust the table a little to make it look neater and we will also format
the sales figures to look like currency.
- Delete the Details row from the table
Note: The details row adds an extra and unnecessary level of drilling to the report. Since we don't need it, we delete it. If you don't believe me, before you delete it, preview your report and drill down to the last level. It is redundant.
Figure:
Deleting the Details row.
- Select the three remaining Sales fields
- Set their Format property to C0 (currency with zero decimal places)
Figure:
Setting currency format.
- Adjust the Column widths to fit the content
Note: Switch between layout and preview adjusting the columns until they are right.
The report should end up looking something like this
Figure:
Voila...One report ready to go.
Deployment Time
Prior to deploying the Report you will need to set the location of your Report Server. To do this right click the Report Project and go to properties. Your Report server will be in the form of http://server/ReportServer.
If you are working on your report server, you can simply set it to
http://server/ReportServer.
Once you have set the Report Server, you can right click the Report Project and click Deploy. Once it is complete you can view your reports in the Report Manager. Typically this URL is in the form of
http://server/ReportServer.
Final Thought
Was that easy or what? We made a report that supports drilling based off an OLAP source and we didnt have to write one line of MDX!
Acknowledgements
Adam Cogan
Grant Paisley
Michael Mileos