Solved

Multiple result sets _OR_ multiple non-nested groups in Crystal Reports.

Posted on 2008-09-29
8
1,681 Views
Last Modified: 2010-04-21
Hi.

I've got a MS SQL 7 Stored Procedure.

It creates 7 temporary tables for its operation.

6 of these are returned as multiple result sets.

I would like to report upon the result sets in Crystal Reports.

The stored procedure generates snapshot data so I cannot run it 7 times and each time ask for a different result set.

I suppose I COULD store the result sets tagged to the user on the first run and each other run return the different set, but what a palaver!



I think that CR does _NOT_ support multiple result sets. At least that is what I've seen in action (no way to indicate multiple sets and in EE and Google searching).



So, I think my only option is to create a unified single result set with a marker to indicate the result set.

If that is the only way, how do I get different headers for each marker to indicate a different result set.


The result sets are not the same, so there would be lots of dead columns per marker.

Assuming this is the only way to go, how do I create multiple sections in CR so that section 1 is for marker 1, section 2 is for marker 2, etc.

Some markers may be empty, some over multiple pages, etc. A bit like sub reports but for a single stored procedure.


0
Comment
Question by:Richard Quadling
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 22595019
I would suggest you post some code.  Based on the nature of the code the answer varies...

HTH
0
 
LVL 40

Author Comment

by:Richard Quadling
ID: 22595224
You really want the SP?

Ok!

This is still in flux as the salesops team is providing me with the tests.
CREATE PROCEDURE dbo.LGV_DIFF_SOPINV_V3
	@s_Contract AS VARCHAR(20),
	@i_Year AS INTEGER,
	@i_Period AS INTEGER,
	@i_Week AS INTEGER
AS
 
IF OBJECT_ID('tempdb..#t_Accounts')          IS NOT NULL DROP TABLE #t_Accounts
IF OBJECT_ID('tempdb..#t_LGV1')              IS NOT NULL DROP TABLE #t_LGV1
IF OBJECT_ID('tempdb..#t_LGV2')              IS NOT NULL DROP TABLE #t_LGV2
IF OBJECT_ID('tempdb..#t_MatchingIDs')       IS NOT NULL DROP TABLE #t_MatchingIDs
IF OBJECT_ID('tempdb..#t_MultipleVehicles')  IS NOT NULL DROP TABLE #t_MultipleVehicles
IF OBJECT_ID('tempdb..#t_QuickAccounts')     IS NOT NULL DROP TABLE #t_QuickAccounts
IF OBJECT_ID('tempdb..#t_SOPINV')            IS NOT NULL DROP TABLE #t_SOPINV
IF OBJECT_ID('tempdb..#t_SuspendedVehicles') IS NOT NULL DROP TABLE #t_SuspendedVehicles
 
CREATE TABLE #t_QuickAccounts
	(
	AccountNumber VARCHAR(10)
	)
 
CREATE UNIQUE CLUSTERED INDEX ix_QuickAccounts
	ON #t_QuickAccounts
		(
		AccountNumber
		)
 
-- Create temporary accounts table with easy to use totals.
CREATE TABLE #t_Accounts
	(
	AccountNumber VARCHAR(10),
 
	SOPINV_Orders INTEGER DEFAULT 0,
	SOPINV_Details INTEGER DEFAULT 0,
	SOPINV_Nett MONEY DEFAULT 0,
	SOPINV_Uplift MONEY DEFAULT 0,
	SOPINV_Total MONEY DEFAULT 0,
 
	LGV1_Orders INTEGER DEFAULT 0,
	LGV1_Details INTEGER DEFAULT 0,
	LGV1_Nett MONEY DEFAULT 0,
	LGV1_Uplift MONEY DEFAULT 0,
	LGV1_Total MONEY DEFAULT 0,
 
	LGV2_Orders INTEGER DEFAULT 0,
	LGV2_Details INTEGER DEFAULT 0,
	LGV2_Nett MONEY DEFAULT 0,
	LGV2_Uplift MONEY DEFAULT 0,
	LGV2_Total MONEY DEFAULT 0
	)
 
