Using list as parameter instead of filter for increasing performance
Hi everyone,
I've got a problem with a BIRT report with a JAZZ datasource. What I want to do:
- Load all workitems (User Stories) I need (that's not my problem) ;-)
- Load all tasks which belong to the User Stories (that's the problem)
My approach:
- I loaded all User Story workitems by using the WORKITEM_SNAPSHOT
- I stored all workitem ID's in a JavaScript variable by using the onFetch method
- Now I want to create the relation between User Story and task
- Therefore I have to use the table LINKS in the COMMONS_SNAPSHOT which contains the information about the relation from workitem (ID) A to workitem (ID) B
- So I need a Parameter Dataset for loading the appropriate tasks from table LINKS
- But I cannot use a parameter of a dataset like using a filter: TARGET_ITEMID in [my JS Variable with all relevant User Story IDs]
- That means, that I have to load all (!) LINKS and filter these afterwards
- And that again is a big performance issue, since there are a lot of links.
I had to face such a situation very often! How can I get the data directly without loading the whole table first?
Hope somebody can help me with this issue!
Thank you very much!
Artur
I've got a problem with a BIRT report with a JAZZ datasource. What I want to do:
- Load all workitems (User Stories) I need (that's not my problem) ;-)
- Load all tasks which belong to the User Stories (that's the problem)
My approach:
- I loaded all User Story workitems by using the WORKITEM_SNAPSHOT
- I stored all workitem ID's in a JavaScript variable by using the onFetch method
- Now I want to create the relation between User Story and task
- Therefore I have to use the table LINKS in the COMMONS_SNAPSHOT which contains the information about the relation from workitem (ID) A to workitem (ID) B
- So I need a Parameter Dataset for loading the appropriate tasks from table LINKS
- But I cannot use a parameter of a dataset like using a filter: TARGET_ITEMID in [my JS Variable with all relevant User Story IDs]
- That means, that I have to load all (!) LINKS and filter these afterwards
- And that again is a big performance issue, since there are a lot of links.
I had to face such a situation very often! How can I get the data directly without loading the whole table first?
Hope somebody can help me with this issue!
Thank you very much!
Artur
4 answers
Please check the following wiki for an example:
https://jazz.net/wiki/bin/view/Main/ReportsCentral#Links
https://jazz.net/wiki/bin/view/Main/ReportsCentral#Links
Comments
Hi Rafik Jaouani,
thanks for your answer. I downloaded the "Links.rptdesign" file and figured out, how to use a string array as input parameter value for a JAZZ Parameter dataset:
"'" + myStringArray.join("','") + "'";
So I can use this when I've got a list of WI_ITEMIDs, because WI_ITEMIDs are strings in my table I use first. But whats about an array of integer? I want to get all lines from the table WORKITEM_CHNGS for a specific set of WI_ITEMIDs in the Workitem Snapshot. But this table has got WI_ID only (which is an integer and not a string). So I cannot use the same approach. I tried, but an exception was thrown because of the mismatching data type.
Using the name of my javaScript array (e.G. myItemIdArray) as parameter value does not work as well.
How can I solve this problem?!
Best regards and thank you in advance!
Artur
Hi Rafik Jaouani,
thanks for your answer. I downloaded the "Links.rptdesign" file and figured out, how to use a string array as input parameter value for a JAZZ Parameter dataset:
"'" + myStringArray.join("','") + "'";
So I can use this when I've got a list of WI_ITEMIDs, because WI_ITEMIDs are strings in my table I use first. But whats about an array of integer? I want to get all lines from the table WORKITEM_CHNGS for a specific set of WI_ITEMIDs in the Workitem Snapshot. But this table has got WI_ID only (which is an integer and not a string). So I cannot use the same approach. I tried, but an exception was thrown because of the mismatching data type.
Using the name of my javaScript array (e.G. myItemIdArray) as parameter value does not work as well.
How can I solve this problem?!
Best regards and thank you in advance!
Artur
thanks for your answer. I downloaded the "Links.rptdesign" file and figured out, how to use a string array as input parameter value for a JAZZ Parameter dataset:
"'" + myStringArray.join("','") + "'";
So I can use this when I've got a list of WI_ITEMIDs, because WI_ITEMIDs are strings in my table I use first. But whats about an array of integer? I want to get all lines from the table WORKITEM_CHNGS for a specific set of WI_ITEMIDs in the Workitem Snapshot. But this table has got WI_ID only (which is an integer and not a string). So I cannot use the same approach. I tried, but an exception was thrown because of the mismatching data type.
Using the name of my javaScript array (e.G. myItemIdArray) as parameter value does not work as well.
How can I solve this problem?!
Best regards and thank you in advance!
Artur
The WORKITEM_CHNGS table stores the whole history of work items. If you do not need that, I would recommend using the live table: LIVE_WORKITEM_CNT.
The table named WORKITEMS is not live but it also exposes the ITEM_ID.
If you really need the history, you can always create a data set that can convert the IDs to ITEM_IDs.
The table named WORKITEMS is not live but it also exposes the ITEM_ID.
If you really need the history, you can always create a data set that can convert the IDs to ITEM_IDs.
Thanks for your quick response!
Yes, what I need is the history and therefore I need the data from WORKITEM_CHNGS and not from LIVE_WORKITEM_CNT. :-)
But even when I convert the IDs to ITEM_IDs (that was my first idea) I have to use an array of integer values for a dataset which queries WORKITEM_CHNGS. So the problem I described earlier (cannot use an integer array as dataset parameter) is not solved.
What I can do is to fetch ALL data from WORKITEM_CHNGS by limiting it with projectAreaID and FROM_TIME / TO_TIME parameters. But this really takes a lot of time because I get too much data first which I have to reduce by using a filter (WI_ID IN myIntegerArray) afterwards...
Is there any chance to do the same to an integer array like to an string array? ("'" + myStringArray.join("','") + "'";)
Because I have to use the WORKITEM_CHNGS table...
I would appreciate any help!!!
Yes, what I need is the history and therefore I need the data from WORKITEM_CHNGS and not from LIVE_WORKITEM_CNT. :-)
But even when I convert the IDs to ITEM_IDs (that was my first idea) I have to use an array of integer values for a dataset which queries WORKITEM_CHNGS. So the problem I described earlier (cannot use an integer array as dataset parameter) is not solved.
What I can do is to fetch ALL data from WORKITEM_CHNGS by limiting it with projectAreaID and FROM_TIME / TO_TIME parameters. But this really takes a lot of time because I get too much data first which I have to reduce by using a filter (WI_ID IN myIntegerArray) afterwards...
Is there any chance to do the same to an integer array like to an string array? ("'" + myStringArray.join("','") + "'";)
Because I have to use the WORKITEM_CHNGS table...
I would appreciate any help!!!