?
Solved

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

Posted on 2013-10-31
8
Medium Priority
?
282 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
[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
  • 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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
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 how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

762 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