Exclude values on report when certain characteristic combinations occur

[Former Member]

Hello,

I have a query in which I would like to exlude certain characteristic combinations from being in the report body.

For example, when I run my query I would like to exclude values from my report when:

The PLANT is a certain value AND when the SALES GROUP is a certain value. (They both need to meet the criteria)

If the plant is 0007 and Sales Group is Unassigned then don't show the corresponding values on the report.

If the plant is 0007 and Sales Group is 030 then the values are displayed.

So that is the distinction.

I am not sure how to achieve this. Any assistance would be great!

Thanks,

Nick

Message was edited by:

Nick Bertz

[former_member188975 · Accepted solution]

Hi Nick,

One way to go about this would be to have formula variables by replacement path pulling in value of Plant and Sales Group. Then instead of base KF, use formulas that check if Plant (formula variable) is 7 and Sales group ==0, the output is 0, else output is the KF. Then in the query properties, suppress the 0.

Hope this helps...

[Former Member · Accepted solution]

Bhanu,

I'll give your suggestion a try. That technique is new to me, so I'll post back if I need guidance.

Thanks,

Nick

[Former Member · Accepted solution]

Hi Bhanu,

I am having some trouble.

I have the following formula (Using formula variables with replacement path):

( ( 'Returns the Plant' == 42 ) AND ( 'Returns the Sales Group' == 0 ) )

That expression evaluates to 'TRUE' when The plant is 42 and the sales group is 0. So for all other combinations it evaluates to false.

When it evaluates to TRUE it returns a 1.

When it evaluates to FALSE it returns a 0.

I wish it was the other way around (True returns 0 and false return 1), because then it'd be easier to configure.

Because I want to set the value for the key figure to 0 when it evaluates to TRUE I am having problems. I cannot figure out how to make a formula that evaluates to FALSE for the specific combination. If I could figure out how to make it false it'd be easy. The problem is probably because I am using 2 conditions instead of one.

Any advice or ideas? (I hope i was clear enough with my dilemma.)

Nick

[former_member188975 · Accepted solution]

Hi Nick,

I think I the issue...try using the NOT function (expression). That should make 0 as 1 and 1 as 0.

Hope this helps...

[Former Member · Accepted solution]

Bhanu,

That did it. I'll post back with more details later today when I finish the second part of the expression.

Thanks!

Nick

[Former Member · Accepted solution]

Summary:

There was a report which a user requested have certain data removed from it. The data that should be removed occured when PLANT = 42 and SALES GROUP = 0 (Not Assigned).

To achieve this, in BEx Query Designer, I created a Formula Variable of type Replacement for both PLANT and SALES GROUP. The purpose of the Replacement Formula Variables was to return the values (the keys of the characteristic) that I needed to use in my logic.

Now that the formula variables are created I created a new formula in the key figure structure on the report.

I defined it as follows:

NOT ( ( 'Returns the Plant' == 42 ) AND ( 'Returns the Sales Group' == 0 ) ) * 'Revenue'

Without the NOT part of the formula, the portion in Parenthesis evaluates to equal 1 (TRUE) when that characteristic combination occurs in the report. I need it to equal 0, so that is where the NOT comes in. It makes that 1 a 0 essentially (FALSE).

So when that characteristic combination occurs the arithmetic is as follows. 0 * Revenue, which evaluates to 0, thus the key figure is essentially eliminated from impacting the results.

It should also be noted that this sort of evaluation with Replacement Path formula variables, only works when the characteristics are included in the report body. So if a user removes one of those characteristics at runtime or designtime, then the Formula that you created in the Key Figures structure will not evaluate a value.

Thanks again Bhanu!

Nick

[Former Member]

Nick,

Restrict the Sales group with '#' and exclude it from selection. then the combination will not be displayed.

[Former Member]

Hi Venkat,

Perhaps my example was to scaled down. I do have other plants that have the sales group as unassigned. Which I still need on the report.

So unfortunately your suggestion would rule those values out.

Any other ideas?

Another example: (in this scenario I want all rows except the one where the plant is 0007 AND the sales group is unassigned (So I don't want Row 3))

Plant Sales Group Volume

0003 xx# xx10

0003 027 xx15

0007 xx# xx20

0007 030 xx10

0007 027 xx20

0009 xx# xx15

0009 015 xx10

0009 030 xx15

Nick