Post reply

SSRS question

  • July 8, 2011 at 10:50 am

    #253419

    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..

  • July 8, 2011 at 10:59 am

    #1350523

    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.

    Leonard
    Madison, WI

  • July 8, 2011 at 11:26 am

    #1350537

    Thanks a lot mate....that was the perfect solution I was looking for..Thanks a lot mate..You are the savior 😀

  • July 8, 2011 at 11:51 am

    #1350557

    You're welcome - glad I could help!

    Leonard
    Madison, WI

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply