none
BAR Chart for rolling month average data issue RRS feed

  • Question

  • Hello Everyone,

    I got stuck while creating Bar Chart in Dashboard Designer 2007. The requirement is :

    1. We have two filter. The first filter is Geography hierachy filter tree that will allow user to select location like region,district,
       town etc. and second filter will allow user to select Month in "MMM/YYYY" format.

    2. Bar Chart need to show rolling month average data for above defined filter criteria for "Ind Total Sale" measure.

    If I use MDX formula to get avg "ind tot sale" month value, where I can use that calculated average value while creating a BAR Chart and
    how i'll pass user selected Month to this mdx.

    I really got frustated fighting with this issue. Any help on this regard will be highly appreciated.

    Best Regards
    Nipendra Garg

    Saturday, May 15, 2010 1:55 AM

Answers

  • Doesn't found like a data issue, but more or less a design issue?  If I was going to configure this layout these are the steps that I would take:

    1) Create the rolling avg calculated measure in the cube - http://sqlblog.com/blogs/mosha/archive/2007/09/04/moving-averages-in-mdx.aspx (you want the caluclation in the cube to reference so that the user will still be able to interact with the analytical chart, avoid custom MDX in the report)

    2) Create the Analytical Chart in DD - place measure(s) on series, month hiearchy on bottom axis, and geography in background (make sure that you have default member selected on geography background)

    3) With the month filter I would leverage the formula editor, so that you would not only use the month selected, but also dispaly the previous XX number of months so that they can look at a trend like Mosha shows in link above (but that is in Excel, similar layout though) - http://office.microsoft.com/en-us/performancepoint/HA102433971033.aspx#3

    Hope that helps.


    Dan English's BI Blog
    Saturday, May 15, 2010 12:40 PM