Hi,

I'm working on a report template that needs to combine data from various linked work items. I've created datasets for the information in each of the work item types involved, and also for the link types (relatedworkitem and blocksworkitem) that join them all. But combining these using joint datasets is a real hassle - joining one WI to its links, joining the results to the second WI, joining that to the other links, joining that to the third WI, ...
And for one of the links I can't even do that - because the souce & targets IDs on relatedworkitem may appear either way round depending on which one it was created from, I've had to do an Advanced dataset that takes an input parameter, finds links with that in either column, and uses a conditional expression to return a calculated value of the ID that doesn't match the parameter - works fine with a nested report, where I can bind the parameter to the outer row's ID, but the joint dataset won't let me specify the argument as one side of the join...
If I was working against a SQL database, I'd just write a query that joins all the tables involed, using a union to handle both halves of the relatedworkitem, ... simple. Is it possible to do something similar against the Facts in Jazz' data warehouse? I tried a Jazz Advanced Data Set, but that still only lets me select a single table name in the Fact Information. And, of course, work item information and link information are stored in different snapshots so I'd need it to be based on more than one data source...
The only other option I've thought of is to save the entire contents of the work items and links datasets to javascript variables in their individual datasets' onFetch, and use a scripted dataset to combine them. I'm already doing something similar in order to populate the work items with their custom attributes, and have all the relevant work item data cached in memory, so I'd only need to add the link info. But is there no easier way?


Andy.