HELP WITH REPORT DESIGNER (SUBTOTALS)

Last Post 10 Mar 2005 07:10 AM by xfonhe. 1 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
wyvernstudios
New Member
New Member

--
27 Dec 2004 07:04 AM
Hello,
I'm a crystal user who just made a transition to SQL Reporting. I have a report thats been causing some headache for a while...
here is the structure
the report in Report designer has two tables
Table 1 has a dataset "CellHeaderQuery" that lists some values (group totals) that is built out of a stored procedure.

Talbe 2 has a dataset "Dataset1" that lists more detail (group totals, etc.) that is built out of a different stored procedure.

Both stored procedures are different and get data from different tables. the only things common are the group names.

This table structure is an existing report, I have to modify it so that the first table (which has the same groups) gets data in an additional column from data in the 2nd table.

For example.

TABLE 1

CELL ID COL1 COL2 COL3 COL4 COL5
12345 4 4 4 * *
1345 4 4 4 * *
145 4 4 4 * *
13345 4 4 4 * *

TABLE 2 (same groupings and vvalues for Cell ID)
CELL ID COL1 COL2 COL3
12345 4 4 4
1345 4 4 4
145 4 4 4
13345 4 4 4

what I need to do is to populate the cells marked with a * (for COL4) and COL5 with the values COL1 + COL2 in table 2 and (for COL5) the value of COL3 in table 2.
How can I do this?
thanks
xfonhe
New Member
New Member

--
10 Mar 2005 07:10 AM
Join the results from the two procedures.

CREATE TABLE #Temp (column definitions)
INSERT INTO #Temp
EXEC procedure1

CREATE TABLE #Temp2 (column definitions)
INSERT INTO #Temp2
EXEC procedure2


-- Data set for report
SELECT t1.Column,
...,
t2.Column,
...
FROM #Temp t1
(INNER/LEFT/RIGHT) JOIN #Temp2 t2 ON t1.Col = t2.Col


Acceptable Use Policy
---