Link to home
Start Free TrialLog in
Avatar of dk04
dk04Flag for United States of America

asked on

SSRS Use Parameter In Filter Expression

I have a group in a report I would like to filter using the Top N Operator in the group filter, but instead of selecting a field from the Expression drop down I would like it to look at a parameter. This will give me the ability to select which field to filter the Top N by. So far I have not had any luck.

The name of the parameter is Parameters!RankBy.

Thanks for your help

Avatar of Tim Humphries
Tim Humphries
Flag of United Kingdom of Great Britain and Northern Ireland image

Can you post the SQL or MDX query that you want to filter?

Tim
Avatar of dk04

ASKER

 
SELECT     sm.StoreID, sm.StoreGameID, sm.MachineName, gd.Credits, gd.Bonus, gd.MoneyIN, gd.Tickets, fd.FiscalYear, fd.FiscalQuarter, fd.FiscalPeriod, fd.FiscalPeriodWeek, sm.GroupName, gd.Plays, gd.CreditPlays, gd.BonusPlays
FROM         GameDataDetailByDay AS gd INNER JOIN
                      StoreGameNetworkID AS sn ON gd.StoreID = sn.StoreID AND gd.NetworkID = sn.NetworkID INNER JOIN
                      StoreMasterGameList AS sm ON sm.StoreID = sn.StoreID AND sm.StoreGameID = sn.StoreGameID INNER JOIN
                      FiscalDatesByDay AS fd ON fd.TransactionDate = gd.TransactionDate
WHERE     (sm.StoreID IN (@Store)) AND (fd.FiscalYear = @Year) AND (fd.FiscalPeriod = @Period) AND (fd.FiscalPeriodWeek IN (@Week)) AND 
                      (sm.GroupName <> 'Token') AND (sm.GroupName <> 'Changer')

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Tim Humphries
Tim Humphries
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of dk04

ASKER

Thanks for the response Tim but the Order By would not isolate just the top 10.
Avatar of dk04

ASKER

I was able to put add the following if statement to the filter for the group-

=IIF(Parameters!RankBy.Value = 1, Sum(Fields!MoneyIN.Value), IIF(Parameters!RankBy.Value = 2, Sum(Fields!Plays.Value)," "))

and then in the available values in the parameter I added a label with value 1 and another label with value 2. This allowed me to have the parameter and the filter looking at the parameter for how to filter the data.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of dk04

ASKER

In the filter the expression is what I mentioned above. The operator is Top N and the value is 10.

SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial