Filter Group By Total by Parameter

Question

Post reply

Filter Group By Total by Parameter

cmickley

Old Hand

Points: 374
More actions
November 3, 2008 at 9:49 am

#195411

I am trying to Filter data on a Group By Total, based on a dropdown param of values. I could do this pretty easily in Crystal.
Please see screen shot for detail. I am trying to Filter data on usage Dropdown, of the Subtotal field.
Thanks for any help!
Charles

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

RSCERO SSC-Addicted Points: 406 More actions · Answer 1

In your query, you should be able to do something like the following:

@ParmValue = passed in parm value

Select

ID

,sum(value) as ValueOut

from

Table

group by

ID

having

sum(value) <= @ParmValue

cmickley Old Hand Points: 374 More actions · Answer 2

RSCERO, I can try this, but I figured there should be the ability to do this via Row filtering where you could set this via the Group properties via the Row.

RSCERO SSC-Addicted Points: 406 More actions · Answer 3

Look at the dataset filter option. You can filter the returned results there using parameter values, or you can use the filter options in the table Group definintion. It sounds like you want to do this in the table, so the group definition is proably the best place for it.

cmickley Old Hand Points: 374 More actions · Answer 4

OK, I have tried about every type of filtering I can do, and can't get this to work.

I've put a filter on the Dataset, but get an error on that, that I cannot use Agregate functions on dataset filters or data region filters.

I have added a filter on the GroupBy, and the Tablix table, but do not get errors, it just does not return correct values. It does not filter it at all.

FYI, I am using SSRS2008. Can you give me an exact example or demo?

thanks

Charles

RSCERO SSC-Addicted Points: 406 More actions · Answer 5

Couple of things, first look at your parameters, they seem a bit confusing:

I think this is what you are looking to filter on:

>= 0 - All in (Parm.Value =0)

< 2 - 0 or 1 (Parm.Value=1)

>= 2 - more than 1 (Parm.Value=2)

I used the following in the visibility property of the row to hide rows based on the selected parm.

=switch(

Parameters!Usage.Value=0 ,False,

Parameters!Usage.Value=1 and sum(Fields!UsageCnt.Value) < 2,False,

Parameters!Usage.Value=2 and sum(Fields!UsageCnt.Value) > 1,False,

1=1,True)

I did this in RS2008

cmickley Old Hand Points: 374 More actions · Answer 6

I think this might work, it seems to be so far.. I just am seeing a couply screwy results, but with adding the same "visible" functionality on GroupBy and Detail rows, it is working good.

Thanks for your help on this.

Charles

cmickley Old Hand Points: 374 More actions · Answer 7

RSCERO, what does the "1=1" part of the expression do?

cmickley Old Hand Points: 374 More actions · Answer 8

So I have included a small snippet of my data run on this report in .xls. The first Param(All In) and the 3 param(More than 1) seems to work file and make data visible correctly and the Sum field is fine. But when you choose the 2nd option (0 or 1), the Sum(Points1) field does not calculate correctly then.

Any idea why?