Search code examples
vb.netcrystal-reportspervasive

Using Crystal Reports in VB.net windows app

I've done much googling and tested various code no avail, so before anyone cries "DUPLICATE" please read the question through.

The Scenario: I have a windows form written in VB.net using visual studio 2013. I have installed the Crystal Reports API, and I've opened an existing report which was provided with Sage Business Vision and removed all the fields (basically stripped it down to just the layout). I need to pull data from 3 different tables in my pervasive database to use in this report.

What I've accomplished: So far, I've created a form and named it 'RPTviewer' and I've dropped the Crystal Report Viewer on it (and left the default name 'CrystalReportViewer1'

The Struggle: I'm having difficulty getting the report to display much of anything. I've tried using the SQL expression fields, but they won't let me use the WHERE clause. It seems I can only specify PART of a SQL string between the 'SELECT' and 'FROM' clauses.enter image description here

My work around for this was to just do all the querying in the windows form and inject the information into predefined fields. I've tried this using an 'Unbound Field':

Private Sub RPTviewer_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    Dim rpt As New OfficeCopyPS

    Dim crOrderNumber As CrystalDecisions.CrystalReports.Engine.TextObject
    crOrderNumber = rpt.ReportDefinition.ReportObjects("OrderNumber")
    crOrderNumber.Text = "1234567"


    CrystalReportViewer1.ReportSource = rpt
    CrystalReportViewer1.Refresh()
    rpt.Load()



End Sub

however, all that gets me at run time is a blank viewer. I've also tried using a Parameter Field, but it just prompts me for a value at run time and ignores what I specified in the form_load event (it does load the report though).

        rpt.SetParameterValue("OrderNumber", "1234567")
    CrystalReportViewer1.ReportSource = rpt
    CrystalReportViewer1.Refresh()
    rpt.Load()

enter image description here

There has to be a way to do this. Is there any documentation? Can anyone suggest a better way to get the information I need into the report? Thanks.

Solution

  • Ok, I got it. I'll summarise my findings here: Parameters are just that: Parameters. They can be used to pass information to functions and query data. Under database expert, you can create a command with a WHERE clause using one or more parameters defined at the time the command is created.

    My big problem was trying to pass the parameters before the report was loaded. below is the correct order:

        CrystalReportViewer1.ReportSource = rpt
rpt.Load()
rpt.SetParameterValue("OrderNumber", "1234567")   'use this to pass parameters
rpt.DataDefinition.FormulaFields("FORMULAFEILDNAME").Text = "'" & This appears in formula & "'"   'This inserts into a formula feild.
    CrystalReportViewer1.Refresh()

    Above is a sample of passing to both parameter and a formula feild. Formula feilds, unlike parameters seem to need to be placed on the report to receive a value. Like the name suggests, formula fields accept formulae as well as functions, loops, decision structure and variables. Note in the code above, I had to encapsulate the string in single quotes so the formula would display it.