RRDI Iteration report
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
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.
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.
Comments
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
[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.
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
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.