Active TopicsMemberlistSearchHelpRegisterLogin

Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Report Design
Message Icon Topic: Crosstab - Three Tables - Month by Date Grouped Post Reply Post New Topic
Author Message
twistednerve
Newbie
Newbie


Joined: 28 Feb 2013
Online Status: Offline
Posts: 5
Quote twistednerve Replybullet Topic: Crosstab - Three Tables - Month by Date Grouped
     Posted: 02 Mar 2013 at 3:01am
Hi,

I am putting together a report that compares salespeople budgets against order enty and invoicing. The report contains three tables all with date fields dd-mm-yyyy, the three tables are linked by a sales territory feild.

The data needs to be compared by month and I do not know how to link the dates in the three tables grouped into months. I can get one table at a time into a crosstab with the dates grouped into months however I am unsure as to how add the data from the other tables to match up by the month all in the one cross tab.

I am looking to acheive the below:

Jan - 13     Budget
                Order Entry
                Invoicing

Feb - 13     Budget
                Order Entry
                Invoicing

I am new to Crystal Reports so I hope that I have explain my problem sufficiently.

Any help would be appreciated Smile


Edited by twistednerve - 02 Mar 2013 at 3:50am
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
Quote hilfy Replybullet Posted: 04 Mar 2013 at 4:15am
So, if I understand you correctly, Budget, Order Entry, and Invoicing are in separate tables.  Is that correct?
 
How are your SQL skills?  The only ways I know of to get this data into a crosstab involve either:
 
1.  Creating a command (SQL Select statement) in Crystal or
2.  Creating a stored procedure in the database
 
Either method would use a union query to return the data in a format that can be worked into the crosstab.
 
-Dell
Proviti, Data & Analytics Practice
www.protiviti.com/US-en/data-management-advanced-analytics
IP IP Logged
twistednerve
Newbie
Newbie


Joined: 28 Feb 2013
Online Status: Offline
Posts: 5
Quote twistednerve Replybullet Posted: 04 Mar 2013 at 10:28am
Hi Dell,
 
Thanks for replying, you are correct, Budget, Order Entry, and Invoicing are in separate tables
 
I have more experience with MS Access so I understand the basics of SQL however would not know where to start in Crystal.
 
If you could point me in the right direction it would be appreciated.
 
Cheers.
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
Quote hilfy Replybullet Posted: 05 Mar 2013 at 3:50am
A command is just a SQL statement using the same syntax as you would for a query run in any tool that accesses your database.
Here's the logic I would use for the command:
 
Select
'Budget' as Record_Type,
<all other fields for your report>
from Budget
  <joins to other tables>
where
  <filters>
 
UNION
 
Select
'Order Entry' as Record_Type,
<all other fields for your report>
from Orders
<joins to other tables>
where
<filters>
 
UNION
 
Select
'Invoicing' as Record_Type,
<all other fields for your report>
from Invoicing
<joins to other tables>
where
<filters>
 
NOTE:  Include ALL of the data you need in the report in your command!  Crystal runs poorly when you combine multiple commands or a command and tables in a single report!
 
Assuming you're running this against an Access database, I would write and test the query in Access and then copy and paste it into Crystal.
 
If you need to run the query based on any parameters, you need to create the parameters in the Command Editor NOT in the Parameters section of the Field Explorer.  You then place your cursor in the command where you want the parameter to be located and double-click on the parameter to insert it.  If the parameter contains a single-value string, you'll need to put single quotes around it.  All other parameter types, including multi-valued parameters, will be handled appropriately.
 
-Dell
Proviti, Data & Analytics Practice
www.protiviti.com/US-en/data-management-advanced-analytics
IP IP Logged
twistednerve
Newbie
Newbie


Joined: 28 Feb 2013
Online Status: Offline
Posts: 5
Quote twistednerve Replybullet Posted: 07 Mar 2013 at 7:04pm
Thanks for your help, I have acheived my objective. I was unable to union all three as two were coming from Visual Fox Pro tables and the third was an Access database (it just crashed CR) so I did two commands and it did not slow things down.
 
Command 1
 
SELECT "Orders" AS "type", month(`salesheader`.`orddate`) AS "Month", `salesheader`.`status`, `salesheader`.`salesman`, `salesheader`.`terr`, `salesdetail`.`qtyord`, `salesdetail`.`sell`
FROM   `salesdetail` `salesdetail` INNER JOIN `salesheader` `salesheader` ON `salesdetail`.`oeno`=`salesheader`.`oeno`
WHERE  (`salesheader`.`orddate`>={d '2013-03-06'} AND `salesheader`.`orddate`<={d '2013-03-07'}) AND (`salesheader`.`status`='3' OR `salesheader`.`status`='4' OR `salesheader`.`status`='5')
 
UNION ALL
 
SELECT "Invoice" AS "type", month(`invheader`.`invdate`)  AS "Month", `invheader`.`status`, `invheader`.`salesman`, `invheader`.`terr`, `invdetail`.`qtyinv`, `invdetail`.`usell`, `invdetail`.`cusell`
FROM   `invdetail` `invdetail` INNER JOIN `invheader` `invheader` ON (`invdetail`.`shipno`=`invheader`.`shipno`) AND (`invdetail`.`oeno`=`invheader`.`oeno`)
WHERE  (`invheader`.`invdate`>={d '2013-03-06'} AND `invheader`.`invdate`<={d '2013-03-07'}) AND (`invheader`.`status`='1' OR `invheader`.`status`='2' OR `invheader`.`status`='3')
 
Command 2
 
SELECT "Budget" AS "type", month(`TerritoryBudgets`.`BudgetDate`) AS Month, `TerritoryBudgets`.`Terr`, "1" AS qty, `TerritoryBudgets`.`BudgetAmount`, `TerritoryBudgets`.`BudgetAmount`
FROM   `TerritoryBudgets`
 
Then I just linked the Terr and Month fields and works perfecty. Turning the date to a month number was the key and as the report is for a financial year there would never be data from the same month from two different years.
 
Thanks again :)
 
 
IP IP Logged
Printable version Printable version

Forum Jump 		You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum

HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | PRIVACY | CLICK PLAYS | KONACAD

© 2004-2026 Brian Bischof. All Rights Reserved.