Performance Point Services Excel Like Stacked Bar Chart

My favorite thing about excel is that you can do just about anything in it, like create a stacked bar chart representing the growth of a measure over time and represent each time period increment as a distinct segment in the each bar like the chart below.

My least favorite thing about excel is being are asked to replicate ‘simple’ excel charts using a PerformancePoint Analytical Chart. Easy right; just create a Quarter-To-Date Calculated Measure in your Cube (Contoso Cube used this example) throw it and your quarters into a chart and you’re done. Well the chart displays the data correctly, measure grows over time but the colors aren’t as nice and the chart does not stack by the value attributed to each quarter

Ok so what happens if we move the QTD measure to the bottom axis and quarters to the series, again the data is correct but this time although the chart identifies each segment attributed to each quarter the chart is no longer displays columns for each quarter.

So what we need is the second chart but with some new calculated measures on the bottom axis.

Each of the measures on the bottom axis represent a time period we are interested in and only returns the value for that period and prior periods. Let’s have a look at the MDX that I used to accomplish the visualization in this chart.

/*Q1 Measure 
Use iif to check if the currentmember of the fiscal quarter attribute = “Q1”, 
True – build string to represent the quarter member, use sttomember function to convert to member, complete --tuple with measure
False - null*/

CREATE MEMBER CURRENTCUBE.[measures].[Q1]
as
iif(LEFT([fiscal quarter].currentmember.membervalue, 2) = "Q1",
(strtomember("[fiscal quarter].&["+
RIGHT([Fiscal Year].currentmember.name, 4)+
RIGHT([Fiscal Quarter].currentmember.name, 1) + "]"),
[Measures].[Inventory On Hand Quantity]), null),
FORMAT_STRING = "Standard", 
VISIBLE = 1; 

/*Q2 Measure 
Use iif to check if the number of the period represented by the currentmember of the fiscal quarter attribute <=2, 
True – Check to see if empty, set to null, else build string to represent the quarter member, use strtomember function to convert to member
complete tuple with measure
False - null*/

CREATE MEMBER CURRENTCUBE.[measures].[Q2] as
iif( cint(right(left([fiscal quarter].currentmember.membervalue,2),1)) <= 2,
iif( cint(right(left([fiscal quarter].currentmember.membervalue,2),1)) <= 2 and
isEMPTY((strtomember("[fiscal quarter].&["+
RIGHT([Fiscal Year].currentmember.name, 4)+
RIGHT([Fiscal Quarter].currentmember.name, 1) + "]"),
[Measures].[Inventory On Hand Quantity])),
null,
(strtomember("[fiscal quarter].&["+
RIGHT([Fiscal Year].currentmember.name, 4)+
RIGHT([Fiscal Quarter].currentmember.name, 1) + "]"),
[Measures].[Inventory On Hand Quantity])),null),/*)*/
FORMAT_STRING = "Standard", 
VISIBLE = 1; 

/*Q3 and Q4 are the same as Q2 just increment the <= number*/

CREATE MEMBER CURRENTCUBE.[measures].[Q3] as
iif( cint(right(left([fiscal quarter].currentmember.membervalue,2),1)) <= 3,
iif( cint(right(left([fiscal quarter].currentmember.membervalue,2),1)) <= 3 and
isEMPTY((strtomember("[fiscal quarter].&["+
RIGHT([Fiscal Year].currentmember.name, 4)+
RIGHT([Fiscal Quarter].currentmember.name, 1) + "]"),
[Measures].[Inventory On Hand Quantity])),
null,
(strtomember("[fiscal quarter].&["+
RIGHT([Fiscal Year].currentmember.name, 4)+
RIGHT([Fiscal Quarter].currentmember.name, 1) + "]"),
[Measures].[Inventory On Hand Quantity])),null),/*)*/
FORMAT_STRING = "Standard", 
VISIBLE = 1; 

CREATE MEMBER CURRENTCUBE.[measures].[Q4] as
iif( cint(right(left([fiscal quarter].currentmember.membervalue,2),1)) <= 4,
iif( cint(right(left([fiscal quarter].currentmember.membervalue,2),1)) <= 4 and
isEMPTY((strtomember("[fiscal quarter].&["+
RIGHT([Fiscal Year].currentmember.name, 4)+
RIGHT([Fiscal Quarter].currentmember.name, 1) + "]"),
[Measures].[Inventory On Hand Quantity])),
null,
(strtomember("[fiscal quarter].&["+
RIGHT([Fiscal Year].currentmember.name, 4)+
RIGHT([Fiscal Quarter].currentmember.name, 1) + "]"),
[Measures].[Inventory On Hand Quantity])),null),/*)*/
FORMAT_STRING = "Standard", 
VISIBLE = 1; 

