SSRS question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Analysis Server and Reporting Services (2008)

SSRS question

Author

Topic

rockstar283
Yak Posting Veteran

96 Posts

Posted - 2011-07-08 : 12:49:15

I have a column product_no in table Product..the values are like 0000000000000000000000000000000000060480, 0000000000000000000000000000000000116788, 0000000000000000000000000000000000119726, 550-500569, 320-00185, 310-PC852912A-000A etc.

So, the requirement is that to show these product nos. without 0s like 60480,116788,119726,550-500569,320-00185, 310-PC852912A-000A to the user..which I have achieved using

SELECT DISTINCT substring(product_no, patindex('%[^0]%',product_no), 18) as NEW_prod_no FROM XXX

Now my problem is that this product no. field/parameter is a multi-valued parameter..so when the user selects multiple products..I get the values back as..

'60480,116788,119726,550-500569,320-00185, 310-PC852912A-000A'

Now I have to insert those 0s back into the product nos. so that I can process them..

e.g. my query will be

select product_name from xxx where product_no in ('0000000000000000000000000000000000060480', '0000000000000000000000000000000000116788', '0000000000000000000000000000000000119726', '550-500569', '320-00185', '310-PC852912A-000A' )

Can anybody help me in getting this logic worked out..thanks in advance..

rockstar283
Yak Posting Veteran

96 Posts

Posted - 2011-07-08 : 13:27:03

Got the answer:

In the report parameters dialog box, you can set the label and the value to be different. The label is what people see, the value is what is used.

For example, if you change your dataset query to return two fields (the unchanged product_no and the new product_no), then you can select the label to be the new product_no field and the value to still be the old product_no field. E.g.

SELECT DISTINCT product_no, substring(product_no, patindex('%[^0]%',product_no), 18) as NEW_prod_no FROM XXX

That way, people don't see the zeroes in the parameter drop-down, but they are still there for your query.

Thanks to Leonard for this..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources