Skip to main content

Notifications

Microsoft Dynamics GP (Archived)

GP SSIS vs. Stored Procedures vs. ?? - Converting Crystal Report to Automated SSRS

Posted on by 220

We have SQL Server 2008 R2 with Visual Studio 2008.  We have SharePoint(2010) and SSRS running in integrated mode; but plan on changing back to native mode to use Business Analyzer and put SharePoint on a MS Cloud Server.  The Dynamics GP version is 2010 soon to be upgraded.  

The old reports admin used a SAP Crystal Report (14) to generate data then cut-paste into a series of Excel 2010 spreadsheets with formulas to produce a certain report for the president/co-owner of the company.  

I have been tasked with making the report automatic with no Excel involved; and to produce the reports as a PDF.

The way we are using the GP database and the results they want cause the query to be a little complex; but mostly repetitive.  Also, there are several manual exceptions to rules, i.e. how a sales id is used to determine if the sale is split with another id or not.

I have crafted a rough-draft query that gets me close to the results; but is causing the "GrossProfit" to be 1.52 times what it should be.

The report is needed every Monday and on the 3rd day of the month for the past month and includes a look back period of three years(including the current YTD).

The user base that needs the report now is about 25; but if successful it will be more and run more often.

Should I use a Stored Proc to generate the data I need? Or a SSIS package?

If it is a SP how should I govern when the data is created? What event(s) should trigger it?

Here is the SQL Code (1st draft):

-- Get Main Data

SELECT SOP30200.CUSTNAME, SOP30200.SLPRSNID, SOP30300.XTNDPRCE, SOP30300.SOPTYPE,

SOP30200.DOCDATE, SOP30300.NONINVEN, SOP30300.SOPNUMBE, SOP30300.ITEMDESC,

SOP30300.EXTDCOST, SOP30200.VOIDSTTS, SOP30200.CUSTNMBR, RM00101.CUSTCLAS,

SOP30300.MRKDNAMT, SOP30300.QUANTITY, SOP30300.QTYFULFI, SOP30300.ITEMNMBR,

RM00301.SLPRSNFN, RM00301.SPRSNSLN, RM00301.INACTIVE

INTO #_Main

FROM   (

(

OMNI.dbo.SOP30300 SOP30300

INNER JOIN OMNI.dbo.SOP30200 SOP30200 ON

(SOP30300.SOPTYPE=SOP30200.SOPTYPE) AND (SOP30300.SOPNUMBE=SOP30200.SOPNUMBE)

)

INNER JOIN OMNI.dbo.RM00101 RM00101 ON

SOP30200.CUSTNMBR=RM00101.CUSTNMBR

)

INNER JOIN OMNI.dbo.RM00301 RM00301 ON

SOP30200.SLPRSNID=RM00301.SLPRSNID

WHERE  

 YEAR(SOP30200.DOCDATE)IN(2011,2012,2013)AND

  SOP30200.VOIDSTTS=0 AND

  NOT (SOP30300.SOPNUMBE='xxxxxx64' OR SOP30300.SOPNUMBE='xxxxxx72' OR SOP30300.SOPNUMBE='xxxxxx98' OR SOP30300.SOPNUMBE='xxxxxx14') AND

  (SOP30300.SOPTYPE=3 OR SOP30300.SOPTYPE=4) AND

  SOP30300.ITEMNMBR NOT  LIKE '011-%'

  --AND SOP30200.SLPRSNID = '7060'--Test ID Filter

--Get Non-Splits

SELECT CUSTNAME,SLPRSNID,XTNDPRCE,SOPTYPE,DOCDATE,NONINVEN,SOPNUMBE,

ITEMDESC,EXTDCOST,VOIDSTTS,CUSTNMBR,CUSTCLAS,MRKDNAMT,QUANTITY,QTYFULFI,

ITEMNMBR,SLPRSNFN,SPRSNSLN,INACTIVE,CAST(0 as DECIMAL(14,6))ExPx,CAST(0 as DECIMAL(14,6))Cost,CAST(0 as DECIMAL(14,6))GrossProfit,

0 as HasSplit

INTO #_NonSplits

FROM #_Main

WHERE

(SLPRSNID NOT LIKE '%-%' OR SLPRSNID = '0710Z')

--Get Splits  

SELECT

CUSTNAME,

SLPRSNID,

CASE WHEN(SLPRSNID LIKE '%-%') THEN SUBSTRING(SLPRSNID,1,4) ELSE SLPRSNID END AS NewID1,

CASE WHEN(SLPRSNID LIKE '%-%') THEN SUBSTRING(SLPRSNID,CHARINDEX('-',SLPRSNID)+1,LEN(SLPRSNID)) ELSE SLPRSNID END AS NewID2,

CASE WHEN(SLPRSNID LIKE '%-%' OR SLPRSNID = '0710Z') THEN 1 ELSE 0 END AS HasSplit,

XTNDPRCE,SOPTYPE,DOCDATE,NONINVEN,SOPNUMBE,ITEMDESC,EXTDCOST,

VOIDSTTS,CUSTNMBR,CUSTCLAS,MRKDNAMT,QUANTITY,

QTYFULFI,ITEMNMBR,SLPRSNFN,SPRSNSLN,INACTIVE,CAST(0 as DECIMAL(14,6)) as ExPx, CAST(0 as DECIMAL(14,6)) as Cost, CAST(0 as DECIMAL(14,6)) as GrossProfit

INTO #_Splits1

FROM #_Main

--Set Splits1  

SELECT CUSTNAME,NewID1 as SLPRSNID,

XTNDPRCE,SOPTYPE,DOCDATE,NONINVEN,SOPNUMBE,ITEMDESC,EXTDCOST,

VOIDSTTS,CUSTNMBR,CUSTCLAS,MRKDNAMT,QUANTITY,

QTYFULFI,ITEMNMBR,SLPRSNFN,SPRSNSLN,INACTIVE,ExPx,Cost,GrossProfit,HasSplit

INTO #_Splits2

FROM #_Splits1

WHERE HasSplit = 1

--Set Splits2

INSERT INTO #_Splits2 (CUSTNAME,SLPRSNID,XTNDPRCE,SOPTYPE,DOCDATE,NONINVEN,SOPNUMBE,ITEMDESC,EXTDCOST,VOIDSTTS,CUSTNMBR,CUSTCLAS,MRKDNAMT,QUANTITY,QTYFULFI,ITEMNMBR,SLPRSNFN,SPRSNSLN,INACTIVE,ExPx,Cost,GrossProfit,HasSplit)

SELECT CUSTNAME,NewID2,

XTNDPRCE,SOPTYPE,DOCDATE,NONINVEN,SOPNUMBE,ITEMDESC,EXTDCOST,

VOIDSTTS,CUSTNMBR,CUSTCLAS,MRKDNAMT,QUANTITY,

QTYFULFI,ITEMNMBR,SLPRSNFN,SPRSNSLN,INACTIVE,

ExPx,Cost,GrossProfit,HasSplit

FROM #_Splits1

WHERE HasSplit = 1

DECLARE @ExPX DECIMAL(14,2)

DECLARE @CUSTNMBR CHAR(15)

/*

Need to get cost, expx, gross profit, etc for Splits Group 1;

*/

/*

Splits Calc's for Group1

*/  

--Get ExPx

SELECT CUSTNAME,SLPRSNID,

XTNDPRCE,SOPTYPE,DOCDATE,NONINVEN,SOPNUMBE,ITEMDESC,EXTDCOST,

VOIDSTTS,CUSTNMBR,CUSTCLAS,MRKDNAMT,QUANTITY,

QTYFULFI,ITEMNMBR,SLPRSNFN,SPRSNSLN,INACTIVE ,

