Crystal XI forumla syntax

I am trying to write a formula for a report that sums up payments received, both positive and negative amounts.

The issue I am running into is that when I drop the appropriate field as a Summary into the report, it displays a blank field in the details section for each entry that would've appeared if I put that transaction amount field into the report.

Further, I cannot just hide the details and do things in a group section (I tried).

I was wondering if someone could help me with this, I am at my wits end. I can display more information about the fields themselves if necessary

On a similiar note, is there anyway to do a Where clause/command in Crystal formula?

ie sum({x}) where transactioncode = "x"On a similiar note, is there anyway to do a Where clause/command in Crystal formula?

ie sum({x}) where transactioncode = "x"

1. You can create a running total.
Select and add your 'Field to summarize', set the 'type of summaries'.
Evaluate it on the 'Use a formula', click x+2 buton and enter your formula:

transactioncode = "x"

Reset summaries as you need.
-------
or
-------
2. create a formula, kind of this one:

if transactioncode = "x" then {x} else 0

then summarize it

About the issue you have, can you post your formula and some example of your desired result, please?

Crystal syntax date query

Does anyone know what the formula is to search for records between 01/04/04 and the end of the last full month?
Thanks.Does anyone know what the formula is to search for records between 01/04/04 and the end of the last full month?

i assume that date1 is ur field at the table1 that need to be search for the formula..at ur report->edit selection formula->record..at this

{table1.date} in date(year,month,day) to date(year,month,day)

regards

Crystal Reports Question for BABU/any GURU

Can some one please help me with the below.

I need to write a formula in crystal reports to give the birth dates for this week. For Example: If I am running the report on a Tuesday I need to see the results from Tuesday to Saturday. I know that we can acheive this in 7 conditional steps, but I am looking for a precise formula. Can some one please help me with this.

Thanks
BTry this logic

Date=Date+5

CR.selectionFormula="{DateField=Date}"

Crystal Reports help!

I am connecting to an AS400 via ODBC using Crystal Reports 9.0. I have a date that is coming out in a 7 digit number format. I need a formula for Crystal Reports that will convert this to a date format.

For example 1051111 would return 11/11/05.

The formula will have to get rid of the first "1" and put the other numbers in the correct format.

If someone can provide me with this formula and where I need to put it in Crystal Reports V9.0 that would be great.

ThanksSomething like this should do it. Just place it in your detail section.

StringVar ConvertDate := Right (ToText({your_number_field),6 );
StringVar YY := ConvertDate [1 to 2];
StringVar MM := ConvertDate [3 to 4];
StringVar DD := ConvertDate [4 to 5];

DD & "/" & MM & "/" & YY;

Brian
http://www.briankuipers.com|||Thanks alot, that helped a little. Except I told you wrong. The original format actually comes down as 1,051,111.00 with the commas and the decimal point.

When I use your formula it is counting the commas as text. I need a formula that would pull out the commas and decimal points and just use the text string of 1051111 that I originally stated was being used.

Thanks

Crystal Reports - Formula

Hi everyone
I really need to get this done, please try to help me.
I'll appreciate any help i get.

I have a View in my SQL Server DB which has following data.
--------------------------
cardtype | totaltype | amount
--------------------------
type1 | R16-Balance | 12000.50
type1 | R32-Balance | 15000.70
type2 | R16-Balance | 18000.50
type2 | R32-Balance | 19450.60
type3 | R16-Balance | 12500.50
type3 | R32-Balance | 18550.80
[hr]

I am trying to create a report in which i have grouped this data by Cardtype, Now the question is I want the difference between the amounts for the same card type. for Example :-
I want my report to look like following:
_____________________________________________________________
type1 R32-Balance 15000.70
type1 R16-Balance 12000.50
--- ------ ----
Outbalance:- 3000.20

type2 R32-Balance 19450.60
type2 R16-Balance 18000.50
--- ----- ----
Outbalance:- 1450.10

type3 R32-Balance 18550.80
type3 R16-Balance 12500.50
---------- ----
Outbalance:- 6050.30
____________________________________________________________

Thanks. Please Look into itHi ..
Group by card type.
Place cardtype | totaltype | amount in detail section.

Create a formula @.fDiff and the code for the formula is
Previous(View.amount)-View.amount

and place the formula in group footer

Try this & revert back.

Regards,
Rohini :)|||I'll try it out...and let you know...

so kind of you to reply...i really needed this...|||hello

this is my first time to post and i'm in need of your help in crystal... i tried to follow the instructions rohini gave but it gives me an error: " The ) is missing."

