SSRS-PPS-Excel Services Project SQL Server 2008

1. SSRS-PPS-Excel Services ProjectSQL Server 2008 SetFocus Business Intelligence Master’s Program Jeff Jacob, MBA P: (312) 772-6142 E1: Jeffrey.K.Jacob@gmail.com E2: Jeffrey.Jacob@SetFocus.com

2. Project Objectives • Develop reporting solutions for AllWorks, Inc. based on the OLAP database, according to specifications, employing SSRS, PerformancePoint Server Dashboard Designer and Excel Services • Create user-configurable Scorecards, Report Tables and Charts using PerformancePoint Server Dashboard Designer • Create user-configurable Report Charts employing SSRS • Create user-configurable Report Pivot Tables and Charts in MS Excel 2007 and incorporate these into PerformancePoint Server Dashboards • Deploy these reporting solutions to desired SharePoint collection site folders for distribution

3. Developing a Scorecard for KPI reporting First, the Analysis Services Scorecard template is chosen. The first scorecard: Scorecards Data Source: AllWorks_Overhead (cube perspective) KPI source: Imported KPI selected: KPI Overhead Trend (see OLAP project for details) KPI Display Name Override: Overhead Trend Goal and Status Properties: * Show no value * Score rollup type = none * Name override = Status KPI Properties: * Value number format = %; Decimal places = 2 * Scoring pattern and indicator – default / stoplight Display requirement – display Overhead Trend KPI data broken out for the OverheadType attribute hierarchy members including the summary ‘all’ Publish All – confirm output in Scorecard editor

4. Developing a second Scorecard for KPI reporting • First, the Analysis Services Scorecard template is chosen. • The second scorecard: JKJ_Client Job Financials • Data Source: AllWorks_Summary (cube perspective) • KPI sources: Imported • KPIs selected: • Objective (Summary) KPI: Client Financials • KPI Open Receivables as Pct of Invoice • KPI Profit Pct • Objective (Summary) KPI: Construction Job Financials • KPI Overhead as Pct of Total Cost • Goal and Status Properties: • * Show no value • * Score rollup type = none • * Name override = Status • KPI Properties: • * Value number format = %; Decimal places = 2 (1 for Profit Pct) • * Scoring pattern and indicator – default / stoplight • Display requirement – show Client Financials KPIs broken out by Client Name; show Construction Job Financials KPI broken out by Project Name • Publish All – confirm output in Scorecard editor

5. Using the Scorecards for reporting in a dashboard • Lastly, the Dashboard Page, Scorecards, is set up to display the two foregoing scorecards, the first of which is to be configurable by Date Quarters • It will consist of three zones (the filter, the first scorecard, the second scorecard) • The Overhead Quarter Filter is set up employing the AllWorks_Overhead perspective’s Named Set: [OverheadQuarters] • This filter is then linked only to the ScoreCards scorecard for end-user run-time configuration

6. Using the Scorecards for reporting in a dashboard The SharePoint deployed ScoreCards dashboard:

7. Creating a Reporting (Analytic) Column Chart Material purchase costs by date quarters A new PerformancePoint Report, Material Purchases is added and defined using a custom MDX query: * On Columns statement = bottom (x-) axis of chart * On Rows statement = vertical (y-) axis of chart (material types as measured by cost) * WHERE statement filters members; here, user-configurable Client Name parameter is added In the dashboard page, Materials, a Client Name filter is set up, using the [Client Plus All] named set and linked to this report/Client Parameter; per specification, it is designed to be used for one attribute hierarchy member at a time (All or individual members)

8. Using the PPS Materials report in a dashboard The SharePoint deployed Materials dashboard page:

9. Creating a Reporting (Analytic) Line Chart Overhead costs by date quarters A new PerformancePoint Report, Overhead Cost is added and defined using a custom MDX query: * On Columns statement = bottom (x-) axis of chart * On Rows statement = vertical (y-) axis (user-configurable Overhead types in terms of the cost measure * WHERE statement here, sets the context - the cost measure In the dashboard page, Materials, a Client Name filter is set up, using the [All Overhead Types] named set and linked to this report/OHTypeFilter Parameter; per specification, it is designed to be used for one or several attribute hierarchy members (only individual members)

10. Using the PPS Overhead report in a dashboard The SharePoint deployed Overhead dashboard page:

11. Creating Reporting (Analytic) Grids/Tables Labor costs by project, configurable by date quarters Labor costs by worker, configurable by date quarters A new PerformancePoint Report, Top 10 Labor Jobs is added and defined using the report designer and then customized: * On Columns statement = column(s) of grid/table – here, Labor Cost * On Rows statement = row(s) of grid/table – here, Top 10 projects according to the measure, Labor Cost * WHERE statement filters the user-configurable date quarters A new PerformancePoint Report, Top 5 Workers is added and defined using the report designer and then customized: * On Columns statement = column(s) of grid/table – here, Labor Cost * On Rows statement = row(s) of grid/table – here, Top 5 workers according to the measure, Labor Cost * WHERE statement filters the user-configurable date quarters

12. Using the PPS Labor report in a dashboard In the dashboard page, Labor, a Quarter filter is set up, using the [Labor Quarters] named set and linked to these reports/Quarter Filter Parameter; per specification, it is designed to be used for one attribute hierarchy member at a time (excluding the ‘All’ member)

13. Using the PPS Labor reports in a dashboard The SharePoint deployed Labor dashboard page:

14. Creating a Reporting (Analytic) Column-Line Chart Employee labor costs and percent of total by employee (name) A new PerformancePoint Report, Pct of Quarterly Labor Dollars is added and defined using a custom MDX query: * A calculated member [Total AllEmps Labor] is defined to provide the total labor cost for all projects in context (a filtered set) for all employees who worked on them * A calculated member [Total Labor] is defined to rename the Labor Cost measure according to desired specifications * A calculated member [% Total] is defined to provide the percentage of total labor cost that an employee incurred (projects the employee worked) * On Columns statement = bottom (x-) axis of chart, here – date quarters * On Rows statement = vertical (y-) axis of chart, here – the selected employee’s labor cost as measured in labor cost dollars * WHERE statement filters members; here, a user-configurable Employee parameter is added

15. Creating a Reporting (Analytic) Grid Employee labor analysis by project, configured by employee A new PerformancePoint Report, % of Labor Dollars by Project is added and defined using a custom MDX query: * A calculated member [Labor All Employees] is defined to provide the total labor cost for all employees in contextof the project * A calculated member [Tot Labor] is defined to rename the Labor Cost measure according to desired specifications * A calculated member [% Total] is defined to provide the percentage of total labor cost that an employee incurred (projects the employee worked) * On Columns statement = grid/table column headers, here – the employee labor analysis measures (based on the foregoing calculated members) * On Rows statement = grid/table row headers, here – the filtered set of projects - those, on which the selected employee worked * WHERE statement filters members; here, a user-configurable Employee parameter is added

16. Using the PPS Employee Analysis report in a dashboard In the dashboard page, Employee Labor Analysis, an Employee filter is set up, using a custom MDX tuple set and linked to these reports/Employee Filter Parameter; per specification, it is designed to be used for one attribute hierarchy member at a time (excluding the ‘All’ member)

17. Using the PPS Employee Labor Analysis reports in a dashboard The SharePoint deployed Employee Labor Analysis dashboard page:

18. Developing a Reporting Services (Analytic) Grid/Table Overhead costs, broken out by type, with respect to configurable quarter, the previous quarter and the percent change Report Name: Overhead Category Data Source: AllWorksOLAP cube (Overhead perspective) Tablix Object: Table Column Headers: Description (Overhead Types), Prev Qtr, Current Qtr, Pct Change One user-configurable parameter: Date Quarter The primary report data set: Modification of report code to permit customization of MDX code for Report Data data sets: Using custom MDX to default the Date Quarter parameter to the last one with activity:

19. Designing and deploying the SSRS Overhead Category report The SSRS design for Overhead Category report: The SharePoint deployed Overhead Category report:

20. Subscribing to the SSRS Overhead Category report The MOSS 2007 subscription to the SSRS-designed Overhead Category report: * Credentials for the data source to this report were added to make the subscription possible

21. Developing a second Reporting Services (Analytic) Grid/Table Project labor cost and hours, detailed by weekend and project, configurable by: employee name, “from” weekend date and “to” weekend date Report Name: Employee Jobs in Date Range Data Source: AllWorksOLAP cube (Labor perspective) Tablix Object: Table Column Headers: ID, Full Name, Week End Date, Project Name, Hours Worked, Labor Cost Three user-configurable parameters: Full Name, From Weekend Date, To Weekend Date (cascading parameters; Name selection determines available weekend dates The primary report data set design: Using custom MDX to restrain available weekend dates according to the selected employee:

22. Designing and deploying the second SSRS Employee Jobs in Date Range report The SSRS design for the Employee Jobs in Date Range report: The SharePoint deployed Employee Jobs in Date Range report:

23. Creating a PivotTable for Excel Services Overhead cost by Type and Quarters (configured for a desired year) Connected to the AllWorksOLAP Overhead perspective, the following measures and dimensions were chosen; quarters displayed, year configurable – to create the below Pivot Table To avoid duplicate display, the top two rows of the above spreadsheet were hidden; cell B1 was named and surfaced as a parameter in Excel Services Options; this pivot table as a single workbook item object was selected for SharePoint site publishing

24. Adding the Pivot Table from Excel Services to a dashboard The .ODC file, the connection string back to the Overhead perspective of the AllWorksOLAP cube, was also deployed to the SharePoint collection site – data sources library; custom MDX used to define the Year filter (one Year member at a time) * For each Excel-based report, the Excel-designed product was first uploaded to the SharePoint collection site, then downloaded into PPS where it became a useable report object to be integrated into the respective dashboard (shown above) page

25. Using the Basic Overhead By Quarter/Year pivot table report in a dashboard The SharePoint deployed Basic Overhead By Dates dashboard page:

26. Creating a Pivot Chart for Excel Services Labor cost by Quarters, broken out by Employee Type Connected to the AllWorksOLAP Labor perspective, the following measures and dimensions were chosen; quarters displayed, year and client name configurable – to create the below pivot chart To avoid duplicate display, the top two rows of the above spreadsheet were covered over by the pivot chart; cells B1 and C1 were named and surfaced as parameters in Excel Services Options; this pivot chart as a single workbook item object was selected for SharePoint site publishing

27. Adding the Pivot Chart from Excel Services to a dashboard The .ODC file, the connection string back to the Labor perspective of the AllWorksOLAP cube, was also deployed to the SharePoint collection site – data sources library; custom MDX used to define the Year and Client Name filters (for each, multiple selections enabled)

28. Using the Quarterly Labor Breakdown pivot chart report in a dashboard The SharePoint deployed Labor History Chart dashboard page:

29. Creating a Pivot Chart for Excel Services Profit Dollars and Percent by Quarters (configured for one or several counties) Connected to the AllWorksOLAP Summary perspective, the following measures and dimensions were chosen; quarters (from DimProject role-playing dimension) displayed, county name configurable – to create the below pivot chart To avoid duplicate display, the top row of the above spreadsheet was covered over by the pivot chart; cell B1 was named and surfaced as a parameter in Excel Services Options; this pivot chart as a single workbook item object was selected for SharePoint site publishing

30. Adding the Pivot Table from Excel Services to a dashboard The .ODC file, the connection string back to the Summary perspective of the AllWorksOLAP cube, was also deployed to the SharePoint collection site – data sources library; custom MDX used to define the County Name filter (multiple selections enabled)

31. Using the Quarterly Job Profit By County pivot chart report in a dashboard The SharePoint deployed Job Profitability Chart dashboard page: