Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 819
  • Last Modified:

Previous Value in Formula

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
crystal_Tech
Asked:
crystal_Tech
  • 20
  • 12
  • 2
1 Solution
 
peter57rCommented:
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
 
crystal_TechAuthor Commented:
So you mean to say
in vb must i get value till first date means dtpicker1.value - 1
Right ?

0
 
peter57rCommented:
That seems to be one of the options you could use.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
crystal_TechAuthor Commented:
Getting Error
Run Time Error 20510
Invalid Formula Name


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

0
 
mlmccCommented:
Have you tried

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

or

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

mlmcc

0
 
crystal_TechAuthor Commented:
Both tried and same following error
Invalid Qualifier
Highlighted on
.Formulas


But

This one is working
    CrystalReport1.Formulas(1) = "TTLBAmt = " & TTLOutStandingAmt & ""
0
 
mlmccCommented:
How is that different from what you started with?

mlmcc
0
 
crystal_TechAuthor Commented:
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
 
crystal_TechAuthor Commented:
Point increased :-)
0
 
mlmccCommented:
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
 
crystal_TechAuthor Commented:
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
 
mlmccCommented:
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
 
crystal_TechAuthor Commented:
Sir I will go for method two

2.  Generate all the summary data in the report itself.
But how
0
 
crystal_TechAuthor Commented:

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
 
mlmccCommented:
DateTime requires both a date and a time field

Where are you using this?

mlmcc
0
 
crystal_TechAuthor Commented:

on this field there is no time storing in database,  just date.
TPT_Ledger.M_Date}
0
 
mlmccCommented:
Then just drop the datetime

mlmcc
0
 
crystal_TechAuthor Commented:

Please show me how.
0
 
crystal_TechAuthor Commented:

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

0
 
mlmccCommented:
{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
 
crystal_TechAuthor Commented:
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
 
mlmccCommented:
I don't understand.

mlmcc
0
 
crystal_TechAuthor Commented:

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
 
mlmccCommented:
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
 
crystal_TechAuthor Commented:

I think you dont understand what i want

Please check my earlier post with files 35219305
0
 
mlmccCommented:
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
 
crystal_TechAuthor Commented:
yes i need the opening balace
i need to calculate it based prior data in the table.
0
 
mlmccCommented:
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
 
crystal_TechAuthor Commented:
ok created a new report
added database table ( TPT_Ledger)
now i want data between  ({@FirstLedgerDate}) to ({@LastLedgerDate}
so what recordselection will be.?
0
 
crystal_TechAuthor Commented:

and how to connect sub report with parameter
0
 
mlmccCommented:
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
 
crystal_TechAuthor Commented:

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
 
crystal_TechAuthor Commented:
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
 
crystal_TechAuthor Commented:
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

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 20
  • 12
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now