CREATE UNIQUE CLUSTERED INDEX ix_Accounts
	ON #t_Accounts
		(
		AccountNumber
		)
 
-- Create temporary details tables.
CREATE TABLE #t_SOPINV
	(
	AccountNumber VARCHAR(10),
	OrderNumber VARCHAR(12),
	Entered DATETIME,
	Authorised DATETIME,
	Fax DATETIME,
	FaxCode VARCHAR(20),
	DetailPrimary INTEGER,
	PriceCode VARCHAR(20),
	Vehicle VARCHAR(20),
	WheelPosition VARCHAR(20),
	VehicleAccount VARCHAR(10),
	VehicleStatus VARCHAR(1),
	Quantity FLOAT,
	Nett MONEY,
	Uplift MONEY,
	Total MONEY
	)
 
CREATE UNIQUE CLUSTERED INDEX ix_SOPINV
	ON #t_SOPINV
		(
		AccountNumber,
		DetailPrimary
		)
 
CREATE TABLE #t_SuspendedVehicles
	(
	AccountNumber VARCHAR(10),
	OrderNumber VARCHAR(12),
	Entered DATETIME,
	Authorised DATETIME,
	Fax DATETIME,
	FaxCode VARCHAR(20),
	DetailPrimary INTEGER,
	PriceCode VARCHAR(20),
	Vehicle VARCHAR(20),
	WheelPosition VARCHAR(20),
	VehicleAccount VARCHAR(10),
	VehicleStatus VARCHAR(1),
	Quantity FLOAT,
	Nett MONEY,
	Uplift MONEY,
	Total MONEY
	)
 
CREATE UNIQUE CLUSTERED INDEX ix_SuspendedVehicles
	ON #t_SuspendedVehicles
		(
		AccountNumber,
		DetailPrimary
		)
 
CREATE TABLE #t_MultipleVehicles
	(
	AccountNumber VARCHAR(10),
	OrderNumber VARCHAR(12),
	Entered DATETIME,
	Authorised DATETIME,
	Fax DATETIME,
	FaxCode VARCHAR(20),
	DetailPrimary INTEGER,
	PriceCode VARCHAR(20),
	Vehicle VARCHAR(20),
	WheelPosition VARCHAR(20),
	VehicleAccount VARCHAR(10),
	VehicleStatus VARCHAR(1),
	Quantity FLOAT,
	Nett MONEY,
	Uplift MONEY,
	Total MONEY,
	VehicleRecordCount INTEGER
	)
 
CREATE UNIQUE CLUSTERED INDEX ix_MultipleVehicles
	ON #t_MultipleVehicles
		(
		AccountNumber,
		DetailPrimary
		)
 
CREATE TABLE #t_LGV1
	(
	AccountNumber VARCHAR(10),
	OrderNumber VARCHAR(12),
	Entered DATETIME,
	Authorised DATETIME,
	Fax DATETIME,
	FaxCode VARCHAR(20),
	DetailPrimary INTEGER,
	PriceCode VARCHAR(20),
	Vehicle VARCHAR(20),
	WheelPosition VARCHAR(20),
	VehicleAccount VARCHAR(10),
	VehicleStatus VARCHAR(1),
	Quantity FLOAT,
	Nett MONEY,
	Uplift MONEY,
	Total MONEY
	)
 
CREATE UNIQUE CLUSTERED INDEX ix_LGV1
	ON #t_LGV1
		(
		AccountNumber,
		DetailPrimary
		)
 
CREATE TABLE #t_LGV2
	(
	AccountNumber VARCHAR(10),
	OrderNumber VARCHAR(12),
	Entered DATETIME,
	Authorised DATETIME,
	Fax DATETIME,
	FaxCode VARCHAR(20),
	DetailPrimary INTEGER,
	PriceCode VARCHAR(20),
	Vehicle VARCHAR(20),
	WheelPosition VARCHAR(20),
	VehicleAccount VARCHAR(10),
	VehicleStatus VARCHAR(1),
	Quantity FLOAT,
	Nett MONEY,
	Uplift MONEY,
	Total MONEY
	)
 
