Filter Group By Total by Parameter

  • 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!


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

    @ParmValue = passed in parm value



    ,sum(value) as ValueOut



    group by



    sum(value) <= @ParmValue

  • 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.

  • 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.

  • 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?



  • 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.


    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,


    I did this in RS2008

  • 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.


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

  • 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?

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

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