Group By in RTC JAZZ Queries for BIRT reports
Hi,
I'm new to RTC, BIRT and JAZZ. I'm trying to create a report which summarizes all workitems grouped by their status. My Datasource is a JAZZ Datasource. Therefore I used the table "LIVE_WORKITEM_CNT" from the "Workitem Snapshot" and tried to do an inner join with the table "STATE" on the column "STATE".
My problem is that the table "STATE" contains duplicated row entries which only differ in their "REQUEST_STATE_ID" but have the same "STATE". After joining the tables the result set also contains duplicated rows. That means, that every workitem in "LIVE_WORKITEM_CNT" is combined with the two entries in the "STATE" table.
How can I avoid this behavior? Or how can I eliminate the duplicated rows?
I tried a SQL-like query with "SELECT DISTINCT" which is not supported for JAZZ data source queries.
I would appreciate any help!
Thanks in advance and best regards
Artur
I'm new to RTC, BIRT and JAZZ. I'm trying to create a report which summarizes all workitems grouped by their status. My Datasource is a JAZZ Datasource. Therefore I used the table "LIVE_WORKITEM_CNT" from the "Workitem Snapshot" and tried to do an inner join with the table "STATE" on the column "STATE".
My problem is that the table "STATE" contains duplicated row entries which only differ in their "REQUEST_STATE_ID" but have the same "STATE". After joining the tables the result set also contains duplicated rows. That means, that every workitem in "LIVE_WORKITEM_CNT" is combined with the two entries in the "STATE" table.
How can I avoid this behavior? Or how can I eliminate the duplicated rows?
I tried a SQL-like query with "SELECT DISTINCT" which is not supported for JAZZ data source queries.
I would appreciate any help!
Thanks in advance and best regards
Artur
3 answers
@Arthur
I am assuming you are running the Report in the Birt Viewer.
"REQUEST_STATE_ID" for the same "STATE" will be unique in every project area process specification.
If you run the report in RTC, it will run for only that Project Area and the you should not get duplicates.
Hope that helps!
I am assuming you are running the Report in the Birt Viewer.
"REQUEST_STATE_ID" for the same "STATE" will be unique in every project area process specification.
If you run the report in RTC, it will run for only that Project Area and the you should not get duplicates.
Hope that helps!
Hi Sandy,
Thanks for your quick response. At the moment I'm using RTC (eclipse) for previewing the report. When creating a dataset I use the "Edit" dialog and "Preview Results" for verifying my results.
I think I need to explain my problem in more detail:
A data row in the snapshot "Workitem Snapshot" in the table "LIVE_WORKITEM_CNT" contains (among many other columns) the columns
- "WI_ITEMID" (the unique ID of my workitem),
- "PROJECT_AREA_ITEMID" (the unique ID of my project area) and
- "STATE_NAME".
"STATE_NAME" seems to me like some kind of reference to a specific state. It looks like "com.xxx.xxx.xxx.verified" or "com.xxx.xxx.xxx.tested".
A data row in the Snapshot "Workitem Snapshot" in the table "STATE" contains
- a "REQUEST_STATE_ID" (e.g. 1234),
- "STATE" (seems to be the "PROJECT_AREA_ITEMID" like in "LIVE_WORKITEM_CNT" followed by a "s" and then by the "STATE_NAME" like in the table "LIVE_WORKITEM_CNT"),
- a "STATE_NAME" (e.g. "In Progress" or "Done"),
- and a "STATE_GROUP" (like "OPEN_STATES" or "IN_PROGRESS_STATES").
When I fetch all rows from the table "STATE", I get rows, which have the same values for the columns "STATE", "STATE_NAME" and "STATE_GROUP". But there are 2 "REQUEST_STATE_ID"s, which means, I have every row twice (beside the fact, that the "REQUEST_STATE_ID" is another one).
How can I get from a row (item) in "LIVE_WORKITEM_CNT" to its appropriate "STATE"? Because the column "REQUEST_STATE_ID" does not exist in the table "LIVE_WORKITEM_CNT". So I tried to (inner) join both tables by building the value "STATE" in the table "STATE" (= "PROJECT_AREA_ITEMID" + "s" + "STATE_NAME"). But when I do this, I get every item twice, because there are two rows in the table "STATE", that match my join.
Or is there any option to filter these duplicates? May be by using something like "group by"?
Best regards and thanks in advanced!!!
Artur
Thanks for your quick response. At the moment I'm using RTC (eclipse) for previewing the report. When creating a dataset I use the "Edit" dialog and "Preview Results" for verifying my results.
I think I need to explain my problem in more detail:
A data row in the snapshot "Workitem Snapshot" in the table "LIVE_WORKITEM_CNT" contains (among many other columns) the columns
- "WI_ITEMID" (the unique ID of my workitem),
- "PROJECT_AREA_ITEMID" (the unique ID of my project area) and
- "STATE_NAME".
"STATE_NAME" seems to me like some kind of reference to a specific state. It looks like "com.xxx.xxx.xxx.verified" or "com.xxx.xxx.xxx.tested".
A data row in the Snapshot "Workitem Snapshot" in the table "STATE" contains
- a "REQUEST_STATE_ID" (e.g. 1234),
- "STATE" (seems to be the "PROJECT_AREA_ITEMID" like in "LIVE_WORKITEM_CNT" followed by a "s" and then by the "STATE_NAME" like in the table "LIVE_WORKITEM_CNT"),
- a "STATE_NAME" (e.g. "In Progress" or "Done"),
- and a "STATE_GROUP" (like "OPEN_STATES" or "IN_PROGRESS_STATES").
When I fetch all rows from the table "STATE", I get rows, which have the same values for the columns "STATE", "STATE_NAME" and "STATE_GROUP". But there are 2 "REQUEST_STATE_ID"s, which means, I have every row twice (beside the fact, that the "REQUEST_STATE_ID" is another one).
How can I get from a row (item) in "LIVE_WORKITEM_CNT" to its appropriate "STATE"? Because the column "REQUEST_STATE_ID" does not exist in the table "LIVE_WORKITEM_CNT". So I tried to (inner) join both tables by building the value "STATE" in the table "STATE" (= "PROJECT_AREA_ITEMID" + "s" + "STATE_NAME"). But when I do this, I get every item twice, because there are two rows in the table "STATE", that match my join.
Or is there any option to filter these duplicates? May be by using something like "group by"?
Best regards and thanks in advanced!!!
Artur