Query Joins returning no data

[Web2CRM]

Hi Cognos Gurus,

I need your expertise with the below dimensional requirement:

Prompt Page with 4 single select mandatory filters:
1. IOT
2. Initiative Group
3. Initiative1
4. Initiative2

I need to get the overlap values($M) for the two initiatives 1 & 2 (initiatives are dynamic in report page)

Here's the steps I made:
1.) I created Query1 for Initiative1 and apply detail filter for parameter initiative1(p_init1)
2.) I created Query2 for Initiative2 and apply detail filter for parameter initiative2(p_init2)
3.) I joined the two queries using 1..1 cardinality equal join (Key) to form Query3

My concern is:
1.) I need to display in crosstab view the total value($M) of each initiative selected in the report per IOT using Query3:

                                 Initiative1                                              Initiative2
                   Value1 ($M)      OppNo Count1               Value1 ($M)     OppNo Count2
IOT1                100                      788                               108                      198
IOT2                  79                      909                               131                      345
IOT3                  46                      135                               199                      177


2.) Get the overlap value($M) for each initiative intersection:

                                 Initiative1                                              Initiative2                                                  Overlap
                   Value1 ($M)      OppNo Count1               Value1 ($M)     OppNo Count2                Value ($M)     OppNo Count
IOT1                100                      788                               108                      198                       V1+V2            Cnt1+Cnt2 
IOT2                  79                      909                               131                      345                       V1+V2            Cnt1+Cnt2
IOT3                  46                      135                               199                      177                       V1+V2            Cnt1+Cnt2

Where:
V1 = Value ($M) for Initiative 1
V2 = Value ($M) for Initiative 2
Cnt1 = OppNo Count for Initiative 1
Cnt2 = OppNo Count for Initiative 2

The report shows data when I use one data item only Value1 ($M) and OppNo Count1, however when I add Value2 ($M) and OppNo Count2 in the report page no data displayed (just the header only).


Many Thanks!!!
Web2CRM

[Francis aka khayman]

what i know is that if a data item is not included in your list/crosstab, the data output is such as if the data item is not included in the query. that is why you get no data when Value2 is included. check your join. does query1 alone return data? how about query2 alone?

how about changing your approach? instead of two queries, just one query with filter [Initiative] in (?pinit1?,?pinit2?)

[Web2CRM]

Hi khayman,

Thank you for your prompt response... I included both data items V1 & V1 and Opp Count1 & Opp Count2 in the crosstab report.  That is when these set of data items are in the crosstab no data is displayed.  I ran each query alone (tabular and in the crosstab) and it returns data.

I need to get the total Value $M for each initiative selected where they overlap, so to do it I linked the two queries by Detail Key.  I'll try your advised approach and see it if fit to the requirements.  Will keep you posted.  Thanks! :D

[Web2CRM]

Hi khayman,

I applied your approach but still no data is showing in the crosstab report.  :(

[Francis aka khayman]

can you post sample data? put them side by side then show which column you use to join?

[Web2CRM]

Hi khayman,

Requirement:

Get the overlap value for two initiatives (dynamic based on user selection in prompt page)

Prompt Page:

Initiative 1 (p_init1)
Initiative 2 (p_init2)

Here are the queries I created:

1.) I created Query1 for p_init1 with data items (IOT, Initiative, OppNo, Detail Key, Value$M)
2.) I created Query2 for p_init2 with data items (IOT, Initiative, OppNo, Detail Key, Value$M)
3.) I joined Query1 and Query2 using Detail Key Equal join (1..1)
4.) Query3 contains (IOT, Initiative, OppNo, Detail Key, Value$M) data items from both queries (Query1 & Query2)

Here's what I need to achieve:

Display in crosstab report the Initiatives selected by the user with corresponding Opp Counts and Value$M.

                   Initiative 1                                 Initiative 2
            Value$M      Opp Count             Value$M       Opp Count
IOT           #                  #                           #                    #
Total         #                  #                           #                    #

Thanks!

[Francis aka khayman]

1. what key are you using to join?

3.) I joined Query1 and Query2 using Detail Key Equal join (1..1)

2. check (post) side by side the values Query1 and Query2 are returning. compare the joined keys if they are exactly the same. for example, most proabably you are joining on IOT.... what are the values of IOT in Query1 in Query2. at first glance they might look the same but if for example IOT in Query1 has a value of "USA" while IOT has a value of "USA " then it won't join because of the extra space.

[navissar]

Hi Cognos Gurus,

I need your expertise with the below dimensional requirement:

Are you really doing joins on dimensional, or was that a slip? Because if you are, you shouldn't. a join is a relational concept, and doesn't fit in with dimensional reporting techniques.