Multi Valued Parameters in SSRS

By
roopesh.valluru
-
100
7394

Friends,

We discussed a lot about Singled Valued parameters here. Please fo through that post before jumping into this. If the user can select only one value from the parameter list then that is called Single Valued Parameters and if the user is able to select More than one then we call it as Multi Valued Parameters. First, Let us see how to add a Single Valued parameter to the report and then convert it to Multi Valued one. So, let’s jump into the topic –

First I created a report(Tabular) using the following query –

SELECT DimProductCategory.EnglishProductCategoryName, DimProductSubcategory.EnglishProductSubcategoryName, DimProduct.EnglishProductName, FactInternetSales.SalesAmount, FactInternetSales.TotalProductCost, FactInternetSales.TaxAmt, FactInternetSales.Freight FROM DimProduct INNER JOIN DimProductSubcategory ON DimProduct.ProductSubcategoryKey = DimProductSubcategory.ProductSubcategoryKey INNER JOIN DimProductCategory ON DimProductSubcategory.ProductCategoryKey = DimProductCategory.ProductCategoryKey INNER JOIN
FactInternetSales ON DimProduct.ProductKey = FactInternetSales.ProductKey

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 PARAMETERS to your report –

  • Right click on Datasource and select “Add Dataset” option.
  • Select the data source and provide the query which pulls values for parameter and as I am adding “Products” as parameter I am using the following query.

select ProductKey,EnglishProductName as “Products” from dimproduct

  • When you click on OK by providing the query, you can see new data set added with TWO columns as shown in below screenshot.
  • Now Right Click on “Parameters” and select “Add Parameters” option. The wizard will get open and Chnage 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 OK and then edit the MAIN DATASET to add this parameter to the query. For that double click on Main dataset “Dataset1” in this case and modify the query with the given one below.

SELECT DimProductCategory.EnglishProductCategoryName, DimProductSubcategory.EnglishProductSubcategoryName, DimProduct.EnglishProductName,
FactInternetSales.SalesAmount, FactInternetSales.TotalProductCost, FactInternetSales.TaxAmt, FactInternetSales.Freight
FROM DimProduct INNER JOIN
DimProductSubcategory ON DimProduct.ProductSubcategoryKey = DimProductSubcategory.ProductSubcategoryKey INNER JOIN
DimProductCategory ON DimProductSubcategory.ProductCategoryKey = DimProductCategory.ProductCategoryKey INNER JOIN
FactInternetSales ON DimProduct.ProductKey = FactInternetSales.ProductKey
Where DimProduct.ProductKey = @ReportParameter1

  • Now Select Parameters tab and map the parameters as shown below.

  • Click on OK and your report is ready. Go to preview tab and test you report. You can see Report data get filtered as shown below  but the problem is at a time ONLY ONE Value can be selected as parameter.

  • To make is a Multi-Valued parameter double click on the parameter and check the option “Allow Multiple Values” as shown below.

  • Now if you preview the report, it will let you select multiple values but on submit it will throw error and pfb screenshot of the same.
  • This is because we are passing MULTI VALUES to the query but the query condition is “=” which expects a scalar value. To solve this issue go to the Main dataset query and change the query as given below.

SELECT DimProductCategory.EnglishProductCategoryName, DimProductSubcategory.EnglishProductSubcategoryName, DimProduct.EnglishProductName,
FactInternetSales.SalesAmount, FactInternetSales.TotalProductCost, FactInternetSales.TaxAmt, FactInternetSales.Freight
FROM DimProduct INNER JOIN
DimProductSubcategory ON DimProduct.ProductSubcategoryKey = DimProductSubcategory.ProductSubcategoryKey INNER JOIN
DimProductCategory ON DimProductSubcategory.ProductCategoryKey = DimProductCategory.ProductCategoryKey INNER JOIN
FactInternetSales ON DimProduct.ProductKey = FactInternetSales.ProductKey
Where DimProduct.ProductKey in (@ReportParameter1)

  • Now preview the report by selecting multiple values and pfb the screenshot of my sample report output.

There it you .. The required output. For multiple parameters see the blog post Cascading parameters here.

That’s it .. Hope you understood .. Happy Coding !!

Regards,

Roopesh Babu V

