none
Trend chart from Scorecard RRS feed

  • Question

  •  

    Hi,

     

    I've some questions regarding the setting up of a trend chart which maps to a created scorecard.

     

    I want my scorecard to show a number of KPI's and objectives. For each KPI I'd like to view a trend chart of how the KPI has done historically, say for the last 4 quarters.

     

    My current scorecard consists of just the 1 target and actual for each KPI which can be chaged by the user using a time intelligence filter. The problem I have is that when i try to base a trend analysis chart on this scorecard it is telling me I need members in the columns of my scorecard. Now I'm assuming that this means I need e.g. something like a number of quarters from my time dimension across the top of the scorecard.

     

    Two questions

     

    1. If I need to add members from my time dimension to the Scorecard, can these be dynamic so that they are always the lats 4 quarters depending on the date that the scorecard is viewed? I should then be able to build the trend chart based on this scorecard.

     

    2. Is there any way to build the trend chart without having to show a number of Quarters in the Scorecard? Ideally I only want to show the one actual and target value for the KPI's filtered by date.

     

    All help greatfully recieved.

     

    Thanks in advance,

     

    G

    Friday, July 18, 2008 6:57 AM

Answers

  • Hi G,

     

    By the sounds of what you want the trend analysis report view is probably not not the best fit because you're not trying to make predictions. The trend analysis report type uses SSAS data mining algorithms to predict the next n points along a time series.

     

    Instead use a simple Analytic chart report to display the trend over time. In order to dynamically apply a set of members (in your case the last 4 quarters based on a filter selection) all you need to do is set up a filter link formulas on the scorecard columns end point as well as the time dimension endpoint of the analytic chart (probably set up on the X axis. I just posted a response to your scorecard question here which covers the formula needed for a dynamic last 4 quarter set, hope that helps. 

     

    Cheers,

    Nick

    Friday, July 18, 2008 9:56 AM
  • Aah, so the the chart will not be connected to filters at all, this way the source cube will need to "know" the current date. What you'll need is some MDX in either the source cube or the query that generates the chart data. Have a look at this post by Mosha Pasumansky, it will assist in creating structures within your cube that automatically determine the current day member in your time dimension. Using that ""as a starting point you create a set of 4 quarters. Remember, though, using hand-made MDX in your Analytic Views reduces your user's interactivity

     

    Another idea: You can achieve this functionality by creating another TI filter with just one item: STPS formula for the Current quarter. That way the chart gets its current quarter automatically and you retain interactivity because the analytic chart does not need any hand-made MDX. The drawback is that this extra filter appearing on the dashoard with only one item may confuse users....

     

    HTH,

    Nick

    Tuesday, July 22, 2008 10:03 AM

All replies

  • Hi G,

     

    By the sounds of what you want the trend analysis report view is probably not not the best fit because you're not trying to make predictions. The trend analysis report type uses SSAS data mining algorithms to predict the next n points along a time series.

     

    Instead use a simple Analytic chart report to display the trend over time. In order to dynamically apply a set of members (in your case the last 4 quarters based on a filter selection) all you need to do is set up a filter link formulas on the scorecard columns end point as well as the time dimension endpoint of the analytic chart (probably set up on the X axis. I just posted a response to your scorecard question here which covers the formula needed for a dynamic last 4 quarter set, hope that helps. 

     

    Cheers,

    Nick

    Friday, July 18, 2008 9:56 AM
  • It does help yes,

     

    I've just read you're answer to my other post and questioned you about this one. I'll see how this works out with the Analytic chart.

     

    Thanks for your help.

     

    Its starting to make a bit more sense now.

     

    Friday, July 18, 2008 10:00 AM
  •  

    Nick,

     

    The analytic chart suits my purposes to an extent. Is it possible to tie a specific KPI to the chart and have it display with the correct data. The dynamic date range along the x axis works fine. At present I have included all KPI's from the dimension table and calculated thoses against the "Actual" measure. There is no lonking to the scorecard as I believe there would have been through the trend analysis chart.

     

    Thanks,

     

    G

    Friday, July 18, 2008 1:11 PM
  • Yes, this is also possible, the solution depends on whether you have defined individual KPIs or you're referencing a dimension that contains the KPI names. For the former you can pass KPI specific context to the Analytic report by defininng custom properties on each KPI and pass that value via a filter link or for the latter you can pass the KPI dimension member name across. Have a look at this post by the PPS team, it should help.

     

    Cheers,

    Nick

    Saturday, July 19, 2008 3:22 AM
  • I'm working on the former method of creating individual KPI's for now. I can set up the custom property, I set this up and the KPI hierarchy - KPI name value. I had hoped that this would link to the KPI hierarchy i have as the series in my Analytic chart. It doesn't appear to do anything though.

     

    If i just specify the MDX for the hierarchy, in this case [KPI].[KPI], as I correct in saying that I should be able to link this to my Analytic chart which uses the same hierarchy?

     

    On a similar note, and refering to the second method where by I use a dimension in the scorecard, Upon adding a new scorecard there are no dimensions available in the right hand pane. Why would this be?

     

    Thanks,

     

    G

     

    Monday, July 21, 2008 8:13 AM
  • Apologies, for the last post, I now have the custom proprty working by specifying the member name in the property and passing it to the Analytic chart.

     

    It looks to work ok but I now have some further questions surrounding the chart.

     

    Is there a way to set the chart to use the last 4 quarters relative to the current date. I have the time intelligence setup so I'm hoping is a simple matter to do this. At present it takes the period from the time intelligence filter, I'd really only like this to impact the scorecard time frame.

     

    The other question i have is how to use the objective KPI values in this chart if they are selected. Prefeably I'd like to show these as the default. There are 4 objective level KPI's which are calculated based on the average of the children. Is it possible to use the objective values in the chart or will i have to use the OLAP model and get the values based on the cube measures and dimensions?

     

    Thanks

     

    G

     

    Monday, July 21, 2008 10:18 AM
  •  GS80 wrote:

     

    Is there a way to set the chart to use the last 4 quarters relative to the current date. I have the time intelligence setup so I'm hoping is a simple matter to do this. At present it takes the period from the time intelligence filter, I'd really only like this to impact the scorecard time frame.

     

     

    Thought I answered how to do a 4 quarter rolling set in a previous post. Not sure what you're looking for, are you wanting to affect both the scorecard and the chart of just one of them. You question is unclear as to what you want to do.

     

     GS80 wrote:

     

     

    The other question i have is how to use the objective KPI values in this chart if they are selected. Prefeably I'd like to show these as the default. There are 4 objective level KPI's which are calculated based on the average of the children. Is it possible to use the objective values in the chart or will i have to use the OLAP model and get the values based on the cube measures and dimensions?

     

     

    You could potentially create a calculated member within the source cube that calculates the average value of the children and display that in the chart. Remember the analytic charts / grids are based on the data from within a cube, not the scorecard. The filter links defined between the two items communicate context not numeric data.

     

    Cheers,

    NB

    Monday, July 21, 2008 11:04 PM
  • Apologies on question 1. The time intelligence filter works ok in that it will filter both the scorecard and the analytic grid based on the time intelligence formula.

     

    I don't really want to use the time intelligence filter on the analytic chart, all i want is for a fixed rolling 4 quarter period analytic chart up to the current quarter. I took from your response previously that the chart updates with the date period specified in the filter. I have maybe picked this up wrong.

     

    Is the above any clearer?

     

    As for my second query, I realised I could get these values out put using the hierarchy in the cube. It looks to work fine.

     

    Thanks,

     

    G

     

    Tuesday, July 22, 2008 7:10 AM
  • Aah, so the the chart will not be connected to filters at all, this way the source cube will need to "know" the current date. What you'll need is some MDX in either the source cube or the query that generates the chart data. Have a look at this post by Mosha Pasumansky, it will assist in creating structures within your cube that automatically determine the current day member in your time dimension. Using that ""as a starting point you create a set of 4 quarters. Remember, though, using hand-made MDX in your Analytic Views reduces your user's interactivity

     

    Another idea: You can achieve this functionality by creating another TI filter with just one item: STPS formula for the Current quarter. That way the chart gets its current quarter automatically and you retain interactivity because the analytic chart does not need any hand-made MDX. The drawback is that this extra filter appearing on the dashoard with only one item may confuse users....

     

    HTH,

    Nick

    Tuesday, July 22, 2008 10:03 AM
  • That does help yes. This is exactly what i wanted to do however I'll take onboard the suggestion that it decreases the interactivity for the user. I may well just set up another filter sitting above the analytic chart to control the look of that.

     

    Thanks for your patience.

    Tuesday, July 22, 2008 10:09 AM