• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 345
  • Last Modified:

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.
0
jebarr111
Asked:
jebarr111
  • 8
  • 5
  • 3
  • +1
2 Solutions
 
momi_sabagCommented:
you can do
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
0
 
jebarr111Author Commented:
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)

Open in new window

0
 
momi_sabagCommented:
i don't know crystal reports
i gave you above the tsql syntax
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
mlmccCommented:
In Crystal

You can use a subreport and use shared variables to pass information between the main report and the suibreport.

You can definitely filter the expense data and select only FY = 2008

mlmcc
0
 
corobvCommented:
Try this:
Add a formula called July Expenses and use this formula

If Month({Sales.Chg_Date}) = 7 and Year({Sales.Chg_Date}) = {ActualExpenses.FSCL_YR} then {ActualExpenses.Exp_01} else 0

But I think using a sub report would be the best way.  

If you use a subreport create a formula to store the variable and in the main report create a formula to display the variable like so
SUBREPORT
WhilePrintingRecords;
Shared NumberVar Expenses;
Expenses:= Sum({ActualExpenses.Exp_01})

MAIN REPORT
WhilePrintingRecords;
Shared NumberVar Expenses;
Expenses
0
 
jebarr111Author Commented:
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"
0
 
mlmccCommented:
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
0
 
jebarr111Author Commented:
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
0
 
mlmccCommented:
Where are the formulas and the subreport?

Where is the subreport?

mlmcc
0
 
jebarr111Author Commented:
Does the subreport need to appear?  Or just the variable named in the subreport?
0
 
corobvCommented:
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.
0
 
jebarr111Author Commented:
Well, I think we have it.....so should I create new subreports for each month, or just new variables?
0
 
jebarr111Author Commented:
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.
0
 
corobvCommented:
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.  
0
 
mlmccCommented:
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
0
 
jebarr111Author Commented:
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.
0
 
mlmccCommented:
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
0
 
jebarr111Author Commented:
Thank you both for all your help with this.  
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 8
  • 5
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now