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

x
?
Solved

SSRS Parameter

Posted on 2012-09-20
5
Medium Priority
?
345 Views
Last Modified: 2012-09-21
I have a report with 10 items in a drop down list as a parameter. However, I want to combine certain items ahead of time and apply arithmetic  to them and pass that calculated value as one of the items in the list. Basically, I have green apples, red apples, fuji apples, oranges, pears, peaches... In the list. I want all apples to appear as apples and sum all apples together versus each specific type. Should this be done ahead of time in my query or?
0
Comment
Question by:LCNW
  • 2
  • 2
5 Comments
 
LVL 25

Assisted Solution

by:TempDBA
TempDBA earned 1000 total points
ID: 38418974
I will go with doing it in backend. Creating a reference table with all the different combinations I want.
0
 
LVL 37

Accepted Solution

by:
ValentinoV earned 1000 total points
ID: 38420780
To add some more words to what TempDBA said:

The easiest option is if you have a method of grouping your apples together using a simple SELECT statement with a GROUP BY.  That means you'll need to have a table like FruitCategory with your different categories.  One record would be: FruitCategoryID 1, FruitCategoryName 'Apples'.

The table containing your Fruit should have an additional column FruitCategoryID that points to the FruitCategory.FruitCategoryID column. So using above example your green apples, yellow apples, Granny Smiths, .... should have 1 as value.

Now you can use a query like:

SELECT FruitCategory.FruitCategoryName, SUM(Fruit.Quantity) as QuantitySUM
FROM Fruit
INNER JOIN FruitCategory on FruitCategory.FruitCategoryID = Fruit.FruitCategoryID
GROUP BY FruitCategory.FruitCategoryID

You don't necessarily need to group by in the select.  Another option is selecting your data as required, including FruitCategoryName, and then using the grouping functionality on the table in the report.

Your report parameter can now be populated using the new FruitCategory table.  Assuming you want to select multiple categories, you'll need an additional WHERE clause like:

SELECT FruitCategory.FruitCategoryName, SUM(Fruit.Quantity) as QuantitySUM
FROM Fruit
INNER JOIN FruitCategory on FruitCategory.FruitCategoryID = Fruit.FruitCategoryID
WHERE FruitCategory.FruitCategoryID IN (@Category)
GROUP BY FruitCategory.FruitCategoryID
0
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 38420993
You have to create Drop down source (separate by query).  Assing the Text and Value. i.e. for Apples value should be all apple in list with comma separated.

As this change you have to change the Report source (Query / SP) to cop up with the parameter change.
0
 
LVL 25

Expert Comment

by:TempDBA
ID: 38421028
Thanks Valentino. Really appreciable.
0
 
LVL 37

Expert Comment

by:ValentinoV
ID: 38422791
No worries!  Thought I'd add a couple of words, meanwhile I enjoyed writing about fruit :)
And it looks like it got appreciated by the asker as well, so mission accomplished!
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
In this article I will describe the Backup & Restore 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.
Integration Management Part 2
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

577 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