If Formula
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=11977
Printed Date: 03 May 2024 at 11:56am
Topic: If Formula
Posted By: REDBA
Subject: If Formula
Date 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
|
Replies:
Posted By: hilfy
Date 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 ------------- Proviti, Data & Analytics Practice http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics |
Posted By: REDBA
Date 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 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 |
Posted By: hilfy
Date 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 ------------- Proviti, Data & Analytics Practice http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics |
Posted By: REDBA
Date 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? Thanks for assistance  |
Posted By: hilfy
Date 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
------------- Proviti, Data & Analytics Practice http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics |
Posted By: REDBA
Date 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 |
Posted By: hilfy
Date 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 ------------- Proviti, Data & Analytics Practice http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics |
Posted By: REDBA
Date Posted: 06 Jan 2011 at 6:29am
any hope?
|
Posted By: hilfy
Date 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 ------------- Proviti, Data & Analytics Practice http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics |
Posted By: asam
Date Posted: 08 Jan 2011 at 8:00am
|
If I understand the issue correctly - -
data from an application are being exported to an Access mdb and tables from the mdb serve as the data source for the report. Each row of data includes a column that may contain more than one name and the desire is to display a separate row in the report for each name in that column.
Proposal:
Create a formula (WhileReading) that begins by using Crystal's Split function to break the name column into an array. Then give the formula the value of the first element in the array.
now create a second formula; create the array; test the array length; if the length is > 1 then give this formula the value of the second element in the array.
You would need as many formulas as you could possibly have names in that column.
Now you can suppress all your detail lines and use each of your formula as a grouping condition and suppress the group when the formula returns a null (or some token value) indicating there is no name for that formula.
Make sense?
|