jebarr111
asked on
Record Selection Syntax Problem
I am trying to report from two databases, neither of which I can edit the structure of. One database contains sales data from various departments, the other contains expenses. However, because of the way the two are structured, I cannot seem to display the report correctly.
In the sales database, there are departments, sales dates, and charge amounts. So far, the report is grouped by department, then by sales date (by month), with a total of all charges for that month by department. I would like to then list the expenses for each month for that department.
The expenses database contains fields for July's expenses (Exp_01), August's expenses (Exp_02), etc. There is also a field for Fiscal Year.
I have tried to write a formula to show monthly expenses based on each month. I can easily say "if Sales Date = "7/2008" then sum({Exp_01)." But, I need to narrow this to only Fiscal Year 2008. In other words, I need "if sales date = 7/2008 then sum ({Exp_01}) WHERE FY = 2008. I don't know if using a where statement is possible though.
I have also tried (successfully) creating a subreport. I can't use this method though, as I need to then write a formula subtracting the expenses from the charges, and the subreport showing expense totals is not a selectable field.
I know it's a bit complicated, & I'll clarify as much as I can if you have a specific question. Thank you in advance for your help with this.
In the sales database, there are departments, sales dates, and charge amounts. So far, the report is grouped by department, then by sales date (by month), with a total of all charges for that month by department. I would like to then list the expenses for each month for that department.
The expenses database contains fields for July's expenses (Exp_01), August's expenses (Exp_02), etc. There is also a field for Fiscal Year.
I have tried to write a formula to show monthly expenses based on each month. I can easily say "if Sales Date = "7/2008" then sum({Exp_01)." But, I need to narrow this to only Fiscal Year 2008. In other words, I need "if sales date = 7/2008 then sum ({Exp_01}) WHERE FY = 2008. I don't know if using a where statement is possible though.
I have also tried (successfully) creating a subreport. I can't use this method though, as I need to then write a formula subtracting the expenses from the charges, and the subreport showing expense totals is not a selectable field.
I know it's a bit complicated, & I'll clarify as much as I can if you have a specific question. Thank you in advance for your help with this.
ASKER
Attached is the actual code I am trying to use with your suggestion......what is the correct syntax?
sum (case when GroupName ({Sales.Chg_Date}, "monthly")="7/2008" and {ActualExpenses.FSCL_YR}=2008 then {ActualExpenses.Exp_01} else 0)
i don't know crystal reports
i gave you above the tsql syntax
i gave you above the tsql syntax
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Interestingly enough, when I add the variable "Expenses", the correct value shows......over and over for each month (even though it's only July's expenses in that particular variable). I know that I can conditionally suppress. However, my last snag is that the July expense amount is correct each time it is repeated, except in the July group on the main report, where it shows as a "0"
You need to declare the main report variable before the subreport
I generally do that in the report header. Simply add a formula
WhilePrintingRecords;
Shared NumberVar Expenses;
''
mlmcc
I generally do that in the report header. Simply add a formula
WhilePrintingRecords;
Shared NumberVar Expenses;
''
mlmcc
ASKER
For some reason, it's still coming up as a zero.....here's how the report appears
PH 101 Department 1
GH1 Month Total Sales Subreport
GH2a 7/2008 64,231 0.00
GH2a 8/2008 66,387 41886.19
GH2a 9/2008 62,123 41886.19
GF1a Quarter: 192,741
PH 101 Department 1
GH1 Month Total Sales Subreport
GH2a 7/2008 64,231 0.00
GH2a 8/2008 66,387 41886.19
GH2a 9/2008 62,123 41886.19
GF1a Quarter: 192,741
Where are the formulas and the subreport?
Where is the subreport?
mlmcc
Where is the subreport?
mlmcc
ASKER
Does the subreport need to appear? Or just the variable named in the subreport?
The sub report needs to appear. if only to provide the sum of the expenses. then you can use the formula to subtract the two totals.
ASKER
Well, I think we have it.....so should I create new subreports for each month, or just new variables?
ASKER
Nope....still not quite right. When I put the Expenses variable in the subreport, then show the subreport on the main report, the value is correct. However, if I just show the variable "expenses" on the main report, for the first group (July), the value is 0. For all other groups, it is correct. I would have no problems just showing the subreport, but if I do any calculations on that variable (for example I did a simple one and added 10,000 to it), for July it shows a total of 10,000.
I would just create new variables. You could use a variable to pass the month to the subreport or use a subreport link to pass either the {Sales.Chg_Date} to it to get the month or a formula that pulls the month from {Sales.Chg_Date} and then over lay the sum of the fields and suppress and show based on the month variable. that way you could show the right sum with the right month all in the same place.
So, create three sums, one for each fields and place them in the exact same spot in the sub report. Then in the suppress formula use the month variable to suppress the ones you don't want and a show the one you do.
So, create three sums, one for each fields and place them in the exact same spot in the sub report. Then in the suppress formula use the month variable to suppress the ones you don't want and a show the one you do.
The subreport doesn't need to appear but you can't suppress it otherwise it won't run.
You could have it set to not grow and kept relatively small.
It sounds like the formulas are working just placed badly.
What formulas are in the main report for the variable? What sections are they in?
What section is the subreport in?
mlmcc
You could have it set to not grow and kept relatively small.
It sounds like the formulas are working just placed badly.
What formulas are in the main report for the variable? What sections are they in?
What section is the subreport in?
mlmcc
ASKER
If I keep the date grouping (month) in the GH1, but place the subreport & variables in the GH2, all seems to be well. I'm now up to 15 different variables passing back & forth between the main report & the subreport (to allow calculations such as revenue - expenses). The formatting is a little strange with the dates in a different section, but it certainly working at this point.
Thank you all for your help. I will go through and award points as fairly as I can. There are multiple answers which were helpful. I don't know if it's possible, but I would like to award full points to the authors of all the "correct" solutions.
Thank you all for your help. I will go through and award points as fairly as I can. There are multiple answers which were helpful. I don't know if it's possible, but I would like to award full points to the authors of all the "correct" solutions.
Unfortunately for us you are limited to 500 points on a question.
Simply click the ACCEPT MULTIPLE SOLUTIONS in one of the solution comments and follow the prompts to award points.
mlmcc
Simply click the ACCEPT MULTIPLE SOLUTIONS in one of the solution comments and follow the prompts to award points.
mlmcc
ASKER
Thank you both for all your help with this.
sum (case when sales_date='7/2008' and fy=2008 then exp1 else 0)
this will sum the sales from that date and that fy