Build Report From Scratch With Commands
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=21011
Printed Date: 07 May 2024 at 7:04am
Topic: Build Report From Scratch With Commands
Posted By: SOSteppenwolf
Subject: Build Report From Scratch With Commands
Date Posted: 04 Sep 2014 at 5:08am
I am trying to create a rolling unit sales history report(which will, in turn, forecast sales) from scratch using Commands. I determined I can't build this through conventional means because there will simply be too many formulas. Here is my previous post on the matter:
http://www.crystalreportsbook.com/Forum/forum_posts.asp?TID=20967&KW= - here
I've succeeded in pulling unit sales and creating a header for the field that specifies the data is for Sept 2011 (36 mos ago). Here is my SQL syntax for that:
SELECT "SalesTable"."UnitSalesQty" - "SalesTable"."UnitReturnQty" AS "NetQty36", DATEADD(m, -36, GETDATE()-day(GETDATE()-1)) AS "MM36", "ItemTable"."ItemNum"
FROM "ERP_app"."dbo"."SalesTable" "SalesTable"
INNER JOIN "ERP_app"."dbo"."ItemTable" "ItemTable" ON "ItemTable"."ItemKey"="SalesTable"."ItemKey"
WHERE "SalesTable"."SalesDate"
BETWEEN DATEADD(m, -36, GETDATE()-day(GETDATE()-1)) AND DATEADD(m, -35, GETDATE()-day(GETDATE()-1))-1 AND "ItemNum" = '1002'
I'm only selecting one item now for simplicity's sake.
My issue is, when I want to add Oct 2011, I don't know whether to add to this command or to create a new one. I've tried both. When I add to this command, I get error messages because the syntax with the date parameters is wrong. When I try create a new command with the "36's" changed to "35's" and add the data to the report, my Sept 2011 totals (which were right) end up too high.
I'm an accounting/finance guy by trade so please pardon any ignorance on my part.
Thanks for your help.
|
Replies:
Posted By: kostya1122
Date Posted: 04 Sep 2014 at 9:19am
probably not the best solution, but it might work for you
SELECT
SalesTable.UnitSalesQty - SalesTable.UnitReturnQty AS NetQty36
,DATEADD(m, - 36, GETDATE() - day(GETDATE() - 1)) AS MM36
,ItemTable.ItemNum
FROM ERP_app.dbo.SalesTable SalesTable
INNER JOIN ERP_app.dbo.ItemTable ItemTable
ON ItemTable.ItemKey = SalesTable.ItemKey
WHERE SalesTable.SalesDate BETWEEN DATEADD(m, - 36, GETDATE() - day(GETDATE() - 1))
AND DATEADD(m, - 35, GETDATE() - day(GETDATE() - 1)) - 1
AND ItemNum = '1002'
union all
SELECT
SalesTable.UnitSalesQty - SalesTable.UnitReturnQty AS NetQty36
,DATEADD(m, - 35, GETDATE() - day(GETDATE() - 1)) AS MM36
,ItemTable.ItemNum
FROM ERP_app.dbo.SalesTable SalesTable
INNER JOIN ERP_app.dbo.ItemTable ItemTable
ON ItemTable.ItemKey = SalesTable.ItemKey
WHERE SalesTable.SalesDate BETWEEN DATEADD(m, - 35, GETDATE() - day(GETDATE() - 1))
AND DATEADD(m, - 34, GETDATE() - day(GETDATE() - 1)) - 1
AND ItemNum = '1002'
|
Posted By: hilfy
Date Posted: 04 Sep 2014 at 11:55am
Rule of thumb when using commands - NEVER join multiple commands. When you do, Crystal will pull ALL of the data from every command into memory and do the join there. This can significantly slow down a report. When you can get everything into a single command, all of that work is pushed to the database where it will be more efficiently processed.
-Dell
-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics
|
Posted By: SOSteppenwolf
Date Posted: 05 Sep 2014 at 3:50am
Thanks for the response.
Did as you suggested, but now Crystal is combining the unit sales for Sep & Oct 2011 in the NetQty36 variable. Also, it did not create a NetQty35 variable or the MM35 header?
So,it's closer than I was! Hopefully once I figure out how to do month 35 I can work it out how to get to the present.
-------------
I'm a accounting/finance guy by trade so please pardon the ignorance!
|
Posted By: SOSteppenwolf
Date Posted: 05 Sep 2014 at 4:12am
When you say "NEVER join multiple commands" do you mean never even create multiple commands?
Thanks,
-------------
I'm a accounting/finance guy by trade so please pardon the ignorance!
|
Posted By: hilfy
Date Posted: 05 Sep 2014 at 4:19am
I have seen instances where a report has multiple commands, but that's in a very specific situation:
Only one command provides the data for the report. Any additional commands are NOT joined to anything and are used specifically to provide data for dynamic prompts.
When you join multiple commands, Crystal will pull ALL of the data for each of them into memory and join them there. If your commands are not returning a lot of data, then the performance hit on this may be acceptable. However, if you're expecting to get thousands of rows of data, your report will be slow and, in some cases, Crystal may run out of memory to do the join.
-Dell
-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics
|
Posted By: SOSteppenwolf
Date Posted: 05 Sep 2014 at 4:29am
Well, the whole reason I ventured into using commands as opposed to building the report "normally" was because the number of records and number of formulas had brought this report to a screeching halt.
So, good to know. Thanks again.
-------------
I'm a accounting/finance guy by trade so please pardon the ignorance!
|
Posted By: lockwelle
Date Posted: 05 Sep 2014 at 12:28pm
well, there always is the other alternative...
Create a stored procedure and have all the data gathered on the server. Put the formulas into it as well and just return the columns that you actually need for the report.
Just a thought, and I know that this is not necessarily available or possible for everyone to do.
|
Posted By: SOSteppenwolf
Date Posted: 08 Sep 2014 at 7:19am
Are commands handled the same way as stored procedures (at the server)? Will using a command instead of all those formulas speed this up? If not, I'm going to have to approach this a different way.
What software would I need to make a stored procedure? MySQL?
Thanks,
-------------
I'm a accounting/finance guy by trade so please pardon the ignorance!
|
Posted By: hilfy
Date Posted: 08 Sep 2014 at 8:15am
Commands and stored procs are both processed in the database. I use commands when I can write a SQL Query to get all of the data I need. I use a stored proc when there is some additional processing I need to do which makes it not possible to write a single SQL Query.
Yes, putting the formulas in your query instead of processing them in Crystal may speed up the report - especially if you're doing aggregation (sums, counts, etc). By doing aggregation in the query, the database returns fewer records and so there's less for Crystal to process.
-Dell
-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics
|
|