Previous Value in Formula

If you only select records between the two dates then in Crystal Reports 7 there is no way to get any values for records outside of those dates.

It seems to me that your options are..
  - calculate the previous total in your vb program and pass it as a parameter to the report
or
- use a subreport at the start of your main report which just calculates the previous total and pass this into the main report using a global variable
or
-do not use the 'from date'  in your record selection at all; instead, select all records up to your 'to date' and use formula fields in the report to sum the record values into different variables based on their date

(or   -  but I can't recall whether this is possible in CR7 or not...
- -do not use the 'from date'  in your record selection at all; instead, select all records up to your 'to date' and use
a 'specific group' to put all records before the 'from date' into their own group. You can then use normal group summaries to get the total.

I can't tell you how to do any of this in VB.

So you mean to say
in vb must i get value till first date means dtpicker1.value - 1
Right ?

That seems to be one of the options you could use.

Getting Error
Run Time Error 20510
Invalid Formula Name


CrystalReport1.Formulas(1) = "TTLBAmt = " & TTLOutStandingAmt & ""

Have you tried

CrystalReport1.Formulas(1).Text = " 'TTLBAmt = " & TTLOutStandingAmt & " ' "

or

CrystalReport1.Formulas(1).Text = "TTLBAmt = " & TTLOutStandingAmt & ""

mlmcc

Both tried and same following error
Invalid Qualifier
Highlighted on
.Formulas


But

This one is working
    CrystalReport1.Formulas(1) = "TTLBAmt = " & TTLOutStandingAmt & ""

How is that different from what you started with?

mlmcc

Oh yeh there is no difference, so why it wasn't working before, anyway leave it please
Let me come to the main problem (Previous Value in Formula)

Please check code
1 > Getting Last Date for Search
2 > Getting First Date for Search,
3 > Getting Value from First Date to Last date
4 > Getting Opening Value from Table
5 > Final Opening Balance is in a Variable (TTLOutStandingAmt)
6 > Variable is in a Formuls(0)   (at last what i want)
But actual Problem starts here...!
In Crystal Report a formula (TTLBAmt ) i placed it on Group Header
Result is same value for all Grouped Name.. Like you can see in attached files,
 

'1'''Getting Last Date for search
    DTPicker3.Value = DTPickerFrom.Value - 1

'2''''Geting first Date from TPT_Ledger Database Query
    If RsMinDate.State = 1 Then RsMinDate.Close
    RsMinDate.Open "Select Min(TPT_Ledger.M_Date) as FirstDate from TPT_Ledger", con, adOpenKeyset, adLockOptimistic
    If RsMinDate.RecordCount > 0 Then
        FirstDate = RsMinDate.Fields(0)
    End If

'3''''Getting Total from "FirstDate" to Last Date
    If Rsldgr.State = 1 Then Rsldgr.Close
    Rsldgr.Open "Select Sum(TPT_Ledger.Vasuli) as TTLVasuli, Sum(TPT_Ledger.Crossing) as TTLCrossing, Sum(TPT_Ledger.Balance) as TTLBalance, Sum(TPT_Ledger.Amt_Rcvd) as TTLAmtRcvd from TPT_Ledger, TranspOpening where TPT_Ledger.M_Date between CDATE('" & FirstDate & "') and CDATE('" & DTPicker3.Value & "')", con, adOpenKeyset, adLockOptimistic
    
    If Rsldgr.RecordCount > 0 Then
    
            If Rsldgr.Fields(0) & "" = "" Then
                TTLVasuli = 0
            Else
                TTLVasuli = Rsldgr.Fields(0)
            End If
            
            
            
            If Rsldgr.Fields(1) & "" = "" Then
                TTLCrossing = 0
            Else
                TTLCrossing = Rsldgr.Fields(1)
            End If
            
            
            
            If Rsldgr.Fields(2) & "" = "" Then
                TTLBalance = 0
            Else
                TTLBalance = Rsldgr.Fields(2)
            End If
                        
                        
                        
            If Rsldgr.Fields(3) & "" = "" Then
                TTLAmtRcvd = 0
            Else
                TTLAmtRcvd = Rsldgr.Fields(3)
            End If


    
'4''Getting Openning Balance From Table
    If RsOpenBal.State = 1 Then RsOpenBal.Close
    RsOpenBal.Open "Select TranspOpening.Open_Bal as OpnBal, TPT_Ledger.G_Name From TranspOpening, TPT_Ledger where TPT_Ledger.G_Name = TranspOpening.Trnsp_Name", con, adOpenKeyset, adLockOptimistic

    If RsOpenBal.RecordCount > 0 Then
            If RsOpenBal.Fields(0) & "" = "" Then
                OpnBal = 0
            Else
                OpnBal = RsOpenBal.Fields(0)
            End If
    End If

''5''''Final Opening Balance
       TTLOutStandingAmt = Val(TTLVasuli) - Val(TTLCrossing) - Val(TTLAmtRcvd) + Val(OpnBal)

''6'''Value From TTLOutStandingAmt
    CrystalReport1.Formulas(0) = "TTLBAmt = '" & TTLOutStandingAmt & "'"

Select allOpen in new window

Same-balance.bmp
Report-Design.bmp

Point increased :-)

