How to create a union in report studio 1

[geethkal12]

Hello All,

I need to create a report using UNION relationship object.
Please suggest me the procedure of using UNION in cognos report studiousing the below DB2 query..

select "NM" from "DTTSKC01"."TTS_FREIGHT_FORWARDER_REF"
where "NM" IN(Select distinct "CLMN_NM" from "DTTSKC01"."TTS_BUS_DETAILS))
UNION
(Select "NM"
from "DTTSKC01"."TTS_FREIGHT_FORWARDER_REF"
where "NM" IN(Select Distinct "TRADING_NM" from "DTTSKC01"."TTS_BUS_DETAILS")).

thanks a lot
Geetha

 

[AngelaCognos]

I think this post got overlooked. Hopefully it's not too late to provide some help.

Create a Query (Query1) and put in it the 'CLMN_NM' data item from your "DTTSKC01"."TTS_BUS_DETAILS" source.

Create another Query (Query2) and put in it the 'NM' data item from your "DTTSKC01"."TTS_FREIGHT_FORWARDER_REF"
source.

Create a third Query (Query3) and join the first two (default inner join) and use either 'NM' value.

Now, do it all again for the second part of the union, resulting in queries 4, 5, and 6. (I see that Query2 could be re-used as it's the exact same source - that's up to you.)

Then, create another Query (Query7) and use the UNION icon on the Query explorer and drag queries 3 and 6 into the shortcutes for the union.

Convoluted? Yup. But it should work.

 

[KristieLee1]

Thank you Angela. I am doing a much simpler join, but am getting the error, "Unable to find query information" for all the items in my report. Any suggestions on this?
I have both the label and the names the same, and have verified that the data types are the same in both queries. I have all my data items in the same order as well.
Any help would be appreciated. Thank you.

 

[AngelaCognos]

Hmm, I put 'Unable to find query information' in at IBM and got nothing of use back so I don't know what that is referring to. That error doesn't sound like a data type error. Does it give you an error number that could be researched at

http://www-01.ibm.com/support/advsrch.wss?rs=3442&loc=en_US#advancedsearch

?

 

[KristieLee1]

The error message is RSV-VAL-004. I looked in the IBM support docs as well, and found nothing.
Of course...this is my first union...so I expected it to be messed up. lol
Do you know of an easy way to see the data types via report studio? I don't have access to the raw data at this point, only report studio.
Thank you!

 

[KristieLee1]

I viewed tabular data for each query, and compared the actual data values of each to find the discrepancy.
I also didn't know that I could put null or zero values in the expression in order to get the data types the same.

 

[AngelaCognos]

So you're all solved now?

FYI, to see an item's data type in Report Studio, when looking at the DataSource pane, right click a data item and it'll tell you if it's a charachter, integer, whatever.

 

[KristieLee1]

Yes...after much pain and agony...I thing I finally got the union to work.
Is there anything that can be done in Framework manager to make these unions easier? I had none of these problems when doing unions in Access. I am really struggling because "they" want Cognos to be all "front end" type work..and I'm used to being able to set up my own relationships, my own joins, and I didn't have these issues in Access. I love Cognos...I'm just still too new I guess.

You are AWESOME! I hope soon I can be as good as you! Cognos is cool, and I am anxious to learn more and more. I've taken the meta data modeling and framework courses via Cognos, but management has chosen someone in the IT department to build and manage the datawarehouse and framework manager stuff.

Anyway...thanks so much for answering my posts. I appreciate your time!

 

[AngelaCognos]

You are most welcome. And yes, you can use the database or Framework Manager to set up unions (think 'Views'). In your case you'd need to talk to IT but I'm pretty certain it's possible.