How to get archived iterations in RRDI?
As Steve Mulligan reported in another question:
[Business View].[Iteration] uses the view VW_ITERATION to get the iteration data from the warehouse. The view contains iterations that have been archived (ISSOFTDELETED=1), but the model adds the filter
ISSOFTDELETED=0, so the archived iterations do not appear in the reports.
ISSOFTDELETED=0, so the archived iterations do not appear in the reports.
I would like to know how to override this in the report studio.
Thanks,
Raf.
Accepted answer
There are two option to making the archived iterations visible in Report Studio.
1. Modify the Reporting Model in Framework Manager to removed the filter that filters out the archived iterations.
2. Use direct SQL in Report Studio to get the Iteration data.
Option 1 requires rational Insight and detailed knowledge of Framework Manager (FM). The Iteration query subject in the FM model could be modified to remove the filter that filters out the archived records. However, that would affect all reports that use that query subject, so would not be recommended. In the FM model, you could make a copy of the Iteration query subject and remove the filter from that. You would then use that new query subject in your report. A drawback of modifying the FM model, is that you will have to do it each time you upgrade Rational Insight to a new version, with a new FM model.
Option 2 is much easier to do and requires no additional work when upgrading to a new version of Rational Insight. It should also work with RRDI as long as you have Report Studio, as you are not modifying the FM model.
The following is how to add and use direct SQL in Report Studio.
1. In your report, go into the Query Explorer window.
2. Create a new query in your report by dragging the "SQL" icon into the Query Explorer main window. You will get a new query (e.g. Query2) with a "SQL" box pointing to it.
3. Click on the "SQL" box, and in the properties section at the bottom left of the screen update the "Data Source" and "SQL" fields.
4. "Data Source" will be the Data source connection to your data warehouse, which will probably be "RIDW".
5. In the "SQL" field enter the following:
SELECT * FROM RIDW.VW_ITERATION
6. The new query (Query2) will now contain all the fields in the Iteration view.
7. The important fields are:
ITERATION_ID
NAME
PROJECT_ID
PROJECT
TIMELINE_ID
TIMELINE
START_DATE
END_DATE
ISSOFTDELETED
8. ISSOFTDELETED=1 is the archived iterations.
9. Create a List in the report to see the data from the query.
10. In Page Explorer drag a List onto the page. The List will get a new query number by default. Change that to the query you just created (e.g. Query2).
11. Add fields from Query 2 into the List
This method does not allow you to reuse the SQL Query in other reports as easily as the option where it is added to the FM model. You can however, copy the query (using copy and paste) to copy the query to another report.
1. Modify the Reporting Model in Framework Manager to removed the filter that filters out the archived iterations.
2. Use direct SQL in Report Studio to get the Iteration data.
Option 1 requires rational Insight and detailed knowledge of Framework Manager (FM). The Iteration query subject in the FM model could be modified to remove the filter that filters out the archived records. However, that would affect all reports that use that query subject, so would not be recommended. In the FM model, you could make a copy of the Iteration query subject and remove the filter from that. You would then use that new query subject in your report. A drawback of modifying the FM model, is that you will have to do it each time you upgrade Rational Insight to a new version, with a new FM model.
Option 2 is much easier to do and requires no additional work when upgrading to a new version of Rational Insight. It should also work with RRDI as long as you have Report Studio, as you are not modifying the FM model.
The following is how to add and use direct SQL in Report Studio.
1. In your report, go into the Query Explorer window.
2. Create a new query in your report by dragging the "SQL" icon into the Query Explorer main window. You will get a new query (e.g. Query2) with a "SQL" box pointing to it.
3. Click on the "SQL" box, and in the properties section at the bottom left of the screen update the "Data Source" and "SQL" fields.
4. "Data Source" will be the Data source connection to your data warehouse, which will probably be "RIDW".
5. In the "SQL" field enter the following:
SELECT * FROM RIDW.VW_ITERATION
6. The new query (Query2) will now contain all the fields in the Iteration view.
7. The important fields are:
ITERATION_ID
NAME
PROJECT_ID
PROJECT
TIMELINE_ID
TIMELINE
START_DATE
END_DATE
ISSOFTDELETED
8. ISSOFTDELETED=1 is the archived iterations.
9. Create a List in the report to see the data from the query.
10. In Page Explorer drag a List onto the page. The List will get a new query number by default. Change that to the query you just created (e.g. Query2).
11. Add fields from Query 2 into the List
This method does not allow you to reuse the SQL Query in other reports as easily as the option where it is added to the FM model. You can however, copy the query (using copy and paste) to copy the query to another report.
Comments
Worked like a charm!
Thanks!
Raf.
Good to hear.
It is useful sometimes to join the SQL Query with a query created from the FM model. This can be done with the Join icon in Query explorer. e.g. you could join the Iteration SQL Query to the Iteration query you originally created from the model.
1 vote