Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

SSRS Use Parameter In Filter Expression

Posted on 2011-10-25
8
Medium Priority
?
812 Views
Last Modified: 2012-05-12
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

0
Comment
Question by:dk04
  • 4
  • 4
8 Comments
 
LVL 15

Expert Comment

by:Tim Humphries
ID: 37030416
Can you post the SQL or MDX query that you want to filter?

Tim
0
 

Author Comment

by:dk04
ID: 37030732
 
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
 
LVL 15

Accepted Solution

by:
Tim Humphries earned 2000 total points
ID: 37036550
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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

Author Comment

by:dk04
ID: 37040044
Thanks for the response Tim but the Order By would not isolate just the top 10.
0
 

Author Comment

by:dk04
ID: 37040089
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
 
LVL 15

Assisted Solution

by:Tim Humphries
Tim Humphries earned 2000 total points
ID: 37040204
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
 

Author Comment

by:dk04
ID: 37040236
In the filter the expression is what I mentioned above. The operator is Top N and the value is 10.

0
 
LVL 15

Assisted Solution

by:Tim Humphries
Tim Humphries earned 2000 total points
ID: 37043346
Glad you've fixed it - don't think I really understood your problem from the original description.
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Written by Valentino Vranken. Introduction: The first step of creating a SQL Server Reporting Services (SSRS) report involves setting up a connection to the data source and programming a dataset to retrieve data from that data source.  The data…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question