Reporting Services in 2008 has provided some exciting new enhancements that will really
spice up your reports. In this example, we will create a charting report that uses the new
charting controls (Dundas charts that MS bought. Sweet!).
The cube data was created from Analysis Services using the AdventureWorksDW database.
Its a fairly simple cube that contains Internet Sales as the fact table, and Date and Product
dimensions. The Date dimension contains a hierarchy with year, month, and day levels. The
Product hierarchy contains Product Category and Product Subcategory levels.
Start a new Reporting Services Project and then select New -> Data Source from the Report Data
menu on the left.
On the "Type" drop-down, choose Analysis Services. Once you
have done that, edit the credentials and select the Analysis Services database that you created.
Once you have created the data source properly, you can now create a Data Set
for your Chart to consume. Locate the Data Source you just created from the
Report Data window, right click on it and select Add Data Set.
Adding a data set will prompt you for more information to create the data set.
You may notice that this property page says nothing about Analysis Services,
but don't worry, it's just a click away! Click on the Query Designer button
to build your "query", in actuality it will allow you to choose your dimensions
and measures for the report as shown below. From here you can really let
loose. From the Dimension column drop-down from the filter section, select
the Dim Product dimension.
Select a product hierarchy that contains a Product Category level. In the
Filter Expression, select the "ALL" level. Next, expand the fact Internet
Sales measure group and drag over the Sales Amount measure onto the
query designer below.
Next, expand your Dim Product dimension from the available dimensions on
the left. In this example I have a Product hierarchy called: E2E Product Hierarchy
that contains the category, subcategory, and product levels. Expand your
product hierarchy and drag over the Product Category level onto the query designer.
For the next dimension, I have a Time dimension with a time hierarchy that
contains Year, Quarter, Month, etc.. If you have created a similar one, expand
the dimension and also expand your hierarchy. Drag over the year level onto the
query designer to finish off our query. Your data set should look similar to the
results below. If your results differ, revisit your hierarchies in Analysis Services
and make adjustments where needed. Click OK when finished.
Back in the Dataset Properties window you will see the created MDX in the
query window. Unfortunately, you can't modify from here as it is grayed out!
Click OK to return to the Report Designer.
Displaying the dataset we just created in a chart is the easy part now!
Drag over a chart control from the toolbox onto the report designer.
Once the chart has been placed on the designer, the only thing you have
to do now is drag over the fields from the dataset onto the Chart landing
areas.
Drag the fields from the dataset from the dataset you created onto the chart
areas as shown below. Drag the Sales_Amount field to the Data Fields area,
drag the Product Category field to the Series area, and drag the Calendar
year to the Category area of the chart.
When you are finished dragging over the fields, simple click the Preview tab to view the results!
