Exporting from a SP to create a pick list for users.

This SP works....thanks to many on this forum.

What I need to do is create a pick list to import into Crystal Reports so I can allow users a simple and full list of values to pick from. If the Query Runs How do I get the unique values listed for export in SSMS for the ChainName listed in the TEMPTABLE below.
Do I create a new Query or do I use some form of Distinct Count....?

USE [WebSnakDS_SNYB_Test]
GO
/****** Object:  StoredProcedure [dbo].[Rpt_SnyderAccountP&LSummary_RB]    Script Date: 10/22/2013 11:47:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--Declare 
	ALTER PROCEDURE [dbo].[Rpt_SnyderAccount_PL_Summary_RB] 
      (
      @DB_Include int  -- 0 = WebSNAK Only, 1 = RAMS Only, 2 = WebSNAK and RAMS 
       )AS

-----------------------------------------------------------------------------------
-- Creating TEMP Table Bringing Macola RAMS and WebSNAK together
-----------------------------------------------------------------------------------

CREATE TABLE #TEMPTABLE
		
(
	[Data_Source] VARCHAR(12),--  what database is the data coming from  macola or rams?
	[InvoiceDate] DATETIME,
	[SNAK_SaleTypesID] CHAR (10),
	[CustPromoAmt] MONEY,
	[MFGCostAmt] MONEY, 
	[GrossAmt] MONEY,
	[CustName] VARCHAR(60),
	[SNAK_CustomerID] VARCHAR(12),
	[ChainName] VARCHAR(42),
	[SNAK_ChainID] VARCHAR(100)
	
	
)

 
-----------------------------------------------------------------------------------
-- WEBSNAK DATA
-----------------------------------------------------------------------------------

if (@DB_Include = 0 or @DB_Include = 2) -- 0 = WebSNAK Data only; 2 = Both WebSNAK and RAMS data
	BEGIN
	
INSERT  INTO #TEMPTABLE

SELECT
	'WEBSNAK',
	Invoices.InvoiceDate AS InvoiceDate,
	InvoiceDetails.SNAK_SaleTypesID AS SNAK_SaleTypesID, 
	InvoiceDetails.CustPromoAmt AS CustPromoAmt,
	InvoiceDetails.MFGCostAmt AS MFGCostAmt,
	InvoiceDetails.GrossAmt AS GrossAmt,
	Customers.CustName AS CustName, 
	Customers.SNAK_CustomerID AS SNAK_CustomerID,
	Chains.ChainName AS ChainName, 
	Chains.SNAK_ChainID AS SNAK_ChainID
FROM
    { oj ((dbo.Invoices Invoices 
     INNER JOIN dbo.Customers Customers 
			ON	Invoices.Customers_Syskey = Customers.Customers_Syskey)
     INNER JOIN dbo.InvoiceDetails InvoiceDetails 
			ON	Invoices.Invoices_Syskey = InvoiceDetails.Invoices_Syskey)
     INNER JOIN dbo.Chains Chains 
			ON	Customers.Chains_Syskey = Chains.Chains_Syskey}
WHERE
	--((Invoices.InvoiceDate >= {ts '2012-01-10 00:00:00.00'} AND
    --Invoices.InvoiceDate < {ts '2012-09-11 00:00:00.00'}) OR
    (Invoices.InvoiceDate >= {ts '2013-01-01 00:00:00.00'} AND
    Invoices.InvoiceDate < {ts '2013-01-31 00:00:00.00'})
    --SNAK_SaleTypesID IN ('S','R','D','F')
GROUP By 
	InvoiceDate,
	Chains.SNAK_ChainID,
	InvoiceDetails.SNAK_SaleTypesID,
	InvoiceDetails.CustPromoAmt,
	InvoiceDetails.MfgCostAmt,
	InvoiceDetails.GrossAmt,
	Customers.CustName,
	Customers.SNAK_CustomerID,
	Chains.ChainName

END
-----------------------------------------------------------------------------------
--RAMS DATA--
-----------------------------------------------------------------------------------

if (@DB_Include = 1 or @DB_Include = 2) -- 1 = RAMS Data only; 2 = Both WebSNAK and RAMS data
	BEGIN
	
INSERT  INTO #TEMPTABLE

SELECT
    	'RAMS',
    	ROSS_SALESHDR.OH_DELIVERY_DATE AS InvoiceDate,
    	ROSS_SALESDTL.OD_RECORD_TYPE AS SNAK_SalesTypesID,
 		CustPromoAmt = SUM(ROSS_SALESDTL.OD_TOTAL_DELIV_QUANTITY * ROSS_SALESDTL.OD_NET_PROMO_PRICE),
 		MFGCostAmt = SUM(ROSS_SALESDTL.OD_TOTAL_DELIV_QUANTITY * ROSS_SALESDTL.OD_PRODUCT_COST_PRICE),
 		GrossAmt = SUM(ROSS_SALESDTL.OD_TOTAL_DELIV_QUANTITY * OD_BASE_PRICE),
 		ROSS_CUSTOMER.CM_FULLNAME AS CustName,
    	RAMS_CrossRef.RAMS_CHAIN_NO AS SNAK_CustomerID,
    	RAMS_CrossRef.SNAK_CHAIN_NO AS ChainName,
    	RAMS_CrossRef.CHAIN_IDENT AS SNAK_ChainID
    	
    	
FROM
     CHLROS05.RAMSDB.dbo.ROSS_SALESHDR ROSS_SALESHDR 
	INNER JOIN CHLROS05.RAMSDB.dbo.ROSS_CUSTOMER ROSS_CUSTOMER 
		ON ROSS_SALESHDR.OH_CUSTOMER_NUMBER = ROSS_CUSTOMER.CM_CUSTKEY
    INNER JOIN CHLROS05.RAMSDB.dbo.ROSS_SALESDTL ROSS_SALESDTL 
		ON ROSS_SALESHDR.OH_TICKET_NUMBER = ROSS_SALESDTL.OD_TICKET_NUMBER
		--AND ROSS_SALESDTL.OD_RECORD_TYPE IN ('S','R','M')
    INNER JOIN CHLROS05.RAMSDB.dbo.ROSS_PRODUCTS ROSS_PRODUCTS 
		ON ROSS_SALESDTL.OD_PRODUCT = ROSS_PRODUCTS.PRD_PRODUCT
	LEFT OUTER JOIN CHLROS05.RAMSDB.dbo.ROSS_IDCODE ROSS_IDCODE1
        ON ROSS_CUSTOMER.CM_SELLZONE = ROSS_IDCODE1.ID_FIELD
    LEFT OUTER JOIN CHLROS05.RAMSDB.dbo.ROSS_IDCODE ROSS_IDCODE2
        ON ROSS_PRODUCTS.PRD_A_GLTYPE = ROSS_IDCODE2.ID_FIELD 
	INNER JOIN WebSnakDS_SNYB_test.dbo.RAMS_CrossRef RAMS_CrossRef
		ON ROSS_CUSTOMER.CM_CHAINID = RAMS_CrossRef.RAMS_CHAIN_NO
		
WHERE
    ROSS_SALESHDR.OH_DELIVERY_DATE >= {ts '2013-01-01 00:00:00.00'} AND
    ROSS_SALESHDR.OH_DELIVERY_DATE < {ts '2013-01-31 00:00:00.00'} --AND
    --ROSS_SALESDTL.OD_PRODUCT >= '0*' AND
    --ROSS_SALESDTL.OD_PRODUCT <= '9999*' AND
    --ROSS_SALESDTL.OD_RECORD_TYPE = 'O' --AND
    --(ROSS_CUSTOMER.CM_DELIVZONE = '400002' OR
    --ROSS_CUSTOMER.CM_DELIVZONE = '400001')
    
GROUP BY
        ROSS_SALESHDR.OH_DELIVERY_DATE,
        ROSS_SALESDTL.OD_RECORD_TYPE,
        ROSS_CUSTOMER.CM_FULLNAME,
        RAMS_CrossRef.RAMS_CHAIN_NO,
        RAMS_CrossRef.SNAK_CHAIN_NO,
        RAMS_CrossRef.CHAIN_IDENT
/*
SELECT
	'RAMS'
	ROSS_CUSTOMER.CM_CHAINID,
	ROSS_IDCODE.ID_DESC
FROM
  { oj RAMSDB.dbo.ROSS_CUSTOMER ROSS_CUSTOMER INNER JOIN RAMSDB.dbo.ROSS_IDCODE ROSS_IDCODE ON
     ROSS_CUSTOMER.CM_SELLZONE = ROSS_IDCODE.ID_FIELD}
ORDER BY
    ROSS_CUSTOMER.CM_CHAINID ASC
    */
    END
    
    SELECT * FROM #TEMPTABLE 

