Solved

Record Selection Syntax Problem

Posted on 2008-10-15
18
327 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
  • 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
 
LVL 100

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 100

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 100

Expert Comment

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

Where is the subreport?

mlmcc
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

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 100

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 100

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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

706 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now