CODEWITHSUNDEEP
vb.netcrystal-reportsreport
Mohamed Kiyas
Mohamed Kiyas

Reputation: 15

get previous total from filtered amount in crystal report

I have created the following report in Crystal Report using VB.Net 2012. The OPENING BALANCE & NET TOTAL rows are not in my report but my question is there.

TR_ID | TR_DATE   |DETAILS | EXPENSE | INCOME   |   BALANCE

OPENING BALANCE                                         0.00

1005   08/24/2015 CASH RTN       0.00  10,000.00   10,000.00

1013   08/25/2015 PURCHASE   3,500.00       0.00    6,500.00

1014   08/25/2015 DEPOSIT    5,000.00       0.00    1,500.00

1013   08/27/2015 SALES          0.00   4,000.00    5,500.00

NET TOTAL                                           5,500.00

I filtered the specific records within the date range of 08/25/2015 and 08/26/2015 & it works well. My question is how I get the previous balance (10000) of the specific date (08/25/2015) in "OPENING BALANCE" row and the total balance in "NET TOTAL" row?

As per the given datas above, the report shows the 4 records well excluding the rows of OPENING BALANCE & NET TOTAL. I want to add the OPENING BALANCE row to show the opening balance of every vendors' accounts when I open the report. And if I view the records of a particular customer within a date range, then OPENING BALANCE row should show the previous balance until the start date of the given date criteria. Ex: If I select records from 08/25/2015 to 08/26/2015 as per above datas, the report will show the two particular records (2nd & 3rd). But I want to show the previous balance 10,000 or the total amount which is above to the star date (08/25/2015) should be shown in the OPENING BALANCE row. And the sub total amount of this vendor's should be shown in the last NET TOTAL row. So, pls help me, how the formulas will come & how to do.

Thank you

I expect your great help Thank you

Upvotes: 0

Views: 1472

Answers (1)

David BS
David BS

Reputation: 1892

It´s not too much clear to me, but you may try to get the values directly from the report using a variable to do it.

With the report opened, you may entry in the CrystalReport option and select "Selection Expert - Saved Data".

You must create a formula using the "Formula Field" at left panel, and build it using the top 3 windows:

  • The first top window shows the fields of the table your report handles;
  • The middle top window show some built-in formulas

SO, you must

  1. Go to formula field and create a new one;
  2. Select the field at the top-left window you want to get (the Openning)
  3. Drag-drop it into the main window (below the 3 top selections) and save it as a new variable
  4. Add this variable into your report. This way, you may get this field for calculations in the same report.

Is it you need to do? Sorry if I cannot understand exactly what you need. Hugs!

UPDATE You may have TOTAL creating a formula of SUM within report.

  1. Click on the field EXPENSE (which appear in 3 rows) and select in menu: CRYSTAL REPORTS - INSERT - SUMMARY. It will create a field that must be positioned at the bottom (in the new Group will be created).
  2. Do the same with INCOME field.
  3. Create a FORMULA FIELD at the bottom to calculate the difference between both SUMARIES. You can name each of these summary fields (like IncSum and ExpSum) and put in that third field: IncSum-ExpSum. You may also put them as invisible.
  4. Your final balance should be:
    OldBalance (the parameter I point next lines) + Movement (the field that reflects IncSum-ExpSum)

To get the last/old Balance, you may have to get it from code as pass it as a PARAMETER. To do this, create a parameter in te report: see in the FIELD EXPLORER you have a choice of PARAMETER FIELD. AFter create it, use this code:

Dim MyCrystalReportt As New ReportDocument()

    MyCrystalReportt.Load(Application.StartupPath & "\ReportSample.rpt\")
    MyCrystalReportt.SetDatabaseLogon(UserID, Password, DataSource, InitialCatalog)

    Dim myLogonInfo As New TableLogOnInfo()
    Dim myTable As Table

    For Each myTable In MyCrystalReportt.Database.Tables
        myLogonInfo = myTable.LogOnInfo
        myLogonInfo.ConnectionInfo.ServerName = DataSource
        myLogonInfo.ConnectionInfo.DatabaseName = InitialCatalog
        myLogonInfo.ConnectionInfo.UserID = UserID
        myLogonInfo.ConnectionInfo.Password = Password

        myTable.ApplyLogOnInfo(myLogonInfo)
    Next

    MyCrystalReportt.SetParameterValue("Data1", LastBalance.Value.ToString)

    ReportsForm.CrystalReportViewer1.Reportsource = MyCrystalReport
    ReportsForm.show

Notes:

  • You may have a form (ReportsForm) with a Crystal Control within (CrystalReportViewer1).
  • Datasource is the Server Name or IP
  • InitialCatalog is the Database (not the table!)
  • The ParameterValue MUST be a string - is numeric is required, convert it to number inside the Report. Use a temp_field to get it (see formulas of Crystal and you will find some to make this task).

I hope it can help you. Good luck.

Upvotes: 1

Related Questions