Cognos Analytics

Hi,

i have one scenario where i have two Queries one is lets say A and another one is B. i have ticket id dataitem in Query A and which is returning 5 values in it lets say 101,102,103,104,105 not exactly 5 some xxxxx numbers but just for example. and i have same  ticket id field in Query B woth same kind of values but this pulls lot of ids as it has no filters and should not be except id filter. 
Now what i need is i want to filter the Query B with Query A returned id values without join them i know i can join them based on this id field and i can get it filtered Query C after join and get the result but as i told you Query B has millions of records i am never getting result report is failing after running for hours as Query B has no filters.

So instead of join can i filter the Query A returned values to Query B so that its runs faster and gets result in mins.

for eg: Query A returned values 101,102,103,104,105

i can manually take these ids and put them in Query B runs faster but without manual dynamically can Query B gets filtered with Query A returned values?

and i also tried this created prompt from Query A to pick returned id values and when we run this report select all then run report is filtering Query B(kept this prompt on id field in filter) and getting fast result but this is only works when you have 1000 or below ids in prompt. Throwing error when we have more than 1000 values in prompt and that too whenever the report runs we need to hit select all everytime to select newly populated values in prompt with this way.

So can anyone Please let me know any ways to achieve this

thanks

------------------------------
harris jayaraj
------------------------------
#CognosAnalyticswithWatson

Hi Harris,

You can do this by creating  filter logically:   QueryB.ticket_id in ( QueryA.ticket_id)
The generated SQL Pattern is;
select ….
from query_b
where ticket_id in ( select distinct ticket_id from query_a )

This can also be done for multiple columns, in that case you would do:
row ( QueryB.ticket_id, QueryB.other_id) in ( QueryA.ticket_id, QueryA.other_id)
This last expression will give an error when validating in Data Modules, but the execution is just fine. (another bug to fix).

// Henk

------------------------------
HENK CAZEMIER
------------------------------

Original Message:
Sent: Thu July 09, 2020 02:36 PM
From: harris jayaraj
Subject: How to pass One Query data item values to filter another Query before Join in between

Hi,

i have one scenario where i have two Queries one is lets say A and another one is B. i have ticket id dataitem in Query A and which is returning 5 values in it lets say 101,102,103,104,105 not exactly 5 some xxxxx numbers but just for example. and i have same  ticket id field in Query B woth same kind of values but this pulls lot of ids as it has no filters and should not be except id filter. 
Now what i need is i want to filter the Query B with Query A returned id values without join them i know i can join them based on this id field and i can get it filtered Query C after join and get the result but as i told you Query B has millions of records i am never getting result report is failing after running for hours as Query B has no filters.

So instead of join can i filter the Query A returned values to Query B so that its runs faster and gets result in mins.

for eg: Query A returned values 101,102,103,104,105

i can manually take these ids and put them in Query B runs faster but without manual dynamically can Query B gets filtered with Query A returned values?

and i also tried this created prompt from Query A to pick returned id values and when we run this report select all then run report is filtering Query B(kept this prompt on id field in filter) and getting fast result but this is only works when you have 1000 or below ids in prompt. Throwing error when we have more than 1000 values in prompt and that too whenever the report runs we need to hit select all everytime to select newly populated values in prompt with this way.

So can anyone Please let me know any ways to achieve this

thanks

------------------------------
harris jayaraj
------------------------------
#CognosAnalyticswithWatson

​This solution will definitely work, but you may also have to adjust the cross-join property on the queries to be allowed.

------------------------------
Mike Teegarden
------------------------------

Original Message:
Sent: Fri July 10, 2020 12:49 AM
From: HENK CAZEMIER
Subject: How to pass One Query data item values to filter another Query before Join in between

Hi Harris,

You can do this by creating  filter logically:   QueryB.ticket_id in ( QueryA.ticket_id)
The generated SQL Pattern is;
select ….
from query_b
where ticket_id in ( select distinct ticket_id from query_a )

This can also be done for multiple columns, in that case you would do:
row ( QueryB.ticket_id, QueryB.other_id) in ( QueryA.ticket_id, QueryA.other_id)
This last expression will give an error when validating in Data Modules, but the execution is just fine. (another bug to fix).

