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 |
4 answers
|
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 |
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. |
|
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!!! |