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.