locked
Using "As Of" date in a report model RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Everyone.

    I have an organisation structure table that contains start and end dates so that I can use it to sequence financial reports with the structure "as of" a set date.

    Along with this, I also have some slow moving dimension tables related to chart of account structure that also have start and end dates.

    Now, I can build a model with this OK and when I create a report, I can add the selections ( "as of date between start orgstructure date and end orgstructure date and as of date between account structure start and end structure start) without any problem but its much to complicated for my ad-hoc report writers to use.

    Is there any way of setting up a parameter field in a model so that I can build the selections into the model itself so my users only need to feed it the date?

     

    Monday, September 13, 2010 2:57 AM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    Unfortunately there is no concept of global report model parameters or parameterized entities to control something like this (it would be an excellent feature and I highly suggest that you create feedback for this on Microsoft Connect if it would be valuable to you--that allows the community to vote on it and helps us prioritize for future releases). If you want users to be able to see current data only you could bake that into the model via named queries in the Data Source View but I can't think of a straightforward way to make it configurable by date.  You might be able to do it by creating a date table with values per day and then some additional entities based on named queries that join the date table to your slow moving dimension tables.  This could avoid the end users having to specify a date range but would probably still be too cumbersome (and somewhat difficult to debug since it would lead to inflated/crossjoined data in the cases where a user failed to specify a filter on the date).

    I don't know if it is feasible for your scenario but SQL Server Analysis Services has better support for this.  You can query SSAS databases using the graphical MDX query designer in Report Builder 2.0/3.0.

    Friday, September 17, 2010 12:49 AM