Multiple Columns in Parameter Field

Hi

I have a report that has a number of parameters in it that the user must select prior to running the report. One of these parameters is the site_ref that they wish to use against the dataset. All my books point to the fact that if you wish, you can select what appears in the parameter field so the user does not get confused. So rather than the user having a list of site_ref which maybe useless to them, they can have a description next to each entry, e.g.

site_Ref Estate_Name
AL Aladdin's Cave
TH Bug's Bunnies Home
XN Aliens Home

Then when the user selects what he wants from list, the actual value that is passed to the parameter is 'TH' for example. In Access I believe this was called Bound to column(?). I can find no where in the 'Report Parameters' dialog box that allows for multiple columns to be displayed. I have written the SQL to create the right data;

Select site_ref, estate_name
From dbo_src_centre_list
Order By site_ref

How can I get the parameter box in Reporting Services to allow the display of both columns but bind to the site ref when it comes to passing the info into the report. None of my books actually tell me how to do it though. I have found how to get one or the other to display and the right one to pass the actually value through, but I would like to see both in list at the same time

Regards

A report parameter valid values list contains a set of value/label pairs. When the report is run, the user sees the label. When the user selects a label, the corresponding value is used as the parameter value.

In your case, you just need to set the label of the report parameter to be bound to the Estate_Name field, while the value is bound to the Site_Ref field.

Additional information on parameters in reports can be found here: http://msdn2.microsoft.com/en-us/library/ms155917.aspx

-- Robert

|||

Thank you Robert. That is exactly what I did, what I was wondering though, is it possible to have two values showing in the parameter field e.g. AC - Aladdin's Cave and tie the value to just the AC.

I will read that link you have sent through and see what direction that pushes me in.

Regards

|||

One way of doing this is to construct the desired label directly in the query:

Select site_ref, site_ref + ' - ' + estate_name AS Label
From dbo_src_centre_list
Order By site_ref

-- Robert

|||

Thank you so much Robert, that is exactly what I wanted. Works perfectly.

Kindest Regards