devxlogo

The Baker’s Dozen: 13 Tips for Building Dashboards with Microsoft BI Tools

The Baker’s Dozen: 13 Tips for Building Dashboards with Microsoft BI Tools

corecards, test results, report cards, summaries?nearly everyone wants to skip past the details and see the bottom line; managers evaluate professional efforts based on performance. Microsoft’s Business Intelligence tools provide developers and power users with the tools and methodologies to produce scorecards and other summaries that represent performance graphically. This article will create a web-based dashboard using several Microsoft products, including the newest product in the BI stack, PerformancePoint Server.

Introducing the Players
If you’ve been following Business Intelligence lately, chances are good that you’ve heard the term “Microsoft BI stack.” This term refers to the set (or pyramid, as some call it) of products that comprise Microsoft’s BI offerings.

First, the foundation for data storage and analysis capabilities is SQL Server 2005 which includes Analysis Services, Integration Services, and Reporting Services: three key pieces of a BI solution.

Second, a collection of Office products work on top of SQL Server 2005 to deliver attractive, customizable output. These products include Microsoft Excel, Reporting Services and ProClarity.

Third, Microsoft PerformancePoint Server works as a portal to integrate output into a series of dashboard pages. PerformancePoint Server includes Scorecard and Dashboard functionality from Business ScoreCard Manager 2005.

Dashboards and Performance
So, you may be asking, what is a dashboard? Generally speaking, a dashboard is an interface (usually web-based) that displays summarized information, so that viewers can analyze it easily. Dashboards usually contain some combination of reports, scorecards, and charts: all designed and arranged so viewers can see information about the data being presented. Dashboards usually contain Key Performance Indicators (KPIs), when there are financial measurements to evaluate performance.

This article presents a fictional jewelry company called “JewelDestiny,” with retail stores across the United States. JewelDestiny tracks sales by both a geography-dependent and geography-independent sales hierarchy, as well as by a product brand hierarchy. They measure the performance of each salesperson and store performance by six factors:

  1. Sales versus pre-defined sales goal.
  2. Percentage of sales with “add-on” items, such as a chain, polishing cloth, etc. (must be at least 40%).
  3. Sales made with an Extended Service Plan, as a percent of Total Sales that were eligible for a plan (must be at least 50%).
  4. Sales made with a Payment Protection Plan, as a percent of Total Sales eligible for a plan (must be at least 35%).
  5. Repairs/resizing (must be at least 5% of total sales).
  6. Credit applications (must submit one per full work day).

JewelDestiny evaluates and monitors performance by whether an employee, store, and sales hierarchy meets these six factors; meeting six of six is perfect, five of six is good, and four of six is acceptable. JewelDestiny management wants a web site dashboard that essentially provides four things:

  1. A web page for managers to view the results of these six factors, for either a single employee or a sales hierarchy, for a specific period of time (a specific day, a month, a quarter, YTD, etc.).
  2. Standard reports, such as the top ten selling stores for a period of time, a line chart showing sales by Product Category for the last twelve months of available data.
  3. A drill-down report that allows an analyst to view sales for a high-level hierarchy definition (product category total for the year), and then drill down to subcategories or even individual items, by quarter, month, or even day.
  4. The ability for “power users” to add new reports as necessary.

What’s on the Menu?
This Baker’s Dozen article will provide the necessary steps to meet the requirements for JewelDestiny performance monitoring in the following Baker’s Dozen tips:

  1. Presenting the end result, a five page dashboard
  2. Building the database and the OLAP solution
  3. Introducing the PerformancePoint designer, and creating the scorecard (dashboard page 1)
  4. Building a simple dashboard (dashboard page 1)
  5. Assigning filters for the scorecard (dashboard page 1)
  6. Building a top 10 sales report (dashboard page 1)
  7. Creating a pie chart from Excel (dashboard page 2)
  8. Reading the pie chart from Excel Services, and placing it into a dashboard (dashboard page 2)
  9. Building an analytic line chart (dashboard page 3)
  10. Building an analytic, drill-down sales cube report (dashboard page 4)
  11. Building a report from SSRS that utilizes custom MDX and reporting parameters (dashboard page 5)
  12. Incorporating the SSRS report into PerformancePoint (dashboard page 5)
  13. Deploying the solution to JewelDestiny’s SharePoint site

