dk04
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
The name of the parameter is Parameters!RankBy.
Thanks for your help
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')
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for the response Tim but the Order By would not isolate just the top 10.
ASKER
I was able to put add the following if statement to the filter for the group-
=IIF(Parameters!RankBy.Val ue = 1, Sum(Fields!MoneyIN.Value), IIF(Parameters!RankBy.Valu e = 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.
=IIF(Parameters!RankBy.Val
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
In the filter the expression is what I mentioned above. The operator is Top N and the value is 10.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Tim