?
Solved

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

Posted on 2013-10-31
8
Medium Priority
?
291 Views
Last Modified: 2013-11-15
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

0
Comment
Question by:ruavol2
  • 3
  • 3
  • 2
8 Comments
 
LVL 101

Assisted Solution

by:mlmcc
mlmcc earned 1000 total points
ID: 39615380
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
 
LVL 11

Expert Comment

by:Louis01
ID: 39616111
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
 
LVL 101

Expert Comment

by:mlmcc
ID: 39616502
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 11

Assisted Solution

by:Louis01
Louis01 earned 1000 total points
ID: 39617238
@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
 
LVL 101

Accepted Solution

by:
mlmcc earned 1000 total points
ID: 39617415
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
 
LVL 11

Assisted Solution

by:Louis01
Louis01 earned 1000 total points
ID: 39617519
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
 

Author Comment

by:ruavol2
ID: 39651273
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
 

Author Closing Comment

by:ruavol2
ID: 39651348
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

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

Question has a verified solution.

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

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
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 shrink a transaction log file down to a reasonable size.
Suggested Courses

850 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