RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


The Baker's Dozen: 13 Tips for Building Dashboards with Microsoft BI Tools : Page 5

PerformancePoint Server, the newest product in the Microsoft Business Intelligence stack, offers tremendous productivity gains for building dashboards to display key performance indicators as well as many other reports and charts.

Tip 9: Building an Analytic Line Chart
Figure 3 contains a web page that shows a line chart of monthly sales for the most recent twelve months, broken out by product category, with an option to select an element from the Sales Geography Hierarchy. The chart also allows users to hover over a data point with the mouse and view the actual data value.

To produce this report, you need to define the data you want to plot (Sales Amount by Product Group), the x-axis range (the last 12 months, which you'll define through a simple MDX named set), and a filter link so that a user can run the chart for a specific sales geography element.

Figure 22. Defining an Analytic Grid in PPS
You can build this page using the PPS designer (see Figure 22) by doing the following:

  • Right-click on the report item in the PPS Workspace Browser, add a new report, and select Analytic Chart from the list of available report templates.
  • Under the Report Type option pull-down, select Line Chart with Markers.
  • Drag the Product Master Hierarchy and Sales Amount measure from the available items on the far left, into the Report Series area.
  • Drag the named set Last12Months from the available items into the Bottom Axis area. You can find this named set as an MDX formula in the JewelDestiny sales cube:
   // Named Set: Last12Months
   LastPeriods ( 12,
      [Date Calendar].[Month Desc].LastChild)
  • Drag the Store Master Sales Hierarchy into the Background area. This allows you to define a filter link on the web page based on the same dimension hierarchy.
Note that you can click the Browse button in the lower right corner of Figure 22: This allows you to preview the analytic grid before you save and publish the grid. Finally, you can add this analytic chart to the dashboard by selecting the dashboard from the workspace browser and dragging the published analytic chart to page three of the dashboard.

Because you defined the Store Master Sales Hierarchy as the background filter, you'll also need to define a filter for the third page, and then establish a filter link with the analytic chart. You can add a filter by doing the following:

  • Because a sales geography filter already exists (remember that you created one for the scorecard on page one), you can create a new zone above the zone for the analytic chart, give the new zone a small amount of the vertical area (e.g. 15%), and drag the Store Master Sales Geography Hierarchy into the zone.
  • Click on the analytic chart zone and then click on the Create Link option button on the main PPS designer screen. Then create a filter link between the analytic chart and the filter, in the same manner that you created filter links back in Tip 5 (see Figure 16 and Figure 17)—essentially, select the Sales Filter on the first tab of the Filter Link Editor dialog box, and then select Member Unique name as the Source Value on the second tab).
Tip 10: Building an Analytic, Drill-Down Report for the Fourth Page
Figure 4 shows a web page that allows a user to start at a very high level of sales data, and drill-down by store sales geography, product hierarchy, and date hierarchy. The drilling down (and up) is almost instantaneous.

You can easily create this type of page using PerformancePoint Server. To do so, right-click on the report item in the PPS Workspace Browser to add a new report, and select Analytic Grid from the list of available Report Templates.