// Henk

------------------------------
HENK CAZEMIER

Original Message:
Sent: Thu July 09, 2020 02:36 PM
From: harris jayaraj
Subject: How to pass One Query data item values to filter another Query before Join in between

Hi,

i have one scenario where i have two Queries one is lets say A and another one is B. i have ticket id dataitem in Query A and which is returning 5 values in it lets say 101,102,103,104,105 not exactly 5 some xxxxx numbers but just for example. and i have same  ticket id field in Query B woth same kind of values but this pulls lot of ids as it has no filters and should not be except id filter. 
Now what i need is i want to filter the Query B with Query A returned id values without join them i know i can join them based on this id field and i can get it filtered Query C after join and get the result but as i told you Query B has millions of records i am never getting result report is failing after running for hours as Query B has no filters.

So instead of join can i filter the Query A returned values to Query B so that its runs faster and gets result in mins.

for eg: Query A returned values 101,102,103,104,105

i can manually take these ids and put them in Query B runs faster but without manual dynamically can Query B gets filtered with Query A returned values?

and i also tried this created prompt from Query A to pick returned id values and when we run this report select all then run report is filtering Query B(kept this prompt on id field in filter) and getting fast result but this is only works when you have 1000 or below ids in prompt. Throwing error when we have more than 1000 values in prompt and that too whenever the report runs we need to hit select all everytime to select newly populated values in prompt with this way.

So can anyone Please let me know any ways to achieve this

thanks

------------------------------
harris jayaraj
------------------------------
#CognosAnalyticswithWatson

Hi Mike,
This pattern of filtering does not introduce a join and there is no check in the code for the setting of the cross-join governor.
Where this does break down is if you need to apply a filter to the Query_A side, in which case you end up having to embed that filter in Query_A. If that happens frequently then you end up with a collection of extra queries.
// Henk

------------------------------
HENK CAZEMIER
------------------------------

Original Message:
Sent: Fri July 10, 2020 09:00 AM
From: Mike Teegarden
Subject: How to pass One Query data item values to filter another Query before Join in between

​This solution will definitely work, but you may also have to adjust the cross-join property on the queries to be allowed.

------------------------------
Mike Teegarden

Original Message:
Sent: Fri July 10, 2020 12:49 AM
From: HENK CAZEMIER
Subject: How to pass One Query data item values to filter another Query before Join in between

Hi Harris,

You can do this by creating  filter logically:   QueryB.ticket_id in ( QueryA.ticket_id)
The generated SQL Pattern is;
select ….
from query_b
where ticket_id in ( select distinct ticket_id from query_a )

This can also be done for multiple columns, in that case you would do:
row ( QueryB.ticket_id, QueryB.other_id) in ( QueryA.ticket_id, QueryA.other_id)
This last expression will give an error when validating in Data Modules, but the execution is just fine. (another bug to fix).

// Henk

------------------------------
HENK CAZEMIER

Original Message:
Sent: Thu July 09, 2020 02:36 PM
From: harris jayaraj
Subject: How to pass One Query data item values to filter another Query before Join in between

Hi,

i have one scenario where i have two Queries one is lets say A and another one is B. i have ticket id dataitem in Query A and which is returning 5 values in it lets say 101,102,103,104,105 not exactly 5 some xxxxx numbers but just for example. and i have same  ticket id field in Query B woth same kind of values but this pulls lot of ids as it has no filters and should not be except id filter. 
Now what i need is i want to filter the Query B with Query A returned id values without join them i know i can join them based on this id field and i can get it filtered Query C after join and get the result but as i told you Query B has millions of records i am never getting result report is failing after running for hours as Query B has no filters.

So instead of join can i filter the Query A returned values to Query B so that its runs faster and gets result in mins.

for eg: Query A returned values 101,102,103,104,105

i can manually take these ids and put them in Query B runs faster but without manual dynamically can Query B gets filtered with Query A returned values?

and i also tried this created prompt from Query A to pick returned id values and when we run this report select all then run report is filtering Query B(kept this prompt on id field in filter) and getting fast result but this is only works when you have 1000 or below ids in prompt. Throwing error when we have more than 1000 values in prompt and that too whenever the report runs we need to hit select all everytime to select newly populated values in prompt with this way.

So can anyone Please let me know any ways to achieve this

thanks

------------------------------
harris jayaraj
------------------------------
#CognosAnalyticswithWatson

Henk Thanks, i tried what you said applied logical filter in Query B as mentioned QueryB.ticket_id in ( QueryA.ticket_id) and when i run view tabular of this QueryB never returning any result i am testing with just one ticket id filterd on Query A but Query B is not returning any data keeps on running if its filtering by Query A means it should return one ticket fastly but Query B is not filtering by Query A ticket value with this logical filter and i tried Mikes suggestion kept the cross join property as allow but still not getting filterd Query B by Query A id value.

FYI, Query A ticket id & Query B ticket ids are coming from different tables of pkg and they dont have joins at pkg level and i am testing all this at report level.

thanks
venkat

------------------------------
harris jayaraj
------------------------------

Original Message:
Sent: Fri July 10, 2020 10:15 AM
From: HENK CAZEMIER
Subject: How to pass One Query data item values to filter another Query before Join in between

Hi Mike,
This pattern of filtering does not introduce a join and there is no check in the code for the setting of the cross-join governor.
Where this does break down is if you need to apply a filter to the Query_A side, in which case you end up having to embed that filter in Query_A. If that happens frequently then you end up with a collection of extra queries.
// Henk

------------------------------
HENK CAZEMIER

Original Message:
Sent: Fri July 10, 2020 09:00 AM
From: Mike Teegarden
Subject: How to pass One Query data item values to filter another Query before Join in between

​This solution will definitely work, but you may also have to adjust the cross-join property on the queries to be allowed.

------------------------------
Mike Teegarden

Original Message:
Sent: Fri July 10, 2020 12:49 AM
From: HENK CAZEMIER
Subject: How to pass One Query data item values to filter another Query before Join in between

Hi Harris,

You can do this by creating  filter logically:   QueryB.ticket_id in ( QueryA.ticket_id)
The generated SQL Pattern is;
select ….
from query_b
where ticket_id in ( select distinct ticket_id from query_a )

This can also be done for multiple columns, in that case you would do:
row ( QueryB.ticket_id, QueryB.other_id) in ( QueryA.ticket_id, QueryA.other_id)
This last expression will give an error when validating in Data Modules, but the execution is just fine. (another bug to fix).

// Henk

------------------------------
HENK CAZEMIER

Original Message:
Sent: Thu July 09, 2020 02:36 PM
From: harris jayaraj
Subject: How to pass One Query data item values to filter another Query before Join in between

Hi,

i have one scenario where i have two Queries one is lets say A and another one is B. i have ticket id dataitem in Query A and which is returning 5 values in it lets say 101,102,103,104,105 not exactly 5 some xxxxx numbers but just for example. and i have same  ticket id field in Query B woth same kind of values but this pulls lot of ids as it has no filters and should not be except id filter. 
Now what i need is i want to filter the Query B with Query A returned id values without join them i know i can join them based on this id field and i can get it filtered Query C after join and get the result but as i told you Query B has millions of records i am never getting result report is failing after running for hours as Query B has no filters.

So instead of join can i filter the Query A returned values to Query B so that its runs faster and gets result in mins.

for eg: Query A returned values 101,102,103,104,105

i can manually take these ids and put them in Query B runs faster but without manual dynamically can Query B gets filtered with Query A returned values?

and i also tried this created prompt from Query A to pick returned id values and when we run this report select all then run report is filtering Query B(kept this prompt on id field in filter) and getting fast result but this is only works when you have 1000 or below ids in prompt. Throwing error when we have more than 1000 values in prompt and that too whenever the report runs we need to hit select all everytime to select newly populated values in prompt with this way.

So can anyone Please let me know any ways to achieve this

thanks

------------------------------
harris jayaraj
------------------------------
#CognosAnalyticswithWatson

Have you tried making sure that the fields actually match or if a trim or lower function is needed to ensure that the data is matching up?

------------------------------
Mike Teegarden
------------------------------