Note that some of these tips are “continuation” tips (e.g. Tips 11 and 12 are a set). I’ll annotate these where necessary.

Before You Begin
This article covers a complete BI solution; here are the elements available for creating the project:

  • The relational database containing all the raw sales data for JewelDestiny
  • A SQL Server 2005 Analysis Services project that creates an OLAP database consisting of a sales cube and dimensions for Products, Sales Geography, and Dates
  • Separate SQL Server Reporting Services projects and Excel spreadsheets for specific pages of the JewelDestiny dashboard
  • A PerformancePoint Workspace project that contains the definition for the JewelDestiny dashboard, which consists of the following: the JewelDestiny scorecard for the six sales performance measures, references to external reports and charts, as well as PerformancePoint analytic reports and charts
  • While not required to run the examples, you have the option to deploy the JewelDestiny dashboard to a SharePoint site
Author’s Note: normally you would not use all of these tools to design reports and charts for a dashboard. One of the objectives of this article is to show the different available tools and their capabilities. Excel, Reporting Services, ProClarity, and even the PerformancePoint designer all provide nice reporting/charting tools. They are also subsets and supersets of one another?this can initially drive you quite mad! You should learn the capabilities and limitations of each of these. There is no one perfect designer.

A Quick ‘411’ on Using PerformancePoint Server
You can download a trial version of PerformancePoint Server by doing a web search on “Office PerformancePoint Server 2007 Trial Software.” Microsoft offers a trial version for both 32-bit and 64-bit environments. You will only need to install the Monitoring module: the other major module (the Planning Module) is beyond the scope of this article. PerformancePoint has a few prerequisites such as running on a server-based operating system that includes the .NET Framework 2.0, the ASP.NET AJAX extensions, SQL Server 2005, etc. I built these examples using RC1 of Windows Server 2008, though you should be able to run the examples using Windows Server 2003 R2.

Microsoft has designed PerformancePoint Server to install on http://localhost:40000. You launch the PPS designer using the shortcut address http://localhost:40000/DesignerInstall/ (the PPS designer is a .NET 2.0 ClickOnce application). You can use the designer to build dashboards, preview them in a web browser, and then publish them to a SharePoint site.

Editor’s Note: This article was first published in the March/April 2008 issue of CoDe Magazine, and is reprinted here by permission.

Tip 1: Presenting the End Result
It’s always good to define the end result up front, especially when you’re dealing with visual requirements. Figures 1-5 show the following:

  • Figure 1: A scorecard that shows the six standard performance measures (filtered by Date, Sales Geography, and Employee), along with a “hot list” of top 10 selling stores for the Date Selection.
  • Figure 2: A pie chart from Excel Services that shows sales broken down by Product Category.
  • Figure 3: A line chart that shows sales for the most recent 12 months of activity.
  • Figure 4: A sales cube that allows users to drill down into sales for a time period and sales/geography dimension definition.
  • Figure 5: A sales report from Reporting Services that shows the user of report parameters.
?
Figure 1. Dashboard page 1 of 5: A Scorecard and Top 10 stores.
?
Figure 2. Dashboard page 2 of 5: Pie chart from Excel Services.

?
Figure 3. Dashboard page 3 of 5: Sales line chart for most recent twelve months.
?
Figure 4. Dashboard page 4 of 5: Analytic grid with drill-down capability.
?
Figure 5. Dashboard page 5 of 5: SSRS report, user can set report parameters.

Along the way, you’ll see how to build the dashboard and pages using a combination of PPS, Excel, and SSRS. You’ll also see the role that MDX plays in customizing output and building filter pick-lists.

Tip 2: Understanding the OLAP Database

?
Figure 6. The JewelDestiny Cube

Figure 6 shows the diagram of the JewelDestiny OLAP database, which consists of the following:

  • Fact tables for Daily Sales and Repairs, along with counts for Extended Service Plans, Payment Protection Plans, and Credit Applications.
  • Dimension hierarchies for Date, Sales Area (two separate hierarchies for sales by geography and sales by business), and Product.
  • A Visual Studio 2005 Analysis Services Solution (see Figure 7).
  • MDX Custom Named Sets and Custom Member calculations from Analysis Services (see Figure 8).

