Sql Server Reporting Services and Parameter Label Fields : Mad Props!

You're reading Mabsterama, the weblog of Matt Hamilton. Enjoy your stay.

Sql Server Reporting Services and Parameter Label Fields

A little tip for people using Sql Server Reporting Services.

Y'know how you can define parameters that have "available values"? You can specify either a literal list of values, or a query from which the values are read. For the query option, you have to specify a "value field" and "label field", in case you want the user to be prompted with something that's meaningful to them. For example, you might want to show the user "ProductName" but use "ProductID" as the value of the parameter.

Now, if you want to display the value of a parameter (called, say, "Product") on a report, you use a TextBox whose value equates to "Parameters!Product.Value". But in the above example, that's only going to display the ProductID, not the meaningful name that the user selected.

So what do you do if you want to display the name of the product, rather than the value of the parameter?

Up until today I've been declaring a query, along the lines of, "SELECT ProductName FROM Products WHERE ProductID = @Product" - which would return the name of the product the user selected. But today I discovered a much better way:

Turns out that the "Parameter" object in Reporting Services has a "Label" property! The expression builder doesn't show it, but it's there! You can add a TextBox to your report which equates to, "Parameters!Product.Label" and it will show the meaningful value that the user selected!

This saves an extra trip to the server, which adds up for oft-used reports. I hope this message serves as some Google-juice for other users with this problem.

Google Ads

Comments

