Jazz Forum Welcome to the Jazz Community Forum Connect and collaborate with IBM Engineering experts and users

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. 

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

Thanks,
Raf.

0 votes


Accepted answer

Permanent link
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. 
Rafael Pirolla selected this answer as the correct answer

1 vote

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


One other answer

Permanent link
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.

1 vote

Your answer

Register or log in 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.

Search context
Follow this question

By Email: 

Once you sign in you will be able to subscribe for any updates here.

By RSS:

Answers
Answers and Comments
Question details
× 12,016
× 403

Question asked: Jan 14 '16, 10:17 a.m.

Question was seen: 3,376 times

Last updated: Jan 15 '16, 9:59 a.m.

Confirmation Cancel Confirm