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;