CASE WHEN SOPTYPE = 4 THEN 0-(

CASE WHEN CUSTNMBR IN('ZZZ200','ZZZ210','ZZZ220') THEN XTNDPRCE

WHEN SOPNUMBE IN ('xxxxxx90','xxxxxx01','xxxx43') THEN 0

ELSE (XTNDPRCE + (MRKDNAMT * QUANTITY))

END) ELSE(CASE WHEN CUSTNMBR IN('ZZZ200','ZZZ210','ZZZ220') THEN XTNDPRCE

WHEN SOPNUMBE IN ('xxxxxx90','xxxxxx01','xxxx43') THEN 0

ELSE (XTNDPRCE + (MRKDNAMT * QUANTITY))

END)

END AS ExPx,

Cost,GrossProfit,HasSplit

INTO #_Splits5

FROM #_Splits1

ORDER BY SOPNUMBE,ItemDesc

--Get Cost

SELECT CUSTNAME,SLPRSNID,

XTNDPRCE,SOPTYPE,DOCDATE,NONINVEN,SOPNUMBE,ITEMDESC,EXTDCOST,

VOIDSTTS,CUSTNMBR,CUSTCLAS,MRKDNAMT,QUANTITY,

QTYFULFI,ITEMNMBR,SLPRSNFN,SPRSNSLN,INACTIVE ,

ExPx,

--This is the "Kit Cost" field from CR

CASE WHEN SOPNUMBE = 'xxxxxxxx79' THEN EXTDCOST

WHEN ITEMNMBR = '123-4567' THEN EXTDCOST

WHEN ITEMNMBR LIKE '032-%' THEN 0.00

ELSE

( --This is the "Returns Cost" field from CR

CASE WHEN SOPNUMBE = 'xxxxxx91' THEN EXTDCOST+54.00

WHEN SOPNUMBE = 'xxxx46' THEN EXTDCOST+196.16

WHEN SOPNUMBE = 'xxxxxx95' OR SOPNUMBE = 'xxxxxx69' THEN EXTDCOST

WHEN SOPNUMBE = 'xxxxxx01' AND QTYFULFI = 0 THEN 0.00

WHEN SOPTYPE = 4 THEN 0-EXTDCOST

ELSE

EXTDCOST

END

)

END

AS Cost,

GrossProfit,HasSplit

INTO #_Splits6

FROM #_Splits5

--Get Gross Profit

UPDATE #_Splits6

SET GrossProfit = CAST((ExPx - Cost) as Decimal)

FROM #_Splits6

--Set ExPx and GP to 1/2 value

UPDATE #_Splits6

SET GrossProfit = GrossProfit/2, ExPx = CAST((ExPx/2) as Decimal),Cost = CAST((Cost/2) as Decimal)

FROM #_Splits6

/*

Splits Calc's for Group2

*/  

--Get ExPx

SELECT CUSTNAME,SLPRSNID,

XTNDPRCE,SOPTYPE,DOCDATE,NONINVEN,SOPNUMBE,ITEMDESC,EXTDCOST,

VOIDSTTS,CUSTNMBR,CUSTCLAS,MRKDNAMT,QUANTITY,

QTYFULFI,ITEMNMBR,SLPRSNFN,SPRSNSLN,INACTIVE ,

CASE WHEN SOPTYPE = 4 THEN 0-(

CASE WHEN CUSTNMBR IN('ZZZ200','ZZZ210','ZZZ220') THEN XTNDPRCE

WHEN SOPNUMBE IN ('xxxxxx90','xxxxxx01','xxxx43') THEN 0

ELSE (XTNDPRCE + (MRKDNAMT * QUANTITY))

END) ELSE(CASE WHEN CUSTNMBR IN('ZZZ200','ZZZ210','ZZZ220') THEN XTNDPRCE

WHEN SOPNUMBE IN ('xxxxxx90','xxxxxx01','xxxx43') THEN 0

ELSE (XTNDPRCE + (MRKDNAMT * QUANTITY))

END)

END AS ExPx,

Cost,GrossProfit,HasSplit

INTO #_Splits3

FROM #_Splits2

ORDER BY SOPNUMBE,ItemDesc

--Get Cost

SELECT CUSTNAME,SLPRSNID,

XTNDPRCE,SOPTYPE,DOCDATE,NONINVEN,SOPNUMBE,ITEMDESC,EXTDCOST,