Original Message:
Sent: Fri July 10, 2020 01:15 PM
From: harris jayaraj
Subject: How to pass One Query data item values to filter another Query before Join in between

Henk Thanks, i tried what you said applied logical filter in Query B as mentioned QueryB.ticket_id in ( QueryA.ticket_id) and when i run view tabular of this QueryB never returning any result i am testing with just one ticket id filterd on Query A but Query B is not returning any data keeps on running if its filtering by Query A means it should return one ticket fastly but Query B is not filtering by Query A ticket value with this logical filter and i tried Mikes suggestion kept the cross join property as allow but still not getting filterd Query B by Query A id value.

FYI, Query A ticket id & Query B ticket ids are coming from different tables of pkg and they dont have joins at pkg level and i am testing all this at report level.

thanks
venkat

------------------------------
harris jayaraj

Original Message:
Sent: Fri July 10, 2020 10:15 AM
From: HENK CAZEMIER
Subject: How to pass One Query data item values to filter another Query before Join in between

Hi Mike,
This pattern of filtering does not introduce a join and there is no check in the code for the setting of the cross-join governor.
Where this does break down is if you need to apply a filter to the Query_A side, in which case you end up having to embed that filter in Query_A. If that happens frequently then you end up with a collection of extra queries.
// Henk

------------------------------
HENK CAZEMIER

Original Message:
Sent: Fri July 10, 2020 09:00 AM
From: Mike Teegarden
Subject: How to pass One Query data item values to filter another Query before Join in between

​This solution will definitely work, but you may also have to adjust the cross-join property on the queries to be allowed.

------------------------------
Mike Teegarden

Original Message:
Sent: Fri July 10, 2020 12:49 AM
From: HENK CAZEMIER
Subject: How to pass One Query data item values to filter another Query before Join in between

Hi Harris,

You can do this by creating  filter logically:   QueryB.ticket_id in ( QueryA.ticket_id)
The generated SQL Pattern is;
select ….
from query_b
where ticket_id in ( select distinct ticket_id from query_a )

This can also be done for multiple columns, in that case you would do:
row ( QueryB.ticket_id, QueryB.other_id) in ( QueryA.ticket_id, QueryA.other_id)
This last expression will give an error when validating in Data Modules, but the execution is just fine. (another bug to fix).

// Henk

------------------------------
HENK CAZEMIER

Original Message:
Sent: Thu July 09, 2020 02:36 PM
From: harris jayaraj
Subject: How to pass One Query data item values to filter another Query before Join in between

Hi,

i have one scenario where i have two Queries one is lets say A and another one is B. i have ticket id dataitem in Query A and which is returning 5 values in it lets say 101,102,103,104,105 not exactly 5 some xxxxx numbers but just for example. and i have same  ticket id field in Query B woth same kind of values but this pulls lot of ids as it has no filters and should not be except id filter. 
Now what i need is i want to filter the Query B with Query A returned id values without join them i know i can join them based on this id field and i can get it filtered Query C after join and get the result but as i told you Query B has millions of records i am never getting result report is failing after running for hours as Query B has no filters.

So instead of join can i filter the Query A returned values to Query B so that its runs faster and gets result in mins.

for eg: Query A returned values 101,102,103,104,105

i can manually take these ids and put them in Query B runs faster but without manual dynamically can Query B gets filtered with Query A returned values?

and i also tried this created prompt from Query A to pick returned id values and when we run this report select all then run report is filtering Query B(kept this prompt on id field in filter) and getting fast result but this is only works when you have 1000 or below ids in prompt. Throwing error when we have more than 1000 values in prompt and that too whenever the report runs we need to hit select all everytime to select newly populated values in prompt with this way.

So can anyone Please let me know any ways to achieve this

thanks

------------------------------
harris jayaraj
------------------------------
#CognosAnalyticswithWatson

Yes field values match as i tried one ticket filtered Query A which i took and kept it in Query B and ran view tabular of it returned the ticket both fields values are same but when i do id in (Query A.id) in Query B is not returning anything keeps on running just for one ticket.

thanks

------------------------------
harris jayaraj
------------------------------