CREATE UNIQUE CLUSTERED INDEX ix_LGV2
	ON #t_LGV2
		(
		AccountNumber,
		DetailPrimary
		)
 
CREATE Table #t_MatchingIDs
	(
	AccountNumber VARCHAR(10),
	OrderNumber VARCHAR(12),
	DetailPrimary INTEGER
	)
 
CREATE UNIQUE CLUSTERED INDEX ix_MatchingIDs
	ON #t_MatchingIDs
		(
		AccountNumber,
		DetailPrimary
		)
 
-- Populate the temporary table with the list of accounts for this contract and default all other columns.
INSERT INTO #t_Accounts
	(
	AccountNumber
	)
	SELECT DISTINCT
		CH_Account
	FROM
		CST_CostHeader
	WHERE
		CH_Sort = @s_Contract
		AND
		ISNULL(CH_Account, '') <> ''
 
-- Populate the temporary quick accounts table with just the list of accounts for this contract.
INSERT INTO #t_QuickAccounts
	(
	AccountNumber
	)
	SELECT
		#t_Accounts.AccountNumber
	FROM
		#t_Accounts
 
-- Get order details for SOPINV
INSERT INTO #t_SOPINV
	(
	AccountNumber,
	OrderNumber,
	Entered,
	Authorised,
	Fax,
	FaxCode,
	DetailPrimary,
	PriceCode,
	Vehicle,
	WheelPosition,
	VehicleAccount,
	VehicleStatus,
	Quantity,
	Nett,
	Uplift,
	Total
	)
	SELECT DISTINCT
		CST_CostHeader.CH_Account,
		POP_Header.POH_Order_Numbr,
		POP_Header.POH_Init_Datetime,
		POP_Header.POH_Date_Auth,
		POP_Header.POH_Fax_Datetime,
		POP_Header.POH_User3,
		POP_Detail.POD_Primary,
		POP_Detail.POD_Price_Code,
		POP_Detail.POD_CostHeader,
		POP_Detail.POD_CostCentre,
		CST_CostHeader.CH_Account,
		CST_CostHeader.CH_Status,
		POP_DETAIL.POD_QtyOrd,
		POP_Detail.POD_Nett,
		POP_Detail.POD_Uplift,
		POP_Detail.POD_Nett + POP_Detail.POD_Uplift
	FROM
		CONTRACTS.dbo.POP_Header
		INNER JOIN
		CONTRACTS.dbo.POP_Detail
			ON
				POP_Header.POH_Contract = @s_Contract
				AND
				POP_Header.POH_Status = 0
				AND
				POP_Header.POH_Date_Auth IS NOT NULL
				AND
				ISNULL(POP_Header.POH_Cust_Year, 0) = @i_Year
				AND
				(
					@i_Period = 0
					OR
					ISNULL(POP_Header.POH_Cust_Period, 0) = @i_Period
				)
				AND
				(
					@i_Week = 0
					OR
					ISNULL(POP_Header.POH_Cust_Week, 0) = @i_Week
				)
				AND
				POP_Header.POH_Order_Numbr = POP_Detail.POD_Order_No
		INNER JOIN
		CONTRACTS.dbo.CST_CostHeader
			ON
				POP_Detail.POD_CostHeader = CST_CostHeader.CH_Code
 
-- Get order details for Suspended Vehicles
INSERT INTO #t_SuspendedVehicles
	(
	AccountNumber,
	OrderNumber,
	Entered,
	Authorised,
	Fax,
	FaxCode,
	DetailPrimary,
	PriceCode,
	Vehicle,
	WheelPosition,
	VehicleAccount,
	VehicleStatus,
	Quantity,
	Nett,
	Uplift,
	Total
	)
	SELECT DISTINCT
		CST_CostHeader.CH_Account,
		POP_Header.POH_Order_Numbr,
		POP_Header.POH_Init_Datetime,
		POP_Header.POH_Date_Auth,
		POP_Header.POH_Fax_Datetime,
		POP_Header.POH_User3,
		POP_Detail.POD_Primary,
		POP_Detail.POD_Price_Code,
		POP_Detail.POD_CostHeader,
		POP_Detail.POD_CostCentre,
		CST_CostHeader.CH_Account,
		CST_CostHeader.CH_Status,
		POP_DETAIL.POD_QtyOrd,
		POP_Detail.POD_Nett,
		POP_Detail.POD_Uplift,
		POP_Detail.POD_Nett + POP_Detail.POD_Uplift
	FROM
		CONTRACTS.dbo.POP_Header
		INNER JOIN
		CONTRACTS.dbo.POP_Detail
			ON
				POP_Header.POH_Contract = @s_Contract
				AND
				POP_Header.POH_Status = 0
				AND
				POP_Header.POH_Date_Auth IS NOT NULL
				AND
				ISNULL(POP_Header.POH_Cust_Year, 0) = @i_Year
				AND
				(
					@i_Period = 0
					OR
					ISNULL(POP_Header.POH_Cust_Period, 0) = @i_Period
				)
				AND
				(
					@i_Week = 0
					OR
					ISNULL(POP_Header.POH_Cust_Week, 0) = @i_Week
				)
				AND
				POP_Header.POH_Order_Numbr = POP_Detail.POD_Order_No
		INNER JOIN
		CONTRACTS.dbo.CST_CostHeader
			ON
				POP_Detail.POD_CostHeader = CST_CostHeader.CH_Code
				AND
				CST_CostHeader.CH_Status NOT IN ('A', 'R')
 
-- Get order details for Multiple Vehicles Records
INSERT INTO #t_MultipleVehicles
	(
	AccountNumber,
	OrderNumber,
	Entered,
	Authorised,
	Fax,
	FaxCode,
	DetailPrimary,
	PriceCode,
	Vehicle,
	WheelPosition,
	VehicleAccount,
	VehicleStatus,
	Quantity,
	Nett,
	Uplift,
	Total,
	VehicleRecordCount
	)
	SELECT DISTINCT
		CST_CostHeader.CH_Account,
		POP_Header.POH_Order_Numbr,
		POP_Header.POH_Init_Datetime,
		POP_Header.POH_Date_Auth,
		POP_Header.POH_Fax_Datetime,
		POP_Header.POH_User3,
		POP_Detail.POD_Primary,
		POP_Detail.POD_Price_Code,
		POP_Detail.POD_CostHeader,
		POP_Detail.POD_CostCentre,
		CST_CostHeader.CH_Account,
		CST_CostHeader.CH_Status,
		POP_DETAIL.POD_QtyOrd,
		POP_Detail.POD_Nett,
		POP_Detail.POD_Uplift,
		POP_Detail.POD_Nett + POP_Detail.POD_Uplift,
		COUNT(*) AS VehicleRecordCount
	FROM
		CONTRACTS.dbo.POP_Header
		INNER JOIN
		CONTRACTS.dbo.POP_Detail
			ON
				POP_Header.POH_Contract = @s_Contract
				AND
				POP_Header.POH_Status = 0
				AND
				POP_Header.POH_Date_Auth IS NOT NULL
				AND
				ISNULL(POP_Header.POH_Cust_Year, 0) = @i_Year
				AND
				(
					@i_Period = 0
					OR
					ISNULL(POP_Header.POH_Cust_Period, 0) = @i_Period
				)
				AND
				(
					@i_Week = 0
					OR
					ISNULL(POP_Header.POH_Cust_Week, 0) = @i_Week
				)
				AND
				POP_Header.POH_Order_Numbr = POP_Detail.POD_Order_No
		INNER JOIN
		CONTRACTS.dbo.CST_CostHeader
			ON
				POP_Detail.POD_CostHeader = CST_CostHeader.CH_Code
		LEFT OUTER JOIN
		CONTRACTS.dbo.BANV_Vehicle
			ON
				POP_Detail.POD_Primary = BANV_Vehicle.VEH_PO_Detail_Num
	GROUP BY
		CST_CostHeader.CH_Account,
		POP_Header.POH_Order_Numbr,
		POP_Header.POH_Init_Datetime,
		POP_Header.POH_Date_Auth,
		POP_Header.POH_Fax_Datetime,
		POP_Header.POH_User3,
		POP_Detail.POD_Primary,
		POP_Detail.POD_Price_Code,
		POP_Detail.POD_CostHeader,
		POP_Detail.POD_CostCentre,
		CST_CostHeader.CH_Account,
		CST_CostHeader.CH_Status,
		POP_DETAIL.POD_QtyOrd,
		POP_Detail.POD_Nett,
		POP_Detail.POD_Uplift,
		POP_Detail.POD_Nett + POP_Detail.POD_Uplift
	HAVING
		COUNT(*) > 1
 
-- Get order details for LGV1
INSERT INTO #t_LGV1
	(
	AccountNumber,
	OrderNumber,
	Entered,
	Authorised,
	Fax,
	FaxCode,
	DetailPrimary,
	PriceCode,
	Vehicle,
	WheelPosition,
	VehicleAccount,
	VehicleStatus,
	Quantity,
	Nett,
	Uplift,
	Total
	)
	SELECT DISTINCT
		#t_Accounts.AccountNumber,
		POP_Header.POH_Order_Numbr,
		POP_Header.POH_Init_Datetime,
		POP_Header.POH_Date_Auth,
		POP_Header.POH_Fax_Datetime,
		POP_Header.POH_User3,
		POP_Detail.POD_Primary,
		POP_Detail.POD_Price_Code,
		POP_Detail.POD_CostHeader,
		POP_Detail.POD_CostCentre,
		CST_CostHeader.CH_Account,
		CST_CostHeader.CH_Status,
		POP_DETAIL.POD_QtyOrd,
		POP_Detail.POD_Nett,
		POP_Detail.POD_Uplift,
		POP_Detail.POD_Nett + POP_Detail.POD_Uplift
	FROM
		CONTRACTS.dbo.POP_Header
		INNER JOIN
		CONTRACTS.dbo.POP_Detail
			ON
				POP_Header.POH_Contract = @s_Contract
				AND
				POP_Header.POH_Priority <> 2
				AND
				ISNULL(POP_Header.POH_Cust_Year, 0) = @i_Year
				AND
				(
					@i_Period = 0
					OR
					ISNULL(POP_Header.POH_Cust_Period, 0) = @i_Period
				)
				AND
				(
					@i_Week = 0
					OR
					ISNULL(POP_Header.POH_Cust_Week, 0) = @i_Week
				)
				AND
				POP_Header.POH_Order_Numbr = POP_Detail.POD_Order_No
				AND
				POP_Detail.POD_QtyOrd <> 0
				AND
				DATEDIFF(Month, GetDate(), POH_Date_Auth) <= 6
		INNER JOIN
		CONTRACTS.dbo.CST_CostHeader
			ON
				POP_Detail.POD_CostHeader = CST_CostHeader.CH_Code
		INNER JOIN
		#t_Accounts
			ON
				CST_CostHeader.CH_Account = #t_Accounts.AccountNumber
 
