Expiring Todayâ€”Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Record Selection Syntax Problem

Posted on 2008-10-15
Medium Priority
338 Views
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
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
• 8
• 5
• 3
• +1

LVL 37

Expert Comment

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

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)
``````
0

LVL 37

Expert Comment

ID: 22720181
i don't know crystal reports
i gave you above the tsql syntax
0

LVL 101

Accepted Solution

mlmcc earned 1000 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

corobv earned 1000 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

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

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

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

ID: 22726116
Where are the formulas and the subreport?

Where is the subreport?

mlmcc
0

Author Comment

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

LVL 2

Expert Comment

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

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

Author Comment

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

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

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

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

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

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

## Featured Post

Question has a verified solution.

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

Why is this different from all of the other step by step guides? Â Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff lâ€¦
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
###### Suggested Courses
Course of the Month11 days, 9 hours left to enroll