Open in new window

RUA Volunteer2?Tableau Trainer & Consultant Sales Exec.Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mlmccCommented:
WHat value are you trying to use?
Is it for the SP parameter?

Do you have another parameter that is created in the report?

YOur version of Crystal doesn't support dynamic parameters so there is no easy way to get the list updated.

mlmcc
0
Louis01Commented:
declare @Rpt_SnyderAccount_PL_Summary_RB table ([Data_Source] VARCHAR(12),
                   [InvoiceDate] DATETIME,
                   [SNAK_SaleTypesID] CHAR (10),
                   [CustPromoAmt] MONEY,
                   [MFGCostAmt] MONEY, 
                   [GrossAmt] MONEY,
                   [CustName] VARCHAR(60),
                   [SNAK_CustomerID] VARCHAR(12),
                   [ChainName] VARCHAR(42),
                   [SNAK_ChainID] VARCHAR(100))

insert into @Rpt_SnyderAccount_PL_Summary_RB
exec Rpt_SnyderAccount_PL_Summary_RB @DB_Include = 2

select distinct ChainName 
  from @Rpt_SnyderAccount_PL_Summary_RB
 order by ChainName 

Open in new window

0
mlmccCommented:
Louis01 -
SInce the intent is to use the list of values in Crystal, how does your comment help?  CR 8.5 doesn't have a way to use that as a source for the list of names.

