Solved

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

Posted on 2013-10-31
8
258 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 100

Assisted Solution

by:mlmcc
mlmcc earned 250 total points
Comment Utility
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
Comment Utility
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 100

Expert Comment

by:mlmcc
Comment Utility
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
 
LVL 11

Assisted Solution

by:Louis01
Louis01 earned 250 total points
Comment Utility
@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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 100

Accepted Solution

by:
mlmcc earned 250 total points
Comment Utility
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 250 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
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…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

743 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

14 Experts available now in Live!

Get 1:1 Help Now