RELATED ARTICLESMORE FROM AUTHOR

100 COMMENTS

  1. what are ed drugs [url=http://cheapdr.top/#]best online drugstore[/url] ed meds online canada

  3. errectile dysfunction [url=http://cheapdr.top/#]best ed medicine[/url] viagra without doctor prescription

  29. indian pharmacy online [url=https://indiapharm.pro/#]indian pharmacies shipping to usa[/url] top 10 pharmacies in india

  31. canadian pharmacy near me [url=http://canadapharm.pro/#]canadian drugs online[/url] canadian drugs pharmacy

  33. online shopping pharmacy india [url=https://indiapharm.pro/#]rx pharmacy india[/url] top 10 online pharmacy in india

  61. De essentie van een online casino is in principe hetzelfde als een fysiek casino. Door middel van gokspelletjes kan je geld winnen. Bekende casinospellen zijn natuurlijk de tafelspellen zoals roulette, blackjack en gokkasten. Een online casino biedt tal van andere voordelen, zo ben je niet gebonden aan openingstijden en kan je 24/7 en van waar dan ook spelen. Thuis, onderweg, bij een vriend, in een café, met de huidige technologie kun je overal spelen. Op je computer, tablet of telefoon, het is allemaal mogelijk. De enige vereiste is eigenlijk dat je toegang hebt tot het internet en een apparaat hebt waarmee je online kan spelen. En uiteraard dat je voldoet aan de eisen van het casino, zoals de leeftijdsgrens en je identiteit verifiëren. OnlineCasinoGround.nl is dé gids met alle betrouwbare en eerlijke informatie over online gokken. Vind hier informatie over de betrouwbaarheid van online casino’s, bonusvoorwaarden en de nieuwste spellen. Experts van OnlineCasinoGround hebben alle spellen getest en voorzien van een eerlijke review.
    http://k-vsa.org/bbs/board.php?bo_table=free&wr_id=34418
    In het ruim opgezette casino vindt u op de ruim 1.000 m2 meer dan 190 speelplekken! Er is dus altijd wel een spel dat u leuk vindt: van de bekende roulette, tot verschillende vintage en nieuwe speelautomaten. Speel bijvoorbeeld op één van onze Batmans. We hebben voor elk wat wils! Spelen kan al vanaf € 0,05 cent. Geschikt voor iedereen met elk budget. Jack’s Casino staat garant voor vele uurtjes speelplezier.  De belangrijkste veranderingen op een rijtje: Verspreiding van het virus mag dus niet te veel zijn toegenomen. “Echt een hele grote winstwaarschuwing”, aldus Rutte. “Of het kan, hangt af van hoe we ons blijven houden aan de gedragsregels.” Een week voor 1 juli wordt dan bekendgemaakt of de versoepeling doorgaat.

  70. list of aarp approved pharmacies [url=http://pillswithoutprescription.pro/#]certified canadian international pharmacy[/url] compare prices prescription drugs

  75. mail order pharmacy india [url=https://indianpharmacy.pro/#]Online medicine home delivery[/url] indian pharmacy

  76. The site hosts a mobile casino along with its core desktop format. These hand-held options allow users to play slots and other online casino games while on the go. Despite this perk, the mobile design does not support the entire catalog of slot games available on its website. Snoop Dogg – The Joker’s Wild Slot Game Snoop Dogg – The Joker’s Wild Slot Game Follow our quick guide to maximize your chances of beating the casino games. As a slots player, you want to maximize your play time. That is why you should only play games with a respectable return to player. RTP is what percentage of a player’s money a slot will return, over the long-term. For example, a slot with an RTP of 98% will return 98% of the money wagered, eventually. Understanding RTP is key to understanding how slots work. Most online slots have an RTP in the mid 90’s. Check the table below for some of this highest RTP slots.
    https://hikvisiondb.webcam/wiki/True_blue_15_deposit_bonus
    It’s simple enough, but for children playing the game, the long-term outcome could be more complicated. A professor from the United Kingdom says that games played on social networks can be addicting and possibly lead to bad habits later in life. Pragmatic Play merupakan perusahaan penyedia game online yang berfokus pada pengembangan slot mobile dan juga aplikasi slot online. Dengan bermain menggunakan taruhan uang asli, game slot demo ini sangat banyak menjadi pencarian pemain yang bermain game online. Terdapat banyak permainan slot dan juga live casino yang bisa kamu mainkan hanya dengan menggunakan ponsel pintar milikmu. Game slot demo gratis yang ini pun bisa kamu mainkan sebagai pengisi waktu hiburan karena tidak menggunakan deposit. Banyak permainan game slot demo gratis yang disediakan, di mana semua itu juga bisa membantu pemain mendapatkan pengetahuan cara bermain slot yang aman.

  80. Sweepstakes gambling sites are gaining in popularity countrywide. At peak times, you can find 100+ cash game tables and multiple poker tournaments running on Global Poker. This page lists the best real money poker sites USA for 2023. Included in our list are sites that provide a steady stream of traffic in Hold’em and Omaha cash games as well as which sites have the best bonuses, and those that accept US credit card deposits. You can only play online poker for real money in the US only in the states where this is legal. While more and more states are changing their legislation to allow real money poker games online, you should never play on an unlicensed poker site. For video poker, you have 14 games to choose from, and for table poker, you can enjoy the most popular poker casino game, Three Card Poker, as well as Let ‘Em Ride, and three variants of Caribbean Poker: Draw, Hold’em, and Stud. 
    https://www.trutheyewear.com/bbs/board.php?bo_table=free&wr_id=131908
    Many online casinos offer bonuses and promotions to players, such as free spins or bonus money. These can be a great way to extend your playing time and increase your chances of winning big. However, it’s important to read the terms and conditions of these bonuses carefully. Some bonuses may come with wagering requirements or other restrictions that can make it difficult to cash out your winnings. Online casinos also offer free spins to players, allowing you to play a particular slot machine without risking your own money. Take advantage of these free spins to learn about the game and increase your chances of winning big. Players should look at a new online slot game’s RTP first, but it’s not the only thing. While a game’s RTP is crucial, volatility, hit rate, and maximum payout also affect its outcome. If you like to win more, regardless of size, a high RTP should be vital to you. RTP is a recommendation, not a guarantee. Choosing a slot with an RTP over 96% is an excellent place to start.

  81. Check out all the details of the Unibet Casino below to learn more, including information on the best features, what we don’t like, plus how to download the platform to your device. Ohio only recently joined the landscape of legalized gambling. Prior to 2009, most types of straightforward gambling were illegal in the Buckeye State. That year, state voters passed a ballot initiative called the Ohio Casino Approval and Tax Distribution, Amendment 3, which placed one casino in each of Ohio’s four largest cities. An update to the Golden Nugget Mobile App allows for easy log in! Simply sign in with your username and password. Cole Williams: Hello, Micky. Roaring 21, in our experience after playing with them since they hit the market in 2018, has all the bona fides you can ask for from a real money offshore casino. Utilizing the best mobile casino software and offering a robust suite of games, Roaring 21 boasts market-leading customer satisfaction and retention.
    https://da1a1.com/community/profile/corneliuspoinde/
    The concept of these zero-stress promotions may seem deceptively simple at first glance. As you’ll see, plenty of rules apply. The free bonus no deposit offers are still more than worth it. Just make sure you understand how the particular promotion works. Other basic Terms and Conditions for casino No Deposit bonus Canada rewards usually include the list of the games that do and do not contribute to meeting the wagering requirements (for example, slots make 100%, table games make 5%). Also, as a rule, players from certain countries do not qualify for getting an online casino bonus No Deposit, regardless of their activity on the site, because of the local gambling legislation followed by their governments. BitStarz offers the best online casino bonus for crypto casino players in Canada, with their main welcome bonus being broken up over your first four deposits.

  82. Some casinos provide applications that can be downloaded from the App Store or Google Play. However, specialized native applications are usually available immediately from the casino’s webpage. Go to the casino’s website and download the program, just as you would on a standard PC. If you’re installing a different Android app, be sure you modify your phone’s settings to enable apps from other sources. You can rely on the casino to provide you with step-by-step guidance. Whether you’re out and about on your phone, or curled up on the sofa with a tablet or laptop, The Phone Casino offers you the best mobile gambling experience in the UK. Our games are designed to take online casino gaming to the next level, with the best quality and performance. But the good news is that downloading the app only takes a second. If you already have an account with the casino, registering through the app is even quicker. 
    https://jaideniyqg219864.ssnblog.com/12065458/pair-a-dice-casino-events
    Video Poker at its best. This strategic game has been given a slick, modern and minimalistic revamp. The new design boasts a cleaner, more elegant display, with a BRAND-NEW console for easier navigation. As you might have guessed, this video poker game carries some hefty payouts for hitting 4 Aces. You can play this game by download the 32Red Casino now. Simply click on the All Aces Video Poker logo below to begin. Currently viewing: Handheld Free professional educational courses for online casino employees aimed at industry best practices, improving player experience, and fair approach to gambling. If a Player has placed an Ante wager and an Aces Up wager but does not make a Play wager, the Player shall forfeit the Ante wager but not the Aces Up wager.

  83. Conocé la Política de devoluciones Cantidad de fichas: 100 Llega mañana con Mercado Envíos El catálogo de juegos permitidos en la región, recoge como novedad la inclusión de juegos de casino donde se incluyen concretamente juegos como el black jack, la ruleta, el póker, la bola, treinta y cuarenta, punto y banca, bacarrá, bacarrá a dos paños, dados, sic bo, pai gow, monte y keno. La ruleta es uno de los juegos de apuestas más antiguos del mundo, lo que ha generado distintas hipotesis sobre sus origenes. Desde la evolución de un juego practicado en la antigua China, pasando como método de entretenimiento en los campamentos del Imperio Romano para entretener a los soldados hasta su aparición en la Francia del siglo XVII de la mano Blaise Pascal, quien ideo una ruleta con 36 números. 
    http://heungil.net/bbs/board.php?bo_table=free&wr_id=45882
    Estos tres juegos son algunos de los muchos que podrás encontrar en cualquier casino online de nuestro dominio. Afortunadamente para todos, los operadores están introduciendo nuevos tipos de juegos como el rasca y gana. Ya hay muchas páginas con juegos como el bingo, el keno o el vídeo póker. Aquí te damos un par de pautas de cómo evaluar la selección de juegos de un casino online en España u otros mercados. El juego en línea en España no sólo es legal, sino también una “tradición local”: a los jugadores españoles les encanta apostar. Según las estadísticas, cada ciudadano español gasta en promedio 480 euros cada año en juegos de azar en línea; la lotería más grande del mundo, llamada “El Gordo”, también se encuentra en España: su premio acumulado es de más de 1.700 millones de euros y las entradas se venden por solo 200 euros.

  85. É muito simples jogar slots e jogos de casino na nossaseção de jogos grátis. Não é necessário descarregar ou instalar qualquer software. Navegue simplesmente através da lista de jogos ou utilize a função de pesquisa para selecionar o jogo que deseja jogar, clique nele e o jogo carregará e ficará pronto para ser jogado. Posteriormente, clique em girar (spin) se estiver a jogar nas slots, faça uma aposta e comece uma ronda nos jogos de mesa. Esses jogos slots machines grátis variam muito em termos de temas e características. Da mitologia grega às clássicas máquinas de frutas e filmes modernos populares, com certeza terá algo para interessar a qualquer jogador. Já sabe o que procura? Encontre jogos slots grátis divididos em categorias para conveniência dos usuários. Slotomania – Slot Machines é perfeito para quem quer se divertir com caça-níqueis muito temáticos. Cada uma das salas possui suas próprias características, tornando o game uma experiência muito mais completa do que apenas um pouco de caça-níqueis na tela do Facebook. Por falar em Facebook, Slotomania roda diretamente na rede social, mas não exige compartilhamentos de progresso com outros usuários do serviço.
    http://trentonwdfj902.fotosdefrases.com/nossa-aposta-casino
    Por fim, os slots do Betfair Casino possuem uma grande variedade de categorias, conforme mostraremos logo abaixo. A nova companhia – que deverá estar formada até 31 de Dezembro deste ano e será dona e concessionária do casino em questão – terá 55% do capital detido pela Macau Legend Development. Os outros 45% ficarão nas mãos da B&G. #navioroupanova Estes operadores também são acusados de incumprimento fiscal. O DoJ apontou ainda para ligações a redes chinesas de crime organizado. Mas é a própria empresa que, nos vários memorandos que envia ao executivo se refere à clarificação da propriedade do casino como um assunto “politicamente sensível e delicado”. Porquê? O governo de Santana Lopes esteve sob “bombardeamento permanente. Imagine-se se fosse feito um decreto com um artigo único a dizer que o casino revertia para a Estoril-Sol.”, diz Assis Ferreira. Já sobre o facto de a própria empresa ter sugerido uma alteração ao artigo 27 da lei do jogo, poucos meses antes de ela ser concretizada, Assis Ferreira argumenta que foi uma “mera sugestão que não foi seguida” (é feita uma alteração ao artigo, mas com um texto diferente).

  86. What Happened: The exhibition match between Mayweather, a boxing legend, and Paul, a YouTube personality, lasted for eight rounds and ended without an official winner being read.During the game, Mayweather was seen sporting an EthereumMax logo and a link to the project’s website. EMAX was also the exclusive cryptocurrency used for purchasing tickets for the event online, its website claims. With that being said, is there more to this obscure altcoin than meets the eye? Will other boxing matches starting accepting EthereumMax as currency? Here’s what you need to know about this crypto, and how high its price could go in 2021. EthereumMax (CCC:EMAX-USD) is in the news today as the crypto faces scrutiny alongside a new lawsuit targeting celebrity promoters. How do you feel about EthereumMax today? Vote to see results
    https://wiki-velo.win/index.php?title=Says_all_cryptocurrencyrelated_are_must_be
    CoinMarketCap said it was not aware of the Coinbase pages and its vice-president of growth and operations Shaun Heng said there was not a partnership with Coinbase. The lucrative SQUID crypto was inspired by the South Korean show “Squid Game” and had managed to soar high in prices in a matter of days. The game was set to launch in November and its promoters said winners would be rewarded with more Squid tokens. But even as the cryptocurrency’s price spiked in value last week, many noted that the token could be fraudulent. I feel that this comment is: The game was set to launch in November and its promoters said winners would be rewarded with more Squid tokens. But even as the cryptocurrency’s price spiked in value last week, many noted that the token could be fraudulent. SQUID USDT

  87. Maserati GranTurismo vs VW Scirocco R Critically, the Panamera finally looks like a Porsche sports car, too. The first-generation Panamera, released in 2010, drove great. Then your eyes met that Quasimodo of a rear end. For many aesthetes, the Panamera’s style, or lack of it, was a deal killer. So hardly numbers that will rule in a Sport Turismo where a regular Pan was ruled out, but my TV frequently tells me every little helps. You are viewing the Parkers beta preview. The exterior paints are complemented with twelve different interior colour palettes and leathers. These include the four mono-tones Black and Agate Grey (standard), Marsala and Saddle Brown (standard in the Turbo), and the six two-tones Black Bordeaux Red, Marsala Creme, Black Chalk, Saddle Brown Luxor Beige, Black Saddle Brown, and Black Luxor Beige. The two Club leather colours Truffle Brown and Cohiba Brown are also available.
    https://fun-wiki.win/index.php?title=2021_electric_kia_soul
    The GLC range is offered with a 5 year, unlimited kilometre warranty. The Mercedes-Benz GLC SUV has various high-tech interior features and a sporty feel, making driving in Glendale, CA, a breeze. Here’s a look at all this incredible vehicle has to offer. Overall, I enjoyed my time in the GLC being pampered by its quiet cabin, confident ride and fantastic Burmester audiophile speaker system. Of the models in this midsized luxury SUV class that I’ve driven, the Benz feels the most like a luxury product. BMW’s X3 feels sportier, Audi’s Q5 feels more high-tech and Lincoln’s MKX is plainly outclassed, but the GLC 300 raises the bar for austerity. For the 2022 model year, Mercedes offers the GLC in three main trims. The entry-level GLC 300 is a satisfying pick. It gives buyers a lot of premium features for the money.

  94. prescriptions online [url=http://fastdeliverypill.com/#]drugs without a doctor s prescription[/url] reputable mexican pharmacies

LEAVE A REPLY

Please enter your comment!
Please enter your name here

− 3 = 4