Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

WHERE clause within IF/THEN/ELSE statement - CR XI

Posted on 2010-08-23
10
Medium Priority
?
270 Views
Last Modified: 2012-05-10
Currently I have a parameter {?Class1}

I have one column of data the pulls based
"If {Command.Class}={?Class1}
Then {Command.Amount}
Else 0"

What I need, is a second column that pulls
" If {?Class1} = "Fall2010"
Then {Command.Amount} WHERE {Command.Class}="Spring2009"
Else 0"

Of course, this errors with everything from WHERE to the end as "...does not appear to be part of the formula."


SIDE NOTE: Command.Class is going to be Spring2011, Spring2010, Spring2009, spring2008, Spring2007, Fall2010,Fall2010, Fall2009, Fall2008, Fall2007
What I need is Crystal to select Command.Amounts based on previous class, but Fall2009 come after Spring2010 not Spring2009 so any other suggestions on how to do this would also be appreciated.

TIA
0
Comment
Question by:stlinae
[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
  • 3
  • 2
  • 2
  • +3
10 Comments
 
LVL 101

Expert Comment

by:mlmcc
ID: 33506639
Where are you doing this?

mlmcc
0
 
LVL 11

Author Comment

by:stlinae
ID: 33507470
I was creating a formula for it to run in the detail section.

(BTW, the data is coming from an QuickBooks SP_Report.)
0
 
LVL 4

Expert Comment

by:msd1305
ID: 33508130
do you mean this?

If {?Class1} = "Fall2010"  and {Command.Class}="Spring2009"
Then {Command.Amount}
Else 0


actually the question is still not very clear.. are you using this is in Record selection formula or is this a formula field?
0
Enroll in September's Course of the Month

This month’s featured course covers 16 hours of training in installation, management, and deployment of VMware vSphere virtualization environments. It's free for Premium Members, Team Accounts, and Qualified Experts!

 
LVL 11

Expert Comment

by:tickett
ID: 33508289
msd1305's solution looks like what you want
0
 
LVL 2

Expert Comment

by:coronoahcoro
ID: 33509971
I don't think what msd1305 suggested is what stlinae want because it will be strange in this case if one record  has two class. I think what stlinae wants is when someone choose a class then CR puts the amount of that class in the first column and the rest of the columns put an amount from the previous term/season/class.

So let's say I choose Spring2010, then I want the amount from class Spring2010 to show up on the first column, the 2nd column will have the amount from Fall2010, 3rd column Spring 2009, and so on.

Please correct me if I'm wrong
0
 
LVL 2

Expert Comment

by:coronoahcoro
ID: 33510265
I was thinking to create a Formula  Fields with Select expression (call it ClassNum) that will look on the Class and set a number for them, like Spring2011 = 1, Fall2010 = 2. Spring2010 = 3, ...

With that I was thinking when a user choose a class, CR will put that amount class on the first column, look on the ClassNum field and create a second and the rest of the column based on previous ClassNum + 1 but of course I forgot that I don't know if CR can create a field programmatically.  
0
 
LVL 11

Author Comment

by:stlinae
ID: 33513059
coronoahcoro, you're onto the right idea. That's exactly what I want to happen. Of course I'm not sure I have any idea how to do it....

msd1305, I tried your suggestion, it brings back nothing but 0's, but thank you for the suggestion. And I'm using this in a formula field.
0
 
LVL 101

Accepted Solution

by:
mlmcc earned 1500 total points
ID: 33536749
I think you will have to use a subreport or a SQL expression.

Some information on SQL Expressions
http://www.experts-exchange.com/Database/Reporting_/Crystal_Reports/Q_23858441.html?cid=748#a27624600

mlmcc
0
 
LVL 11

Author Closing Comment

by:stlinae
ID: 33561886
While the excepted solution gives information on the steps needed to do this, I'm still completely lost on the HOW.
0
 
LVL 35

Expert Comment

by:James0628
ID: 33574014
FWIW ...

 I've been following this, but didn't jump in sooner because you'd gotten some suggestions and, frankly, I was being lazy and waiting to see how they panned out.  :-)

 If I follow what you're trying to do, I think you can do this with a cross-tab.  I do have a couple of questions though.

 You're trying to get a total of {Command.Amount} for each term, right?  For example, if someone asked for Fall2010, you'd have total columns like:

 Fall2010   Spring2010   Fall2009   Spring2009   Fall2008   Spring2008   <and so on>

 Assuming that I'm correct about that, are those grand totals for the entire report, or for some group on the report?

 If someone asks for Fall2009, for example, what happens to the records for terms after that (Spring2010, etc.)?  Do you have a record selection formula that will filter out those records or do they get included somewhere in your counts?

 James
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

705 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