Solved

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

Posted on 2008-09-29
8
1,669 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:RQuadling
  • 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:RQuadling
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
 
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 40

Author Comment

by:RQuadling
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:RQuadling
ID: 31501117
2 solutions
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how the fundamental information of how to create a table.

747 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now