It's all about the answers!

Ask a question

How to get archived iterations in RRDI?

Rafael Pirolla (1649) | asked Jan 14 '16, 10:17 a.m.
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. 

I would like to know how to override this in the report studio.


Accepted answer

permanent link
Steve Mulligan (1761321) | answered Jan 14 '16, 11:06 a.m.
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:
6. The new query (Query2) will now contain all the fields in the Iteration view.
7. The important fields are:
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. 
Rafael Pirolla selected this answer as the correct answer

Rafael Pirolla commented Jan 15 '16, 6:50 a.m.

 Worked like a charm!


Steve Mulligan commented Jan 15 '16, 9:59 a.m.

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.

One other answer

permanent link
Rafik Jaouani (5.0k16) | answered Jan 14 '16, 10:46 a.m.
Hi Rafael, this is impossible with RRDI. You will need the Framework Manager tool to edit the data model and change a flag that controls this behavior. The Framework Manager tool only ships with Rational Insight.

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.