CODEWITHSUNDEEP
sql-server-2008reporting-servicesssrs-2008ssrs-grouping
D.R.
D.R.

Reputation: 1219

SSRS report with multiple child tables

So I am working on an Invoicing Report in SSRS 2008. The database contains 4 relevant tables:
- Work Order
- Labor
- Materials
- Services (Subcontractors)

Obviously the work order table contains all the relevant information about the overall work order (we display things such as location, priority, etc). For this invoice report, I need to display the work order details up at the top, then show the labor, materials, and services used on the work order (with totals for each), then show a complete total for the entire cost of the work order.

My issue is this: I can do a dataset that works with Work Order + any ONE of the child tables, however I cannot figure out how to do all 3! I can't simply do a parameter for WONUM with 3 (or 4) tables on it, because this report will have MANY work orders (one per page) on it. When I use a dataset with the Work Order table and one child table, I group by WONUM then do a page break between each instance.

Any ideas for how to handle it? Most answers I came across say make one gigantic "union all" dataset and then group it after that, or use subreports for each child table. However, we will be exporting this report to Excel, and I've been told that subreports do not render properly when exported.

Any and all help is greatly appreciated! Thanks!

EDIT:

Below are the 4 queries I'd LIKE to use:

This retrieves all the work orders that need to be billed:

SELECT wonum, description, location FROM workorder WHERE billable=1 AND status='COMP'

This retrieves the labor for a work order (specified by @wonum)

SELECT regularhrs, laborrate, totalcost FROM labor WHERE refwo=@wonum

This retrieves the materials for a work order (specified by @wonum)

SELECT description, quantity, unitcost, totalcost FROM material WHERE refwo=@wonum

This retrieves the services (subcontractor hours) for a work order (specified by @wonum)

SELECT description, hours, laborrate, totalcost FROM service WHERE refwo=@wonum

So as I stated in the original post, my first query retrieves all the work orders that need to be billed, then for each work order (one per page), I need to retrieve the labor, materials, and services and display it in 3 tables below the work order details, then put an overall total cost on the work order invoice (at the end of each work order, not the end of all work orders)

I can get a screenshot of my current report if that would help also. Just let me know in comments!

Upvotes: 3

Views: 1487

Answers (1)

StevenWhite
StevenWhite

Reputation: 6034

Your query should look something like this

SELECT WO.wonum, WO.description as WorkorderDescription, WO.location, L.regularhrs, L.laborrate, L.totalcost,
M.description as MaterialDescription, M.quantity, M.unitcost, M.totalcost as MaterialCost,
S.description as ServiceDescription, S.hours, S.laborrate, S.totalcost as ServiceCost
FROM workorder AS WO
INNER JOIN labor AS L on L.refwo = WO.wonum
INNER JOIN material AS M on M.refwo = WO.wonum
INNER JOIN service AS S on S.refwo = WO.wonum
WHERE billable = 1 AND STATUS = 'COMP'

This will efficiently gather the information you need into one dataset. You will need to use the grouping features to setup the table in SSRS. You may have to do some additional research if you get stuck on getting the table layout right.

Upvotes: 1

Related Questions