{"id":280,"date":"2008-11-11T16:08:48","date_gmt":"2008-11-11T23:08:48","guid":{"rendered":"http:\/\/www.nicholasgoodman.com\/bt\/blog\/2008\/11\/11\/hidden-little-trend-arrows-2\/"},"modified":"2008-11-11T16:08:48","modified_gmt":"2008-11-11T23:08:48","slug":"hidden-little-trend-arrows-2","status":"publish","type":"post","link":"http:\/\/www.nicholasgoodman.com\/bt\/blog\/2008\/11\/11\/hidden-little-trend-arrows-2\/","title":{"rendered":"Hidden little trend arrows"},"content":{"rendered":"

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.<\/p>\n

Consider, for instance, this little bit of MDX:<\/p>\n

with member [Measures].[Variance Percent] as ‘([Measures].[Variance] \/ [Measures].[Budget])’, format_string = IIf(((([Measures].[Variance] \/ [Measures].[Budget]) * 100.0) > 2.0), “|#.00%|arrow=’up’<\/strong>“, IIf(((([Measures].[Variance] \/ [Measures].[Budget]) * 100.0) < 0.0), “|#.00%|arrow=’down’<\/strong>“, “#.00%”))
\nselect NON EMPTY {[Measures].[Actual], [Measures].[Budget], [Measures].[Variance], [Measures].[Variance Percent]} ON COLUMNS,
\nNON EMPTY Hierarchize(Union({[Positions].[All Positions]}, [Positions].[All Positions].Children)) ON ROWS
\nfrom [Quadrant Analysis]<\/p><\/blockquote>\n

which produces this lovely set of arrows letting the user know how their individual variance value rates in terms of KPI thresholds.<\/p>\n

\"200811111457\"<\/a><\/p>\n

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.<\/p>\n

Happy Visual Cue Indicator day to you all.<\/p>\n","protected":false},"excerpt":{"rendered":"

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 […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":[],"categories":[9,11],"tags":[],"_links":{"self":[{"href":"http:\/\/www.nicholasgoodman.com\/bt\/blog\/wp-json\/wp\/v2\/posts\/280"}],"collection":[{"href":"http:\/\/www.nicholasgoodman.com\/bt\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.nicholasgoodman.com\/bt\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.nicholasgoodman.com\/bt\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"http:\/\/www.nicholasgoodman.com\/bt\/blog\/wp-json\/wp\/v2\/comments?post=280"}],"version-history":[{"count":0,"href":"http:\/\/www.nicholasgoodman.com\/bt\/blog\/wp-json\/wp\/v2\/posts\/280\/revisions"}],"wp:attachment":[{"href":"http:\/\/www.nicholasgoodman.com\/bt\/blog\/wp-json\/wp\/v2\/media?parent=280"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.nicholasgoodman.com\/bt\/blog\/wp-json\/wp\/v2\/categories?post=280"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.nicholasgoodman.com\/bt\/blog\/wp-json\/wp\/v2\/tags?post=280"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}