-- Get order details for LGV2
INSERT INTO #t_LGV2
	(
	AccountNumber,
	OrderNumber,
	Entered,
	Authorised,
	Fax,
	FaxCode,
	DetailPrimary,
	PriceCode,
	Vehicle,
	WheelPosition,
	VehicleAccount,
	VehicleStatus,
	Quantity,
	Nett,
	Uplift,
	Total
	)
	SELECT DISTINCT
		#t_Accounts.AccountNumber,
		POP_Header.POH_Order_Numbr,
		POP_Header.POH_Init_Datetime,
		POP_Header.POH_Date_Auth,
		POP_Header.POH_Fax_Datetime,
		POP_Header.POH_User3,
		POP_Detail.POD_Primary,
		POP_Detail.POD_Price_Code,
		POP_Detail.POD_CostHeader,
		POP_Detail.POD_CostCentre,
		CST_CostHeader.CH_Account,
		CST_CostHeader.CH_Status,
		POP_DETAIL.POD_QtyOrd,
		POP_Detail.POD_Nett,
		POP_Detail.POD_Uplift,
		POP_Detail.POD_Nett + POP_Detail.POD_Uplift
	FROM
		CONTRACTS.dbo.POP_Header
		INNER JOIN
		CONTRACTS.dbo.POP_Detail
			ON
				POP_Header.POH_Contract = @s_Contract
				AND
				POP_Header.POH_Priority <> 2
				AND
				ISNULL(POP_Header.POH_Cust_Year, 0) = @i_Year
				AND
				(
					@i_Period = 0
					OR
					ISNULL(POP_Header.POH_Cust_Period, 0) = @i_Period
				)
				AND
				(
					@i_Week = 0
					OR
					ISNULL(POP_Header.POH_Cust_Week, 0) = @i_Week
				)
				AND
				POP_Header.POH_Order_Numbr = POP_Detail.POD_Order_No
				AND
				POP_Detail.POD_QtyOrd <> 0
				AND
				DATEDIFF(Month, GetDate(), POH_Date_Auth) <= 6
		INNER JOIN
		CONTRACTS.dbo.CST_CostHeader
			ON
				POP_Detail.POD_CostHeader = CST_CostHeader.CH_Code
		INNER JOIN
		CONTRACTS.dbo.PRC_Price_Recs
			ON
				POP_Detail.POD_Price_Code = PRC_Price_Recs.PRCode
				AND
				PRC_Price_Recs.PRName NOT LIKE 'Trailer Insp%'
				AND
				PRC_Price_Recs.PRName NOT LIKE 'Truck Insp%'
		INNER JOIN
		#t_Accounts
			ON
				CST_CostHeader.CH_Account = #t_Accounts.AccountNumber
 
-- Aggregare results for each account.
UPDATE
	#t_Accounts
	SET
		SOPINV_Orders  = SOPINVAgg.[Orders],
		SOPINV_DetailS = SOPINVAgg.[Details],
		SOPINV_Nett    = SOPINVAgg.[Nett],
		SOPINV_Uplift  = SOPINVAgg.[Uplift],
		SOPINV_Total   = SOPINVAgg.[Total],
 
		LGV1_Orders    = LGV1Agg.[Orders],
		LGV1_DetailS   = LGV1Agg.[Details],
		LGV1_Nett      = LGV1Agg.[Nett],
		LGV1_Uplift    = LGV1Agg.[Uplift],
		LGV1_Total     = LGV1Agg.[Total],
 
		LGV2_Orders    = LGV2Agg.[Orders],
		LGV2_DetailS   = LGV2Agg.[Details],
		LGV2_Nett      = LGV2Agg.[Nett],
		LGV2_Uplift    = LGV2Agg.[Uplift],
		LGV2_Total     = LGV2Agg.[Total]
 
	FROM
		#t_Accounts
 
		LEFT OUTER JOIN
		(
		SELECT TOP 100 PERCENT
			#t_Accounts.AccountNumber,
			COUNT(DISTINCT #t_SOPINV.OrderNumber) AS [Orders],
			COUNT(#t_SOPINV.DetailPrimary) AS [Details],
			SUM(ISNULL(#t_SOPINV.Nett, 0)) AS [Nett],
			SUM(ISNULL(#t_SOPINV.Uplift, 0)) AS [Uplift],
			SUM(ISNULL(#t_SOPINV.Nett, 0)) + SUM(ISNULL(#t_SOPINV.Uplift, 0)) AS [Total]
		FROM
			#t_Accounts
			INNER JOIN
			#t_SOPINV
				ON
					#t_Accounts.AccountNumber = #t_SOPINV.AccountNumber
		GROUP BY
			#t_Accounts.AccountNumber
		ORDER BY
			#t_Accounts.AccountNumber
		) SOPINVAgg
		ON
			SOPINVAgg.AccountNumber = #t_Accounts.AccountNumber
 
		LEFT OUTER JOIN
		(
		SELECT TOP 100 PERCENT
			#t_Accounts.AccountNumber,
			COUNT(DISTINCT #t_LGV1.OrderNumber) AS [Orders],
			COUNT(#t_LGV1.DetailPrimary) AS [Details],
			SUM(ISNULL(#t_LGV1.Nett, 0)) AS [Nett],
			SUM(ISNULL(#t_LGV1.Uplift, 0)) AS [Uplift],
			SUM(ISNULL(#t_LGV1.Nett, 0)) + SUM(ISNULL(#t_LGV1.Uplift, 0)) AS [Total]
		FROM
			#t_Accounts
			INNER JOIN
			#t_LGV1
				ON
					#t_Accounts.AccountNumber = #t_LGV1.AccountNumber
		GROUP BY
			#t_Accounts.AccountNumber
		ORDER BY
			#t_Accounts.AccountNumber
		) LGV1Agg
		ON
			LGV1Agg.AccountNumber = #t_Accounts.AccountNumber
 
		LEFT OUTER JOIN
		(
		SELECT TOP 100 PERCENT
			#t_Accounts.AccountNumber,
			COUNT(DISTINCT #t_LGV2.OrderNumber) AS [Orders],
			COUNT(#t_LGV2.DetailPrimary) AS [Details],
			SUM(ISNULL(#t_LGV2.Nett, 0)) AS [Nett],
			SUM(ISNULL(#t_LGV2.Uplift, 0)) AS [Uplift],
			SUM(ISNULL(#t_LGV2.Nett, 0)) + SUM(ISNULL(#t_LGV2.Uplift, 0)) AS [Total]
		FROM
			#t_Accounts
			INNER JOIN
			#t_LGV2
				ON
					#t_Accounts.AccountNumber = #t_LGV2.AccountNumber
		GROUP BY
			#t_Accounts.AccountNumber
		ORDER BY
			#t_Accounts.AccountNumber
		) LGV2Agg
		ON
			LGV2Agg.AccountNumber = #t_Accounts.AccountNumber
 
-- Build table of matching Detail Primaries.
INSERT INTO #t_MatchingIDs
	(
	AccountNumber,
	OrderNumber,
	DetailPrimary
	)
	SELECT
		#t_SOPINV.AccountNumber,
		#t_SOPINV.OrderNumber,
		#t_SOPINV.DetailPrimary
	FROM
		#t_SOPINV
		INNER JOIN
		#t_LGV1
			ON
				#t_SOPINV.DetailPrimary = #t_LGV1.DetailPrimary
		INNER JOIN
		#t_LGV2
			ON
				#t_SOPINV.DetailPrimary = #t_LGV2.DetailPrimary
				AND
				#t_LGV1.DetailPrimary = #t_LGV2.DetailPrimary
 
-- Remove matching rows
DELETE
	#t_SOPINV
FROM
	#t_SOPINV
	INNER JOIN
	#t_MatchingIDs
		ON
			#t_SOPINV.AccountNumber = #t_MatchingIDs.AccountNumber
			AND
			#t_SOPINV.DetailPrimary = #t_MatchingIDs.DetailPrimary
 
DELETE
	#t_LGV1
FROM
	#t_LGV1
	INNER JOIN
	#t_MatchingIDs
		ON
			#t_LGV1.AccountNumber = #t_MatchingIDs.AccountNumber
			AND
			#t_LGV1.DetailPrimary = #t_MatchingIDs.DetailPrimary
 
DELETE
	#t_LGV2
FROM
	#t_LGV2
	INNER JOIN
	#t_MatchingIDs
		ON
			#t_LGV2.AccountNumber = #t_MatchingIDs.AccountNumber
			AND
			#t_LGV2.DetailPrimary = #t_MatchingIDs.DetailPrimary
 
-- Debug
SELECT * FROM #t_QuickAccounts ORDER BY #t_QuickAccounts.AccountNumber
SELECT * FROM #t_Accounts ORDER BY #t_Accounts.AccountNumber
SELECT * FROM #t_SOPINV ORDER BY #t_SOPINV.PriceCode, #t_SOPINV.AccountNumber, #t_SOPINV.OrderNumber, #t_SOPINV.DetailPrimary
SELECT * FROM #t_LGV1 ORDER BY #t_LGV1.PriceCode, #t_LGV1.AccountNumber, #t_LGV1.OrderNumber, #t_LGV1.DetailPrimary
SELECT * FROM #t_LGV2 ORDER BY #t_LGV2.PriceCode, #t_LGV2.AccountNumber, #t_LGV2.OrderNumber, #t_LGV2.DetailPrimary
SELECT * FROM #t_SuspendedVehicles ORDER BY #t_SuspendedVehicles.PriceCode, #t_SuspendedVehicles.AccountNumber, #t_SuspendedVehicles.OrderNumber, #t_SuspendedVehicles.DetailPrimary
SELECT * FROM #t_MultipleVehicles ORDER BY #t_MultipleVehicles.PriceCode, #t_MultipleVehicles.AccountNumber, #t_MultipleVehicles.OrderNumber, #t_MultipleVehicles.DetailPrimary
--SELECT * FROM #t_MatchingIDs ORDER BY #t_MatchingIDs.AccountNumber, #t_MatchingIDs.OrderNumber, #t_MatchingIDs.DetailPrimary
 
-- Cleanup
DROP TABLE #t_Accounts
DROP TABLE #t_LGV1
DROP TABLE #t_LGV2
DROP TABLE #t_MatchingIDs
DROP TABLE #t_MultipleVehicles
DROP TABLE #t_QuickAccounts
DROP TABLE #t_SOPINV
DROP TABLE #t_SuspendedVehicles
GO

Open in new window

0
 
LVL 100

Accepted Solution

by:
mlmcc earned 250 total points
ID: 22596644
>>I think that CR does _NOT_ support multiple result sets.
You are absolutely correct.  Crystal will only accept a single resultset from the stored procedure.

One way would be to store as your mentioned then use a subreport for each of the result sets.

mlmcc
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 34

Assisted Solution

by:James0628
James0628 earned 250 total points
ID: 22620107
If you're going to combine them into one result set, you can just add a literal column (eg. result_set) that is set to a different value in each set (eg. 'set1', 'set2', etc.).  You can use more descriptive names, but the report will be grouping, or at least sorting, the data by that field, so if you want the result sets in a specific order, the easiest thing is to use values that get the result sets in the order you want.

 In the report, you would group on the result_set column and create different group header and footer sections and detail sections for each result set.  Each section would have the appropriate headings, fields, totals, etc. for that result set.  You could also have different page header or footer sections for each result set.  Then you'd use conditional suppression on the sections so that each one only shows on the report for the appropriate result set.  For example, if you had 3 result sets, the report structure might be something like:

 Group Header 1a - Suppressed if result_set <> 'set1'
 Group Header 1b - Suppressed if result_set <> 'set2'
 Group Header 1c - Suppressed if result_set <> 'set3'

 Detail 1a - Suppressed if result_set <> 'set1'
 Detail 1b - Suppressed if result_set <> 'set2'
 Detail 1c - Suppressed if result_set <> 'set3'

 Group Footer 1a - Suppressed if result_set <> 'set1'
 Group Footer 1b - Suppressed if result_set <> 'set2'
 Group Footer 1c - Suppressed if result_set <> 'set3'


 It could get more complicated, for example if you also wanted sub-groups within a result set, but that's the basic idea.

 James
0
 
LVL 40

Author Comment

by:Richard Quadling
ID: 22622456
That's exactly what I've done.

In working with this, I found that I could re-use 2 of the sections.

And having done the work, they say "Ah - too much information!" so a rewrite already.

Users are NEVER happy.

Thanks for your comments.
0
 
LVL 34

Expert Comment

by:James0628
ID: 22623093
Heh heh.  Yeah, it's always fun when you create something and then they say "no, that's not what we want after all".  :-)  Good luck with it.

 James
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 22624427
I agrre.  Users think they need all this information in one report then when they get it they find it  is as you said TMI.  It is too difficult to pull the information they need.

If this is resolved, please close the question as appropriate.

mlmcc
0
 
LVL 40

Author Closing Comment

by:Richard Quadling
ID: 31501117
2 solutions
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

828 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