none
score cards RRS feed

  • Question

  • Hi,

     I have a bunch of score cards, monthly, quarterly, and yearly... they loa dthe data any time mid of next month, how can my score cards know the latest month updated and how should I set my source Time , Time Dimension, Refrense date? so that it automatically updates with the latest month data

    Wednesday, November 23, 2011 3:47 PM

Answers

  • I would not exactly say it Current Month, it is most likely the latest month with data. If you have AdventureWorks DW setup use this code:

     

    WITH SET [CurrentMonth] AS TAIL( NONEMPTY ([Date].[calendar].[month]),1)
    
    SELECT Measures.[order count] on Columns,
    [CurrentMonth] on Rows
    from [adventure works]
    

    So to create a Named Set in Cube, use only the first line in your Calculations tab of SSAS solution.
    http://dailyitsolutions.blogspot.com/
    • Edited by Umair.Khan Thursday, December 1, 2011 3:14 PM
    • Marked as answer by Reddy07 Thursday, December 1, 2011 7:20 PM
    Thursday, December 1, 2011 3:14 PM

All replies

  • Please provide more info like do you want the scorecards to update automatically? how are your scorecards set up like do they have date filters applied, filter connection point  like row, column or filter etc.

    Every time there is new data in the data source scorecards would load that data provided the cache time has over lapsed.


    http://dailyitsolutions.blogspot.com/
    Wednesday, November 23, 2011 6:05 PM
  • yeah, I want to update the score cards automatically.

    I have set the source Time as Refrence Member 12-03 and refrence Date as 11/1  Hierarchy level as Month.

    so If the dates changes  suppose tommrow December starts but the cube  doesnt have NOv data so  the score card shows empty were it should show Oct data so i change the source  Refence Date to 12/1 then it shows Oct data/......... once Noc data is loaded in 2nd week agaian I need to chnage the Refence date to 11/1 it shows Nov data.

    Wednesday, November 30, 2011 2:07 PM
  • Will this work:

    Change all your time filters to load NONEMPTY Time dimension members from your data source. This way if you don't have Nov data, Nov dates would not be listed in the time filters...

    Since Nov is not loaded, your current date with data would be Oct. And your scorecard would load data for Oct.

    Once there is data loaded for Nov, the filters would automatically pick Nov and scorecard would display data for that date.

    You don't need to make changes to reference date. Just set to overlap with dimension member.


    http://dailyitsolutions.blogspot.com/
    • Edited by Umair.Khan Wednesday, November 30, 2011 3:51 PM
    Wednesday, November 30, 2011 3:51 PM
  •  I donno How to use NON EMPTY  Time Dimension,

     I am filtering  it by KPI  Current Month for MONTH KPI, Current Quarter for Quarter KPI, and current Year for Year KPI..

    Wednesday, November 30, 2011 7:03 PM
  • Run this on MDX Query editor:

    SELECT Measures.[YourDefaultMeasure] on Columns,
    NONEMPTY ([yourTimeDimension].members) on Rows
    from [your Cube]

    You should see only the dates with data. And the last member should be Oct. On the scorecard bind each KPI with each filter to get required data.


    http://dailyitsolutions.blogspot.com/
    Wednesday, November 30, 2011 9:09 PM
  • I use   MOnth for KPI under  

    Data Mappings --> Enter a Time Filter Formula

     

    do you mean to say to use MDX Tuple formula    in the KPI?

    Wednesday, November 30, 2011 9:22 PM
  • That is right.

    OR you just create 3 Named Sets in your cube like CurrentMonth, CurrentQuarter, CurrentYear and use those. Where these sets would only return one member from date dimension.


    http://dailyitsolutions.blogspot.com/
    Wednesday, November 30, 2011 9:51 PM
  • Current MOnth with NOnempty MDX?canu tell me the syntax  please?
    Thursday, December 1, 2011 1:48 PM
  • I would not exactly say it Current Month, it is most likely the latest month with data. If you have AdventureWorks DW setup use this code:

     

    WITH SET [CurrentMonth] AS TAIL( NONEMPTY ([Date].[calendar].[month]),1)
    
    SELECT Measures.[order count] on Columns,
    [CurrentMonth] on Rows
    from [adventure works]
    

    So to create a Named Set in Cube, use only the first line in your Calculations tab of SSAS solution.
    http://dailyitsolutions.blogspot.com/
    • Edited by Umair.Khan Thursday, December 1, 2011 3:14 PM
    • Marked as answer by Reddy07 Thursday, December 1, 2011 7:20 PM
    Thursday, December 1, 2011 3:14 PM