# crucible 24/02/2006 11:36 AM	can you believe I was searching for some help and this came up... actually it was the second hit... just goes to show that theres no one out there with an idea of Reporting Services. Inclusive.
# KB 23/03/2006 10:52 AM	Nice work, any idea on how to list parameter values in a text box when there are nulte-value parameters?
# mabster 23/03/2006 11:07 AM	You can use the "Join" function to create a single string out of the array of labels, KB. Like this: =Join(Parameters!Product.Label, ",")
# Thanks man 30/09/2006 1:15 AM	Thanks man, You just saved my butt. I was trolling around for how to do this and it was driving me up the wall. Not very intuitive at times, this reporting services. Thanks for the valuable info. PK
# lenak 27/01/2007 8:39 AM	yes, but it douesn't work! I tried =Join(Parameters!Environments.Value, ",") or =Join(Parameters!Environments.Label, ",") givers me an error: #Error help please!
# tommy 30/01/2007 1:41 AM	maybe your'e using ssrs 2005. i was using the ".label"-property since month - recently updating to ssrs 2005 it turns out that there is no way to access the label right now.... here an errormessage for example: Methode nicht gefunden: System.String Microsoft.ReportingServices.ReportProcessing.ReportObjectModel.Parameter.get_Label(). (rsRuntimeErrorInExpression)
# jeremy 23/05/2007 1:47 AM	When I try to use the label property it only gives me a blank field. Is there anything else you need to do?
# mabster 23/05/2007 8:39 AM	Shouldn't be anything special you need to do, Jeremy. What exactly are you using as your expression?
# rick 18/07/2007 5:32 AM	Make sure that you are using a multi-valued parameter (i.e., for "Product" in this example) when you use =Join(Parameters!Product.Label, ",") else you'll get the ever descriptive #Error
# huck5 25/08/2007 10:06 AM	Sweet. Thanks.
# Terri 11/09/2007 8:39 AM	WAHOO so cool this was exactly what I needed THANK YOU
# Puja 20/09/2007 12:25 AM	Excellent .. thats what i have to say i was looking for "=Join(Parameters!Product.Label, ",")" and thought this could not be done but guess what it worked ... this was really quick and hey its on top listing on google. thanks again and keep up good work cheers
# Neil Gardner 14/12/2007 8:02 AM	Thanks heaps.. Being trying to do this for a while. Managed to display the first selected parameter but couldnt display multivalue items. Glad there are people around like you that share their knowledge.
# Sneha Shetty 9/02/2008 8:26 AM	Hi the post is very helpful....god bless you :) COuld ne1 tell me if Drill Down is possible in SSRS 2005 Charts or Dundas Charts, if yes, pls gimme some references. Thanks Sneha
# jeff 12/02/2008 6:28 AM	.Label = perfect. Thanks for the post.
# Ken 27/02/2008 5:28 AM	I get an #Error too when try this.
# Dave 29/02/2008 4:18 AM	excellent, - thanks for this, saved me a lot of code work a rounds - thanks again
# Nunu 25/03/2008 1:03 AM	I was trying to create a mialing label. How can I do it. Is there a label wizard in RS? I used a column = 2 but I couldnt list all the labels on the same page. It prints one label a page. Thanks, Nunu
# dave 8/05/2008 7:46 AM	thanks - & I do love your "omniscience is just a Google-search away"
# Chanu 30/05/2008 2:18 AM	Great!! It worked Thanks
# Ariel 28/06/2008 6:28 AM	Excelent! It worked very fine. Thanks a lot.
# Matt 21/10/2008 2:31 AM	This is great. Thanks for reporting this Matt. I was working with SSRS a while back and this was always frustrating. I just started a new SSRS project and thought someone must have come up with a better way by now. Google brought me here.
# Steve 4/02/2009 7:07 PM	Thanks dude, you just saved me hours of time.
# Thomas Holm 3/07/2009 9:19 PM	Fabulous! Thank you for this tip...
# Kevin 4/07/2009 4:50 AM	I know you can use the Join function on parameters, but how can you use the Join function on a returned dataset? If you can't, is there something similar that allows you to aggregate the values of a returned dataset into one comma-separated field? Thanks in advance!
# Ron 22/07/2009 12:21 AM	Great information. Saved my butt!!! Thanks for the post, Ron
# Ned 12/12/2009 2:19 AM	Dude! You're totallt awesome. I've spent the last two hours trying to figure out exactly what you just described. Excelent! Thanks
# Mayumi 28/01/2010 1:33 AM	Awesome! Thanks!
# jvaldez 7/05/2010 12:19 AM	Thx so much...
# Poncho Rodriguez 29/05/2010 12:29 AM	Awesome function - thanks for leading the way. One thing I noticed is that it does not seem to work when using a Multi-Value, Non-Queried, Null default value parameter. Using the "Parameters!**.Value" seems to be the only expression that will return a value without a '#Error' or a blank, but it only displays the first value in that parameter set, even with the '=Join(Parameters!.Value, ","). Maybe I am doing it wrong? BTW - another way I found helpful to the users is to display selectable and available parameters from a select query was to join the item and description fields in the query. E.G. "SELECT item, (item + description) as label FROM table" This looks really good on a report when using the example "Parameters!*.Label" above.
# TC 20/09/2010 1:58 PM	Excellent! Thank you :)
# Bill Roe 5/02/2011 8:57 AM	Just what I was looking for! Thanks alot!
# THART 3/03/2011 3:25 AM	Perfect! Thanks!
# Dennis 22/04/2011 9:32 PM	If you are looking for better xml reporting software, please take a look at Windward's Xml Reports. With Windward you design reports in Microsoft Word, Excel, or PowerPoint so report design is a lot faster and easier - and non-programmers can design reports. xml reports www.windwardreports.com/.../xml-reports.htm
# The Timmer 27/07/2011 8:47 AM	Awesome! I have been banging my head on this one for awhile now. Thanks for the information!
# Geoff Johnson 28/07/2011 7:20 AM	I see you posted this tip five years ago. And it has been helping people ever since, including me! I needed this tip today. Your post saved me a lot of hassle trying to accomplish my goal. Thanks a bunch.
# Marcia Wilson 28/08/2011 5:21 AM	Thanks. This is just what I was looking for.
# Ivica 1/09/2011 5:37 AM	thanx, was trying to get this for long time.
# Albert 17/09/2011 6:10 AM	Thanks! This was not obvious even in Report Builder 3!
# Philipp 5/10/2011 11:22 PM	I have a problem which is related to this discussion and i hope someone can help me. I want to Pass Parameters to the Reportserver, which is not the problem. But I want to use e.g. the Built-in fields. so its like that: In RDL: textbox gets the value @reportfooter --> which is in the reportfooter the parameter sets the value to: Globals!PageNumber.... Now my Report should show the PageNumber in the ReportFooter, but it shows: Globals!PageNumber as plain text. Is there a possibility to pass parameters that the function behind is used? My problem is, that i want to use somethings like wildcards in my webservice: My Pagefootertext - %%PAGENUMER%% -- some other text. Now my webservice changes the %%PAGENUMER%% to Globals!PageNumber and the Report shows the number. I hope someone can help me.
# Liz 18/01/2012 9:20 PM	Brilliant! Thank you!
# MakStir 26/04/2012 12:41 AM	I'm already familiar with using the .Label for the Parameters which work quite fine. The problem I'm experiencing is when I run the report from a portal site, it is not showing anything....nothing. Anyone encountered this and have a solution?
# MakStir 26/04/2012 12:57 AM	Found the solution myself. When the report is run from within Visual Studio during development, it presents boolean parameters with a drop down combo box. When the report is deployed to the portal, the boolean parameter is shown as two radio button representing True and False. The only work around I came up with for this to show on the report correctly is by setting the textbox on the report as follows: =IIF( Parameters!IncludeNewItems.Value, "Yes", "No" ) PROBLEM SOLVED! If you find a better solution, let me know. Thanks.
# Wayne 26/07/2012 8:37 PM	A 6 year old thread that has saved me from going mad! Thank you
# Eric 27/07/2012 5:06 AM	I, too, was saved by this thread! Thank you, Matt!
# Ann 23/11/2012 10:52 PM	please help me. how to join fields value in a single row ?
# Ann 23/11/2012 10:56 PM	throwing error When i gave =join(Fields!PRODUCTID.Value,","). do we have any other option to join the table field value in SSRS 2005 report ? please help me.......
# mabster 24/11/2012 9:48 AM	Ann, Fields are, by definition, single values (the value of that field in that particular row). They don't hold multiple values like a parameter can, and therefore can't be joined.
# d-cpt 15/12/2012 1:45 AM	Thanks for the great post. May I ask for a little more as can we list the collection of selected values of a param in bullet style instead of comma separate.
# John 7/02/2013 12:45 AM	Thank you for providing some product documentation - something traditionally created by manufacturers, but now Microsoft literally has its user community working to provide, without lowering license fees.
# Nilesh 26/03/2013 7:05 AM	thx man.. it solved my problem in just 5 minutes !!!
# Terri 13/04/2013 1:21 AM	Thank you!!!!
# Egidio 19/04/2013 9:31 PM	hello guys , I have a problem with a label for a series.The label is showed all along the line but i want the label just one side for example right side.I do not need the label of the series inside the graph.Thank you in advance
# xyz 25/07/2013 5:15 PM	I want to use join function like if i passed id of parameter then i want to show the name of that fields of particular id with multiple selection please help as soon as possible
# kk 30/07/2013 12:04 AM	Thanks a lot this solved my problem.
# Adnan 6/03/2014 5:00 AM	Thanks a lot. Finally solved the issue using method you told. Thanks a lot
# Dee Law 22/04/2014 12:54 AM	Hey Matt, Can help me out with this ? www.codeproject.com/.../SSRS-Grouping-o
# Abe 23/07/2014 4:40 AM	8 years letter and still relevant. Thanks for this tip!
# mukesh bhagatat 20/08/2014 11:17 AM	I have one more question. can I show report header above the report parameters (listing ) in report view using SSRS? I have searched on google but didn't find any solution. can you please suggest about it.
# Nicole 7/10/2014 6:49 AM	Yeah for this long-lived thread! However it is not working for me. What am I doing wrong? Title is: Average CPU Queue for Parameters!ServerID.Label but it displays exactly as typed without replacing Parameters!ServerID.Label with the server name (even though selection of Server Name/Server ID seems to be working correctly) Thanks!
# Nicole 7/10/2014 6:59 AM	Sorry, should mention that I'm working in SQL Server 2012 SP 2 Standard Data Tools, and when the report builds it says "Building the report ... for SQL Server 2008 R2 Reporting Services".
# mabster 7/10/2014 11:15 AM	Hi Nicole, You can't just use this expression in the text. Your TextBox will have to have a proper expression to build the string. Something like this: ="Average CPU Queue for " & Parameters!ServerID.Label Try that and see how you go.
# Barb 7/03/2015 3:59 AM	Hi, I have a question regarding parameters in SSRS. Is there a way to group in a parameter? I have a query that uses dept IN (1,2,3,4,5,6,etc). Is there a way for a user to select a parameter for dept A (which includes 1,2,3) OR select dept B (which includes 4,5,6,etc) without having to select each individual dept number?
# mabster 10/03/2015 10:30 AM	Hi Barb, I would handle that sort of scenario either in the database (by adding a 'category' sort of field to the departments table) or in the query, by using CASE statements in the WHERE clause to pull specific records based on whether dept A or B was selected.
# Jos 16/05/2015 4:05 AM	Hi, I need to change the parameter day MM/dd/yyyy to visualize as dd/MM/yyyy. Can you help me?
# jamal ahmad 18/05/2015 9:38 PM	=Parameters!Farm.value replace value with Label =Parameters!Farm.Label
# JC 29/05/2015 7:19 AM	Hi, how can i make an expression to visualize only the 15 first days of every month grouping by year.
# Kevin Kueny 2/11/2016 7:55 AM	Thanks, Nice! I should've thought of that, but I sometimes forget to see what options are there besides .value
# Karyn H 5/05/2017 10:02 AM	Thank you so very much! You're a life saver! Spent hours searching for this solution before I came across your site. I appreciate you sharing this!
# dendroicist 3/10/2017 3:10 AM	You're still saving lives! Found this today after screwing myself into the ground over this labeling issue. Much thanks!
# panlisa 6/12/2017 10:12 PM	such a great help thank you. this is what i searching for

Mad Props!

Tags