Share Query between Report and Subreport

  • Thread starter Thread starter blinton25
  • Start date Start date
B

blinton25

Hello,

I have a report and subreport which use the same query,
but right now the query is run once for the main and once
for the subreport. Is there anyway to allow the subreport
to reuse the results of the main report query?
 
The only way (that I am aware of) to do this is to create a temporary table
of the results of the query.
 
Hi,

Intersting that you should say that, I am using a
temporary query, based on the following passthrough query,
so how would I achieve this?


CREATE PROCEDURE test2

@PrevYear1 varchar(4),@CurrYear1 varchar(4)

AS
DECLARE @MyTableVar TABLE (regionorder nvarchar
(2),Regions3 nvarchar(21),January nvarchar(10), February
nvarchar(10), March nvarchar(10),April nvarchar(10),May
nvarchar(10),June nvarchar(10),July nvarchar(10),August
nvarchar(10),September nvarchar(10),October nvarchar
(10),November nvarchar(10),December nvarchar(10))

-- Call UDF.

INSERT @MyTableVar SELECT
regionorder,Regions3,January,February,March,April,May,June,
July,August,September,October,November,December FROM
dbo.RegionofResidenceCrosstabFunc(@PrevYear1,@CurrYear1)

SELECT regionorder, Regions3, 1 As Quarter, January as A,
February as B, March as C FROM @MyTableVar
UNION ALL
SELECT regionorder, Regions3, 2 As Quarter, April, May,
June FROM @MyTableVar
UNION ALL
SELECT regionorder, Regions3, 3 As Quarter, July, August,
September FROM @MyTableVar
UNION ALL
SELECT regionorder, Regions3, 4 As Quarter, October,
November, December FROM @MyTableVar;
GO
 
I'm not sure what you are asking. You can create temporary tables on SQL
Server (check Books On Line) or in Access (except an ADP).
 
Hello,

I got the issue worked out using the temporary tables as
you suggested, and the performance has improved
significatly.
 
Back
Top