How to map a parameter in report builder 3.0 into SQL Query to retrieve data from the database

Hi all,

Currently, I want to get images of an item in my report for illustration purpose.

Below is my query to get image for item 'GL-10000' in the database. However, I would like to pass a parameter value, '@sItem', from my report so that it would get all related pics of items.  

SQL Query:

Select top 1 item_picture_mst.picture from item_picture_mst
where item_picture_mst.item in (select item_all.item from item_all where item_all.item = 'GL-10000')
ORDER BY item_picture_mst.picture DESC;

Thanks

Cheeers,

Jack

May 28th, 2015 10:43pm

Please try below, if that is not solved - explain how you want to pass values to parameters for '@sItem'.

Select top 1 item_picture_mst.picture from item_picture_mst
where item_picture_mst.item in (select item_all.item from item_all where item_all.item IN ('GL-10000'))
ORDER BY item_picture_mst.picture DESC;

Free Windows Admin Tool Kit Click here and download it now
May 28th, 2015 10:51pm

Hi Maruthi

Here is what I want to have

  SQL Database                                                                                Report on Report Builder 3.0

The query which bound to fixed value "GL-10000"    Here, I have a parameter [@sItem]                                                                                 value=Parameter!sItem.Value.

                                                                            When I ran this report, it will show

                                                                                           a list of item 

                                                                                       Item        Picture

                                                                                      Item A

                                                                                      Item B

                                                                                                  Item C

I want to pass the value of 'sItem' to the query above so that I can proper images for the nearby column.

P/S I'm a newbie so I can't upload the image for an clear picture. :(

Thank you very much for your help.

Jack 



  • Edited by Jack Hua 3 hours 44 minutes ago
May 28th, 2015 11:20pm

Hi Maruthi

Here is what I want to have

  SQL Database                                                                                Report on Report Builder 3.0

The query which bound to fixed value "GL-10000"    Here, I have a parameter [@sItem]                                                                                 value=Parameter!sItem.Value.

                                                                            When I ran this report, it will show

                                                                                           a list of item 

                                                                                       Item        Picture

                                                                                      Item A

                                                                                      Item B

                                                                                                  Item C

I want to pass the value of 'sItem' to the query above so that I can proper images for the nearby column.

P/S I'm a newbie so I can't upload the image for an clear picture. :(

Thank you very much for your help.

Jack 



  • Edited by Jack Hua Friday, May 29, 2015 3:23 AM
Free Windows Admin Tool Kit Click here and download it now
May 29th, 2015 3:16am

Hi Jack,

According to your description, you have stored the pictures in the database. The table should have two columns: [Item] and [Picture]. Then you want to create a report, when you select values form the repot parameter, corresponding item value and picture will display, right?

In your scenario, you could create a report parameter and add a where clause on query level. Please refer to steps below:

1. Create a report parameter @Item, select Allow multiple values in General tab within Report Parameter Properties. Then specify the Available values like below:

2. In the dataset, add a where clause to the query like below:

select *from t0601 where item in (@Item) order by JPG

3. Design the report like below, filter the report based on parameter values:

Reference:
Tutorial: Adding Parameters to a Report (SSRS)
Lesson 7: Creating an SSRS parameterized report 

If you have any question, please feel free to ask.

Best regards,
Qiuyun Yu

June 1st, 2015 2:58am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics