Solved

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

Posted on 2008-09-29
8
1,686 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

 
LVL 35

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 35

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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

734 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