Solved

Record Selection Syntax Problem

Posted on 2008-10-15
18
336 Views
Last Modified: 2010-04-21
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
Comment
Question by:jebarr111
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 5
  • 3
  • +1
18 Comments
 
LVL 37

Expert Comment

by:momi_sabag
ID: 22720096
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
 

Author Comment

by:jebarr111
ID: 22720156
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
 
LVL 37

Expert Comment

by:momi_sabag
ID: 22720181
i don't know crystal reports
i gave you above the tsql syntax
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 101

Accepted Solution

by:
mlmcc earned 250 total points
ID: 22720598
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
 
LVL 2

Assisted Solution

by:corobv
corobv earned 250 total points
ID: 22721066
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
 

Author Comment

by:jebarr111
ID: 22723595
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
 
LVL 101

Expert Comment

by:mlmcc
ID: 22723636
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
 

Author Comment

by:jebarr111
ID: 22723822
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
 
LVL 101

Expert Comment

by:mlmcc
ID: 22726116
Where are the formulas and the subreport?

Where is the subreport?

mlmcc
0
 

Author Comment

by:jebarr111
ID: 22729513
Does the subreport need to appear?  Or just the variable named in the subreport?
0
 
LVL 2

Expert Comment

by:corobv
ID: 22729638
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
 

Author Comment

by:jebarr111
ID: 22729840
Well, I think we have it.....so should I create new subreports for each month, or just new variables?
0
 

Author Comment

by:jebarr111
ID: 22729869
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
 
LVL 2

Expert Comment

by:corobv
ID: 22730047
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
 
LVL 101

Expert Comment

by:mlmcc
ID: 22733527
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
 

Author Comment

by:jebarr111
ID: 22733591
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
 
LVL 101

Expert Comment

by:mlmcc
ID: 22736289
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
 

Author Closing Comment

by:jebarr111
ID: 31506267
Thank you both for all your help with this.  
0

Featured Post

Want Experts Exchange at your fingertips?

With Experts Exchange’s latest app release, you can now experience our most recent features, updates, and the same community interface while on-the-go. Download our latest app release at the Android or Apple stores today!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question