Group Selection based on Formula

[tkaz]

CR 11
SQL DB

I have a report that has 3 groups...Co, Location, and Material. I have subtotals at each group.

On the Location group, I have a calculation to determine External Sales as a % of Total. I only want to include locations where the % is >= 20%.

I can't use the formula in the selection criteria and thought I could use it in Group Selection but it says it can't be used because it hasn't been evaluated.

Can someone help me with this? I don't want to just hide...I want to completely remove them so as not to be included in my subtotals and grand total.

Thank you.

 

[lbass]

You need to show us the content of your formula for calculating the percentage.

-LB

 

[tkaz]

Is this what you want to see?

if Sum ({MSSA.MatlUnits}, {MSSA.Loc})=0
then 0
else
Sum ({@External Units}, {MSSA.Loc}) % Sum ({MSSA.MatlUnits}, {MSSA.Loc})

There is a formula for External Units, Internal Units and Total at the Material level that is subtotaled at Location and Co.

 

[lbass]

Try this in the group selection area:

if Sum ({MSSA.MatlUnits}, {MSSA.Loc}) <> 0 then
Sum ({@External Units}, {MSSA.Loc}) % Sum ({MSSA.MatlUnits}, {MSSA.Loc}) > 20 else
false

Sometimes group selection will take formulas when they are not nested. This should work as long as there are no summaries within {@External Units}. You should always show the contents of nested formulas too when asked for formula contents.

-LB

 

[tkaz]

Thank you so much! This seems to work fine.

I appreciate you working with me on this and I'll try to provide all the necessary info for any future requests.