| Author |
Message |
REDBA
Newbie
Joined: 23 Dec 2010
Location: United States
Online Status: Offline
Posts: 9
|
   Topic: If Formula
Posted: 04 Jan 2011 at 9:17am |
I'm using Crystal Reports 11
I want to write If formula as following:
If {x} = "First1, Last1; First2, Last2; First3, Last3"
then ("First1 Last1" and "First2 Last2" and "First3 Last3")
but it gave me error
|
 IP Logged |
|
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3701
|
Posted: 04 Jan 2011 at 10:07am |
Please explain more clearly what you're trying to do - what is "First1, Last1;..."? Where does this come from?
Also, what is the purpose of your "and"'s?
Are you trying to split something into multiple parts?
-Dell
|
|
|
|
IP Logged |
|
REDBA
Newbie
Joined: 23 Dec 2010
Location: United States
Online Status: Offline
Posts: 9
|
Posted: 04 Jan 2011 at 10:29am |
I’m using crystal reports 11 and exporting data from Raiser’s Edge
I’m trying to build solicitor report
Usually the solicitor name looks like “Last, First” but when there are more than one solicitor sharing the same proposal the name (in the database file) will show as following:
Last1, First1; Last2, First2
While if it is only one solicitor on this specific proposal it will show as following:
Last1, First1
I need assistance in getting the solicitor names to appear separately because the way it is will consider “Last1, First1; Last2, First2” as 1 solicitor
So I need to adjust it somehow to tell that there are 2 solicitors involved in this specific proposal and they are: “Last1, First1” and ” Last2, First2”
So each one of them will get the credit, not just one
The way I did it as following :
If {CnAct_1.CnAct_1_Solicitor(s)}= “Last1, First1; Last2, First2” then “Last1, First1” else
If {CnAct_1.CnAct_1_Solicitor(s)}= “Last1, First1; Last2, First2” then ,” Last2, First2”
I knew logically it is wrong .. it worked but give the credit to one of them only not both
I want to say:
If {CnAct_1.CnAct_1_Solicitor(s)}= “Last1, First1; Last2, First2” then “Last1, First1” and ” Last2, First2”
This is way gave me an error
Thank you
|
|
IP Logged |
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3701
|
Posted: 05 Jan 2011 at 3:46am |
Are you grouping by solicitor or by proposal? If you're grouping by solicitor, this can be done but it's not simple. How good are your SQL skills? The only way I can think of to get this working is to write a select statement (Command in Crystal) where you union together three queries:
1. The first will get everything where there is only one solicitor:
CnAct_1.CnAct_1_Solicitor(s) not like '%;%'
2. The second will parse out the first name from the field when there's more than one.
3. The third will parse out the second name from the field when there's more than one.
This will give you a result set where there is one row per proposal/solicitor combination which is what you need to produce the report your looking for.
-Dell
|
|
|
|
IP Logged |
|
REDBA
Newbie
Joined: 23 Dec 2010
Location: United States
Online Status: Offline
Posts: 9
|
Posted: 05 Jan 2011 at 4:10am |
I'm grouping by solicitor
I had never done any SQL formulas before
The problem there is up to 3 solicitors in some proposals
I was just using simple IF statement for every single case
Can you be more specific how that select statement will look like?
I’m just beginner level in Crystal Reports
Thanks for assistance 
|
|
IP Logged |
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3701
|
Posted: 05 Jan 2011 at 4:35am |
The problem with just using an If statement is that you'll still only have one record per proposal no matter how many solicitors there are. You need to have one record per proposal/solicitor combination.
Crystal can get its data through a couple of different mechanisms. The one you're probably using is to select one or more tables and set up the joins (links) between them. From this, Crystal creates the SQL query to get the data for your report. You can see this query by selecting "Show SQL Query" on the database menu.
Another methods for getting data is to write the SQL query yourself instead of having Crystal do it for you. In Crystal, this is called a Command. This is what you're going to have to do to get the data you're looking for in the format you need for your report. Unfortunately. I'm not familiar with Raiser's Edge and I don't know what type of database it runs on, so I'm not going to be able to help you with the specific SQL that you need to write. Do you have anyone in your company who can help you with this?
-Dell
|
|
|
|
IP Logged |
|
REDBA
Newbie
Joined: 23 Dec 2010
Location: United States
Online Status: Offline
Posts: 9
|
Posted: 05 Jan 2011 at 10:11am |
Unfortunately no one here can help me with this since I’m the only one familiar with Crystal Reports. About Raiser’s edge it is fundraising and donor management software mainly for nonprofit organizations. The way it works with Crystal Reports export database tables in MDB format. Then use these tables externally with Crystal Reports.
I’ll appreciate your help and assistance
|
|
IP Logged |
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3701
|
Posted: 05 Jan 2011 at 10:26am |
Unfortunately, I haven't ever really worked with Access (that's what an MDF file is), so I don't know the specific syntax you'll need to get what you're looking for. Let me think on this a little more and see whether I can come up with another way of doing this.
-Dell
|
|
|
|
IP Logged |
|
REDBA
Newbie
Joined: 23 Dec 2010
Location: United States
Online Status: Offline
Posts: 9
|
Posted: 06 Jan 2011 at 6:29am |
any hope? 
|
|
IP Logged |
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3701
|
Posted: 06 Jan 2011 at 7:34am |
I have some thoughts rumbling in the back of my brain on this - I think it can be done, but it's a little complex and I'm still working out the details.
-Dell
|
|
|
|
IP Logged |
|
|
|
