It's all about the answers!

Ask a question

RRDI Iteration report


Rafael Pirolla (1617) | asked Jan 11 '16, 2:15 p.m.
I'm trying to get the 'Timeline Name' of some archived 'Iteration's.

I can see the Iteration name over here 'ODS/Request Area/Request/Iteration Name' but there is no such Iteration on 'ODS/Iteration Area/Iteration/Name' or Iteration ID.

Apart from this. I can see a field on Report Studio under 'ODS/Iteration Area/Iteration' called 'Project Name' that does not appear in the dictionary:

http://www-01.ibm.com/support/knowledgecenter/SSYMRC_5.0.2/com.ibm.team.reports.doc/topics/ccm/Iteration.html

Is that so?

Thanks,
Raf.

Accepted answer


permanent link
Cathy Xu (390412) | answered Jan 11 '16, 11:59 p.m.
Hi Raf,

You said "there is no such Iteration on 'ODS/Iteration Area/Iteration/Name' or Iteration ID", however I can see "Name" and "Iteration ID" under 'ODS/Iteration Area/Iteration'.

The
'Project Name' field on Report Studio under 'ODS/Iteration Area/Iteration' does not appear in Iteration data dictionary, that is true. The fields you see from Report Studio are from RIDW VIEWs, which may come from multiple TABLEs JOINed together. You can run "Show Generated SQL/MDX " against a query (from Report Studio>Tools) to see which VIEW it refers to and further look at the view info...

For example:
CREATE VIEW "RIDW"."VW_ITERATION" ("ITERATION_ID", "NAME", "REFERENCE_ID", "EXTERNAL_ID", "ASSETREGISTRY_ID", "ASSETREGISTRY_NAME", "PROJECT_ID", "PROJECT_NAME", "PHASE_ID", "PHASE_NAME", "OWNER_ID", "OWNER_NAME", "PARENT_ITERATION_ID", "PARENT_ITERATION_NAME", "TIMELINE_ID", "TIMELINE_NAME", "STATUS", "DESCRIPTION", "START_DATE", "END_DATE", "EXPECTED_START_DATE", "EXPECTED_END_DATE", "URL", "EXTERNAL_KEY1", "EXTERNAL_KEY2", "EXPECTED_TOTAL_POINTS", "EXPECTED_DEFECTS", "EXPECTED_VALIDITY_RATE", "REC_DATETIME", "ISSOFTDELETED") AS
SELECT I.ITERATION_ID, I.NAME, I.REFERENCE_ID, I.EXTERNAL_ID, I.ASSETREGISTRY_ID,A.NAME,I.PROJECT_ID, P.NAME AS PROJECT_NAME,I.PHASE_ID,PH.NAME,I.OWNER_ID,R.FULL_NAME AS OWNER_NAME,I.PARENT_ITERATION_ID,PI.NAME AS PARENT_ITERATION_NAME,I.TIMELINE_ID,T.NAME AS TIMELINE_NAME,I.STATUS,I.DESCRIPTION,I.START_DATE, I.END_DATE , I.EXPECTED_START_DATE, I.EXPECTED_END_DATE,I.URL,I.EXTERNAL_KEY1,I.EXTERNAL_KEY2, I.EXPECTED_TOTAL_POINTS,I.EXPECTED_DEFECTS,I.EXPECTED_VALIDITY_RATE,I.REC_DATETIME,I.ISSOFTDELETED FROM RIODS.ITERATION I JOIN RIODS.PROJECT P ON P.PROJECT_ID=I.PROJECT_ID
JOIN RIODS.PHASE PH ON PH.PHASE_ID=I.PHASE_ID JOIN RIODS.RESOURCE R ON R.RESOURCE_ID=I.OWNER_ID JOIN RIODS.ASSETREGISTRY A ON A.ASSETREGISTRY_ID=I.ASSETREGISTRY_ID JOIN RIODS.ITERATION PI ON PI.ITERATION_ID=I.PARENT_ITERATION_ID JOIN RIODS.TIMELINE T ON T.TIMELINE_ID=I.TIMELINE_ID;

Hope this helps.
Rafael Pirolla selected this answer as the correct answer

Comments
Rafael Pirolla commented Jan 12 '16, 6:31 a.m.

 Hey Cathy, thanks for the reply!


Let me ask the first question another way:

If I select everything from the 'ODS/Iteration Area/Iteration' with a filter like:
[Business View].[Iteration].[Is Deleted]<>0

Nothing comes up. Is this so? I cannot find the deleted Iterations anywhere apart from the old Requests - but I need the Timeline they belong to and I think I can only find the Timeline in the Iteration table...

谢谢,
Raf.

One other answer



permanent link
Steve Mulligan (1761318) | answered Jan 12 '16, 1:04 p.m.
[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.

If you are using Cognos Report Studio, you can use a direct SQL query to get the iteration data, including the ones that have been archived.  I can describe how to do that if you are interested.

Comments
Rafael Pirolla commented Jan 14 '16, 10:21 a.m.

Hi Steve,


I have asked two questions in this one, so I marked Cathy's for the first question.
I opened this new one. If you could describe it over there, I would be thankful.

Thanks,
Raf.

Your answer


Register or to post your answer.