COGNOS 10 Report Studio
I am trying to flag the occurrences of a data item in column [Customer] determined by column [Division] and set three values for it in column [flag]. If data item [Customer] has both 'Q' and 'Z' data item values for column [Division] in separate rows I want the value of the data item [flag] set to 2 for every repeated occurrence of data item [Customer] where customer is a member of both 'Q' AND 'Z' Division. If this condition exists I want the value of the [flag] column equal to 2 for all rows where the [Customer] column value occurs. If data item value [Division] for [Customer] is only 'Q' then set [flag] to 1 and if data item value [Division] for [Customer] is only 'Z' then set [flag] to 0. There are thousands of possible values for the [Customer] column data item and multiple rows can contain the desired [customer] / [flag] combinations. How would I create column [flag] in the example below?
I want Customer separated by unique values. Notice Customer X is repeated 3 times and Customer A is repeated twice and the value in the [flag] column for each is 2 - ALL [flag] values where a customer is repeated In a row is 2.
DESIRED RESULTS:
Customer Rep division Count
A : 1 : Q : 1
B : 2 : Z : 0
X : 3 : Q : 2
X : 4 : Z : 2
X : 4 : Q : 2
D : 5 : Z : 0
S : 3 : Q : 2
H : 4 : Q : 1
S : 4 : Z : 2
I am trying to separate repeated occurrences of Customer in from occurrences of a customer based on division. If the customer is in both Z and Q I want to set a flag to 2. If the customer is only in Q (can be multiple times) set the flag to 1 and if the customer is in Z (can be multiple times) set it to 0. I want these values for conditional styling
Quote from: AARDVARK1 on 09 Jun 2015 02:07:11 PM
COGNOS 10 Report Studio
I am trying to flag the occurrences of a data item in column [Customer] determined by column [Division] and set three values for it in column [flag]. If data item [Customer] has both 'Q' and 'Z' data item values for column [Division] in separate rows I want the value of the data item [flag] set to 2 for every repeated occurrence of data item [Customer] where customer is a member of both 'Q' AND 'Z' Division. If this condition exists I want the value of the [flag] column equal to 2 for all rows where the [Customer] column value occurs. If data item value [Division] for [Customer] is only 'Q' then set [flag] to 1 and if data item value [Division] for [Customer] is only 'Z' then set [flag] to 0. There are thousands of possible values for the [Customer] column data item and multiple rows can contain the desired [customer] / [flag] combinations. How would I create column [flag] in the example below?
I want Customer separated by unique values. Notice Customer X is repeated 3 times and Customer A is repeated twice and the value in the [flag] column for each is 2 - ALL [flag] values where a customer is repeated In a row is 2.
DESIRED RESULTS:
Customer Rep division Count
A : 1 : Q : 1
B : 2 : Z : 0
X : 3 : Q : 2
X : 4 : Z : 2
X : 4 : Q : 2
D : 5 : Z : 0
S : 3 : Q : 2
H : 4 : Q : 1
S : 4 : Z : 2
I am trying to separate repeated occurrences of Customer in from occurrences of a customer based on division. If the customer is in both Z and Q I want to set a flag to 2. If the customer is only in Q (can be multiple times) set the flag to 1 and if the customer is in Z (can be multiple times) set it to 0. I want these values for conditional styling
How about changing the technique slightly - having -1 for 'Q' only, 1 for 'Z' only and 0 for both? Would that work?
If so, you could add a calculation with the expression
total(if ([division] = 'Q') then (-1) else if ([division] = 'Z') then (1) else (null) for [Customer])
Cheers!
MF.