/*Use scope statements to set the value for the all member of the quarter attribute when combined with the calculated members to null*/

SCOPE(([Fiscal Quarter].[All],{[Measures].[Q1]})); 
THIS =  null; 
END SCOPE; 

SCOPE(([Fiscal Quarter].[All],{[Measures].[Q2]})); 
THIS =  null; 
END SCOPE; 

SCOPE(([Fiscal Quarter].[All],{[Measures].[Q3]})); 
THIS =  null; 
END SCOPE; 

SCOPE(([Fiscal Quarter].[All],{[Measures].[Q4]})); 
THIS =  null; 
END SCOPE; 
Advertisements

PPS Cascading Filters Save the Day

The Scenario:

Time Intelligence filter based on a typical date dimension filtering scorecard and Analytical reports, needed to add an Excel Services Report with a Parameter based on the Year attribute of the time dimension. I tried connecting the TI filter to year parameter available in the Excel Services report with a connection formula like: EXISTS([Date].[Years].members, <<UniqueName>>,”MeasureGroup”) expecting it to work, problem was that excel did not like it. Cascading filters to the rescue.

The Solution:

Created a Member Selection filter from the descendants of the Years Attributes, connected the TI filter to the Member Selection filter with the above connection formula, passed the member selection filter to the report, excel is happy again

The Gotchas:

– Cascading Filters filter Nulls, so if you need to return members that are in fact null you need to create a plug measures and set that as your filter measure

– Member Selection filters not based on a Function like Children or Descendants that you pass a connection formula into get created with a ‘With Set’ Expression and not the expected ‘strtoset’ expression and do not work very well (my experience anyway)

Will post a how to shortly.

Cascading Filters PerformancePoint 2010 SP1

Well I have installed SP 1 and on my local SharePoint and tried out the PPS cascading filters, and I think that the PPS team has hit a home run on this one. You now have the ability to separate hierarchical data into separate filters while maintaining the integrity of your hierarchy and you can filter the values of non-related dimensions members using the NONEMPTY function and a ‘Connection Formula’ (MDX generated with SP1, for connection formulas looks way better)

What I did

  • Fired up Dashboard Designer
  • Created a connection to a simple cube that I have
  • Created a Member Selection Filter for a dimension called department

  • Created a Member Selection Filter for a Dimension called location

  • Created a new Dashboard and added the two filters to the Dashboard
  • Added a connection link from the Department Filter to the Location Filter

  • You will need to define a Connection formula like the following
    • NONEMPTY((DESCENDANTS(locations,,leaves), <<UniqueName>>))
  • The results
    • As you change the Department filter the Location filter is cascaded to only reflect locations that have data associated to the selected Department, Awesome


OData, SharePoint Lists and PerformancePoint Server

A colleague of mine Alan Whitehouse blogged about SharePoint Lists as data sources for PerformancePoint Services, you can find his post here.

PerformancePoint Services, SharePoint Lists and Business Connectivity Services.

After reading his blog I was inspired to:

  • Create a WCF web service that consumes the public Netflix oData feed
  • Create a SharePoint External Content Type in SharePoint Designer
  • Create an External Content List in SharePoint
  • Use that list as a source for PerformancePoint Items

Below is the Result, a very simple visualization displaying the Movie, Short Synopsis and Netflix Url

Performance Point SSRS Reports

The other day a client needed a utilize the same SSRS Report in 4 places on the same Performance Point Dashboard, pretty simple right, yup create 4 PPS SSRS reports with the correct default parameters and drop them on a dashboard. Well it turns out that when the reports refresh themselves the default parameters do not get reused and the parameter used for reports gets carried forward from the report that was previously refresh, Arg!

So the solution was to create a SharePoint page with four report viewer web parts. One thing to note is that every time you edit a reportviewer web part the tool bar options all get re checked, even if you have painstaking unchecked them previously.

Modifying the Appearance of a Dashboard created in PerformancePoint Services Dashboard Designer

I had a client the other day ask me how to modify the appearance of the Navigation links that PPS will create for you if you have checked off the ‘Include Page list for Navigation’ property in your dashboard.

The Answer is to modify the following element in the PPSDashboard css file:

.ppsma-pageOverviewList
{
margin-left: 5px;
padding-top: 5px !important;
margin-bottom: 0px;
list-style-type: none;
font-family: Tahoma;
font-size: 8.25pt;
width: auto;
}

you will find the PPSDashboard.css file in the following location:

C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\TEMPLATE\LAYOUTS\PPSWebParts

checkout the following posts for additional Styling information:

PerformancePoint Services Scorecard Table Design

Alignment of PerformancePoint Services Filters