IBM Cognos 8 Platform > Report Studio

How to create a variable that returns a Y/N given certain conditions?

(1/1)

thoros:
Hey Guys,

I'm pretty new to Cognos and I was wondering if anyone knows how I might accomplish a certain task:

In query A, we have customer information.
In query B, we have pricing records.

I need to create a variable that returns a 'Y' for each Customer record returned in query A as long as that customer is found in query B.

Does anyone know how that might be handled using a boolean variable? If I cannot accomplish this through a boolean variable, then how else can I create this?

Thanks a ton!

MFGF:

--- Quote from: thoros on 09 Jun 2021 10:52:37 am ---Hey Guys,

I'm pretty new to Cognos and I was wondering if anyone knows how I might accomplish a certain task:

In query A, we have customer information.
In query B, we have pricing records.

I need to create a variable that returns a 'Y' for each Customer record returned in query A as long as that customer is found in query B.

Does anyone know how that might be handled using a boolean variable? If I cannot accomplish this through a boolean variable, then how else can I create this?

Thanks a ton!

--- End quote ---

Hi,

One important question that occurs is what do you need this variable for? Is it to display the Y values in the report? To conditionally show or hide something? To conditionally highlight something? Something else?
I'm just trying to get a feel for whether you need a variable or something different.

In terms of what you're asking, you could add a join object in the Query Explorer that joins your two queries using an inner join, and use a third query to show the joined results. Any rows in this third query would then by definition have records in both of your original queries. You could then create a calculation or a variable (or whatever you need) based off this third query?

Cheers!

MF.

thoros:

--- Quote ---Hi,

One important question that occurs is what do you need this variable for? Is it to display the Y values in the report? To conditionally show or hide something? To conditionally highlight something? Something else?
I'm just trying to get a feel for whether you need a variable or something different.

In terms of what you're asking, you could add a join object in the Query Explorer that joins your two queries using an inner join, and use a third query to show the joined results. Any rows in this third query would then by definition have records in both of your original queries. You could then create a calculation or a variable (or whatever you need) based off this third query?

Cheers!

MF.

--- End quote ---

Hey MF,

Appreciate you taking the time to respond! To answer your question, I need this variable to simply return a 'Y' for each customer generated in query A granted they have an appearance in query B! Some of the customers in query A will not having pricing records in query B; in that case, we can return an 'N' or just a blank.

The final query, query C, will look like this below:

Customer#     Pricing Records?

12345                 Y
67891                 Y
54321                 N

I think the join would work however my hesitation is the syntax for the boolean variable. I'm not really sure how to write an expression that just says:
IF queryA.Company# and queryA.Cust# found in queryB then set boolean variable to 'Y' else set boolean variable to 'N'

Let me know if I can provide further details,
TJ

MFGF:

--- Quote from: thoros on 10 Jun 2021 08:11:15 am ---Hey MF,

Appreciate you taking the time to respond! To answer your question, I need this variable to simply return a 'Y' for each customer generated in query A granted they have an appearance in query B! Some of the customers in query A will not having pricing records in query B; in that case, we can return an 'N' or just a blank.

The final query, query C, will look like this below:

Customer#     Pricing Records?

12345                 Y
67891                 Y
54321                 N

I think the join would work however my hesitation is the syntax for the boolean variable. I'm not really sure how to write an expression that just says:
IF queryA.Company# and queryA.Cust# found in queryB then set boolean variable to 'Y' else set boolean variable to 'N'

Let me know if I can provide further details,
TJ

--- End quote ---

Hi,

If it's just to display an item with a Y or N, then a variable (or at least what Cognos terms a variable) isn't needed here. The approach I would take is:

In the Query Explorer, add a Join object from the toolbar. Drag your Query A and Query B into the inputs of the join, and add a new query (C) as the output of the join. Pick your item or items to join the queries on, and set the cardinality of the join to be 1..1 <---> 0..1 so that you get all rows from Query A regardless of whether there are corresponding rows in Query B (a left outer join). Make sure Query C contains all the items from Query A and at least one item from Query B.

Base your reporting object (a List I'm guessing) on Query C, and add a query calculation that has the expression

if ([the item that came in to Query C from Query B] is null) then ('N') else ('Y)

Then use this query calculation in your list.

Hopefully that should give you what you need?

Cheers!

MF.

Navigation

[0] Message Index