Multi valued Parameters using Stored Procedures in SSRS Reports

105
21047

Friends,

Here in this post we are gonna discuss about how to pass multiple values to SP(Stored Procedures) to get data for SSRS Reports. If you wish to define parameters using Sql Queries then you can find the sample here. For this demonstration purpose I Created the following Stored procedure –

Create procedure sp_getColors
as
select Distinct Color as “Colors” from dimproduct

Now to demonstrate the same, I create a tabular report using the following SP –

CREATE PROCEDURE [SP_SALES]
AS
BEGIN
SELECT
DimProduct.Color
,DimProduct.Class
,DimProduct.EnglishProductName
,FactInternetSales.SalesAmount
,FactInternetSales.TaxAmt
,FactInternetSales.OrderQuantity
,FactInternetSales.UnitPrice
FROM DimProduct
INNER JOIN FactInternetSales ON DimProduct.ProductKey = FactInternetSales.ProductKey
END

Create a Dataset using the above given SP as shown below.

Now create a tabular report using the Dataset created as shown below.

At this point of time there are no Parameters defined and hence you will get all the data from the database and will be displayed in the report. PFB the steps to be followed to ADD PARAMETER to your report using SPS as source –

  • Right click on Datasource and select “Add Dataset” option.
  • Select the data source and select the option “Stored Procedure” radio button and provide the Stored Procedure name which pulls values for parameter and as I am adding “Color” as parameter I am using the following stored procedure which pulls Colors data.

  • As there is no INPUT parameters for this Stored Procedure we can continue by Clicking OK. When you click on OK , you can see new data set added with ONE columns as shown in below screenshot.
  • Now Right Click on “Parameters” and select “Add Parameters” option. The wizard will get open and Change the default value given for NAME and PROMPT if required.

  • Now select the “Available Values” tab which is on the Left hand side of the wizard and select the option “Get Values From a Query”. Provide the Dataset name,Value Field and Label Field as shown below –
  • Click on OK.
  • Now the report parameter is ready and the same can be seen in the below given screenshot.

  • If you see the above screenshot the  Parameters looks like working fine but if you see the chart area the data doesn’t look correct. This is because we added parameter but not linked this parameter selection to the Main Dataset from which we are getting data and filling the chart. Now I will modify the Main Dataset in order to fetch data by applying parameter values. Edit the main Dataset and update the query as shown below –
First Execute the below give function –
CREATE FUNCTION [dbo].[FnSplit]
(@List nvarchar(2000),@SplitOn nvarchar(5))
RETURNS @RtnValue table
(Id int identity(1,1),Value nvarchar(100))
AS
BEGIN
While (Charindex(@SplitOn,@List)>0)
Begin
Insert Into @RtnValue (value)
Select Value = ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List)-1)))
Set @List = Substring(@List,Charindex(@SplitOn,@List)+len(@SplitOn),len(@List))
End
Insert Into @RtnValue (Value)
Select Value = ltrim(rtrim(@List))
Return
END
and then execute this SP.

ALTER PROCEDURE [SP_SALES]
(@Color VARCHAR(4000))
AS
BEGIN
SELECT
DimProduct.Color
,DimProduct.Class
,DimProduct.EnglishProductName
,FactInternetSales.SalesAmount
,FactInternetSales.TaxAmt
,FactInternetSales.OrderQuantity
,FactInternetSales.UnitPrice
FROM DimProduct
INNER JOIN FactInternetSales ON DimProduct.ProductKey = FactInternetSales.ProductKey
Where Color in (select Value from dbo.FnSplit(@Color,’,’))
END

Now Select Parameters tab and map the parameters for the main Dataset as shown below.

  • Click on OK and your report is ready. Go to preview tab and test you report.

That’s it .. Your Report with multi valued Parameters passed to  dataset which is created using SP is ready .. Hope you understood the concepts .. Happy Coding !!

Regards,
Roopesh Babu V