Where is the code?
I assume it is in the application?

When the code runs the report you are only passing 1 value into the report not 1 for each record.

mlmcc

yeh but Crystal report is grouped by G_Name thats why i just created one value
another thing is
 i dont know how to get a value for each group. (what query must i run)

There are 2 ways to do this

1.  Use your existing method and pull data for 1 group at a time then run the report

2.  Generate all the summary data in the report itself or in a subreport.

mlmcc

Sir I will go for method two

2.  Generate all the summary data in the report itself.
But how

I am not sure i am doing this on right way...
Created couples of formulas in Crystal Report

{TPT_Ledger.Vasuli} - {TPT_Ledger.Crossing} - {TPT_Ledger.Amt_Rcvd} AND {TPT_Ledger.M_Date} >= DateTime(Date ({@FirstLedgerDate})) AND {TPT_Ledger.M_Date} <= DateTime(Date ({@LastLedgerDate}))

Is it right thing to get previous value..? if yes than why getting error " Not Enough Arguments have been given to this function" through me on DateTime(Date.

If No than What else left to try..(Please Reply me asap)
Thanks a lot

DateTime requires both a date and a time field

Where are you using this?

mlmcc

on this field there is no time storing in database,  just date.
TPT_Ledger.M_Date}

Then just drop the datetime

mlmcc

Please show me how.

I tried So many ways but no luck ... :-(

{TPT_Ledger.Vasuli} - {TPT_Ledger.Crossing} - {TPT_Ledger.Amt_Rcvd} AND {TPT_Ledger.M_Date} >= (Date ({@FirstLedgerDate})) AND {TPT_Ledger.M_Date} <= (Date ({@LastLedgerDate}))

What is this for?
{TPT_Ledger.Vasuli} - {TPT_Ledger.Crossing} - {TPT_Ledger.Amt_Rcvd}

You probably need to coompare it to something

mlmcc

Value i want from
Group by with {TPT_Ledger.G_Name} and from {@FirstLedgerDate}) to ({@LastLedgerDate}) = Sum of {TPT_Ledger.Vasuli} and sum of {TPT_Ledger.Crossing} and sum of {TPT_Ledger.Amt_Rcvd}
then from Sum {TPT_Ledger.Vasuli} minus {TPT_Ledger.Crossing} and Minus {TPT_Ledger.Amt_Rcvd} = What i want (Previous Value)

Hope you understand

I don't understand.

mlmcc

this is field name
TPT_Ledger.Vasuli  
TPT_Ledger.Crossing
TPT_Ledger.Amt_Rcvd

500
100
50

500-100-20 = 350

now i hope you understand

I don't follow on the previous value

You can create a formula like

{TPT_Ledger.Vasuli} - {TPT_Ledger.Crossing} - {TPT_Ledger.Amt_Rcvd}

You can then do a summary on that to get the total

You could also do sums on the individual values then do the math
Sum({TPT_Ledger.Vasuli}) - Sum({TPT_Ledger.Crossing}) - Sum({TPT_Ledger.Amt_Rcvd})

Formulas don't have previous values as such.

You can use  previous on the fields
previous ({TPT_Ledger.Vasuli}) - previous ({TPT_Ledger.Crossing}) - previous ({TPT_Ledger.Amt_Rcvd})

mlmcc

I think you dont understand what i want

Please check my earlier post with files 35219305

You need  the opening balance?

Is that in the database or do you need to calculate it based prior data in the table?

mlmcc

yes i need the opening balace
i need to calculate it based prior data in the table.

To calculate it in the report, you will probably need to use a subreport or include all the data required in the report and suppress records outside the range.

You could possibly calculate it in the application and create a recordset the report could use with all the data.

You could also create a table in the database that is linked with the totals.

mlmcc

ok created a new report
added database table ( TPT_Ledger)
now i want data between  ({@FirstLedgerDate}) to ({@LastLedgerDate}
so what recordselection will be.?

and how to connect sub report with parameter

What are those values?
ARe they SP parameters or Crystal formulas?

If they are Crystal formulas why are you using formulas?

IN the SELECT EXPERT you can use a formula like

{DateField} >= {@FirstLedgerDate} AND  {DateField} <= {@LastLedgerDate}

How are you going to get the opening balance?

mlmcc

i think we discuss lots on this problem
and still i m hanging ..?

question is how to connect sub report,
and how can i connect my sub report with main
all fields and parameters we discussed
and still we are discaussing. same thing

might we are not on the same way..

Desired result i wanted from sub report,
but
i didn't get it, so i tried another way and i solved my problem.