Many readers of this blog use JPivot. The solidly average web based Pivot Viewer that I’ve heard described as a “relic” of the cold war – no frills utility software. However, as maligned as JPivot is, it does have some great features and has been production quality software for years now. One of these hidden little features that is in JPivot (and also in Pentaho) is the quick and easy way to add trend lines to a JPivot screen by simply using MDX.
Consider, for instance, this little bit of MDX:
with member [Measures].[Variance Percent] as ‘([Measures].[Variance] / [Measures].[Budget])’, format_string = IIf(((([Measures].[Variance] / [Measures].[Budget]) * 100.0) > 2.0), “|#.00%|arrow=’up’“, IIf(((([Measures].[Variance] / [Measures].[Budget]) * 100.0) < 0.0), “|#.00%|arrow=’down’“, “#.00%”))
select NON EMPTY {[Measures].[Actual], [Measures].[Budget], [Measures].[Variance], [Measures].[Variance Percent]} ON COLUMNS,
NON EMPTY Hierarchize(Union({[Positions].[All Positions]}, [Positions].[All Positions].Children)) ON ROWS
from [Quadrant Analysis]
which produces this lovely set of arrows letting the user know how their individual variance value rates in terms of KPI thresholds.
The secret of course, is the arrow= tag in the format string. Easy enough. “up” is a green up arrow. “down” is a little red arrow. “none” is no arrow.
Happy Visual Cue Indicator day to you all.
Nicholas, we’re begining a new project with the tried and true JPivot client. You consider it a relic, and indeed is not a rich UI, however I’ve not found a better open source or inexpensive client for mondrian. Do you have any suggestions?
Hi Nick… great post…
My luck has it that the arrows are not showing up, but rather a “square” box appears beside my figures. I am using Pentaho BI Platform 1.7.0.1062 and Mozilla. Same result using WIE 6.0..
Any ideas ? Kind reagrds, DMurray3
@DMurray3
Hi :
Right click on that “square” box and try to see the properties. It should tell you about the URL from where the image (up/down arrow) is coming from.
Just ensure that you have that image file at that location.
Hi Nick.. and Gopal…
Again many thanks to Nick for the blog and Gopal for your interest.
I found the problem causing the arrows not be correctly displayed. One must use straight-single and double-quotes instead of the “slanted” ones appearing in Nick’s copy of the MDX. Changing these throughout the MDX solved the problem.
Happy New Year to all… DMurray3
Hi,
When i tried to export this reports as an Excel or PDF.
Variance Percent shows empty.
please help.
Thanks,
Manoj