It's all about the answers!

Ask a question

How do you carry previous values forward over several rows of data?


Steve Maples (132) | asked Aug 18 '15, 5:16 p.m.

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)

Accepted answer


permanent link
Rafik Jaouani (5.0k16) | answered Aug 20 '15, 1:06 p.m.
JAZZ DEVELOPER
Hi Steve, you need to look at the [Business View].[Request Baseline] and join it with [Business View].[Request History]
I think this will give you what you need.
Steve Maples selected this answer as the correct answer

Comments
Steve Maples commented Aug 25 '15, 9:46 a.m.

This worked exactly as  I needed.  Thank you!

Your answer


Register or to post your answer.


Dashboards and work items are no longer publicly available, so some links may be invalid. We now provide similar information through other means. Learn more here.