As you read through the article you’ll see that these MDX code pieces play important roles in customizing output.

?
Figure 7. The JewelDestiny Analysis Services Solution
?
Figure 8. Named Sets and Calculations in Analysis Services

Tip 3: Getting Started with the PPS Designer
Launch the PPS designer again from http://localhost:40000/WebService/PmService.asmx.

Author’s Note: Before going any further, Tips 3-5 create the first dashboard page. As usual, the first one is always the hardest! The end of Tip 5 contains a numbered list of the steps, so if you find yourself lost at any point, or just want to see a recap, check that list.

?
Figure 9. Creating a Data Source

After you launch the designer, you’ll need to define a data source for the dashboard. From the Workspace Browser pane on the far left, right-click on Data Sources and select “New Data Source.” The PPS designer will prompt you for the type of Data Source; select “Analysis Services,” and then name the Data Source JewelDestiny. Finally, as shown in Figure 9, select the server, database, and cube.

Also note in Figure 9 that the JewelDestiny data source icon (in the Data Source Workspace Browser) has a small pencil symbol inside. The pencil symbol indicates that you’ve made a change to a local element of the workspace, and you need to “publish” it to the server. So right-click on the JewelDestiny item in the Workspace Browser and select “Publish.”

?
Figure 10. Creating a Dashboard and a Dashboard Page

Now that you’ve defined a Data Source, you can get down to the business of creating the scorecard for the six sales factors. You can actually create all five web pages for the entire dashboard and then fill them in as you go.

To create the dashboard, right-click on the dashboard item in the Workspace Browser and select the option to create a new dashboard. You’ll see a screen to create a new dashboard page (see Figure 10). This allows you to split each page into one or more zones. If you need to display more than one report on a page, you can select a template that best suits the page. (You can select a single zone for the entire page, and then add zones/columns later). In the case of the first page you’ll place the sales factor scorecard in the upper left, the filter selections for the scorecard in the upper right, and the top 10 store sales report on the bottom.

Next, Figure 11 shows how you can create multiple pages for the dashboard. You can specify a specific name for each page that will appear as a tag page when you go to deploy (or preview) the dashboard.

?
Figure 11. Pages in a Dashboard
?
Figure 12. Workspace Browser

Throughout the article, you’ll create KPIs (Key Performance Indicators), Scorecards, and Reports: Figure 12 shows how the PPS designer arranges these elements.

Finally, you can save your work as the workbench file (an XML file with a BSWX extension).

Tip 4: Creating the KPIs and the Scorecard
Finally, with all these “housekeeping” steps out of the way, you can create the scorecard. Scorecards are comprised of KPIs (Key Performance Indicators). If you’re not already familiar with the concept of KPIs, you can easily find definitions in blogs, web searches, and books. Suffice it to say that the six sales factors for JewelDestiny are KPIs: financial measurements to evaluate performance. The goal is to create a dashboard that displays how an employee or store or other geographic sales area is performing, according to the six sales factors.

To create each KPI, right-click on the KPI item in the Workspace Browser to add a new KPI. With each new KPI, you need to specify the actual value and the target value as the Sale Amount measure and the Sales Goal measure from the OLAP cube (see Figure 13). You can then select the threshold scoring indicator: the range of values to measure the actual data against the target data (see Figure 14). In the example for actual sales versus sales goal, anything at or above 100 percent is good (green light), anything below that but greater than 85 percent is a mild warning (yellow light), and anything below that is a red flag (red light).

?
Figure 13. Creating KPI, Part 1 (Defining the Actual/Target Values)
?
Figure 14. Creating KPI, Part 2 (Defining the Threshold Scoring Indicators)

After you create the first KPI, the remaining ones are easy: just create a KPI with a name, select the actual and target values and an indicator range. Note that in the case of the percentages, the target values are stored as a fixed value (e.g. 35 percent for Payment Protection Plans). Obviously you could also create a measure column in the OLAP database to handle this.

Finally, if you want to create labels that drill down (for instance, the Scorecard in Figure 1 shows Net Sales Objectives, Percentages, etc.), you can create a special kind of KPI called an Objective KPI. (You select the KPI type when you first create the KPI-either as an Objective KPI or a Blank KPI).