VOIDSTTS,CUSTNMBR,CUSTCLAS,MRKDNAMT,QUANTITY,

QTYFULFI,ITEMNMBR,SLPRSNFN,SPRSNSLN,INACTIVE ,

ExPx,

--This is the "Kit Cost" field from CR

CASE WHEN SOPNUMBE = 'xxxxxxxx79' THEN EXTDCOST

WHEN ITEMNMBR = '123-4567' THEN EXTDCOST

WHEN ITEMNMBR LIKE '032-%' THEN 0.00

ELSE

( --This is the "Returns Cost" field from CR

CASE WHEN SOPNUMBE = 'xxxxxx91' THEN EXTDCOST+54.00

WHEN SOPNUMBE = 'xxxx46' THEN EXTDCOST+196.16

WHEN SOPNUMBE = 'xxxxxx95' OR SOPNUMBE = 'xxxxxx69' THEN EXTDCOST

WHEN SOPNUMBE = 'xxxxxx01' AND QTYFULFI = 0 THEN 0.00

WHEN SOPTYPE = 4 THEN 0-EXTDCOST

ELSE

EXTDCOST

END

)

END

AS Cost,

GrossProfit,HasSplit

INTO #_Splits4

FROM #_Splits3

--Get Gross Profit

UPDATE #_Splits4

SET GrossProfit = CAST((ExPx - Cost) as Decimal)

FROM #_Splits4

--Set ExPx and GP to 1/2 value

UPDATE #_Splits4

SET GrossProfit = GrossProfit/2, ExPx = CAST((ExPx/2) as Decimal),Cost = CAST((Cost/2) as Decimal)

FROM #_Splits4

/*

Need to get cost, expx, gross profit, etc for NON-splits

*/

--Get ExPx

SELECT CUSTNAME,SLPRSNID,

XTNDPRCE,SOPTYPE,DOCDATE,NONINVEN,SOPNUMBE,ITEMDESC,EXTDCOST,

VOIDSTTS,CUSTNMBR,CUSTCLAS,MRKDNAMT,QUANTITY,

QTYFULFI,ITEMNMBR,SLPRSNFN,SPRSNSLN,INACTIVE ,

CASE WHEN SOPTYPE = 4 THEN 0-(

CASE WHEN CUSTNMBR IN('ZZZ200','ZZZ210','ZZZ220') THEN XTNDPRCE

WHEN SOPNUMBE IN ('xxxxxx90','xxxxxx01','xxxx43') THEN 0

ELSE (XTNDPRCE + (MRKDNAMT * QUANTITY))

END) ELSE(CASE WHEN CUSTNMBR IN('ZZZ200','ZZZ210','ZZZ220') THEN XTNDPRCE

WHEN SOPNUMBE IN ('xxxxxx90','xxxxxx01','xxxx43') THEN 0

ELSE (XTNDPRCE + (MRKDNAMT * QUANTITY))

END)

END AS ExPx,

Cost,GrossProfit,HasSplit

INTO #_NonSplits2  

FROM #_NonSplits

ORDER BY SOPNUMBE,ItemDesc

--Get Cost

SELECT CUSTNAME,SLPRSNID,

XTNDPRCE,SOPTYPE,DOCDATE,NONINVEN,SOPNUMBE,ITEMDESC,EXTDCOST,

VOIDSTTS,CUSTNMBR,CUSTCLAS,MRKDNAMT,QUANTITY,

QTYFULFI,ITEMNMBR,SLPRSNFN,SPRSNSLN,INACTIVE ,

ExPx,

--This is the "Kit Cost" field from CR

CASE WHEN SOPNUMBE = 'xxxxxxxx79' THEN EXTDCOST

WHEN ITEMNMBR = '123-4567' THEN EXTDCOST

WHEN ITEMNMBR LIKE '032-%' THEN 0.00

ELSE