I tried to put ) marks on all possible places but still no luck...

I'm trying to get the total quantity per product line and I have already inserted a group for product line and placed the necessary fields in the details...

Please please reply soon! I'm anxious to see my first report! :)

Thanks in advance!|||hi siomai
u do not type the field name..in the formula editor..instead u double click the field..or try the following

Previous({View.amount})-{View.amount}

Try this one..All the best

regards,
Rohini|||Hello RohiniR!

I feel so stupid...so view is the table name! :blush: Now I get it... thanks! Stay cool! :wave:|||hi again!

The code is now error free but it gives the wrong result...

I did it as a sum...was it right that I just replaced the "-" sign to "+" sign? will this work with multiple line items,say, 5 line items or more?:confused:

Please answer back... :o

Thanks and best regards!|||Hi!

I noticed that it only adds the first two records. What I need is to sum many records. Is there a way without using insert summary? or can you suggest an approach to come up with a formula to get the sum of a field with grouping? Please assist me on this!|||siomai..
i d'nt know why u do not want to insert summary.
When we use formula ,u can give..
sum({View.Amount})

view is the table name..and amount is the field.. . :rolleyes:

but this formula returns the whole sum and not the sum for a group..
for the sum of a group u can u running total...
select
field to summarize - Amount ,
type of summary - sum,
evaluate - for each record,
reset - on change of group - (and select the group name) :lol:

Try this and get back yaar..hope this 'll work..
tell me why u do not want to insert summary.

Regards,
Rohini :thumb:|||Hi!
The reason why I don't want to insert summary is because I need it for my other formula..I have to come up with the total quantity per product line then use the sum as the divisor to the sum of net amount with this i'll be able to come up with the Average price sold.

Please give me your suggestions if i can't get the summation of a field per group on how to come up with the formula for total net amount/total quantity per product line. I'll be forever thankful! :o

a million THANKS!!!|||Hi!

I have tried your suggestion but I also need is to come up with this formula:

sum of amount/sum of qty

so if i use the insert running total, i still cannot use the summary field in my formula. :( that's the reason why i don't want to use the insert summary/running total method.

Please help me and tell me a better approach to solve my problem...|||Hello, I was given the task of creating Crystal Reports by my boss and I have no formal training on this application. Any assistance would be appreciated. I have the below code, but am getting the following error message "The ) is missing".

Code:
//count the number of days between the greatest received date (when the ticket was logged) and the greatest last modified date (the last date the ticket was updated).

count (maximum (date {CallLog.RecvdDate})) - maximum (date {CallLog.ModDate}))|||Are the {CallLog.RecvdDate} and {CallLog.ModDate} fields in date_time format?

Actually, to find "the number of days between the greatest received date (when the ticket was logged) and the greatest last modified date (the last date the ticket was updated)" you have to create a formula:

maximum (date ({CallLog.RecvdDate})) - maximum (date({CallLog.ModDate}))

But if both of the fields are in date format, your formula will be more simple:

maximum ({CallLog.RecvdDate}) - maximum ({CallLog.ModDate})|||Thanks you for the reply. I get the following error: "A number, currency, amount, or date is required here."

{CallLog.RecvdDate} and {CallLog.ModDate} are both String types. I have tried to convert them into date types without luck.|||Are those fields strings? Can you give me an example of the fields' value, please?|||Both {CallLog.RecvdDate} and {CallLog.ModDate} are strings.
An example of {CallLog.RecvdDate} is "2007-05-19" and an example of {CallLog.ModDate} is "2007-05-19".

I tried to convert the strings to date by using the below code, but it didn't work: maximum (date{CallLog.RecvdDate}) - maximum (date{CallLog.ModDate})|||Create 2 formulas which will convert the field values into date format (actually, you can copy them):

Formula1 - @.RecvdDate:

Date(tonumber(left(trim({CallLog.RecvdDate}),4)),tonumber(mid(trim({CallLog.RecvdDate}),6,2)),tonumber(right(trim({CallLog.RecvdDate}),2)))

Formula2 - @.ModDate:

Date(tonumber(left(trim({CallLog.ModDate}),4)),tonumber(mid(trim({CallLog.ModDate}),6,2)),tonumber(right(trim({CallLog.ModDate}),2)))

You can use these formulas in your report instead of those fields.

To find the difference, create another formula, call it @.difference (or whatever you like):

maximum ({@.RecvdDate}) - maximum ({@.ModDate})

Don't forget to read the help file on the used functions.

Crystal Report Sorting Problem

Hi,

I have set a formula field '@.sorting' , where {?yy} is a parameter.

