How to make the fields visible on table created with database view

[sath]

Hi,

We have created a database view with story(rm_story) and metric instance(metric_instance) tables and generated a report on it.

When the story state is blocked, substate and blocked reason fields will be visible on the form and those fields are populated with values on the report as well. Once the story is moved to any other state from blocked, substate and blocked reason values returns empty. I believe this is happening because the report is on database view and it will display only the fields visible on the form at the time it is retrieving data. What other options do we have to get those fields with values displayed all the time on the table/report?

[Maik Skoddow]

Hi @sath 

You can't do this with traditional reporting because a report can only display data that exists today. If you want to report over time, you need to use Performance Analytics. However, if it's not already running, you can only display historical data if the Story table is audited. For audited tables, you then can run a historical data collector that builds the data foundation retroactively.

Maik

[sath]

Hi @Maik Skoddow  Thank you.

We need below columns to be displayed:

Story Number

Summary
State

Substate

Blocked reason

Start time

End time

Duration

Assignment group

Assigned to

I have checked audit table, you can display the old value and new value of a field that has been changed, but not the start and end time along with duration calculation.

We were able to get start and end time of a specific state along with duration calculation only from Metric Instances table, but we can not refer story fields from that table which is why we created a database view which won't work for our requirements since that's a live snapshot of data like you said.

We have Performance Analytics installed, which table should we refer to/customization needs to be done to get all these columns on a report?

[Ankur Bawiskar]

@sath 

I agree with @Maik Skoddow here.

Please follow the steps he mentioned and let us know.

If my response helped please mark it correct and close the thread so that it benefits future readers.