Solved

Previous Value in Formula

Posted on 2011-03-24
36
764 Views
Last Modified: 2012-05-11
In formula how can i get Previous value

like if Dtpickerfrom value is 01-12-2010 and DtpickerTo value is 31-12-2010,
then i want Grand total before 01-12-2010 in formula (xyzBalance)
CrystalReport1.ReplaceSelectionFormula "{TPT_Ledger.G_Name} = '" & Text1.text & "' and {TPT_Ledger.M_Date} >= date(" & Year(DTPickerFrom.Value) & "," & Month(DTPickerFrom.Value) & "," & Day(DTPickerFrom.Value) & ") And {TPT_Ledger.M_Date} <= date(" & Year(DTPickerTo.Value) & "," & Month(DTPickerTo.Value) & "," & Day(DTPickerTo.Value) & ")"

Open in new window

0
Comment
Question by:crystal_Tech
  • 20
  • 12
  • 2
36 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 35205193
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.

0
 
LVL 1

Author Comment

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

0
 
LVL 77

Expert Comment

by:peter57r
ID: 35205531
That seems to be one of the options you could use.
0
 
LVL 1

Author Comment

by:crystal_Tech
ID: 35206799
Getting Error
Run Time Error 20510
Invalid Formula Name


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

0
 
LVL 100

Expert Comment

by:mlmcc
ID: 35207002
Have you tried

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

or

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

mlmcc

0
 
LVL 1

Author Comment

by:crystal_Tech
ID: 35207378
Both tried and same following error
Invalid Qualifier
Highlighted on
.Formulas


But

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

Expert Comment

by:mlmcc
ID: 35207434
How is that different from what you started with?

mlmcc
0
 
LVL 1

Author Comment

by:crystal_Tech
ID: 35219305
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 & "'"

Open in new window

Same-balance.bmp
Report-Design.bmp
0
 
LVL 1

Author Comment

by:crystal_Tech
ID: 35219327
Point increased :-)
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 35219648
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
0
 
LVL 1

Author Comment

by:crystal_Tech
ID: 35219722
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)
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 35220147
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
0
 
LVL 1

Author Comment

by:crystal_Tech
ID: 35220961
Sir I will go for method two

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

Author Comment

by:crystal_Tech
ID: 35221899

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
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 35222150
DateTime requires both a date and a time field

Where are you using this?

mlmcc
0
 
LVL 1

Author Comment

by:crystal_Tech
ID: 35222166

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

Expert Comment

by:mlmcc
ID: 35222246
Then just drop the datetime

mlmcc
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 1

Author Comment

by:crystal_Tech
ID: 35222374

Please show me how.
0
 
LVL 1

Author Comment

by:crystal_Tech
ID: 35222648

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

0
 
LVL 100

Expert Comment

by:mlmcc
ID: 35222738
{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

0
 
LVL 1

Author Comment

by:crystal_Tech
ID: 35227687
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

0
 
LVL 100

Expert Comment

by:mlmcc
ID: 35227968
I don't understand.

mlmcc
0
 
LVL 1

Author Comment

by:crystal_Tech
ID: 35228066

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
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 35228115
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




0
 
LVL 1

Author Comment

by:crystal_Tech
ID: 35228133

I think you dont understand what i want

Please check my earlier post with files 35219305
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 35228174
You need  the opening balance?

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

mlmcc
0
 
LVL 1

Author Comment

by:crystal_Tech
ID: 35228197
yes i need the opening balace
i need to calculate it based prior data in the table.
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 35228609
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
0
 
LVL 1

Author Comment

by:crystal_Tech
ID: 35229423
ok created a new report
added database table ( TPT_Ledger)
now i want data between  ({@FirstLedgerDate}) to ({@LastLedgerDate}
so what recordselection will be.?
0
 
LVL 1

Author Comment

by:crystal_Tech
ID: 35231022

and how to connect sub report with parameter
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 35233316
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
0
 
LVL 1

Author Comment

by:crystal_Tech
ID: 35305351

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..
0
 
LVL 1

Accepted Solution

by:
crystal_Tech earned 0 total points
ID: 35355255
Ok
i solved it, without using sub report

how i did is
1 - Create a temp table in database.
2 - inserted a desired value one by one using while loop in vb form
3 - just drag and drop value from temp table to crystal report.

Thats i wanted
0
 
LVL 1

Author Closing Comment

by:crystal_Tech
ID: 35381925
Desired result i wanted from sub report,
but
i didn't get it, so i tried another way and i solved my problem.
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now