May 1, 2008 at 8:47 am
I need a subreport within a main report.
The main report will print detail lines about a voucher and then will subtotal the cost for each voucher. After each voucher group, I need to show a subreport that shows the funding for the voucher. The subreport will use a different dataset from the main report, and the primary key will be the voucher id.
The costs listed for the voucher in the main report and the funding elements listed for the voucher in the subreport have a many-to-many relationship, so I have to use different datasets.
I need to know
a) how to relate the subreport to the main report through the voucher id
b) how to position the subreport so that it is integrated with the main report and not printed at the end of the report. The main report is in a table.
I have read many of the posts on this subject and have read chapters in books and columns at Microsoft. This must be a hard thing to explain because no one has made much sense yet.
If anyone out there can explain this in a step-by-step fashion, I would really appreciate it!
Thanks!
May 1, 2008 at 9:03 am
First if you are working with a sub eport your main report needs only 1 dataset for the voucher data and your subreport will have the dataset for the funding elements. The subreport must be created separately from the main report.
When working with a table in SSRS you can "nest" items in the table, so you can add a row to the footer of each Voucher Group and place your subreport there. Your subreport needs to have a voucher_id parameter which you will pass to it from the main report. When you put the subreport in the footer row, right-click it and select properties, then you will choose the Funding Elemenets report as the subreport. Go to the parameters tab and assign voucher_id from the main report dataset to the voucher_id parameter on the subreport.
The only issue with this is performance as the subreport will be called once for each voucher_id.
If it is possible to return all the data to the report in 1 dataset and then use SSRS grouping to deal with it you will get better performance. You can then nest a table in the group footer for the funding element data.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 1, 2008 at 10:19 am
Thanks very much for the information. That's very helpful.
I would love to avoid the subreport scenario. If I put a table in the footer, won't it just show up at the end of the report rather than after each grouping of the main report? Is there a way to pass the voucher Id to the nested table if it will print after each grouping?
May 1, 2008 at 10:38 am
When you group on the voucher_ID, you will automatically get a header and footer for the group in addition to the table header and footer.
You'll want your subreport in the group footer, not the table footer.
Also, I suggest you merge the individual cells of the the group footer row before you insert the subreport control. That'll save you having to readjust widths.
[font="Comic Sans MS"]toolman[/font]
[font="Arial Narrow"]Numbers 6:24-26[/font]
May 1, 2008 at 10:42 am
Hi Jack,
I was emboldened to give this a shot based on your information. I got the table in the footer but it all prints at the bottom of the report and the data is not related by voucher id. I included voucher id as a hidden field in the nested table hoping that the report would know I want the funding lines to show for each voucher. It didn't.
Is there a way around this?
May 1, 2008 at 10:47 am
I did put the table in the group footer. The table footer disappeared when I did this and all the nested stuff prints at the end. It's like the group footer became a table footer. I am unable to expand the primary table any to see exactly what happened.
May 1, 2008 at 11:13 am
Can you attach the rdl file for the report to the thread. Reporting Services can be difficult to explain in a forum without seeing what is being done.
It sounds as though you do not have a group on your table. If you have no groups on your table you can right-click on the left side of the detail row and select add row and then add the subreport to that second row.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 1, 2008 at 11:22 am
I think what's happening is you don't have your main report and subreport linked properly. You're likely just getting info back from the first record in the subreport dataset.
Does your subreport dataset query have a parameter set to gather data by individual voucher_id or is it just getting data for all vouchers?
If it's just getting data for all, you need to change it by inserting a where clause.
If you're using a SQL script inside your report, try something like:
SELECT...
FROM...
WHERE voucher_id = @voucher_id
If you're using a stored proc, you'll need to declare the parameter variable.
Example:
CREATE PROCEDURE proc_name
(@voucher_id VARCHAR(10))
/*assuming voucher_id is varchar, length <= 10*/
...
SELECT...
FROM...
WHERE voucher_id = @voucher_id
Then, back in the Layout window, right click the subreport control, select your subreport from the dropdown then select the Parameters tab. On the left side (Parameter Name) of the resulting screen, drop down and select the parameter. On the right hand side (Parameter Value), drop down and select the voucher_id field. This will link the subreport to the main report.
[font="Comic Sans MS"]toolman[/font]
[font="Arial Narrow"]Numbers 6:24-26[/font]
May 1, 2008 at 12:43 pm
I tried to upload the .rdl file but it's not allowed here.
May 1, 2008 at 1:07 pm
Yippee! I've having some success!
I realized I did have the nested table in the report footer and not the group footer. I tried again and got it in the group footer and now it seems to be working!!!
Thank you one and all!! :kiss:
May 1, 2008 at 1:10 pm
Attached is a zip file with a datasource and 2 reports. The orders report has the order details as a subreport. The datasource points to adventure works on the local sql server. You should be able to figure it out from this.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 1, 2008 at 1:28 pm
Thanks, Jack.
I will check that report out. Gotta run to a meeting now . . .
May 7, 2008 at 6:01 am
hi guyz i have a problem a bit similar to the post by sing4you... i created a report with two tables using one dataset and in my query i have it like this
create procedure MyProcedure
@BatchNo int
as
SELECT dbo.Properties.PIN,dbo.Properties.BatchNumber,Barcode,
dbo.Properties.Description, dbo.Properties.Extent
FROM dbo.Properties where dbo.Properties.BatchNumber = @BatchNo
the @BatchNo has many dbo.Properties.PIN so when i select BatchNo i need the report to display each record on each page but it's only display records for the first dbo.Properties.PIN.
my query runs fine in SQL Server but not displaying correctly on the report.
May 7, 2008 at 6:25 am
Nomvula,
Based on the information you provided it does not appear that you need 2 tables. You could do the report with 1 table with a group on Batch Number. Then put all the PIN information in the details row. On the group set the group header to repeat on new page and you will be able to see the Batch Number. If you do not want to see the PIN repeat on multiple records you can set that text box (cell on the detail row) to hide duplicates and each PIN will only show once. So you could have the report look like this:
Batch Number 1
PIN Description Extent Barcode
1 Test Many --------
Test 2 Few *******
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 7, 2008 at 7:10 am
thankx Jack for responding so quick
the thing is the format of my report is different from the normal layout,
i'm displaying the property information here's the layout of my report
my batchNo has 5 different PIN#
Table #1
Description Test Address: 121 South Road
Pin 1 Barcode: **********
Zoning AG
Table #2
OwnerName OwnerID Extent TitleDeed
Test 1234 100 T/1999
Test 2 1235 200 T/1999
so when i select BatchNo: 99999 on my dropdown i should have 5 pages for Pin#1 to #5
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply