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

dk04Asked:
Who is Participating?
 
Tim HumphriesDirectorCommented:
Hi,

Could you set your parameter up with the Value of the parameter being the ordinal position of the field you want to rank by and then use ORDER BY (@RankBy) in the SQL?

So Bonus would have a value of 5 for example.
Not so easy to handle descending order by though...

Can you not just use the interactive sort capabilities in SSRS, or is the limiting of rows critical?

Tim
0
 
Tim HumphriesDirectorCommented:
Can you post the SQL or MDX query that you want to filter?

Tim
0
 
dk04Author Commented:
 
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

0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
dk04Author Commented:
Thanks for the response Tim but the Order By would not isolate just the top 10.
0
 
dk04Author Commented:
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.
0
 
Tim HumphriesDirectorCommented:
Hmmm... not sure how that limits the top 10.
If you tweak your sql so the select statement is :

Select TOP 10  sm.StoreID, sm.StoreGameID...etc

Then you  will get the first ten rows depending on the ordering of the rows. (Sorry - forgot to mention that last time!)

Tim
0
 
dk04Author Commented:
In the filter the expression is what I mentioned above. The operator is Top N and the value is 10.

0
 
Tim HumphriesDirectorCommented:
Glad you've fixed it - don't think I really understood your problem from the original description.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.