In RRDI Report Studio we need to be able to show certain values over time on a day to day basis that are not tied to the DW snapshots.  This value could be a string value, or an integer (integer extension), and could be from a custom attribute. 

For example, I need to show historical story point value for every day of a given iteration

Date                      US ID                     SP Value              History Value

1/1/2015              1234                       0

1/2/2015              1234                       1                              1

1/3/2015              1234                       1

1/4/2015              1234                       5                              5

1/5/2015              1234                       5

1/6/2015              1234                       5

1/1/2015              1235                       0

1/2/2015              1235                       8                              8

1/3/2015              1235                       8

1/4/2015              1235                       5                              5

1/5/2015              1235                       5

1/6/2015              1235                       5

The above would show that for US 1234, it was 0, changed to 1 on 1/2, changed 5 on 1/4. Simliar for 1235, it was first 0, changed to 8 on 1/2, then to 5 on 1/4,  So we see the appropriate values daily.

I've been able to use ODS request History [Business View].[Request History], and join that with Request Metrics area [Request Metrics] date information to get a view that shows every day (within a date range), and show a value (story Point) on dates that had a change in history (story point value was set or changed).  However, on days where there was no “change” or history record, the SP value is 0 since there is no history record to join to.

This is how it’s displaying:

Date                      US ID                     SP Value              History Value

1/1/2015              1234                       0

1/2/2015              1234                       1                              1

1/3/2015              1234                       0

1/4/2015              1234                       5                              5

1/5/2015              1234                       0

1/6/2015              1234                       0

1/1/2015              1235                       0

1/2/2015              1235                       8                              8

1/3/2015              1235                       0

1/4/2015              1235                       5                              5

1/5/2015              1235                       0

1/6/2015              1235                       0

So the question is:  Is there a way to set the value (SP Value) to the previous row value when (SP Value) is null to get the report to show the daily value?  And, this would need to work independent of the type of value (string, number, etc…).

I’ve tried adding a rownumber, copying the original query and doing a self join by rownum-1, but can only get it to cascade the value once, then goes back to zero.

Please help as this function is critical to several reports we need to produce.  We are currently on CLM 5.0, with base RRDI install (No Insight)