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; 

Generating multiple rows from a single record with start and end columns

Working on a project where the reporting requirement is to take a single record containing the overall total, the start and end period and generate multiple rows including all periods between the start and end. I knew that I could get the results that I needed with a common table expression (CTE) but time was of the essence so I turned to my trusty friend the ‘Cursor’. I know Cursors are for the weak and the lazy, but i needed to prototype something really fast and for me this was the easiest way. For those of you wondering why is he blogging about being weak and lazy, I did return to the CTE method and was rewarded with the required results using a fraction of the SQL and produced a stored procedure which is easier to modify and tweak as reporting requirements change.

Below you will find some sample code used to generate multiple rows from a single datarow containing a start and an end column. In this example I show both using integers and dates to denote the beginning and end and end values.

/*using integers as starting and ending values*/
create table #cteTempInt
(
id int identity (1,1)
,amount numeric(18,2)
, Divisor int
, starting int
, ending int
)

insert into #cteTempInt Values(1200, 7, 1, 6)
insert into #cteTempInt Values(4300, 10, 2, 11)
insert into #cteTempInt Values(2400, 4, 3, 6)

print 'results from integer sample'
;with t(id, periodAmount, period, ending) AS
(
select id, amount/divisor as periodAmount, starting as period, ending from #cteTempInt
union all
select id, periodAmount, period +1 as period , ending from t
where period+1 <= ending
)

select id, periodamount, period,
row_number() over(partition by id order by id,period) as paymentNumber from t order by id, period

DROP TABLE #cteTempInt

/*using dates as starting and ending values*/

create table #cteTempDate
(
id int identity (1,1)
,amount numeric(18,2)
, Divisor int
, starting date
, ending date
)

insert into #cteTempDate Values(1200, 7, '2012-01-01','2012-06-01')
insert into #cteTempDate Values(4300, 10,'2012-02-01','2012-11-01')
insert into #cteTempDate Values(2400, 4, '2012-05-01','2012-08-01')

print 'results from date sample'
;with t(id, periodAmount, period, ending) AS
(
select id, amount/divisor as periodAmount, starting as period, ending from #cteTempDate
union all
select id, periodAmount, dateadd(mm, 1, period) as period , ending from t
where dateadd(mm,1,period) <= ending
)
select id, periodamount, period,
row_number() over(partition by id order by id,period) as paymentNumber from t order by id, period

drop table #cteTempDate

results from integer sample
id          periodamount                            period      paymentNumber
----------- --------------------------------------- ----------- --------------------
1           171.4285714285714                       1           1
1           171.4285714285714                       2           2
1           171.4285714285714                       3           3
1           171.4285714285714                       4           4
1           171.4285714285714                       5           5
1           171.4285714285714                       6           6
2           430.0000000000000                       2           1
2           430.0000000000000                       3           2
2           430.0000000000000                       4           3
2           430.0000000000000                       5           4
2           430.0000000000000                       6           5
2           430.0000000000000                       7           6
2           430.0000000000000                       8           7
2           430.0000000000000                       9           8
2           430.0000000000000                       10          9
2           430.0000000000000                       11          10
3           600.0000000000000                       3           1
3           600.0000000000000                       4           2
3           600.0000000000000                       5           3
3           600.0000000000000                       6           4

results from date sample
id          periodamount                            period     paymentNumber
----------- --------------------------------------- ---------- --------------------
1           171.4285714285714                       2012-01-01 1
1           171.4285714285714                       2012-02-01 2
1           171.4285714285714                       2012-03-01 3
1           171.4285714285714                       2012-04-01 4
1           171.4285714285714                       2012-05-01 5
1           171.4285714285714                       2012-06-01 6
2           430.0000000000000                       2012-02-01 1
2           430.0000000000000                       2012-03-01 2
2           430.0000000000000                       2012-04-01 3
2           430.0000000000000                       2012-05-01 4
2           430.0000000000000                       2012-06-01 5
2           430.0000000000000                       2012-07-01 6
2           430.0000000000000                       2012-08-01 7
2           430.0000000000000                       2012-09-01 8
2           430.0000000000000                       2012-10-01 9
2           430.0000000000000                       2012-11-01 10
3           600.0000000000000                       2012-05-01 1
3           600.0000000000000                       2012-06-01 2
3           600.0000000000000                       2012-07-01 3
3           600.0000000000000                       2012-08-01 4

(20 row(s) affected)

Squarifed TreeMaps Update

Just updated the code section of my previous post, fixed the formatting and the copy – paste into html editor mistakes.