mlmcc
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Louis01Commented:
@mlmcc
It's a simple uncomplicated solution to the problem.

1. Get results in a dataset with rows.
2. Connect dataset (or resultset or whatever you want to call a list of rows in a structure) to the parameter control.
3. Can't think of anything more to do.. oh... test

;)

{p.s. "CR 8.5 doesn't have a way to use that as a source for the list of names"
I admit I have not used CR8.5, but are you telling me you cannot connect a column in the rows from output of a SQL statement to a control in CR 8.5?}
0
mlmccCommented:
Agree, it is a solution to producing the list but CR8.5 doesn't have dynamic parameters so you can't connect the query to the parameter for the list.

The report already has a complex SP associated with it.  It might be possible to add the SP as a secondary source but when there are changes to the data the report would have to opened and the list updated manually.

Crystal would create a cross join between the 2 sources but that might be ok so long as your SP isn't used in the report.

mlmcc
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Louis01Commented:
ah, mlmcc, I understand. Apologies.... I  I'm afraid if parameter values cannot be updated dynamically in a CR report, there is no solution to the problem using CR. I am sure though the same approach could be used for a front end form to capture the parameter(s).

@Author: Perhaps you could tell us more of the architecture of the system, allowing us to propose a viable solution? How do your users access the report?

Example (VB6 - since that was the last time I can recall using CR - and trolling a little bit):
With CRReport
        .DiscardSavedData
        .Database.Tables.Item(1).SetLogOnInfo ..........
        Set CrtParameters = CRReport.ParameterFields
        .ParameterFields.GetItemByName("@emailaddresses_CSV_or_whatever_the_parameter_name_is").AddDefaultValue a_variable_containing_the_output_from_louis01s_awesomely_simple_sql
    End With

Open in new window

Note: VB6 rusty - hope my declarations are correct.
0
RUA Volunteer2?Tableau Trainer & Consultant Sales Exec.Author Commented:
I wonder if I should accept this solution and post another on the SQL Server guys section and see about creating a Stored Procedure that can mimic the Dynamic or Rather Cascading Parameter feature in newer versions of Crystal Reports. Just a thought.
0
RUA Volunteer2?Tableau Trainer & Consultant Sales Exec.Author Commented:
The solution is it would seem to use a more modern version of Crystal Reports....believe me I would like to and the client has it. Just not allowed to use it. So I stuck with the 8.5. So alternatively It sounds like I might be able to use something in the SP maybe a Sub Query to pull those records for the pick list.....? I will post this question on the SQL Server guys and see what they say. Thank you as always.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.