Original Message:
Sent: Fri July 10, 2020 01:52 PM
From: Mike Teegarden
Subject: How to pass One Query data item values to filter another Query before Join in between

Have you tried making sure that the fields actually match or if a trim or lower function is needed to ensure that the data is matching up?

------------------------------
Mike Teegarden

Original Message:
Sent: Fri July 10, 2020 01:15 PM
From: harris jayaraj
Subject: How to pass One Query data item values to filter another Query before Join in between

Henk Thanks, i tried what you said applied logical filter in Query B as mentioned QueryB.ticket_id in ( QueryA.ticket_id) and when i run view tabular of this QueryB never returning any result i am testing with just one ticket id filterd on Query A but Query B is not returning any data keeps on running if its filtering by Query A means it should return one ticket fastly but Query B is not filtering by Query A ticket value with this logical filter and i tried Mikes suggestion kept the cross join property as allow but still not getting filterd Query B by Query A id value.

FYI, Query A ticket id & Query B ticket ids are coming from different tables of pkg and they dont have joins at pkg level and i am testing all this at report level.

thanks
venkat

------------------------------
harris jayaraj

Original Message:
Sent: Fri July 10, 2020 10:15 AM
From: HENK CAZEMIER
Subject: How to pass One Query data item values to filter another Query before Join in between

Hi Mike,
This pattern of filtering does not introduce a join and there is no check in the code for the setting of the cross-join governor.
Where this does break down is if you need to apply a filter to the Query_A side, in which case you end up having to embed that filter in Query_A. If that happens frequently then you end up with a collection of extra queries.
// Henk

------------------------------
HENK CAZEMIER

Original Message:
Sent: Fri July 10, 2020 09:00 AM
From: Mike Teegarden
Subject: How to pass One Query data item values to filter another Query before Join in between

​This solution will definitely work, but you may also have to adjust the cross-join property on the queries to be allowed.

------------------------------
Mike Teegarden

Original Message:
Sent: Fri July 10, 2020 12:49 AM
From: HENK CAZEMIER
Subject: How to pass One Query data item values to filter another Query before Join in between

Hi Harris,

You can do this by creating  filter logically:   QueryB.ticket_id in ( QueryA.ticket_id)
The generated SQL Pattern is;
select ….
from query_b
where ticket_id in ( select distinct ticket_id from query_a )

This can also be done for multiple columns, in that case you would do:
row ( QueryB.ticket_id, QueryB.other_id) in ( QueryA.ticket_id, QueryA.other_id)
This last expression will give an error when validating in Data Modules, but the execution is just fine. (another bug to fix).

// Henk

------------------------------
HENK CAZEMIER

Original Message:
Sent: Thu July 09, 2020 02:36 PM
From: harris jayaraj
Subject: How to pass One Query data item values to filter another Query before Join in between

Hi,

i have one scenario where i have two Queries one is lets say A and another one is B. i have ticket id dataitem in Query A and which is returning 5 values in it lets say 101,102,103,104,105 not exactly 5 some xxxxx numbers but just for example. and i have same  ticket id field in Query B woth same kind of values but this pulls lot of ids as it has no filters and should not be except id filter. 
Now what i need is i want to filter the Query B with Query A returned id values without join them i know i can join them based on this id field and i can get it filtered Query C after join and get the result but as i told you Query B has millions of records i am never getting result report is failing after running for hours as Query B has no filters.

So instead of join can i filter the Query A returned values to Query B so that its runs faster and gets result in mins.

for eg: Query A returned values 101,102,103,104,105

i can manually take these ids and put them in Query B runs faster but without manual dynamically can Query B gets filtered with Query A returned values?

and i also tried this created prompt from Query A to pick returned id values and when we run this report select all then run report is filtering Query B(kept this prompt on id field in filter) and getting fast result but this is only works when you have 1000 or below ids in prompt. Throwing error when we have more than 1000 values in prompt and that too whenever the report runs we need to hit select all everytime to select newly populated values in prompt with this way.

So can anyone Please let me know any ways to achieve this

thanks

------------------------------
harris jayaraj
------------------------------
#CognosAnalyticswithWatson