Solved

Reporting Services - SubReport enhancement

Posted on 2010-08-23
10
709 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
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

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

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

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

733 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