Solved

Reporting Services - SubReport enhancement

Posted on 2010-08-23
10
694 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
 
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

760 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

16 Experts available now in Live!

Get 1:1 Help Now