Topic
  • 2 replies
  • Latest Post - ‏2013-07-29T19:04:32Z by OpherB
Shiraz79
Shiraz79
4 Posts

Pinned topic Reporting on days of the year where data is not stored in DB

‏2013-07-23T20:22:19Z |

I have a cube requirement that I don't believe is possible to fulfill and would appreciate some feedback please.

The requirement is to report a count of all claims, for each status, for every day of the year.  However, my database only contains records on those days where a status has changed.

Example:

Claim#  Date                      Status

1              2012-01-01          Pending
1              2012-01-10          Active
1              2012-01-21          Completed
2              2012-01-02          Pending
2              2012-01-02          Active
2              2012-01-09          Completed

An example of what we are reporting on would be to show that on 2012-01-08 one claim was Pending and one claim was Active.  On 2012-01-09, one claim was Pending and one claim was Completed.  Keep in mind that this is for a cube and we're reporting on millions of records.

Is this feasible to build a report in Report Studio with the way the data is currently stored in the database?  I've experimented with creating multiple queries with recursive joins but have not been successful.

Thanks!

  • OpherB
    OpherB
    664 Posts

    Re: Reporting on days of the year where data is not stored in DB

    ‏2013-07-29T19:04:00Z  

    I don't think you can do this in a cube. In a relational setting you'll need a Calendar table which has records for all dates, so you can fill in the gaps. Join to this Fact table with an outer join, so you will get all dates, then set appropriate Group By footers for the kinds of sub-total/total you want to show.

    Good luck,
    Opher

     

  • OpherB
    OpherB
    664 Posts

    Re: Reporting on days of the year where data is not stored in DB

    ‏2013-07-29T19:04:32Z  

    I don't think you can do this in a cube. In a relational setting you'll need a Calendar table which has records for all dates, so you can fill in the gaps. Join to this Fact table with an outer join, so you will get all dates, then set appropriate Group By footers for the kinds of sub-total/total you want to show.

    Good luck,
    Opher