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.
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.