Figure 23. Defining an Analytic Grid
Figure 23 shows the layout designer for an analytic grid. From the list of available measures and dimensions, drag the Geography Hierarchy into the Rows area in the lower left. After that, drag the Date Calendar hierarchy, Product Hierarchy, and then Sales amount Measure into the columns area. (If you want to view the data differently than what's in Figure 4, by all means, arrange the rows and columns to your liking.)

Note that you can click the Browse button in the lower right corner of Figure 23: This allows you to preview the analytic grid before you save and publish the grid. Finally, you can add this analytic grid to the dashboard by selecting the dashboard from the workspace browser and dragging the published analytic grid to page four of the dashboard. Almost too easy, isn't it?

Tip 11: Building a Report from SSRS
The fifth and final dashboard page is a Monthly Sales Trend Chart-a bar chart that shows a single vertical bar for total sales by month (see Figure 5). By default, the chart shows the last twelve months of available sales data; however, the user can select a number other than twelve. Additionally, the chart must plot sales from the same time period last year as a solid black line. You will use SSRS for the report, as SSRS allows you to define end-user parameters.

Before you even build the report, you'll need to define a calculation to return the total monthly sales for the same time period, one year prior. The following example shows the code for the calculated member SalesLY, which uses the MDX function ParallelPeriod to define the date scope as 12 months prior to whatever the current month happens to be.

   AS ([Measures].[Sale Amount],
      ParallelPeriod ([Date Calendar].
      [Business Year-Quarter Desc-Month Desc-Full Date].
      [Month Desc],12 )),
      FORMAT_STRING = "Currency,"VISIBLE = 1  ;
With that calculated member safely stored in the OLAP cube, you can use the member in SSRS.

I covered the mechanics for building an SSRS 2005 report against an OLAP database in the January/February 2008 issue of CoDe Magazine (Tip 13). Essentially, you want to enter the MDX code shown below into the MDX editor.

   --  Retrieve Sales for This year and for Last year, 
   --  for the last X Number of Months
   --  Note the @NumMonths parameter
   SELECT NON EMPTY { [Measures].[SalesLY], 
       [Measures].[Sale Amount] } ON COLUMNS,
       ([Date Calendar].
          [Business Year-Quarter Desc-Month Desc-Full Date].
          [Month Desc].ALLMEMBERS )  ON ROWS
       (SELECT (LastPeriods(@NumMonths,
          [Date Calendar].[Month Desc].LastChild)) ON COLUMNS 
   FROM [JewelDestiny])
Figure 24. SSRS Design, 1 of 2
Note that the code contains a parameter for the number of months (NumMonths): You'll need to define this parameter by clicking on the Query Parameters button in the report's data tab.

In the report's layout area, you can insert a new chart from the SSRS report toolbox and define the chart type as a Column…Simple Column chart. To populate the chart data properties, drag the column Month Desc from the report dataset to the Category Fields area, and the Sale Amount and Sale LY columns to the Data Fields area (see Figure 24).

To plot the Sale LY column as a horizontal line (a common question on SSRS technical forums), do the following (see Figure 25):

Figure 25. SSRS Design, 2 of 2
  • Right-click on the chart to access the chart properties.
  • Go to the Data tab, click on the Sales LY selection.
  • Click Edit to Edit the Chart Value.
  • In the Edit Chart Value dialog box, choose the Appearance tab, and check the option to Plot Data as Line.
Before you preview the report, you'll note in the Report Parameters pull-down that SSRS automatically translated the NumMonths query parameter into an actual report parameter. You can customize the label for the prompt in the Report Parameters dialog prompt.

Finally, you'll need to deploy the report to an SSRS Report Server so that you can access it from the PPS designer. To deploy, you must set two items in the Report Project: the TargetReportFolder (SSRSJewelDestinyReports), and the TargetServerURL location where the SSRS report server resides (http://localhost:8080/reportserver). On the latter, note the port number: If you've installed SharePoint, you'll need to provide the port number for your default web site).

Tip 12: Incorporating the SSRS Report into PerformancePoint
Figure 26: Importing an SSRS Report into PerformancePoint Server
After you create and deploy the SSRS report, accessing it from PPS is a breeze. From the PPS Workspace Browser, right-click on report to add a new report, and select SQL Server Report as the Report Template. Give the report the name MonthlySalesTrend.

In the editor for the new report (see Figure 26), enter the same server name that you used for the TargetServerURL report project property back in Tip 11. After you enter the server name you can click on the Browse button in Figure 26 to navigate to the Report folder and actual report name. Note that you'll need to check on the options to show the SSRS toolbar and SSRS parameters so that the user can enter a parameter for the number of months.

Finally, publish the report (right-click on the report to publish it). Then on the fifth page of the dashboard, you can drag the report from the list of available reports on the right into the main page zone. That's all there is to it!

Tip 13: Deploying to SharePoint
Figure 27: Deploying the Dashboard to a SharePoint Site
Finally, after previewing all the pages using the PPS designer and you're satisfied with the output, you can deploy the dashboard to a SharePoint site. Click on the SharePoint Site icon on the PPS main toolbar to display the dialog box, Deploy a Dashboard to a SharePoint Site (see Figure 27). You can specify the site URL, the Document library, and (on a subsequent dialog box) the Master Page layout.

You can find the entire source code for this article at www.commongroundsolutions.net. For additional information, check out my blog. The next Baker's Dozen article will present 13 tips for building a data-driven ASP.NET 3.5 application.

Kevin S. Goff is the founder and principal consultant of Common Ground Solutions, a consulting group that provides custom web and desktop software solutions in .NET, Visual FoxPro, SQL Server, and Crystal Reports. Kevin has been building software applications for 17 years. He has received several awards from the U.S. Department of Agriculture for systems automation. He has also received special citations from Fortune 500 companies for solutions that yielded six-figure returns on investment. He has worked in such industries as insurance, accounting, public health, real estate, publishing, advertising, manufacturing, finance, consumer packaged goods, and trade promotion. In addition, Kevin provides many forms of custom training.
Email AuthorEmail Author
Close Icon
Thanks for your registration, follow us on our social networks to keep up-to-date