Separate Reports for Separate Data.

[dr.fahad]

Hello

I have a small application using vb6 platform & using sql2000 database. In database, I have 3 tables, Login, Bank & Transaction. I have; lets say, 5 bank accounts in that database. Now, what I need to do is to create a report which should show the transactions separately for all banks. I have created a report, that, shows, the transactions of all banks, altogether, separating them, via groups, but, I am unable to create the report, for separate banks.

Is this achievable? If Yes!, How?

Can anybody help me out.

I will create Separate Command buttons, on the form, and call the same report from them. If there is any other way of achieving the same, please do share it with me.

Guidance is requested.

[wes4dbt]

What did you use to create the report, Crystal, Datareport, ... In either case, you probably should look at using a parameter to select only the bank you want to print. I usually use a Textbox or combobox to have the operator enter the parameter value, Using a separate command button for each bank doesn't seem like a good idea. What if you add/delete a bank.

[dr.fahad]

I havent used any filters at vb level, frankly speaking I cant figure out how to. I want to use the filters at report level. I want to know, is it possible.?

As for your question of "If the bank is added/deleted". This is a good point. I didnt think of that.

Any idea, on how to set filters at report level?

[dr.fahad]

I have used Crystal Report 8.5 as the reporting tool. Is it possible to use the combo box for the operator to select the bank code, and with that, the application would be able to show the transactions of that particular bank in the report. How many reports will I have to create for that purpose? And if it is possible, how can that be achieved? Any Ideas?

[jggtz]

You could use the "Record Selection Formula" at running time an pass to the report before call it

I can show you an example, but first you should post your code where you are calling the report ( to check what kind of CR engine you are using)

[dr.fahad]

I've created a couple of reports, each been called by two command buttons. Each of the 2 command buttons has the following code, which is calling the report.

Private Sub cmdPrint_Click()
Report1.Reset
' Assign report name
Report1.ReportFileName = App.Path & "\Report\abc.rpt"
' command to display printer setup icon
Report1.WindowShowPrintSetupBtn = True
' Set window state
Report1.WindowState = crptMaximized
' command to display the report
Report1.Action = 1
End Sub

Report1 is the crystal report control, that is being used in the code.

One report shows the total transactions of all the banks, present in the database, while the other one shows the balances of the all the banks.

[jggtz]

Ok, you're using CR OCX
The way to filter one report, in your case to print records from 1 bank only, you can set the "SelectionFormula" property before call the rpt

Private Sub cmdPrint_Click()

    Report1.ReportFileName = App.Path & "\Report\abc.rpt"
    Report1.WindowShowPrintSetupBtn = True
    Report1.WindowState = crptMaximized
    Report1.SelectionFormula = "{tablex.fieldx}='" & txtBank.Text & "'"
    Report1.Action = 1

End Sub

(just change the table & field names for the real ones)

[dr.fahad]

THANKS JGGTZ

YOU'RE A COOL.

One more question? How can I set the filter for certain date.

I mean, along side the Text Box, I also want to show the transaction for a certain number of days, lets say from 01/12/2012 till 15/12/2012, how can I do that? The date is optional, I mean, if the date is entered the report shows the transaction for that period, and if it is remained untouched, then the report shows the complete transaction.

[jggtz]

Following the same example... DateF & DateT are the dates range
In this example the date range is not optional

Sub Somewhere
    Dim DateF As Date
    Dim DateT As Date
    '
    'here set date values from some controls
    '
End Sub


Private Sub cmdPrint_Click()

    Dim MySF  As String

    MySF = "{tablex.fieldx}='" & txtBank.Text & "'"
    MySF = MySF & " And {tablex.datex} >= Date(" & Format(DateF,"yyyy,mm,dd") & ")"
    MySF = MySF & " And {tablex.datex} <= Date(" & Format(DateT,"yyyy,mm,dd") & ")"
    Report1.ReportFileName = App.Path & "\Report\abc.rpt"
    Report1.WindowShowPrintSetupBtn = True
    Report1.WindowState = crptMaximized
    Report1.SelectionFormula = MySF
    Report1.Action = 1

End Sub

(just change table & field for the real names)

[dr.fahad]

Thanks Jggtz for all your help. I really appreciate that.

Thanks..