Top 20 Filter/RunningValues

[dianemarie]

Hello, I have a report filtering a group on Top 20 and subtotaling with RunningValues so that only the totals of the Top 20 are calculated, and not the entire dataset. The issue is when there is less than 20. (I know this is a common request.) I've been all over the web on this one but am currently working on this solution, which does not require a lot of background coding.

For Group1, I have one row summing the order quantity (grouped by client), then another row with the RunningValue sums for order quantity. I also have a RunningValue countdistinct to number the Top 20. I put a Top 20 filter on sum(ordqty) and then a conditional Hidden expression on the RunningValue row so that the RunningValue row shows only the last row. This is easy enough when there are 20 or more rows. When there are less than 20, I found the solution below, which works. My problem is I can't get the two conditional expressions to work together, only separately. Any ideas on where I'm going wrong would be greatly appreciated. (I'm using an internal parameter hard-coded to show Top 20, by the way, although I could probably just as easily hard-code it in the expression.)

To show only the last RunningValue row when there are 20 or more entries, this expression in the Visibility property of the row works:

=iif(RunningValue(Fields!xSelectTypeMlrIDorListID.Value,CountDistinct,Nothing) <> Parameters!LimitTop.Value,true,false)

To show this footer with RunningValues when the number of grouped values returned is less than 20, this works:

=iif(RunningValue(Fields!xSelectTypeMlrIDorListID.Value,CountDistinct,nothing) <>
CountDistinct(Fields!xSelectTypeMlrIDorListID.Value,"table1"),true,false)

I can't get them to work together. I tried using an or:

=iif((RunningValue(Fields!xSelectTypeMlrIDorListID.Value,CountDistinct,nothing) <>
CountDistinct(Fields!xSelectTypeMlrIDorListID.Value,"table1") or
RunningValue(Fields!xSelectTypeMlrIDorListID.Value,CountDistinct,Nothing) <> Parameters!LimitTop.Value),true,false)

But this dropped the conditionally hidden row in both scenarios. I think I might be off on my logic but now I've just hit a wall and can't get past it. Any thoughts would be greatly appreciated, even if it's that I'm on the totally wrong track. Thank you.

 

[xlbo]

Any reason you're doing this in the report rather than in the SQL query?

If you do it there, you can just use standard sums etc in the report and it won;t matter if there are less than 20...

SELECT TOP 20 (Column_Name)


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[dianemarie]

My queries to date have been pretty basic, but I could try this. I'm grouping on an expression, (the user gets to pick the group criteria via a parameter...A or B) and also gets to pick the summed top 20 field (order quantity or shipped quantity) via a parameter. So it's not a static Select Top 20 (orderqty), plus the Top 20 is determined not on individual records but on the group. But if you're telling me this is all doable, then I can go to our SQL guy to get help with the query. Thanks RG.

 

[xlbo]

Would've thought it's more do-able in SQL than in RS!!

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question