Following is the formula:-

if {?yy}="1" then
{Table1.RSRCE_NME}
else if {?yy}="2" then
{Table2.PROJ_NME}

Now i have set this formula field '@.sorting' as a SortField. I am able to dynamically change the sort parameter. But i am unable to set the Order [Asc/Desc] of the sort field.

What should be done?. Please help. :)

Thanx in advance
Rajesh RajendranHi,
Goto Report > Record Sort. In the menu you can see the Formula field and table fields. Select @.sorting. Now the report will be sorted by @.sorting.

Madhivanan

Crystal Report In Vb.net 2005

HELLO FRIENDS

i want to know how to convert the numbers to letters

ex :

5462 = five thousands four hundered and sixty two.

is there any formula can covert numbers to letters.

because i have a Recipts report and i want the total to be in both numbers and letters .

thanks
husseinSee help on ToWords function

Crystal Report Group by Formula

Hi,

I am a novice and any help would be much appreciated.

The database I am using is as below:

A/C No. Name Payments

1 Brown 10.00
1 Brown 25.00
1 Brown 40.00
2 Jones 100.00
2 Jones 150.00
2 Jones 175.00
2 Jones 56.00
3 Smith 65.00

I have grouped the report by account number. I have then created a formula that categorises the customer based on their payments. I then wish to group customers by these categories but Crystal will not allow me to do so.

The best I can do at the moment is export the initial report to Excel and then create a new report in Crystal based on this.

Can anyone point me in the right direction?

Thanks

Andyyou can group by using formula
can u write the formula, so that i can check it.

Crystal Report Formula

I am trying to create a formula that performs significant figures.

The formula(s) I have come up with this far is as follow:

ROUND(val({FLATDATA.ACOM}),2-(1+INT(ABS(val({FLATDATA.ACOM})))))

However, when I try to view the report, I get the error message "The number of decimal places is too large or not an integer."

Has anyone ever run into this problem?what is the value of 2-(1+INT(ABS(val({FLATDATA.ACOM}))))?

Crystal Report formula

I am currently working on a payroll distribution report and I am have some trouble. I have pulled in the employees for the payroll along with the gross and net amounts of their paychecks. The only other peice that I need is the OT amount which comes from a different table. I have linked the two tables and when I try to pull in the OT amount with a (IF THEN, so I can try to only pull in the dollar amount for anyone who has an OT code) statement it gives me $0.00 listed for all of their pay codes. I need the OT dollar amount for just those that had OT and $0.00 for those that did not. Each person has several pay lines and I need everyone not just those with OT so filtering out the OT code will not work. Any suggestion would be deeply appreciated.

Example of what I am getting
Gross Net OT
398 John Doe 500.00 150.00 0.00 (This is a base pay)
398 John Doe 500.00 150.00 50.00 (I only need this line if an individual had OT)

What I need to see:
Gross Net OT
398 John Doe 500.00 150.00 50.00 (Had OT)
399 Sue Brown 600.00 250.00 0.00 (Did not have OT)If your OT table doesn't allow for nulls, you wouldn't need an IF statement. If so, you'd just check if it was null - if so put 0, if not put the value.
I.E.: if IsNull({Overtime.OT}) Then $0 Else ${Overtime.OT} //the $ sign is for converting to currency

Or am I not understanding your question?

Crystal Report 8.0, Summary function !

Friends,

I'm working with crystal report 8.0 and SQL server.

I need to create a formula in group section, which should return the summary value of next group.

Or is there any function for get summary value if the group value is 'x'. same as SumIF( , , ) in excel.You need to write a formula having the code

If groupvalue='x' then
{field}

and use the summary using this

Crystal Report ****

Alright Boys and Girls,

I am in ****. I have a report, and in this report I use multipe formula fields to count 4 different occurences. So for each record, and each month from 1 year ago to today, i count how many people were housed, how many people moved in, how many people moved out, and what my baseline of occupants is. I use another formula field to get each month from this month, back 12 months obviously, which is used to determine housed, ect. ect..
I have no problem getting my totals with my "formulated months". But when I go to graph it, im screwed. It wants one column for the x axis, say a month column with 12 values. Well I have 12 formulated fields giving me the 12 months. For each of those months i have 4 calculations letting me know housed, moved out, ect. So for each record I have 48 calculations happening. Can anyone give me any sort of idea on how to graph this or atleast compress it down so i can graph it.

Thanks,
RyanHi,

How you are calculating the counts (housed, moved in).

I had a situation like this. For each and every month, I had to calculate multiple counts. I'd created a group for date field and for every month. Created running totals for each group.

In this way, we can easily add fields in our chart.

Just generate the chart on change of Date and add the running total fields in the next list. This is for Bar chart.

Hope this will work!|||Well here is my pickle. I don't have a single column for the date. For instance, say i have a record, for that single record, i get todays date, and i calculate 12 formulated fields for each month from todays date back 12 months. so, if you look at the record, you see 12 formulated months, beneath that record, you are going to calculate whether or not someone moved in, moved out, was housed, and your base line. So underneath these 12 months, i have 4 parameters, that are working off the parameter month fields, which in turn are calculated from todays date. Thats the tuff part, I don't have a single date column to work from, I wish to god I did. Im stuck, if anyone knows of any great crystal resource sites I would be happy to hear it!

Crystal Formula - SQL

I started with this selection criteria:

({@.Meditech Date to PC Date} in {?Beginning Date} to {?Ending Date})

Then I did this:

{@.Meditech Date to PC Date} in {?Beginning Date} to {?Ending Date} and
({LabSpecLPatientIndex.Prefixes} in ["IM", "R"] and {LabLSpecResultTests.Result} <> "")

I got data just fine on that.

Then I further altered it with this:

({@.Meditech Date to PC Date} in {?Beginning Date} to {?Ending Date}) and
(({LabSpecLPatientIndex.Prefixes} ="IM" and {LabLSpecResultTests.Test} = "902.9550"
and {LabLSpecResultTests.Result} <> "") or ({LabSpecLPatientIndex.Prefixes} ="R" and
{LabLSpecResultTests.Result} <> ""))

And I get no data. What did I miss? I want all records with between those dates, that have prefixes = IM or R that have results (not null results). If the prefix = IM, then I only want it if Test = "902.9550" ...but I want all records with a Prefix R.

TIA.
Briana{@.Meditech Date to PC Date} in {?Beginning Date} to {?Ending Date} and
({LabSpecLPatientIndex.Prefixes} in ["IM", "R"] and {LabLSpecResultTests.Result} <> "") and {LabLSpecResultTests.Test} = "902.9550"

Crosstab - row records effecting formula

I'm using CR 10, and doing a report that uses two sub-reports. I have the parameter set and they are being passed to both sub-reports, both are cross tabs. One important parameter to mention is the date parameter, which is a range that selects the hours, and the results of calls that occurred for that day. So there are two types of data, the hours, and the call results.

The first sub-report handles the hours and other summaries. There is a formula that takes the datetimeout datetimein and outputs the total hours. The only way that it takes and does this by a particular day is if the formula is in the cross-tab report.

The problem is when the formula is in the cross-tab report it sums it up by how many records it finds for that date. What I did to fix this problem is to divide by the count of rows.

It is fine when you do just one date, however when you do more than one date you end up with it dividing by the total amount of records it finds for all dates, not by the amount for that particular day.

The way that it counts the records is not by date specifically, its only by the field of a database itself.

Is there a way to make it dynamic to maybe pull an array of the counts per record for that day? If so then I would be able to divide it by the day, and the output is correct. The main problem is that I need it to dynamically pick out each date, since the amount of dates, and the actual dates will vary.

Also, is there a way that I can put it in the cross-tab report and actually give me the correct data without this whole divide issue?

The formula I'm using is basically 'count({WEEKLY.datetimein});'

The output should look like this

10/16 | 10/17 | Total
Hours 80.11 | 75.11 | 155.22
Total Calls 729 | 616 | 1345

But it actually outputs this currently

10/16 | 10/17 | Total
Hours 79.60 | 75.62 |155.22
Total Calls 689.74 | 655.26| 1345

Any help whatsoever is much appreciated, thank you. Let me know if you have any other questions. I really need some help on this, I've been struggling trying to find a solution.i can't help but notice over 26 people looked at my problem, and didn't post, I'm hoping that I can get some help with this issue. If there is anymore information that I can provide, or absolutely any information anyone has to share reguarding my issue please let me know.

Cross tab formula

Hi guys,

I am a Crystal beginner *trying* to make a cross tab formula work.

The db has 2 fields which hold the similar data.
I want to do something like:

If {field1} like "A*" or if {field2} like "A*"
then etc etc

But it is not letting me - it only recognises the first part of the if statement
So, any ideas why thats happening?
Cant you compare or pull from two fields in the same formula?
....and if you can't do it with an if/else then any ideas as to how I would do this?You don't want / need the 2nd "if"

If {field1} like "A*" or {field2} like "A*"
then etc etc