( --This is the "Returns Cost" field from CR

CASE WHEN SOPNUMBE = 'xxxxxx91' THEN EXTDCOST+54.00

WHEN SOPNUMBE = 'xxxx46' THEN EXTDCOST+196.16

WHEN SOPNUMBE = 'xxxxxx95' OR SOPNUMBE = 'xxxxxx69' THEN EXTDCOST

WHEN SOPNUMBE = 'xxxxxx01' AND QTYFULFI = 0 THEN 0.00

WHEN SOPTYPE = 4 THEN 0-EXTDCOST

ELSE

EXTDCOST

END

)

END

AS Cost,

GrossProfit,HasSplit

INTO #_NonSplits3

FROM #_NonSplits2

--Get Gross Profit

UPDATE #_NonSplits3

SET GrossProfit = CAST((ExPx - Cost) as Decimal)

FROM #_NonSplits3

--SELECT * FROM #_NonSplits3

-- Combine the Non-Splits and the Splits with Calc'd EXPX,Cost, and GrossProfit

SELECT CUSTNAME,SLPRSNID,

XTNDPRCE,SOPTYPE,DOCDATE,NONINVEN,SOPNUMBE,ITEMDESC,EXTDCOST,

VOIDSTTS,CUSTNMBR,CUSTCLAS,MRKDNAMT,QUANTITY,

QTYFULFI,ITEMNMBR,SLPRSNFN,SPRSNSLN,INACTIVE ,

ExPx,Cost, GrossProfit,HasSplit

INTO #_Results

FROM #_Splits4

INSERT INTO #_Results (CUSTNAME,SLPRSNID,XTNDPRCE,SOPTYPE,DOCDATE,NONINVEN,SOPNUMBE,ITEMDESC,EXTDCOST,VOIDSTTS,CUSTNMBR,CUSTCLAS,MRKDNAMT,QUANTITY,QTYFULFI,ITEMNMBR,SLPRSNFN,SPRSNSLN,INACTIVE ,ExPx,Cost, GrossProfit,HasSplit)

SELECT CUSTNAME,SLPRSNID,

XTNDPRCE,SOPTYPE,DOCDATE,NONINVEN,SOPNUMBE,ITEMDESC,EXTDCOST,

VOIDSTTS,CUSTNMBR,CUSTCLAS,MRKDNAMT,QUANTITY,

QTYFULFI,ITEMNMBR,SLPRSNFN,SPRSNSLN,INACTIVE ,

ExPx,Cost, GrossProfit,HasSplit

FROM #_Splits6

INSERT INTO #_Results (CUSTNAME,SLPRSNID,XTNDPRCE,SOPTYPE,DOCDATE,NONINVEN,SOPNUMBE,ITEMDESC,EXTDCOST,VOIDSTTS,CUSTNMBR,CUSTCLAS,MRKDNAMT,QUANTITY,QTYFULFI,ITEMNMBR,SLPRSNFN,SPRSNSLN,INACTIVE ,ExPx,Cost, GrossProfit,HasSplit)

SELECT CUSTNAME,SLPRSNID,

XTNDPRCE,SOPTYPE,DOCDATE,NONINVEN,SOPNUMBE,ITEMDESC,EXTDCOST,

VOIDSTTS,CUSTNMBR,CUSTCLAS,MRKDNAMT,QUANTITY,

QTYFULFI,ITEMNMBR,SLPRSNFN,SPRSNSLN,INACTIVE ,

ExPx,Cost, GrossProfit,HasSplit

FROM #_NonSplits3

SELECT  SUM(GrossProfit)

FROM #_Results

WHERE

(DOCDATE >= '08/01/2013' AND DOCDATE <= '08/31/2013')AND

SLPRSNID <> '0710Z'

--ORDER BY GrossProfit DESC

/*

DROP TABLE #_Results

DROP TABLE #_NonSplits

DROP TABLE #_NonSplits2

DROP TABLE #_NonSplits3

DROP TABLE #_Splits5

DROP TABLE #_Splits6

DROP TABLE #_Splits4

DROP TABLE #_Splits3

DROP TABLE #_Splits2  

DROP TABLE #_Splits1

DROP TABLE #_Main

*/

