310 likes | 494 Views
SSRS-PPS-Excel Services Project SQL 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. Project Objectives.
E N D
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
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
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
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
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
Using the Scorecards for reporting in a dashboard The SharePoint deployed ScoreCards dashboard:
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)
Using the PPS Materials report in a dashboard The SharePoint deployed Materials dashboard page:
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)
Using the PPS Overhead report in a dashboard The SharePoint deployed Overhead dashboard page:
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
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)
Using the PPS Labor reports in a dashboard The SharePoint deployed Labor dashboard page:
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
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
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)
Using the PPS Employee Labor Analysis reports in a dashboard The SharePoint deployed Employee Labor Analysis dashboard page:
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:
Designing and deploying the SSRS Overhead Category report The SSRS design for Overhead Category report: The SharePoint deployed Overhead Category report:
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
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:
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:
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
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
Using the Basic Overhead By Quarter/Year pivot table report in a dashboard The SharePoint deployed Basic Overhead By Dates dashboard page:
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
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)
Using the Quarterly Labor Breakdown pivot chart report in a dashboard The SharePoint deployed Labor History Chart dashboard page:
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
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)
Using the Quarterly Job Profit By County pivot chart report in a dashboard The SharePoint deployed Job Profitability Chart dashboard page: