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
. 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
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,
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.
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.