Using complex query joints in Cognos Reporting Studio 10

[Martin_Petrov]

Dear all, i would like to ask you for an advice for the following issue i am facing when trying to join multiple queries in Cognos Reporting Studio.
Let's say i want to join two queries and get to a new one as follows:

Client Data + revenue data = client_revenue data

and then try to join the newly created one with a different query as follows:

client_revenue data + planning data = client revenue data_planning data.  I am unable to do that in Cognos Reporting Studio or am i mistaken?

Then, if i intend to go even further and out of the newly created query client revenue data_planing data create a pivot table to change the view to display just say aggregated client view with revenue and planning data and then join this data with another query which contains client data what should i do?
I used Oracle Hyperion in the past and i used to do it fast but with Cognos i struggle.
Thanks in advance!

[MFGF]

I am unable to do that in Cognos Reporting Studio or am i mistaken?

Hi,

You are mistaken. :)

Then, if i intend to go even further and out of the newly created query client revenue data_planing data create a pivot table to change the view to display just say aggregated client view with revenue and planning data and then join this data with another query which contains client data what should i do?

Why are you creating all these queries in your report? If you have a framework that defines how the underlying tables join anyway, and base your report on a package published from that framework, all you then need to do is to drag and drop the desired items into your crosstab.

Did you take any training in Cognos? If not, I can heartily recommend it - you will get an excellent grounding in the basics of how it works and the best and easiest ways of using it.

MF.

[Martin_Petrov]

Hello, thanks for your post. I understand and am aware i can do the joints in Framework manager but it will not work @ 100% for me. I believe i did not explain my case properly. My question is simple - can i do in Cognos a join between two tables,see the result, alter the way the data looks (make a pivot view to include specific items only) and then join it to another table based on a specific criteria. I am not sure framework manager works for me because i have to make joins of multiple keys from one table to multiple keys from another table and get all data from both tables, i believe such join cannot be done that's why i want to alter the data from one of the tables in Cognos (let's say table1) - have a view where i have client1+country1, client2+country2, client3+country3 in one unique row for each (i.e. make a pivot) and then do the join of the pivot with table 2.


table 1         table 2
client1+country1   client2+country2
clien2+country2   clien3+country3
clien2+country2   clien2+country2
client1+country1   client1+country1
clien3+country3   clien3+country3
clien3+country3   client2+country2
clien3+country3   client3+country3
client1+country1   client1+country1
client1+country1   client2+country2
clien2+country2   client3+country3

[MFGF]

Hello, thanks for your post. I understand and am aware i can do the joints in Framework manager but it will not work @ 100% for me. I believe i did not explain my case properly. My question is simple - can i do in Cognos a join between two tables,see the result, alter the way the data looks (make a pivot view to include specific items only) and then join it to another table based on a specific criteria. I am not sure framework manager works for me because i have to make joins of multiple keys from one table to multiple keys from another table and get all data from both tables, i believe such join cannot be done that's why i want to alter the data from one of the tables in Cognos (let's say table1) - have a view where i have client1+country1, client2+country2, client3+country3 in one unique row for each (i.e. make a pivot) and then do the join of the pivot with table 2.


table 1         table 2
client1+country1   client2+country2
clien2+country2   clien3+country3
clien2+country2   clien2+country2
client1+country1   client1+country1
clien3+country3   clien3+country3
clien3+country3   client2+country2
clien3+country3   client3+country3
client1+country1   client1+country1
client1+country1   client2+country2
clien2+country2   client3+country3

You can define joins using single or multiple part keys - both in FM and in Report Studio. I'm struggling to understand why you don't think FM will do what you require. Why do you believe multi-key joins are not possible in FM?

What do you mean by "Make a pivot"?

MF.

[Martin_Petrov]

Hi, i am using a simple example of what i am trying to do. I am not sure based on what logic FM will decide how to define the multiple joints. The table for which i am saying i should make a pivot has let's say sub client revenue data on each row, in other words client1+country1 can be found on many different rows and the sub client in each row will be different so will be the revenue. Thus there is no way for me to do a joint with FM with these conditions in place (i think). So what i mean by doing a pivot is a reference to excel - i have a table with the key item (client1+country1)on one unique row and revenue aggregated on key level (summing up all sub client revenue) and then join it with another table which has the same key on multiple rows.

Do you understand my point now?

[MFGF]

Hi, i am using a simple example of what i am trying to do. I am not sure based on what logic FM will decide how to define the multiple joints. The table for which i am saying i should make a pivot has let's say sub client revenue data on each row, in other words client1+country1 can be found on many different rows and the sub client in each row will be different so will be the revenue. Thus there is no way for me to do a joint with FM with these conditions in place (i think). So what i mean by doing a pivot is a reference to excel - i have a table with the key item (client1+country1)on one unique row and revenue aggregated on key level (summing up all sub client revenue) and then join it with another table which has the same key on multiple rows.

Do you understand my point now?

Hi,

I'm struggling to see why you think this is an issue in FM. You can create a relationship based on as many items (on each side of the join) as you like. It's easy to have Client and Country from one query subject joining to Client and Country from another. Plus you can press the ellipsis button at the bottom and convert the simple join relationship into an expression which can be as simple or complex as you care to make it. Creating a relationship based on two items wouldn't need that though. What am I missing?

MF.

[Martin_Petrov]

Hello, i think i will get to a very practical example in order to be able to explain properly as it seems i am failing :)

So, i have table 1 to join with table 2 (see the attachment) and i want to join using key client & country. I want to see the revenue for each person i.e. revenue field from table 1 to be transferred to table 2. The problem is that in table 1 you have the key (client & country) in more than 1 row, i.e. the value is not unique - because you have a subclient as well. When i was referring to the pivot was simply to eliminate the sub client view and have the revenue aggregated on key level in each unique row (in other words have only one row for client1+country1 combination of 200+50+20 = 270 revenue units). I am not sure how can FM do the joints  (aggregate and transfer the revenue data into table 2 considering there are same keys on multiple rows in both tables). I hope it's clear now.

[bdbits]

You keep putting the scenario in terms of the report. Think in terms of tables and their relationships (it appears you are relational). If those are defined properly in FM, aggregation at any 'level' is automatic in Cognos. You are just used to less capable products.  ;D

Like MFGF, I have seen nothing that you cannot model in FM. As long as the model reflects the underlying tables correctly, Cognos will figure out how to join and aggregate for you.

This is relatively easy to test out for yourself, just create a small test package with your tables in it, then use Report Studio or Workspace Advanced to try it out. You can view the generated SQL if you want to validate it.

[Martin_Petrov]

Thank you sir, i will give it a try and hopefully it will work. (Spot on with the comment about usage of less capable products) :) I hope this is the case, i will keep you posted (if you are interested if i succeeded or not.

[mayank_sanghvi]

Hi Martin,

This video is help you to create joins in Framework Manager and Report Studio. But if you are looking for performance then you need to do the same at FM level or if possible create at database level (using view and use view in FM).

Other Cognos Expert kindly share your point of view.

https://www.youtube.com/watch?v=K_6D5ZgbL9M