You’re offline. This is a read only version of the page.
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
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.
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
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?
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
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,113
Super User 2024 Season 2
#2
Martin Dráb
229,918
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (