Solved

How to Load External Images into a Reporting Services Report Dynamically

Posted on 2010-08-12
34
3,040 Views
Last Modified: 2012-05-10
I want to load images into a report dynamically for a specific product. For each product I seek to view the report for, there may be no image, one image or multiple images. These images are stored in an external file system and their path is stored in a database table column called PhotoLink. I run a stored procedure that accepts ProductNumber as an input parameter and returns the file path for the image(s) stored in the Database table PhotoLink column. The path looks similar to this one:

file://ABC02YGK/Photos/XYZ-12342-002 (1000 XXX)[HB]-1.jpg

In the reports layout view, I have added an image control from the toolbox and set the following properties:

Source=External
Value =Fields!PhotoLink.Value
Sizing=FitProportional

When I run the report, the report asks for the ProductNumber as input parameter and correctly renders the image in case the product has only one image path stored in the database table column “PhotoLink”. Until here there is no issue. However, I need to further enhance the report and should dynamically add and populate single or multiple image controls to the report depending upon the number of images this product contains. Is this possible at all and how can I achieve this?
0
Comment
Question by:skaleem1
  • 19
  • 12
  • 3
34 Comments
 
LVL 10

Expert Comment

by:itcouple
ID: 33423754
Hi

Created report with product parameter insert table with on details row and one column, create dataset to return paths based on your parameter insert image into the table and set expression to =Fields!myField.value. Then in your main report destination 'cell' add the new report as subreport and pass the parameter.

I have just tried that and it worked on my PC.

I think that is what you are after.

Regards
Emil
0
 
LVL 1

Author Comment

by:skaleem1
ID: 33423874
I am a novice to SSRS. Can you please walk me through. So are you saying that I should create a new report and insert the table in it and then insert image in the details column of that table?
0
 
LVL 27

Expert Comment

by:planocz
ID: 33424021
your value has to have the whole directory path. or a dataset filed that has the directory in it.
Example:      =http://ABC02YGK/Photos/XYZ-12342-002 (1000 XXX)[HB]-1.jpg
Also the directory where the images are has to have perrmissions from the web and/or server to the report.

0
 
LVL 1

Author Comment

by:skaleem1
ID: 33424553
itcouple,

As you suggested, First step - I have created a new report with product number parameter, inserted a table with one details row and one column, created dataset to return paths based on my parameter, inserted image into the table and set expression to =Fields!myField.value. When I run the report it works fine in the preview and I can see the image.

What is the next step as my main original report does have an independent image control. It is not inside any table cell. Which destination cell are you talking about where I need to add the new report I created in the first step as subreport and pass the parameter.
?
0
 
LVL 10

Expert Comment

by:itcouple
ID: 33425053
Hi

If you have one 'row' detail then chose of the cells in this row and insert subreport from the toolbox. right click the subreport go to properties and select the report you created. If you are using 2008 then on the left you should see Parameters. Click Add button and in the drop down box you should see the parameter you created for the subreport. Select it and provide value. which in your case will probably be Fields!ProductNumber.Value and try it. If you have multpli photos then the right height should automatically adjust itself to the number of photos you have in subreport for each row.

Let me know if you get stuck with it.

Regards
Emil
0
 
LVL 10

Expert Comment

by:itcouple
ID: 33425085
sorry for gramma errors. and missed words... it's late here :)
0
 
LVL 1

Author Comment

by:skaleem1
ID: 33430165
I am using SSRS 2005 and I have tried the following:

First step - I have created a new report named "ImagesReport" with product number parameter, inserted a table with one details row and one column, created dataset to return paths based on my parameter, inserted image into the table and set expression to =Fields!ProductNumber.value. When I run the report it works fine in the preview and I can see the image.

Second Step - In my existing report, I removed the image control and replaced it with a one row detail table - inserted a subReport control in the first cell of the table, right clicked the subReport control -selected properties - Subreport Properties dialog box opens - in the General tab, I have selected the subreport "ImagesReport" I created in the first step - in the Parameters tab, I have selected ProductNum as the Parameter Name in the LHS column, and selected =Fields!ProductNumber.value as the Parameter  Value in the RHS column - Clicked OK.

The original Report called "ProductInfoSheet" also accepts the same ProductNum parameter to return the data other than the images. Now when I run the report, the report returns all the data but I do not see the images in the subReport section. Is there anything I am missing?
0
 
LVL 1

Author Comment

by:skaleem1
ID: 33430185
Forgot to mention that the images are not displayed in the subReport section but there is no error
0
 
LVL 10

Expert Comment

by:itcouple
ID: 33430600
Hi

I don't see anything wrong with what you did. Not sure why it doesn't display the images properly.

Can you attach the RDLs? I will try it at home (Just finishing work)

Have a nice weekend
Emil
0
 
LVL 1

Author Comment

by:skaleem1
ID: 33431159
Thanks for your reply. For security reasons, I have slashed some info from the two rdl files. Please see the attached - also don't forget to remove the txt extension...
ImagesReport.rdl.txt
SellSheetImages.rdl.txt
0
 
LVL 10

Expert Comment

by:itcouple
ID: 33432035
Hi

I've checked the files and I think you don't have dataset selected for the table where you have image. You will need dataset which returns 1 row so it can use the subreport. I will test it now.

Regards
Emil
0
 
LVL 10

Expert Comment

by:itcouple
ID: 33432068
hmm or maybe not. It seems it works on my without dataset (the one I created previously)
0
 
LVL 10

Expert Comment

by:itcouple
ID: 33432122
Actually in your case that will is probably the case as you refer to Fields!PartNumber.value so you do need dataset for the table.

Let me know how it goes.

Regards
Emil
0
 
LVL 1

Author Comment

by:skaleem1
ID: 33432729
How and where would I select the dataset for the table? Any quick tips?
0
 
LVL 1

Author Comment

by:skaleem1
ID: 33432938
Well I added the dataset to the table and now it works but there is an issue. I want three images per row of the table and any further images should move down to the second row. How can I achieve this?
0
 
LVL 27

Expert Comment

by:planocz
ID: 33433656
Make smaller pictures.
0
 
LVL 1

Author Comment

by:skaleem1
ID: 33433719
can you pls elaborate?
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 27

Expert Comment

by:planocz
ID: 33433885
You need to adjust the image box smaller if you are using cell sizes then you need to change to cells by merge cells together then add Rectangle  in large rectangle cell unit and then add textboxes to hold your text info about the images.
0
 
LVL 1

Author Comment

by:skaleem1
ID: 33435353
I am a little lost. Do I need to make the image box of the subreport smaller? How would I have three images per row of the table with additional images pushed to the second row of the table? The dataset for the subreport only returns one column called Photolink that contains multiple rows to the image file path, e.g

Photolink
file://ABC02YGK/Photos/XYZ-12342-001 (1000 XXX)[HB]-1.jpg
file://ABC02YGK/Photos/XYZ-12342-002 (1001 XXX)[HB]-1.jpg
file://ABC02YGK/Photos/XYZ-12342-003 (1002 XXX)[HB]-1.jpg
file://ABC02YGK/Photos/XYZ-12342-004 (1003 XXX)[HB]-1.jpg
file://ABC02YGK/Photos/XYZ-12342-005 (1004 XXX)[HB]-1.jpg

First three images should appear in the first row of the table in the three columns and the remaining three images should appear in the second row of the table in the first two columns. Any ideas how to best achieve it?
0
 
LVL 10

Expert Comment

by:itcouple
ID: 33435746
Hi

If this answered your original question please accept my solution.

Regarding the second question. This is achievable what you have to do is to divide your subreport query itno 3 rows (one group) so you have 8 picutres you will have group 1 (picture 1,2,3) Group 2 (picture 1,2,3) Group 3 (Picture 1,2) Then you have in subreport grouping on rows (group) but you also change it to matrix so picture 1,2,3 field is in matrix column. That will give you desired result.

I have a function to devide query into equal rows and I will send it to you. Regarding setting up matrix let me know if this is clear.... My partner Katie will actually try that as well as she wants to practise matrix data regions :p

Regards
Emil
0
 
LVL 10

Expert Comment

by:itcouple
ID: 33436235
Hi

I've written something about it here http://www.itcouple.co.uk/T-SQL-Function-Grouping-Rows-in-query-result.aspx

If it doesn't make sense let me know :)

We will also try to do the subreport

Regards
Emil
0
 
LVL 1

Author Comment

by:skaleem1
ID: 33437406
Great. The first step to convert it into a matrix worked perfectly and I got the following results:

PartNum      Color      PhotoLink      RowGrouping
XYZ-12342-001      Pink      file://ABC02YGK/Photos/XYZ-12342-001 (1000 XXX)[HB]-1.jpg      1
XYZ-12342-001      Pink      file://ABC02YGK/Photos/XYZ-12342-001 (1001 XXX)[HB]-1.jpg      1
XYZ-12342-001      Pink      file://ABC02YGK/Photos/XYZ-12342-001 (1002 XXX)[HB]-1.jpg      1
XYZ-12342-001      Pink      file://ABC02YGK/Photos/XYZ-12342-001 (1003 XXX)[HB]-1.jpg      2
XYZ-12342-001      Pink      file://ABC02YGK/Photos/XYZ-12342-001 (1004 XXX)[HB]-1.jpg      2

I guess I need help in achieving the second step i.e. to divide my subreport query into 3 columns (one group) so I have 5 pictures (in this case), I will have group 1 (pictures 1,2,3) and Group 2 (pictures 4,5). Then I will have grouping on rows (group) in subreport. I need walkthrough on this one. If you want, I can open a new question after accepting this and send you the link. Please let me know.

Thanks for your help.

0
 
LVL 10

Expert Comment

by:itcouple
ID: 33438166
Hi

Actually I have missed Column Grouping from the query ;P. Here the proper SQL (it uses Common Table Expression (CTE) which you might not be familiried if not just think about it as a view :)

with t AS
(
SELECT
        PROD_ID,
      PROD_Name,
      PRPH_Link,
      dbo.udfRowNumberGrouping(
      ROW_NUMBER() over(PARTITION BY PROD_ID
      order by PRPH_ID),3) as RowGrouping
FROM PROD_Product
      INNER JOIN PRPH_ProductPhoto
      ON PROD_ID = PRPH_PROD_ID
)
      select *, ROW_NUMBER() over(PARTITION BY t.PROD_ID, RowGrouping
      order by t.PROD_ID) as ColumnGroping  from t

Notice number 3 before 'AS RowGrouping' which actually will be used to do ColumnGrouping.......sound strange..... anyway in subreport insert matrix data region from toolbox and set row group for productID and second grouping for RowGrouping Field and in the column grouping set one group using 'ColumnGroping' field then just insert your picture inside and use the expression you had previously to get the link.

I'm attaching a picture which shows product 1 with 2 picturtes, product 2 with one picture and product 3 with 3 pictures. Don't worry about empty space in product 2 you won't get it as you have only one product per subreport.

I hope that makes some sense...

Regards
Emil
PhotoTable.JPG
0
 
LVL 10

Expert Comment

by:itcouple
ID: 33438181
bdw the picture show (2 column not 3 this is only because I run with number 2 not 3)
0
 
LVL 1

Author Comment

by:skaleem1
ID: 33438283
Here is my query that generated the results above. Can you help me include the column grouping please?

SELECT TOP 5 b.AccID, b.AccPartNum, Color, PhotoLink
      dbo.udfRowNumberGrouping(ROW_NUMBER() over(PARTITION BY AccID
    ORDER BY HWDetailsID),3) as RowGrouping FROM HWDetails a
      JOIN Accessories b ON a.HardwareID=b.AccID
      WHERE HardwareID=@AccID
0
 
LVL 1

Author Comment

by:skaleem1
ID: 33438323
I have modified it and now I have the revised one as follows:

with t AS
(
SELECT TOP 5 AccID, b.AccPartNum,  PhotoLink,
      dbo.udfRowNumberGrouping(ROW_NUMBER() over(PARTITION BY AccID
    ORDER BY HWDetailsID),3) as RowGrouping FROM HWDetails a
      JOIN Accessories b ON a.HardwareID=b.AccID
      WHERE HardwareID=2386
)
      select *, ROW_NUMBER() over(PARTITION BY t.AccID, RowGrouping
      order by t.AccID) as ColumnGroping  from t


and I have the following results:

AccID      PartNum      Color      PhotoLink      RowGrouping      ColumnGrouping
25      XYZ-12342-001      Pink      file://ABC02YGK/Photos/XYZ-12342-001 (1000 XXX)[HB]-1.jpg      1      1
25      XYZ-12342-001      Pink      file://ABC02YGK/Photos/XYZ-12342-001 (1001 XXX)[HB]-1.jpg      1      2
25      XYZ-12342-001      Pink      file://ABC02YGK/Photos/XYZ-12342-001 (1002 XXX)[HB]-1.jpg      1      3
25      XYZ-12342-001      Pink      file://ABC02YGK/Photos/XYZ-12342-001 (1003 XXX)[HB]-1.jpg      2      1
25      XYZ-12342-001      Pink      file://ABC02YGK/Photos/XYZ-12342-001 (1004 XXX)[HB]-1.jpg      2      2
0
 
LVL 1

Author Comment

by:skaleem1
ID: 33438329
let me try the results formatted better:

AccID      PartNum            Color      PhotoLink                                    RowGrouping      ColumnGrouping
25      XYZ-12342-001      Pink      file://ABC02YGK/Photos/XYZ-12342-001 (1000 XXX)[HB]-1.jpg      1      1
25      XYZ-12342-001      Pink      file://ABC02YGK/Photos/XYZ-12342-001 (1001 XXX)[HB]-1.jpg      1      2
25      XYZ-12342-001      Pink      file://ABC02YGK/Photos/XYZ-12342-001 (1002 XXX)[HB]-1.jpg      1      3
25      XYZ-12342-001      Pink      file://ABC02YGK/Photos/XYZ-12342-001 (1003 XXX)[HB]-1.jpg      2      1
25      XYZ-12342-001      Pink      file://ABC02YGK/Photos/XYZ-12342-001 (1004 XXX)[HB]-1.jpg      2      2
0
 
LVL 1

Author Comment

by:skaleem1
ID: 33438343

Sorry, I have slashed the file path to make it look better:
 
AccID PartNum  Color PhotoLink      RowGrouping ColumnGrouping
25 XYZ-12342-001 Pink file://ABC/Photos/XYZ-12342-001 (1000)[HB]-1.jpg 1  1
25 XYZ-12342-001 Pink file://ABC/Photos/XYZ-12342-001 (1001)[HB]-1.jpg 1  2
25 XYZ-12342-001 Pink file://ABC/Photos/XYZ-12342-001 (1002)[HB]-1.jpg 1  3
25 XYZ-12342-001 Pink file://ABC/Photos/XYZ-12342-001 (1003)[HB]-1.jpg 2  1
25 XYZ-12342-001 Pink file://ABC/Photos/XYZ-12342-001 (1004)[HB]-1.jpg 2  2
0
 
LVL 1

Author Comment

by:skaleem1
ID: 33438362
AccID PartNum       Color PhotoLink                               RowGrouping ColumnGrouping
25    XYZ-12342-001 Pink  file://Photos/XYZ-12342-001 (1000).jpg  1            1
25    XYZ-12342-001 Pink  file://Photos/XYZ-12342-001 (1001).jpg  1            2
25    XYZ-12342-001 Pink  file://Photos/XYZ-12342-001 (1002).jpg  1            3
25    XYZ-12342-001 Pink  file://Photos/XYZ-12342-001 (1003).jpg  2            1
25    XYZ-12342-001 Pink  file://Photos/XYZ-12342-001 (1004).jpg  2            2
0
 
LVL 1

Author Comment

by:skaleem1
ID: 33438371
sorry, the above looks better. Above is the result I am getting from the query. What can now I do with the subreport.
0
 
LVL 10

Accepted Solution

by:
itcouple earned 500 total points
ID: 33439660
Hi

Now that you have partNumber, Row Grouping and ColumnGrouping you have to add matrix data region from the toolbox (We will delete the old one in the report). Click the matrix and at the bottom you should see row / column grouping. If your dataset is filtered by product then just modify row grouping and in the group specify 'RowGrouping' Field. the same for ColumnGrouping specify 'ColumnGrouping' Field. Then image with expression. and remove one row and one column (just row/column WITHOUT associated groups)

See attached Photo.

Hope that helps
PhotoTableInstructions.JPG
0
 
LVL 1

Author Closing Comment

by:skaleem1
ID: 33441156
itcouple,

This worked perfectly for me. I will accept this as a solution and will post you a link of a new thread related to this report. Since you know all the background, I think you are the best person to answer to this,

Thanks for all the help
0
 
LVL 1

Author Comment

by:skaleem1
ID: 33441177
Please see the following open question I just added to ee:

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/MS-SQL_Reporting/Q_26404197.html

Thanks for all the help
0
 
LVL 1

Author Comment

by:skaleem1
ID: 33496817
itcouple,

Please see the following question I just opened and is an extension to this question and you can understand the background faster to understand it:

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/MS-SQL_Reporting/Q_26420983.html

Please help
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now