Right-click in PerformancePoint Content and select New Filter….

PerformancePoint Services 2010 Configuration
• Installed as a Service Application with SharePoint 2010
No need for separate configuration with SP (needed in 2007)
• Go to Central Administration…Application Management…Manage Service
Applications…PerformancePoint Service Application
• Important configuration: Secure Store/Unattended Service Account
PPS: What we’ll create in the end
• Full list of PPS Dashboard pages
PPS: What we’ll create in the end
• The PPS Dashboard Designer
PPS: First KPI Scorecard
Page 1: Basic KPI scorecard
1 KPI, with filters on Geography and Date Hierarchy
KPI allows expansion for entire Product hierarchy
KPI shows goal/status for selected period, plus trend for 1 year ago
PPS: First KPI Scorecard
Filter Link Connection
Dashboard zone
web parts
We can also
filter on empty
There’s quite a bit going on “under the hood”
PPS provides “filter link connections” to drive the data content based on user filter
PPS also provides a dashboard zone interface on top of SharePoint web parts, to simply
web page authoring
PPS Page 1, Step 1: PPS: Create Site Collection
Go to http://sfmpbisql:38610 (Central Administration)
In Central Administration, go to “Create Site Collections”
Specify a Title, URL (using /sites/), and Template (we’ll use Enterprise….Business Intelligence Center)
Use the following notation for the site URL: class + initials (e.g. 2011I3KSG)
PPS Page 1, Step 2: Go to new site collection
Go to the new site collection (also Add the new site collection as an Internet Explorer Favorite)
In the new site collection, go to the PerformancePoint Content Library
Then click “Add New Item”. This will launch the PPS Dashboard designer (which is actually an inmemory Windows Application)
PPS Page 1, Step 3: Empty PPS Dashboard Designer
• PPS Dashboard designer, where PPS content is created
• In the dashboard designer, right-click on Data Connections to create a new
Data Source
PPS Page 1, Step 4: Creating a Data Source
Must create a data
source to an OLAP
You can create data
sources to other
physical databases,
but PPS is “first and
foremost” an OLAP
For now, we’re
funneling all data
access through the
Unattended Service
Account (which is
PPS Page 1, Step 5: the KPI we’ll bring in
EXISTING KPI in the OLAP Database for AdventureWorks
KPI uses measures [Gross Profit Margin] and [Gross Profit Margin LY]
Uses Goal based on Category
Contains Trend to compare current period to Last Period
PPS Page 1, Step 6: Create scorecard
1) RIGHT-CLICK and create a new scorecard
3) Select OLAP DB
4) Import KPIs from OLAP
2) Select Analysis Services
5) Select KPI
PPS Page 1, Step 7: customize scorecard
1) Scorecard with KPI, we want to drag product Categories hierarchy into the ROWS (drag to
the right of the Product Gross Profit Margin KPI)
2) Select All Products
3) Product Categories hierarchy now
appears in scorecard
PPS Page 1, Step 8: Customize scorecard
1) For the KPI goal, we want to change column heading, and while we want to show the
Goal/Target, we don’t want to show variance. So right-click on “Goal and Status” option, and
select “Metric Settings”, and make the following changes
PPS Page 1, Step 9: Customize scorecard
1) For the KPI trend, we don’t want to show any numbers…just simply the indicator, without any data value or
variance value. So right-click on “Trend” option, and select “Metric Settings”, and make the following changes
2) Right-click on row
selector for KPI row, go
to EDIT menu in upper
left, to change fill color
or font or other display
PPS Page 1, Step 10: Customize KPI display
1) Go to the KPI (in the Workspace browser), select the KPI value in the center of the screen, then click the Number
Format link to format the KPI value as a %. (Also do this for the Goal and Status, to show as a percent)
2) Go to the KPI Goal and Status, then select “Set Scoring Pattern and Indicator” and follow default prompts to change
the KPI indicator from a thermometer to more basic traffic/stop lights
PPS Page 1, Step 11: Customize KPI display
If you right-click on the KPI and save….and then go back to the Scorecard (in the workspace browser) and rightclick and save – this will “refresh” the scorecard
Note that the trend isn’t showing – remember that the MDX for the KPI trend uses the Profit Margin “last year”,
and we don’t (yet) have a base date on which to go back a year
PPS Page 1, Step 12: Dashboard page filters
1) Need to create filters so that users can select Sales
Territories and Time Periods
2) Want user to be able to select from entire Sales
territory Hierarchy
3) Only want user to be able to select a Fiscal Year,
Semester, Quarter, or Month….don’t want users
selecting individual days
4) Right-click in PerformancePoint Content and select
New Filter….and create filters using MDX code
PPS Page 1, Step 13: Dashboard page
1) Need to create a
dashboard and the first
dashboard page
2) Right-click in
Content and create a
new Dashboard
3) Select a Template (2
rows for this one)
4) Initially the zones are
even vertically. To make
the top zone (for the
filter) use as little
vertical space as
possible, right-click in
the top zone, select
Zone Settings
5) In the Zone Settings
dialog, go to the 2nd tab
(size) and set the Height
to 7%
PPS Page 1, Step 14: Dashboard page links
1) Need to drag scorecard and 2
filters into the appropriate
dashboard sections (zones)
2) Then must create filter links
(filter connection) between
the 2 filters and the scorecard
3) In both instances, connect to
the Scorecard Page, using
Member UniqueName as the
Source Value
PPS Page 2: Analytic Grid and Chart
Filters on Product Hierarchy, Geography, and all Years except first and current
Analytic chart shows monthly sales as % of the product’s parent, both for selected year and prior year
Analytic grid shows all other products in the same category (product siblings), their % of parent, and % of parent for all related grandparent
Note that in July, Sales for Road Bikes went up….but the % of Parent went down, because Touring Bikes were introduced in July
Helpful to spot trends in product category share, product cannibalization
PPS Page 2: Analytic Grid and Chart
Filter zone at the top. Note
that we’ll use logic for each
of the 3 filters. The Product
Categories and Geography to
City dropdowns will only
include data where Sales
exist. The Year dropdown
will exclude the first year of
data and the last year of
data. So custom MDX will be
used for the filters.
Analytic Chart with dual-Y
axis to show sales by month,
and the sales as a % of the
Product’s Parent. Show will
demonstrate PPS capability
to automatically generate
dual Y-axis when it detects a
measure with a whole dollar
amount (sales) and a
measure that’s a % (Pct of
Siblings report to show
breakdown of product
siblings, as well as parent
sales. The report will require
some custom MDX.
PPS Page 2, Step 1: Analytic Grid and Chart
Since you might have closed
the dashboard designer, we
need to reload it
Go back to your site
collection, then go to the
PerformancePoint Content
library, and select the open
dropdown for the dashboard.
In the shortcut menu, select
“Edit in Dashboard Designer”
PPS Page 2, Step 2: Create the Analytic Chart
In the Dashboard
designer / Workspace
browser, right-click
on PerformancePoint
content and select
New…Report (even
though we’re
creating a chart)
Select Analytic Chart
from the Report
Template selection
Then select the data
source (the
OLAP database)
PPS Page 3, Step 2: Create the Analytic Chart
Chart area allows us to drag/drop
measures and dimensions
Select the “Internet Sales”
Measure group in the upper right,
to only show measures and
dimensions related to that Fact
Drag the measures and
dimensions into the
corresponding areas, as show in
this screen shot
(Note that Product Categories
Hierarchy does not appear in the
Reason for Background: to
“prepare” the chart for the fact
that it will be filtered on Calendar
Year, and some element from the
Customer Geography hierarchy
and Product Categories Hierarchy
Note that chart automatically
shows a dual Y-axis
You can go to the Edit Menu in
the upper left to set chart
background color and default font
Note that % of Parent calculations
are showing as 100% - because
there is no context (yet) of a
Product Selection. That will
change at runtime
PPS Page 3, Step 3: Create the Analytic Grid
In the Dashboard
designer / Workspace
browser, right-click on
content and select
Select Analytic Grid
from the Report
Template selection
Then select the data
source (the
AdventureWorks OLAP
PPS Page 3, Step 3: Create the Analytic Grid
Product Siblings report requires
custom MDX
Standard designer doesn’t
support ability to use SIBLINGS
Must go to Query tab, and enter
Note use of ASCENDANTS to grab
the user’s product selection, and
show all hierarchical parents
Note that we must now declare
the “background” parameters
Use <<Parameter>> notatation,
and then drag in the
hierarchy/attribute that will
eventually be used in the filter
PPS designer will automatically
insert parameters at the bottom,
when you use the << >> notation
in the MDX code
PPS Page 3, Step 4: Create the 3 filters
Dashboard Page requires 3 filters,
all using MDX code
Right-click in PerformancePoint
Content and create 3 filters (one at
a time), all using MDX
The first should be called
ProductCategoriesFilter, and the
display method will be a Tree.
Note that the MDX only shows
items that have sales
The second should be called
CustomerToCityFilter, and the
display method will be a Tree.
Note that the MDX only shows
items that have sales
The third should be called
CalendarYearsInTheMiddle, and
the display method will be a List.
Note that the MDX only shows
Years “in the middle” (excludes the
first year and last year)
PPS Page 3, Step 5: Create the Dashboard page
Go to the main
dashboard and add a
new page
Select the template for 3
In the top zone, drag in
the 3 filters we just
In the next zone down
(the first of the two
larger zones), drag in the
Product Pct of Parent
In the bottom zone, drag
in the Product Sibings
Then establish the filter
connections (links), see
next slide
PPS Page 3, Step 6: Set Filter Connections
Set a filter connection
between the Calendar
Years filter and the 2
Date Calendar Year
endpoints in the Chart
and Report,
Set a filter connection
between the Product
Categories filter and the
2 Product Categories
endpoints in the Chart
and Report
Set a filter connection
between the Customer
to City Filter and the 2
Customer Geography
Endpoints in the Chart
and Report
PPS Page 4: Analytic Chart w/user options
Page 3: Employee (Salesman) Monthly Gross Profit Chart
Filters on all Years except first and current , also on Salesman
Allows user to select from multiple levels in an employee org chart
End user can drill down to other related business dimensions (see next slide)
PPS Page 4: Analytic Chart w/user options
Page 3: Employee (Salesman) Monthly Gross Profit Chart (continued)
End user has several drill down capabilities
Can right-click on a plotted point for a salesman and drill down to other dimensions (e.g. Product Category)
Can also click on the legend on a supervisor and re-generate the chart for all child employees
PPS Page 4 : Analytic Chart w/user options
Note that employee filter will
select all employees from
Employee Org Chart Hierarchy
that have reseller sales
Also, we want the user to be able
to select multiple employees:
One issue: if we want to use this
same filter on other dashboards,
but only allow the user to pick a
single employee, WE MUST BUILD
A NEW FILTER! We can’t use the
existing one and modify the
selection rule!
Drag entire Employee hierarchy into the series – allows us
to plot managers and employees
Also must prepare chart to be filtered on Calendar Year
Why place the Margin % into the Background? To keep the
legend from repeating it as a series
PPS Page 5: Analytic Chart w/decomposition tree
Page 4: Pie Chart Decomposition Chart
Analytic Chart showing Reseller sales by Year, Product Category and Country
Right-click option on any pie-slice for a decomposition graph that breaks down the selected
year-Country-Product Category (next slide)
PPS Page 5: Analytic Chart w/decomposition
Page 4: Pie Chart Decomposition Chart (continued)
Decomposition chart allows visual drilldown to any related dimension, based on the context of the
original pie slice from the previous slide (e.g. Bikes for Canada in 2008)
We don’t need to do anything to make the decomposition chart available – it’s a new feature built into
the PPS runtime engine. However, it’s not available when running 64-bit versions of Internet Explorer.
PPS Page 6: Create KPI Manually
Manual KPI created in PPS, Actual sales
as a % of Target Goal
Page 5: Manual KPI
We can create dashboard scorecards with KPIs from SSAS, or we can create KPIs manually in PPS
Also note that scorecard shows the people who report to Amy (children in the Employee Hierarchy) in
the columns axis, and dates underneath CY 2007 in the rows axis
The Manual KPI will define a ratio between the reseller sales (the “actual value”) and the reseller sales
quota (the “Target value”)
PPS Page 6, Step 1: Create KPI Manually
Right-click in the Dashboard
designer on PerformancePoint
Content and create a new KPI, as a
Blank KPI
PPS Page 6, Step 2: Define manual KPI
For the manual KPI, must first
define the Actual number
that we are evaluating
(Reseller Sales, by Employee)
In the Actual row, select Data
Mappings, and Change the
Source to use the ADW OLAP
DB, and then select the
Measure ( [Reseller Sales
Also set the Number Format
to use Currency
The next step will be to define
the target (the Reseller Sales
Quota). The KPI will state the
Actual value as a ratio/% of
the Target.
PPS Page 6, Step 2: Define Manual KPI
Next, must define the Target
value, against which the
Actual Value will be expressed
(as a ratio/%)
In the Target row, select Data
Mappings, and Change the
Source to use the ADW OLAP
DB, and then select the
Measure ( [Sales Amount
Also set the Number Format
to use Currency
PPS Page 6, Step 3: Set Status rule for KPI
Normally, a KPI from an OLAP cube would contain MDX code to define the display
Because we’re building the KPI ourselves, we need to define ranges for Good, Bad, 40
PPS Page 6, Step 4: Create Scorecard
Next, must create
the new scorecard
This will be similar
to creating the first
KPI scorecard,
except that we’ll
select the manual
KPI that we
created in PPS, as
opposed to
selecting a KPI
from an SSAS OLAP
PPS Page 6, Step 4: Create scorecard
Take the option to Select the
Then select the Manual KPI at
the bottom, and select NEXT,
NEXT, and FINISH in the next 3
That will eventually create the
KPI scorecard…we can then
click the EDIT menu dropdown
and set the fill color for the
main KPI row to a different
Note that at runtime, the
scorecard will show the
hierarchical children of the
employee selection along the
COLUMNS axis of the scorecard,
and the hierarchical children of
the date selection along the
ROWS axis
However, we don’t drag those
dimensions into the scorecard =
we’ll address it dynamically
with MDX code
PPS Page 6, Step 5: Create Filters
In a moment we’ll create 2
The filters will prompt for
the Employee Hierarchy,
and the Calendar Hierarchy,
but only down to the
Quarter Level.
Remember that the KPI
spans 2 fact tables (Reseller
sales and Sales Targets).
We can only use dimensions
(and dimension attribute
levels) that are common to
both dimensions:
In this case, Calendar
Quarter (and higher) and
the Employee hierarchy
PPS Page 6, Step 5: Dashboard page filters
1) Need to create filters so that users can select
Employees Hierarchy and Calendar Hierarchy (just
down to quarter)
2) Right-click in PerformancePoint Content and select
New Filter….and create filters using MDX code
PPS Page 6, Step 5: Dashboard page
Create a new dashboard page, with 2 rows…and set the height of the top row to 7%
From the Details on the right, Drag the 2 new filters (Employee Selections and Calendar
to Quarter) to the top
From the Details on the right, Drag the new scorecard to the bottom
PPS Page 6, Step 5: Set filter connections
Create a filter connection link between the Employee Filter and the scorecard
Scope the target of the filter link to the scorecard columns
Enter the MDX snippet, <<UniqueName>>, <<UniqueName>>.Children in the Connection
PPS Page 6, Step 5: Set filter connections
Create a filter connection link between the Calendar Quarter and the scorecard
Scope the target of the filter link to the scorecard rows
Enter the MDX snippet, <<UniqueName>>, <<UniqueName>>.Children in the Connection
PPS Page 7: KPI Hotlink scorecard
Dashboard zone
web parts
Page 7: KPI scorecard hotlink – user can click on a KPI and get a supporting analytic chart on the right, based on KPI and date selected
Filter on Date Hierarchy
Screenshot shows Top 10 cities by Reseller sales for Q4 2007 (user clicked the Channel Revenue KPI and the date of Q4 FY 2007.
Screenshot shows Top 10 cities by Internet sales for Q4 2007 (user clicked the Internet Revenue KPI and the date of Q4 FY 2007.
Notice how the 2 charts have a background color that matches the KPI
We will create the charts first, and then the KPI scorecard, and then “link them together”)
PPS Page 7, Step 1: Named Sets for 2 Charts
What we’ll use for the 2 charts – 2 TOPCOUNT Named Sets to grab the top 10 cities
based on Internet Sales and also on Reseller Sales
PPS Page 7, Step 2: Create the Reseller Top 10 Cities Chart
Drag [Reseller Gross
Profit Margin] and
[Reseller Sales
Amount] into the
Series area (the two
measures we want to
Drag the named set
[Top 10 Cities
Reseller Data] into
the bottom axis
Drag the
hierarchy into the
background (to
prepare the chart for
a filter from the Date
Fiscal Hierarchy)
Chart will
automatically plot
Profit Margin (%) on
the secondary Y axis,
as a line chart
Note the background
color for the chart
area is light tan – to
match the
background of the
KPI for Reseller Sales
PPS Page 7, Step 3: Create the Internet Top 10 Cities Chart
Drag [Internet Gross
Profit Margin] and
[Internet Sales
Amount] into the
Series area (the two
measures we want to
Drag the named set
[Top 10 Cities
Internet Data] into
the bottom axis
Drag the
hierarchy into the
background (to
prepare the chart for
a filter from the Date
Fiscal Hierarchy)
Chart will
automatically plot
Profit Margin (%) on
the secondary Y axis,
as a line chart
Note the background
color for the chart
area is light blue– to
match the
background of the
KPI for Internet Sales
PPS Page 7, Step 4: Create the KPI scorecard
First, create a new KPI (right-click in
the PerformancePoint Content area
and create a new KPI)
Select Objective KPI as the template
type, and then call it “Financial
Then right-click in
PerformancePoint Content and add
a new Scorecard, against the
Analysis Services ADW OLAP DB
Select the 2 KPIs for Internet
Revenue and Channel Revenue
(Both KPIs compare internet
customer sales and reseller sales to
a sales goal, and both have a trend
After the scorecard is created, go to
both KPIs in the Workspace browser
(which will be under “Financial
Perspective-Grow Revenue”) and
format and set the indicators as
seen on the left
PPS Page 7, Step 5: Customize the scorecard
For the new KPI scorecard….
Set the background color for the 2 KPIs
to the same background colors used for
the 2 charts (for Internet and Reseller
Set the metric settings for the Sales Goal
column heading and display
Set the metric settings for the Trend
column heading (no data values, just the
PPS Page 7, Step 5: Customize the scorecard
Drag the Financial
Perspective KPI (the
objective KPI) into the
Scorecard, and above
the first KPI (note the
small blue arrow
pointing upwards)
That will place the financial perspective above the
Internet Revenue. To indent the 2 KPIs
underneath the objective KPI, select both KPIs
(using the row selector) and then in the EDIT menu
at the top, click the “increase indent” button. That
should generate this result (you can then bold the
Financial Perspective )
PPS Page 7, Step 6: Build the dashboard page
In the dashboard
designer, add a new
dashboard page
Use the Header
with 2 columns
In order to use the
right column as an
area where 1 of
multiple reports
can be displayed
(based on the user
clicking the KPI), we
must make the
zone stacked
Right-click in the
right column, go to
zone settings, and
set the Orientation
to Stacked
PPS Page 7, Step 7: Drop the content into the dashboard zones
Drag the Fiscal YHQ filter into the top zone area, the KPI hotlink scorecard into the left column, and the 2 Top 10 charts
(reports) into the right column.
Note that the two charts show vertically (one on top of the other) at design time; however, at runtime, only one will
show, based on the KPI selected by the user
PPS Page 7, Step 8: Set the Filter Connections for the Scorecard
Create a filter connection between the Fiscal YHQ filter, and the scorecard
The Filter target (“Connect To”) should be the Rows of the scorecard (since we want to take what the user
selected and then show the selection + the hierarchical children)
Use the Connection formula <<UniqueName>> , <<UniqueName>>.Children
PPS Page 7, Step 9: Set the Filter Connection between
the scorecard and the 2 charts
Because we want
the two Top 10
Charts to utilize the
user date selection
in the scorecard
rows (and not
merely the date
filter at the top)….
We want to drag
the scorecard Row
Member Unique
Name into each of
the two top 10
Make sure that in
both cases, the
“Connect To”
Target is Date.Fiscal
(from when we
“prepared” both
charts that they’d
be filtered on some
aspect of the Fiscal
PPS Page 7, Step 10: Set the Display condition
between the scorecard and the 2 charts
In order to show
the corresponding
chart, based on
the KPI selected
on the left…
Must drag in the
Display Condition
from the KPI
scorecard to each
of the 2 charts
(one at a time)
In the Display
Condition Dialog,
select the KPI
(Channel Revenue
or Internet
Revenue) that’s
associated with
the chart
Reporting Services 2008R2 - Configuration
• Step 2: In the
• Need to
provision a
Report Server
database for
Reporting Services 2008R2 - Configuration
• Step 2: In the
• SSRS Web Service
URL (used by
SharePoint 2010)
contains the web
server, the default
application name
Reporting Services 2008R2 - Configuration
• Step 2: In the SSRS
Optional: can specify a
scale-out deployment
We can have a 2nd
instance of Reporting
Services (either on a
2nd server or a 2nd
named instance on the
same server) that uses
the same ReportServer
database as the
primary instance
Reporting Services 2008R2 - Configuration
Step 3: in
SharePoint Central
Administration, go
to General
Application Settings
and then into
Services....this got
created from the
prerequisite install
Specify the Web
Service URL (from
the SSRS
configuration), plus
credentials from an
Administrator Group
Reporting Services 2008R2 - Configuration
• Step 3 (continued):
• Go to “Add a
Report Server to
the Integration”
• Specify your
server name,
database instance,
and Admin or
other service
account for the
server – must be a
Domain Account
• SharePoint 2010
and SSRS 2008 are
now talking to
each other!
Reporting Services 2008R2 - Configuration
Step 4: In SharePoint 2010,
if we want to generate
report output to HTML
pages on a schedule and
allow users to view them in
the browser….
Must set this option in Web
Application General settings
(Strict is the default, must
change to Permissive)
Reporting Services 2008R2 - Configuration
In the site location, select the Site Actions dropdown and create 2 new Document
SSRSDeployedReports will hold deployed SSRS Reports
SSRSGeneratedReports will hold report output generated via Report Subscriptions
SSRS 2008R2 – What we’ll create
• SSRS2008R2SharePoint Project
• 1 Shared Data Source to
AdventureWorks OLAP (that points to
SSAS Server sfmpbisql)
• 3 reports that show newest SSRS
• Will deploy to SharePoint Document
Library and view them in SharePoint
• Will set up subscription for automatic
delivery to another SharePoint
Document Library
• Will deploy individual report pieces as
Report parts for reuse
• Will incorporate reports into PPS
SSRS 2008R2 – Project properties
SSRS Project properties: Necessary deployment settings
TargetDataSetFolder: http://sfmpbisql/sites/KSGFedTraining/Datasets (optional)
TargetDataSourceFolder: http://sfmpbisql/sites/<<SiteCollection>>/Data Sources
TargetReportFolder: http://sfmpbisql/sites/<<SiteCollection>>/SSRSDeployedReports/SSRS2008R2Examples
TargetReportPartFolder: http://sfmpbisql/sites/<<SiteCollection>>/Report Parts (optional)
TargetServerURL: http://sfmpbisql/sites/<<SiteCollection>>
Deploy reports and view in SharePoint SSRSDeployedReports library
Report 1: SSRS Performance Gauge
Grouping is by
Sales and quota are for the
four quarters of the year
Sparkline plots the Actual Sales
and Quota for the 4 quarters of
the year
Report that shows groups, sparklines, performance gauges, and interactive sorting
Bullet Graph Performance Gauge to
plot the % of Quota as a “progress
bar” style KPI. It’s made up of 3
ranges (light green/yellow/red), a
major pointer (based on % of quota)
and a second pointer to pay bonuses
at 85%)
Report 1: SSRS Performance Gauge
• This exists as a 4-part video:
SSRS Performance Gauge – Step 1: Create DataSet
Create DataSet
against ADW
OLAP database
Drag Reseller
Sales Amount
and Sales
Amount Quota
as measures
Drag in
Hierarchy and
Quarter of Year
based on
Calendar Year
and make sure
to check as
parameter is
created, turn
off parameter
SSRS Performance Gauge – Step 2: Create Empty
Table/Tablix and set DataSet
• Drag
table and
set table
to define
SSRS Performance Gauge – Step 3: Create Employee Group
• Add row
group at the
bottom, as a
parent row
• Define row
group based
on Employee
and add a
group header
SSRS Performance Gauge – Step 4: Set summary columns
and hide detail row
In the row group
header, click in
upper right corner
to add
(SUM) for Reseller
Sales amount and
Sales Amount
SSRS implements
a SUM since we’re
placing in group
header (it sums
the quarterly
dataset rows by
Click in Row
selector to hide
the detail section
(report shows 1
line per
employee, in the
SSRS Performance Gauge – Step 5: Drag in columns and
• Format both
sales cells as
Currency, with
• To eliminate
showing cents,
set # decimal
places to zero
SSRS Performance Gauge – Step 5: Drag in columns and
For the column
for sales as % of
Use Expression
above for % of
Quota – note that
both numerator
and denominator
use SUM
Format % of
Quota as a
Percent with 2
Note: make SURE
to name the
SSRS Performance Gauge – Step 6 : Preview
• Preview
to show
we have
so far
SSRS Performance Gauge– Step 7: Add Sparkline
• Now to add
sparkline to
show sales
and quota as
• Drag in a
sparkline into
the table, and
use a Line
Chart with
SSRS Performance Gauge – Step 8: Set data to plot in
sparkline chart
• For the sparkline:
• Click inside to get
the Chart data
window pane to
• Use Reseller sales
amount and Sales
• Preview…note
that since original
dataset had rows
by quarter within
employee, both
measures are
broken out by
quarter (4
SSRS Performance Gauge – Step 9: Format sparkline chart
To set line
colors, click in
the chart data
window pane
on the value
Right-click to
go to series
Go to the Fill
Color section
To increase
white space
between the 2
plotted lines,
increase the
height of the
group header
row by
stretching the
row selector
SSRS Performance Gauge – Step 10: Add area for new
performance gauge
• To add the performance
gauge, right-click to add a
new column to the right
• Temporarily increase
height of group header
row (by stretching row
SSRS Performance Gauge – Step 11: Performance Gauge
Chart Type
• Drag in a gauge
from the
toolbox to the
new column on
the right
• Select a Linear
Bullet Graph
• By default, it
shows like this…
SSRS Performance Gauge – Step 12: Set 3 Ranges for Bullet
• First, we’ll set the range properties
• Note there are 3 different ranges (3
different shades of grey)
• Select the right-most range, right-click to
go to Range Properties
• Set Range Scale property to 90 and 100
• Set Fill color to Light Green
• Repeat this for the middle range (use
range scale values of 80 and 90, and set Fill
color to Khaki)
• Repeat this for the left-most range (use
range scale values of 0 to 80, and go to
additional colors and select color of
• We’ve selected 3 light versions of Red,
yellow, and Green….the actual horizontal
bar indicator will use sharper versions of
those colors, to create a nice contrast
SSRS Performance Gauge – Step 13: Set secondary pointer
• The gauge has 2
pointers: a primary
pointer (for the
performance gauge
based on % of quota),
and a 2ndary gauge
(we’ll pay bonuses at
85% of quota)
• Set pointer property
value for 2nd pointer to
a hard-coded value of
SSRS Performance Gauge – Step 14: Set Primary Pointer
• For the
pointer, go
to pointer
and set the
value to the
multiplied by
• Sample
SSRS Performance Gauge – Step 15: Set color rule for
Primary Pointer
• Set color rule
for primary
• Similar to a
90% and above
is good (green),
between 80%
and 90% is
yellow, anything
SSRS Performance Gauge – Step 16: Preview
• Preview
• Still
need to
have a
rule for
SSRS Performance Gauge – Step 17: Set color rule for
Secondary Pointer
• Set color rule for 2ndary
• Go to pointer
properties, and set
pointer fill color
expression to show in
black if % of quota is
over 85% (eligible for
bonus), or red if lower
than 85% (not eligible)
• Can’t use green….must
use color that won’t
blend in
SSRS Performance Gauge – Step 18: Turn off annotations
and labels on axis
• To strip out axis labels for the
performance gauge, right-click
on bottom axis and uncheck
“show labels” and “show tick
• Preview
SSRS Performance Gauge – Step 19: Set Chart background
• To set
d color for
on gauge
as a whole,
and set
back fill
color to
gradient fill
as shown
SSRS Performance Gauge – Step 20: Decrease row height
and adjust
• Now
height of
• Adjust start
width and
end width
for each of
the 3 ranges
(same value
for each)
SSRS Performance Gauge – Step 21: Adjust height/width of
primary Pointer
• For the
pointer, go
to pointer
and set
width to 25
SSRS Performance Gauge – Step 22: Adjust height/width of
secondary Pointer
• Go to secondary
pointer properties,
and set width to
7and Length to 35
(your actual
selections may vary)
• These are being
done to size
accordingly now
that the group row
header has been
SSRS Performance Gauge – Step 23: Preview
• Preview
• Notice how Amy and Brian are below 85%, and are
not eligible for bonuses (bonus pointer is red)
• David Campbell is above 85%, so bonus pointer is
black (eligible for bonus)
SSRS Performance Gauge – Step 24: Set color heading
back/fore colors
• Now to set
column header
• Select header
row selector
and set
SSRS Performance Gauge – Step 25: Preview
• What design mode should look like
right now
SSRS Performance Gauge – Step 26: Set Interactive Sorting
for Sales, Quota, and % of Quota
Implement interactive
sorting on 3 column
headings (reseller
sales, quota, and % of
One at a time, click on
column headings, and
go to interactive
In all 3 cases, when
enabling interacting
sorting, make sure to
sort on the Employees
Three expressions for
the 3 measures are to
the right
Note that we can’t use
the ReportItems
collection – must
reuse the numerator
and denominator for
the value of the
calculation on which
to sort
SSRS Performance Gauge – Step 27: Preview
• Final preview!
SSRS Performance Gauge – Step 27:
Access from PPS
Report URL must be site collection + Report
Repository Folder + Project Folder + Report
(with RDL extension)
If you’re using PPS to create SharePoint dashboards,
you can devote specific dashboard pages to SSRS
Can seamlessly integrate deployed SSRS reports, and
take advantage of hierarchical PPS dropdown filters
We can place them in specific PPS page zones –
arguably works cleaner than web parts
Must specify the report server URL, and the location
of the report
(No help with discovery – have to provide the URLs
Sometimes must specify SSRS virtual directory
Note: must uncheck “show toolbar” and “show
We can place this in a dashboard, and use existing
filter for CalendarYearsInTheMiddle to “link” to
the existing Report Parameter
• Full Report URL was
Report 2: Map and Chart
• Report that shows mapping, SSRS charting
SSRS Sales Map and Chart
• This exists as a 2-part video:
• http://www.commongroundsolutions.net/SSRSMap1_of_2.zip
• http://www.commongroundsolutions.net/SSRSMap2_of_2.zip
Report 3: basic report
• A very simple report
of monthly sales for
a Category
• We’ll use this to set
up a report
subscription, to run
automatically for
every category
SSRS 2008R2 – Automatic Delivery
• Can run report schedules - send output to user Document Libraries
• Uses SQL Server Agent – make sure Agent is running
• Create report shared schedule
– Site Actions/Site Settings/Reporting Services-Manage Shared Schedules
– Add a new schedule (actually writes a job entry in SQL Server Agent)
• Modify report data source to store credentials securely on the
server (for unattended execution)
• Create a new subscription for the report
Go to Report/Manage Subscriptions/Add subscription
Deliver to SharePoint Document Library (Generated reports)
Set output format
Associate with the report schedule
Assign any parameters (can’t used Linked Reports)
SSRS 2008R2 – Automatic Delivery
• Instead of creating subscriptions manually, we can populate a
relational control table with entries we’d otherwise provide
• SharePoint interface will prompt us for necessary fields
• Once again, the source of data (as well as the data source for the
relational control table that contains the subscription information)
must have credentials securely on the server (for unattended
• This can be a time saver – if you have a large # of recipients
• Also dynamic – will pick up changes when we insert new rows to the
relational control table
SSRS 2008R2 – Automatic Delivery
T-SQL code to create tblSubscriptionFileShare, to store data-driven subscription information
Reporting Services 2008R2 – Configuration continued
• Some important links:
• Configuring Report Server Integration:
– http://msdn.microsoft.com/en-us/library/bb326213.aspx
• Install and Configure SharePoint Integration on multiple servers:
– http://msdn.microsoft.com/en-us/library/bb677365.aspx
• Configure SSRS on an NLB (Network Load Balance Cluster):
– http://msdn.microsoft.com/en-us/library/cc281307.aspx
Excel Services 2010 - Configuration
• Excel Services is installed as a Service Application with
SharePoint 2010 (similar to PerformancePoint Services being
installed as a Service Application)
• Go to Central Administration…Application
Management…Manage Service Applications…Excel Services
• Important configuration feature: Unattended Service Account
Application ID (in Global Settings), Maximum Workbook Size
(Trusted File Locations)
Excel Services 2010 - Configuration
• Must create a
secure store
application ID
Excel Services 2010 - Configuration
• Secure Store
Application ID
will be used as
SignOn ID in
published to
Excel Services 2010 - Configuration
• In Excel,
creating a
to an OLAP
must use the
• Deployment
to SharePoint
will not
without this!
Excel Services 2010 – Scatter Chart for cities in
a country (What we’ll create)
Y-axis: revenue by
Pearson correlation: measures the correlation or
strength of linear dependence.
Observation points in a scatter graph:
each marker represents a city, their #
of customers (bottom axis) and
Internet sales (left axis). Chart shows
relationship between the 2 variables
.85 to 1 = strong correlation
.75 to .85 = moderate correlation
< .75 = weak correlation (not very reliable)
We can create this spreadsheet
in Excel against OLAP data,
deploy to SharePoint, and then
use in a PPS dashboard, and
take advantage of PPS filters
Regression trendline: shows slope calculation (every 1
customer results in $1508.7 + $)4640.9 in revenue)
R-squared represents “goodness of fit” of plotted
points relative to trendline (closer the line passes
through the points, closer to 1 is the value)
Bottom axis: Customer Count by City
Excel Services 2010 – Scatter chart for cities in
a state
Stronger correlation, better
“fit” when running for a
state, versus a country
Excel Services-Step 1: Set up Sharepoint site
In the site collection, Create a new Document Library called ExcelDocuments
In the library, go to Connect Office and Add the Document Library to the list of
SharePoint Sites in Office
(This will make it easier to save Excel Spreadsheets to this specific library)
Excel Services-Step 2: Access OLAP data in
Go onto Excel, Make a data
connection to Analysis Services
Select the sfmpbisql server
Select the adventureworks
2008R2 database, and the
ADW BI SharePoint Perspective
Excel Services-Step 3: Define Secure Store
Service ID in the Connection
When saving the
connection, make
sure to go to
settings and set the
Otherwise, users
won’t be able to
interact with it when
Deployed to
Excel Services-Step 4: Create Pivot Table
Create a default Pivot Table
(Ultimately we’ll create a
pivot chart that will read from
the Pivot Table range of cells)
Excel Services-Step 5: Define Pivot table from
Using the Pivot Table
field list, click on the
Customer Count
measure and the
Internet Sales
Amount Measures
Drag the Customer
Geography hierarchy
into the Report Filter
Drag the City
Location into the
Row Labels
CRITICAL: make sure
to change the
worksheet name at
the bottom to
Excel Services-Step 6: Try to create Pivot
Chart from Pivot table
Try to create
a Pivot chart
against the
Pivot table
data, using a
Scatter Chart
Note the
error: we
can’t –
limitation of
What can we
Excel Services-Step 7:Must build range for
Pivot Chart manually, from cell offsets
Workaround: we can create 2 range names in Excel
that use the Excel function OFFSET, to read the
range of cells down to row 288
288 is the max number of rows for cities
Go to Design, then name manager, and add 2 new
Name the formulas CustomerCount and
Excel Services-Step 8: Create Pivot Chart
manually, “outside” Pivot Table
The Name manager
should now look like
what you see on the
Now, click “outside”
the pivot table
(column E, for
instance), and try to
insert a Pivot chart
as a scatter chart,
where we’re not
selecting the Pivot
table as the source
(In a minute, we’ll
tell the pivot chart to
use the formula
Excel Services-Step 9: Create Ranges
Right-click on the empty chart and select
“Select Data”
Click Add (under Legend Entries)
Enter the 2 formula names for X and Y axis
– =CustomerCountSales!CustomerCount
– =CustomerCountSales!SalesAmount
Excel Services-Step 10: Scatter Chart from
Excel Pivot Chart based on all cities
Excel Services-Step 11: re-run for Oregon
(easier to test)
Let’s filter this
down to a simpler
Oregon…pivot chart
reads the named
formulas to
generate scatter
graph of cities, their
customer counts,
and revenue, for
One problem…Pivot
table named
formula range
shows Grand
total…we DON’T
want to plot the
grand total in the
chart. Just cities!!!
Excel Services-Step 12: Turn off totals in Pivot
Go into Pivot Table
options (right-click on
Pivot table)
In Totals and filters
tab, uncheck the 2
options for showing
Excel Services-Step 13: Pivot Chart without
Now the
pivot chart
plots the
cities, not
the total
skews the
Excel Services-Step 14: Format and add
Some formatting
Right click and
change the inner
chart plot area
background fill color
Change the outer
background fill color
Change Y axis to
avoid showing
Right-click on
plotted markers to
Add a trendline
Excel Services-Step 15: Add trendlines
Select option for Linear
regression (default)
Set options to show the slope
equation, along with the Rsquared value for “goodness of
fit” of linear regression with
respect to the actual
Excel Services-Step 16: Result of trendlines
Result of pivot
You can move the
textbox showing
slope and R-squared
calculation to the
lower right corner
of chart
Excel Services-Step 17: Pearson formula
Some analysts might
want to use the
Pearson formula, to
determine how
densely packed the
observations are (how
much correlation
there is between the
counter count and the
Use Pearson formula
in a cell outside the
We’ll give those cells a
name, so that we can
access them from PPS
Excel Services-Step 18: Name the formulas
Click on the label and
shift click to the
formula itself, then
right-click and define
that cell range with a
name of
Also, click in cell B1
(where we have the
dropdown for the
geography filter) and
go to the name
manager and give the
filter the name of
These names will be
critical when we
publish this sheet to
Excel Services-Step 19: Rename the chart
Also…name the chart. Click on the chart, then click on the Layout menu option at the
top, and in the upper right corner, change the chart name to ScatterChart.
Excel Services-Step 20: Save to SharePoint
Click on File, then Save and Send,
and then Save to SharePoint
Notice in the lower right corner
that the site collection library
(from when we created the
SharePoint Document library )
shows as the location to save
Double click on that folder
Excel Services-Step 21: Publish options
That will take us to the site
Before saving, must go to
publish options
Only save the following items in
the workbook (ScatterChart,
GeoFilter, and PearsonFormula)
Then go to the 2nd publish
options tab (Parameters)
Excel Services-Step 22: Publish parameters
In the parameters tab
of the publish options,
click ADD and check
the GeoFilter
parameter….this must
be published to
SharePoint for PPS to
recognize it
Back in the SAVE AS
screen, uncheck the
option to Open with
Excel in the Browser.
Just click the option to
Excel Services-Step 23: Access in PPS
Now we want to access the chart from inside a PPS
In PPS dashboard designer for the site collection,
create a new report as an Excel services report
Specify the site collection name (core site collection
name): PPS will prompt for the document library,
workbook and item name
VERY IMPORTANT…must do this twice…(2 Excel
reports). One for the ScatterChart, one for the
Excel Services-Step 24: Build PPS Filter
Create a new dashboard
filter: Customer
Use filter and descendants
to only go from Country to
State-Province, and only
where Internet Sales Amount
is > 0
Use Tree display
Excel Services-Step 25: Add Dashboard page
In the
create a new
page as 2 rows
Go to zone
settings in the
top row and set
the height to
The filter and
the Pearson
formula will go
into the top
The chart will
go in the
bottom zone
Excel Services-Step 26: Manage zones
In the top
zone, after
setting the
height to 7%,
split the zone
in half
Drag the new
filter and the
Formula into
the top zone
Drag the chart
into the
bottom zone
Excel Services-Step 27: Connect filters
Must link the filter (
Customer Country /
State) to BOTH the
Excel Scatter Chart
AND the Excel
Pearson Formula
Both filter links will
connect to the same
filter parameter
(GeoFilter), using
Member Unique
Save and publish!!!
Note that if you make
a change to the
spreadsheet back in
Excel, all you need to
do is resave and
republish in Excel

similar documents