105 COMMENTS

  1. You actually said this perfectly!
    best canadian prescription prices [url=https://canadianonlinepharmacybase.com]get prescription online[/url] canada pharmacy online reviews

  2. Our lapalingo.com casino review team also noticed that your activity will be monitored as you deposit and play. Once you reach a certain level, a member of the casino’s team will get in touch with an exclusive invitation into their Loyalty Club. You’ll be offered special perks like reduced wagering requirements, a personal account manager and much more. When you see that Evolution Gaming is supplying the live dealer games in a casino you can be confident that you are in for a high-quality gaming experience. That is certainly the case at Lapalingo, where NetEnt and Evolution power the live casino. Choose from live baccarat, blackjack, roulette and Dream Catcher tables to enjoy some fantastic live gaming action. This is certainly a major attraction and a good reason to sign up with Lapalingo if you are a fan of live casino action. https://high-wiki.win/index.php?title=Free_welcome_bonus_no_deposit_required_casino No deposit bonuses are designed to get people playing, so you’ll sometimes find that deposit offers can have better terms and conditions. The latter’s stricter requirements prevent people from just running off with free cash. At the very least, there’s certainly more casinos to choose from, if you’re willing to deposit. And don’t forget that if you do deposit at a casino it opens up the possibility to play in their freeroll slot tournaments. Wild Women Week Of course, unless you are a novice in the online casino world, you already know that not every casino will offer this type of promotion. As a matter of fact, very few online casinos will let you play casino games using the free spins to win real money, especially without the need for a deposit. So while we have done our best to find those bonus deals, you can see from the list above that only a handful of casinos do let you claim such promotions. And when you add the fact that we only mention casinos with impeccable reputation and long history in the industry, you can understand why this list will never be as long as many would desire.

  3. Nicely put, Appreciate it!
    freedom writers essay introduction [url=https://dissertationwritingtops.com]how to write application essay[/url] dissertation writing help

  4. You actually expressed this exceptionally well!
    buy prescription drugs online [url=https://northwestpharmacylabs.com]mexican pharmacies[/url] international pharmacies that ship to the usa

  5. Whoa many of fantastic knowledge!
    canadian pharmaceuticals online reviews [url=https://canadianpharmacies-24h.com]pharmacy online[/url] viagra generic online pharmacy

  6. Seriously quite a lot of fantastic data.
    best canadian prescription prices [url=https://rxpharmacyteam.com]aarp recommended canadian pharmacies[/url] canadian pharmacies online

  7. @CAMHnews He works with the Health Officer Council on the issue of drug policy and publishes and lectures on the subject of “A Public Health approach to the regulation of currently illegal drugs”. Read this next: Boris Johnson to consider legalisation of psilocybin Mar 30 2022 By now, many of you have read the CBC news article about a psychedelic-assisted therapeutic session that took place as part of If you were one of our 500 registrants or simply did not get a chance to join us, we have the video recordings here for you. Health Canada regulates the Psychedelics under the Controlled Drugs and Substances Act (CDSA) – MDMA and ketamine are Schedule I controlled substances, while LSD and psilocybin are both Schedule III controlled substances. In all cases, this means that there is a general prohibition on the sale, export, import, possession, and production of the Psychedelics. However, under Section 56(1) of the CDSA, the Minister of Health has the ability to grant exemptions to these restrictions if the Minister deems them necessary for a medical or scientific purpose, or otherwise in the public interest. We will touch on local efforts toward advancement under Section 56(1) below. https://community.novum.com.my/forum/profile/eileenlavater8/ “Eligibility may be negatively impacted if an individual knowingly and directly invests in stocks or business ventures that specifically pertain to marijuana growers and retailers,” according to the document. “Decisions to willfully invest in such activity could reflect questionable judgment and an unwillingness to comply with laws, rules, and regulations.” Investors are betting heavily on Canadian marijuana companies, eagerly preparing for its imminent legalization in the country. The shares of the largest Canadian marijuana company, Canopy Growth Corp., for example, began a steep climb in mid-August when the company announced Constellation Brands would pay US$4-billion for a stake in Canopy. Canopy’s shares doubled between mid-August and the end of the month. As Canada moves to legalize recreational cannabis, the value of the nascent market has ballooned to more than $25 billion, swelling the ranks of affluent pot investors who have significant holdings in companies such as Canopy Growth Corp. and Aphria Inc. The U.S. legal market is expected to reach $75 billion (U.S.) in sales by 2030, almost as large as North America’s soft drink market, according to research firm Cowen & Co. Canadian sales could soon be worth between $7 billion and $12 billion a year, Beacon Securities estimates.

  8. Nicely put, Cheers!
    international drug mart canadian pharmacy online store [url=https://online-pharmacy-inc.com]pharmacie canadienne[/url] prescription drugs without prior prescription

  9. Richtig spannend wird das Ganze jedoch erst, sobald du dich entscheidest, einen gratis Bonus in Anspruch zu nehmen. Hierbei ist eine Anmeldung bei einem online Casino nötig, jedoch brauchst du keinerlei Einzahlung zu leisten. Dann nennt man das gratis Spielen mit Bonus. Das Haus verlassen, ins Auto steigen und die nächste Spielbude ausfindig machen war gestern. Heute ist man mit nur wenigen Klicks in einer bunten nahezu grenzenlosen Welt voller Spiele. Tausende von Anbieter haben die Digitalisierung zu Ihrem Vorteil genutzt und Online-Casinos entwickelt. Die Nachfrage ist seitdem kaum mehr zu stoppen, sodass die Online Glücksspielindustrie, obwohl sie zeitlich gesehen, noch in ihren Kinderschuhen steckt, innerhalb kürzester Zeit ein Milliardengeschäft geworden ist. Diese Beliebtheit kommt nicht von irgendwo her, sondern liegt begründet in folgenden Vorteilen: https://rollingonaprayer.com/community/profile/bonitahoulding0/ Am besten Du suchst mal nach diesem Suchbegriff “casino bonus ohne einzahlung” bei Google. Dann erhältst Du Seiten, die eine Übersicht bieten. Die breite Auswahl an Wettoptionen ist ein weiteres unterscheidendes Merkmal. So hat das Casino of Gold für jeden Spieler etwas zu bieten – vom Anfänger bis hin zum Highroller. Außerdem hast du die Möglichkeit, alle Spielautomaten kostenlos zu testen. Diese Option ist jedoch für eine begrenzte Anzahl an Wetten möglich, aber sie bietet dir die Möglichkeit, die Spiele zu testen und Casino of Gold besser kennenzulernen, bevor du anfängst mit Echtgeld zu spielen. Wir haben bei den einzelnen Slots und Tischspielen im StarGames-Casino nach Angaben zu den Mindesteinsätzen und Limits gesucht. Die Suche war leider erfolglos. Andere Online-Casinos informieren hier wesentlich besser über minimale und maximale Einsätze, zum Teil bei jedem einzelnen Spiel. Zu Mindesteinsatz und Limits findet man beim Sportwetten-Angebot von StarGames dagegen klare Angaben:

LEAVE A REPLY

Please enter your comment!
Please enter your name here

3 + 2 =