*This post is locked for comments

  • ASheppardWork Profile Picture
    ASheppardWork 220 on at
    RE: GP SSIS vs. Stored Procedures vs. ?? - Converting Crystal Report to Automated SSRS

    Here is an update for anyone running into this problem themselves.  

    I have taken Sanjay's advice and applied some new twists to it.  

    First of all, I am making new tables.  These new tables are populated by a series of stored procs that populate the data at various times based on expressed customer needs.  Then my SSRS reports are running against Stored Procs that query the newly created tables while passing in parameters from the user.  Allowing me to use the saved execution plans to make the query more efficient and the result sets smaller for transmission over the network.  

    The first attempt I tried was to return the entire set (the query I posted above) to SSRS and then have the logic work there; however, that produced over 186,000 records and took more than 45 minutes to process.  The second attempt was to put all 186,000 records into a master table and still have SSRS do the math on gross profit, percentages, grouping etc.  That did improve things but I the time it took to populate in SSRS was too poor (15 minutes).  

    Finally, I stumbled on some posts on-line that were using stored procedures as data sources that would run against tables and would store the execution plan as long as it was using the proper syntax.  Now the entire report takes less than 90 seconds to populate on the SSRS site and transmits only 24 records; which are the number of users being reported on.

    Lastly, we had to have a rule that ran the reports on the 3rd business day of the month, so I used a aux calendar table created with a number table and company specific holiday logic and used SQL Job Agent to run each day testing if it was the 3rd business day.  If the test is true the SQL Agent run the scheduled job from the SSRS schedules identified by the jobs guid from the Sql Server Agent Jobs list.

    The end result is the same report that required CR, Excel, SSMS, and manual calculations now runs solely in SSRS and is automatically delivered to over 30 users daily.

  • Verified answer
    Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: GP SSIS vs. Stored Procedures vs. ?? - Converting Crystal Report to Automated SSRS

    Hello,

    We do run lots of SSRS reports, each as various ways of compiling data.  General rule which has worked:

    If the report requires pre-processing of data, meaning  multiple steps of selects and inserts into before the data is ready for usage by SSRS report, and the data pre compile process  is more than 15 seconds and the the query is run against a large data base which is expected to run for more than 1 to 2 minutes, SSIS is package  scheduled by SQL Agent is preferred  to pre compile of data - reasons -very less change of Query time out with SSIS Packages and  it can be scheduled during less peak times .

    Where there is user intervention by way of selection criteria, and the data compile is a complex,   stored procs offers some benefits by way SQL cached plans.

    For less complex and highly filtered reports a direct query at SSRS will do.

    In all cases do run the query against SQL server database engine tuning advisor  and where ever applicable use the "with no locks"

    Cheers!

    Sanjay

  • ASheppardWork Profile Picture
    ASheppardWork 220 on at
    RE: GP SSIS vs. Stored Procedures vs. ?? - Converting Crystal Report to Automated SSRS

    Sanjay,  Is there any benefit from a memory/data efficiency standpoint to either option?  What should I look out for in terms of traffic that could slow or stop the server if I implemented the Stored Procedure?

  • Suggested answer
    Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: GP SSIS vs. Stored Procedures vs. ?? - Converting Crystal Report to Automated SSRS

    What I do for my report is

    0) All the reports are automated delivery, no user execution, run at scheduled time every day

    1) The select statement is last statement of the query which enable the data population at designer.

    2) The drop statements are first in the list,  if object_id('xxxxx') is not null then drop table db..table and the are is populated to temp tables via select into statements.

    Note: For the where clause you can create SSRS parameters and access the parameters at the where clause if user are going to use the report and select parameters - need to be careful if two users use the same report same time - as there  is a drop statement.  SSIS population or Store proc  will also work fine, it all boils down to which technique you are comfortable.

    Cheers!

    Sanjay

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

November Spotlight Star - Khushbu Rajvi

Congratulations to a top community star!

Forum Structure Changes Complete!

🔔 Be sure to subscribe to the new forums you are interested in to stay up to date! 🔔

Dynamics 365 Community Platform update – Oct 28

Welcome to the next edition of the Community Platform Update. This is a status …

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,113 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 229,918 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans