Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Reporting Services - SubReport enhancement

Posted on 2010-08-23
10
708 Views
Last Modified: 2012-06-27
I have a subreport that displays matrix in a matrix control. Please review the details in the earlier question I posted few days ago:

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

This sub report returns multiple images in multiple rows and columns, all images are of same size. The new enhancement of this subreport requires that the first image returned from the query should be displayed as a large sized image in the first column and the remaining images should be displayed in smaller sizes in multiple columns and rows located on the RHS of the left hand side large sized image. The attached file is a screenshot of the image layout. Can you please help with this. It appears that we have to display the top image returned in a regular image control located on the LHS and the remaining images as 4 columns in a Matrix control, basically an extension of what we did in the referenced question link. Please see the screenshot and suggest.

Thanks.


Images-Layout.JPG
0
Comment
Question by:skaleem1
  • 6
  • 4
10 Comments
 
LVL 10

Expert Comment

by:itcouple
ID: 33505281
Hi

Another challenge? Someone seems to use a lot of creativity :) I like that.

I've just tested one approach and it might work

Create a table with 2 column one row.
in first column place the image + size you want. and created dataset to populate it (one picture per productID?) then convert your matrix report to subreport and place it into second column... Pass the ID + image path you have already used so you can get all pictures for partnumber ID without the already used image.

That seems to work find on my report. Even default re-sizing might be appropriate.

Hope that helps

Let me know how it goes I will reply tomorrow ;)

Regards
Emil
0
 
LVL 1

Author Comment

by:skaleem1
ID: 33505455
That appears to make sense. First question, the same dataset we used today that returned matrix subreport, how can I have one top image returned for the main report image control and then the remaining images for the subreport. I am talking in terms of query - for the part numbers I am going to pass to the main report as comma delimited parameters, how can I get the top partnumber and get the top 1 image, then use the remaining partnumbers in the string to be passed to the sub report? any suggestions?
0
 
LVL 10

Accepted Solution

by:
itcouple earned 500 total points
ID: 33529228
Hi

Sorry for delay in reply....The answer was kind of included but not very clear I think :)..... create 2 parameters for subreport partnumber and image used as 'big' picture. this way you can get all images and exclude the one already used in the subreport/matrix by applying filter using the parameter value (image value).

I hope that helps.
Emil
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 1

Author Comment

by:skaleem1
ID: 33560402
Sorry for my delayed response. I got involved in some other stuff and did not get a chance to try your suggestions. I will try to get back to you soon after I try your suggestions...Thanks for your help.
0
 
LVL 1

Author Comment

by:skaleem1
ID: 33570821
OK, I tried today by doing the following:

I passed a single varchar input parameter to the Main report stored procedure. This input parameter is named @PartsList and contains multiple part numbers in comma delimited format. The main report stored procedure only takes the first part number from the @PartsList parameter string and returns it in the dataset to display the large image. I have also added an extra column to the main report dataset that has the @PartsList returned (the input parameter for the main report stored procedure). I pass this to the subreport as an input parameter. The subreport stored procedure uses all the parts from this list except the first one and returns the parts images in the matrix view as you instructed.

Based on the number of part numbers passed in the input parameter @PartsList, can I call different subreports from the main report, e.g, if there are 5 part numbers passed, then I call the subreport with the appropriate Matrix control and image sizes (say subreport1), if there are 10 part numbers passed, then I call a different subreport (say subreport 2) with the appropriate Matrix control and image sizes etc. Is this how I should do it, or can I have a single subreport dynamically changing the Matrix control and the image sizes based on the number of part numbers passed in the input parameter? What do you think
0
 
LVL 10

Expert Comment

by:itcouple
ID: 33571361
Hi

The answer is no! Unfortunatelly SSRS doesn't allow for dynamic subreports. But there is still hope. I think you can use the matrix one subreport to do it. You should be able to pass parameters to change number of column and pass parameters to change width/heigh if necessary.

Regards
Emil
0
 
LVL 1

Author Comment

by:skaleem1
ID: 33571612
How can I dynamically do it, do I have to write any coding in the sub report? any directions?
0
 
LVL 10

Expert Comment

by:itcouple
ID: 33588484
Hi

You will have to perform two tasks:
1) Set matrix columns dynamically by passing a parameter "number of columns" into the existing subreport dataset.
a) You might have to apply some logic on main report to get number of columns.
2) You should be able to change subreport cell width/height using expressions so for 3x3 you set 50x50px and for 2x2 75x75px

Hope that helps
Regards
Emil
0
 
LVL 1

Author Comment

by:skaleem1
ID: 33589855
First two, i.e 1) and a), should not be a problem. However, the last one i.e:

>>2) You should be able to change subreport cell width/height using expressions so for 3x3 you set 50x50px and for 2x2 75x75px <<

How can do it using expressions, remember the subreport is composed of a Matrix control. Inside the matrix control there is a list control. Inside the list control there is an image and a text box used for caption. Firstly, which control should I write the expression in, the list or the image control?


0
 
LVL 1

Author Closing Comment

by:skaleem1
ID: 33590554
I will accept this as a solution. Thanks for your help.

Please see the new thread I have just created for a related question I was asking in my last post:

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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

809 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