?
Figure 15. Scorecard with KPIs

After you finish creating the KPIs (and you also publish them), you can create the Scorecard that will display the KPIs by right-clicking on the Scorecard item in the Workspace Browser and selecting “New Scorecard.” You can give the new Scorecard a name and then drag the KPIs from the Details list on the far right (see Figure 15). After you publish the scorecard, you can go back to the dashboard and drag the scorecard to the appropriate dashboard page.

Tip 5: Linking the Scorecard with Filters
You’re almost there! Now you need to define filters for the scorecard so that the end user can view the scorecard by Sales Hierarchy or Employee, and by Date Hierarchy.

On the dashboard page, you can go to the third tab marked “Filter” and add a new filter. The PPS Designer gives you options to define a new filter by a custom MDX query, a simple Member Selection, or a Named Set. In this instance, you want to display the entire Geography hierarchy. Essentially, the code will amount to the following, which uses the MDX DESCENDANTS function:

   DESCENDANTS (    [Store Master].[Geography Hierarchy].[All],    [Store Master].[Geography Hierarchy].    [Store Master],    SELF_AND_BEFORE )

While you could just enter a custom MDX query, you may find that you’ll want to use the query elsewhere. In this case, it’s better to define the MDX query as a Named Set in the OLAP database, for reusability. Listings 1 and 2 show the MDX code for calculated named sets in the OLAP database. You’ll find the code from these listings in the calculations tab of the JewelDestiny OLAP cube (in the Analysis Services project, see Figure 8).

?
Figure 16. Creating Filters

Once you’ve defined these MDX Named Sets (which you can now use anywhere), you can select them when you create your filters (see Figure 16).

One more step! You may be asked, “OK, I created my scorecard?.I created my filter?.how does the scorecard recognize the filter?” Good question! The answer-filter links! You can either right-click on the scorecard that you dropped onto the page, or you can click the Create Links toolbar option at the top of the PPS designer to create filter links.

You create filter links in two steps on the two-tab dialog boxes in Figure 17 and Figure 18. First (on the Link Items tab), you select the filter that you wish to associate with the scorecard, so select the Geography Hierarchy filter first (you’re going to do this three times, for each of the three filters). Second (on the Link Options tab), you select the Endpoint Item Filters, and select Member UniqueName as the Source Value.

?
Figure 17. Creating Filter Links, Tab 1
?
Figure 18. Creating Filter Links, Tab 2

That’s it! OK, that was quite a bit of work. So before I go any further, let me stop and review what you’ve done so far:

  1. You created a Data Source to the Analysis Services OLAP database (see Figure 9).
  2. You created a dashboard and five dashboard pages, each with a particular zone layout (see Figure 10 and Figure 11).
  3. You created KPIs (that you’ll eventually display on the scorecard) where you defined an actual value (Net Sales Dollars), the target value (Sales Goal), and then you defined a threshold scoring pattern (see Figure 13 and Figure 14).
  4. You designed the scorecard by dragging and dropping the KPIs from the list of details onto the scorecard area (see Figure 15).
  5. You dragged the scorecard into the relevant dashboard page.
  6. You defined dashboard filters on Geography (from the OLAP database Named Set FullGeographySet in the first code example below), on Date in Figure 16 (from the Named Set FullSetOfDates in the second code example below), and Employees (just a basic Member Selection of the Employee dimension).
  7.    CREATE SET CURRENTCUBE.[FullGeographySet]   AS DESCENDANTS (           [Store Master].[Geography Hierarchy].[All],        [Store Master].[Geography Hierarchy].[Store Master],   SELF_AND_BEFORE ) ;             CREATE SET CURRENTCUBE.[FullSetOfDates]   AS DESCENDANTS (       [Date Calendar].        [Business Year - Quarter Desc - Month Desc - Full Date].        [All],    [Date Calendar].        [Business Year - Quarter Desc - Month Desc -  Full Date].        [Full Date],   SELF_AND_BEFORE ) ;          
  8. You created filter links between the filters and the elements (reports, scorecards, etc.) that you dropped into a dashboard page (See Figure 17 and Figure 18). Note that a single report/scorecard can have multiple filter links.
  9. You previewed the dashboard.

Tip 6: Building a Top 10 Report
Now that you’re an expert on the process, you can take a crack at the last remaining part of the first page (see Figure 1). You want a report showing top 10 stores by sales amount, for the date range filter only.

As you might have guessed, you need to write a little bit of MDX to handle the logic of top 10 stores. Once again, the Analysis Services project has two named sets called SalesRankSet and TOP10SalesStores. Note that they must appear in that order in the OLAP cube, as the latter references the former.

   // Named Set SalesRankSet   ORDER(   [Store Master].[Store Master].children ,      [Measures].[Sale Amount], BDESC )   // Named Set TOP10SalesStores   TOPCOUNT([SalesRankSet],10)

Now, you could stop here; however, the requirements call for you to actually show the ranking number for each store. You can do that easily by creating a calculated member (also in the list of available calculations for the OLAP cube) that used the MDX RANK function to assign a sequential number to each store within the SalesRankSet.

   // Calculated Member SalesRankNumber   RANK([Store Master].[Store Master].CurrentMember,          [SalesRankSet])
?
Figure 19. Creating the Top 10 Report

At this point, you just need to create a simple analytic grid in the PPS designer to utilize these measures and sets. Right-click on Reports in the Workspace Browser, select Analytic grid as the report type, and provide a name (“Top 10 Selling Stores across the Country”). In the design area (see Figure 19), select Top10SalesStores for the rows, SalesRankNumber and Sale Amount for the columns, and the Date Calendar Hierarchy as the Background (for any filter links).

Finally, publish the report, and then drag it into the first page of the dashboard (you’ll want to create a zone to the right of the scorecard). Then you can create a filter link between the Date filter and the report.

Tip 7: Creating a Pie Chart from Excel Services
One of the more humorous ironies of the modern software era is that despite all the capabilities in new tools, some users still prefer the familiarity of stalwart product names. The most ubiquitous tool is arguably Microsoft Excel, which many power users continue to rely on for many business tasks. Despite the designer capabilities in PPS, SSRS, and other tools, some business analysts still prefer to create chart definitions in Excel.

Fortunately, the news is good. Excel 2007 allows users to create charts and reports against an OLAP database and then publish them to Excel Services. From there you can create a report definition in PPS that is associated with the spreadsheet.

?
Figure 20. Creating a Pie Chart in Excel

Figure 20 shows how you can create a chart against an OLAP database in Excel.

  • Select the Data pull-down menu, select Other Sources, and then select Analysis Services.
  • Enter your server name (where the OLAP database resides) and any login credentials.
  • Select the JewelDestiny database and then click Finish.

At that point you can select from the Field List to design a chart the way you would normally create a PivotChart in Excel. For this chart, I’ve created a pie chart that shows sales by product Category by a date element.

After you design the chart, you can publish it to Excel Services by selecting Publish?Excel Services, and then navigating to the URL/site where you will deploy the spreadsheet. In Figure 20 I’m simply publishing to the Documents area of my local SharePoint installation. Also note that you’ll want to click on the Excel Services Options button, which allows you to publish just those parts of the spreadsheet that are relevant.

Tip 8: Reading the Pie Chart from Excel Services

?
Figure 21. Reading an Excel Chart into PerformancePoint Server

Now that you’ve created the Excel chart, you can easily access it from PPS. In Figure 21, I’ve created a new report with a template type of Excel Services?this prompts me for the default SharePoint site and Document library, and then will display all the available Excel spreadsheets in that library. Simple!

After you’re satisfied with the display of the spreadsheet, you can add this spreadsheet to the dashboard by selecting the dashboard from the workspace browser and dragging the published spreadsheet to page two of the dashboard.

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.

   CREATE MEMBER CURRENTCUBE.[MEASURES].SalesLY   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   FROM        (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.

devxblackblue

About Our Editorial Process

At DevX, we’re dedicated to tech entrepreneurship. Our team closely follows industry shifts, new products, AI breakthroughs, technology trends, and funding announcements. Articles undergo thorough editing to ensure accuracy and clarity, reflecting DevX’s style and supporting entrepreneurs in the tech sphere.

See our full editorial policy.

About Our Journalist