Building Output with Trend Lines
I-Data Weekly - March 24, 2008
Kevin S. Goff, Microsoft MVP 2008
Welcome to I-Data Weekly, a technical blog dedicated to Business
Intelligence with the Microsoft SQL Server family of products.
I-Data stands for Intelligent Data, reflecting the goal of presenting the type of
content you'll find in SetFocus' Master's
Program - Business Intelligence Track .
Each week, we'll present a different technical piece from our
Business Intelligence Courseware - covering items such as data patterns
with SSIS packages, building hierarchical OLAP cube dimensions, custom MDX code,
advanced reporting with SQL Server Reporting Services, dashboard construction with
PerformancePoint Server, and SharePoint integration. As Microsoft is scheduling
SQL Server 2008 for release later this year, we'll also include some coverage of
the new BI features that SQL 2008 has to offer.
This week's installment of I-Data Weekly focuses on Building
Output with Trend Lines. We'll use SQL Server 2005 Reporting Services
for our example, and will cover the following
- Defining the end result of how we want the chart to look (a bar chart that
shows monthly sales, and then 2 trend lines to show sales from
last year, along with a monthly sales goal)
- Laying some groundwork by constructing some necessary MDX calculations to support
the chart
- Building the base MDX query for the chart
- Designing the chart in SQL Server Reporting Services
- Customizing the chart to show monthly sales from last year, along with sales goal,
as trend lines
1 - Defining the end result: a Reporting Services chart that shows sales and trend
lines
The
SetFocus BI training methodology involves starting at the end, to visualize
the final result, and then working backwards (step by step) to build the results.
Our goal here is to produce a chart that shows monthly sales, monthly sales
for the same time period a year ago, and then the monthly sales goal (for
simplicity sake, let's say the monthly sales goal is a flat 4% growth from the same
month last year).
So if we sold $10,000 in July 2003 and $10,500 in July of 2004, we would have exceeded
the July 2004 sales goal by $100 (the sales goal would have been $10,400).
For the report, we'll use the AdventureWorks OLAP database.
Here is an example of the chart we'd like to produce.
(Special note: many of these images have been resized - if
you want to see the entire original image, simply right-click on the image and take
the "Save Picture As..." option.)
2 - Laying some groundwork: constructing some reusable MDX Code
Anytime you build a chart or report against an OLAP database, you'll want to perform
a mental checklist of what items and measures you have immediate access to, and
which ones you don't. In this instance, there are two items that you'll need to
construct:
- A calculation that determines the Monthly Sales from the same
time period a year ago (MonthlySalesLY)
- A calculation that determines sales goal (MonthlySalesGoal,
which we'll calculate as MonthlySalesLY increased by four percent)
First, we'll create a reusable calculation in the AdventureWorks
OLAP cube, to calculate sales from 12 months prior. There are several ways
we could do this, including using the MDX LastPeriods function
or the MDX LAG function. Here we'll use the LAG
function.
Second, you'll need to create an MDX calculated member to determine
Sales Goal. Here we can reference the MonthlySalesLY calculation.
(Remember that you need to create these in order!)
Remember, calculated members will "honor" any dimension slicing - in this instance,
the calculation will run for each month in the month range that the user selects,
and will also honor any other dimension slicing (e.g. by customer region, by product,
etc.)
The
SetFocus BI Master's Program devotes an entire week to MDX programming, so that
students will have a strong understanding of MDX and how to apply it to different
business situations.
3 - Building the data result set for the chart
After creating the necessary MDX calculated members, you can create a new report
in SQL Server Reporting Services. You'll start by creating a new data source
against the Analysis Services OLAP AdventureWorks cube,. Then you can go to the
Data tab and use the Cube View selection list on the left hand side to drag the
Month, Internet Sales Amount, and our two calculated members (MonthlySalesLY
and MonthlySalesGoal) into the design area.
Note: any time you introduce a new calculation, you'll want to
test it. In this case, you might want to take the results of MonthlySalesLY
for the date range below, and make sure it's the same as the regular sales amount
for July 2001 through June 2002.
You can also define a filter range, as parameters, if you want the user to be able
to select a starting and ending month.
4 - Designing the chart
Designing charts in SQL Server 2005 Reporting Services is similar to most charting
tools. You drag an instance of the chart object (from the SSRS toolbox) into
the main report layout area. You can drag the three data measures (Internet
Sales Amount and our two MDX calculated members) into the Data Values area, and
then drag the Month into the X-Axis category area. If you pull up the chart
properties and go to the Data tab, you'll see the following.
Unfortunately, there's one problem. By default, the chart will plot all three measures
as a bar (either as a standalone bar, or as a stacked bar). However, we want
to show our two calculated members as trend lines. Fortunately, it's easy to customize.
5 - Customizing the display of chart data elements
If you select the Monthly Sales LY item (in the list of Data Values) and click EDIT,
you'll see a dialog box like the following:>
If you go to the Appearance tab, you can choose to plot the data as a line.
You can also set a marker size, market type/shape, and you can even customize the
line further with the Series Style option. So you can create different line
styles for both calculated members, to achieve the final display in the beginning
of this article.
Next week:
In the last few articles, we've looked at output (as well as some MDX code to help
us along the way). Next week we'll do something a little different: we'll
walk through creating a custom Fiscal Calendar dimension/hierarchy. Since
practically EVERY reporting system uses dates in some form, this should be helpful
for many.
Final thoughts:
The tools in the Microsoft BI stack provide a large amount of functionality to produce
output to satisfy some business requirement. Being able to show a trend line
is just one of many many examples. In the future we'll cover more ways to use SQL
Server Reporting Services to produce important business output.
The
SetFocus Business Intelligence Masters Program devotes an entire week to
covering functionality in SQL Server Reporting Services.
About the author:
Email this Author!