Dynamic crystal report from a datatable and export to PDF

All the samples I've seen use a pre-defined crystal report. How would I create a dynamic crystal report using a datatable (or dataset) as the datasource and export it to PDF, all from one button click?

So I have a query:

myQuery = "SELECT Users.UserKey, Rules.RuleDescr
FROM Users
INNER JOIN userRules ON Users.UserKey = userRules.UserKey
INNER JOIN Rules ON userRules.RuleKey = Rules.RuleKey"

And I fill a datatable with the results of that query.

How the heck do I create the Crystal Report to show the results grouped by UserKey and then export to PDF.

Here's the code I have so far:

Private Sub GenerateReportBTN_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles GenerateReportBTN.Click
        Dim CrystalReportDocument As ReportDocument
        Dim CrystalExportOptions As ExportOptions
        Dim CrystalDiskFileDestinationOptions As DiskFileDestinationOptions

        Dim Filename As String = "C:\Report.pdf"
        CrystalReportDocument = New ReportDocument
       
        'TODO: Set the datasource, and define the report to show userkey and ruledescr, grouped by userkey

        CrystalDiskFileDestinationOptions = New DiskFileDestinationOptions
        CrystalDiskFileDestinationOptions.DiskFileName = Filename
        CrystalExportOptions = CrystalReportDocument.ExportOptions
        With CrystalExportOptions
            .DestinationOptions = CrystalDiskFileDestinationOptions
            .ExportDestinationType = ExportDestinationType.DiskFile
            .ExportFormatType = ExportFormatType.PortableDocFormat
        End With
        CrystalReportDocument.Export()
    End Sub
LVL 8
mppetersAsked:
Who is Participating?
 
RacinRanConnect With a Mentor Commented:
Try rptReportDocument.SetDataSource(dsUserRules.tables(0))
0
 
DotNetLover_BaanCommented:
Hi there,
with the version of Crystal Reports that comes with VS.Net, you can NOT create repport dynamically. Go for stand alone version 10.
-Baan
0
 
mppetersAuthor Commented:
Ok then. Forget the dynamic part of it.

How would I create the report from the results of a query like the following?

SELECT Users.UserKey, Rules.RuleDescr
FROM Users
INNER JOIN userRules ON Users.UserKey = userRules.UserKey
INNER JOIN Rules ON userRules.RuleKey = Rules.RuleKey
UNION
SELECT ur.UserKey, Rules.RuleDescr
FROM
  (SELECT Users.UserKey, Roles.RoleKey, Roles.RoleDesc
   FROM Roles
   INNER JOIN UserRole ON Roles.RoleKey = UserRole.RoleKey
   INNER JOIN Users ON UserRole.UserKey = Users.UserKey) ur
INNER JOIN rolerules ON ur.RoleKey = rolerules.RoleKey
INNER JOIN Rules ON rolerules.RuleKey = Rules.RuleKey
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
DotNetLover_BaanCommented:
First create the report "Strongly typed". From ur code, create and fill a dataset with the results and pass it to the report.
Get this free e-book on creating crystal reports:
http://www.crystalreportsbook.com/FreeEbook.asp
-Baan
0
 
mppetersAuthor Commented:
Thanks DotNetLover.

I understand the concept, but what is the code?
0
 
RacinRanCommented:
If you've created the report as a rpt file in your vb project then the code looks like this:

        Dim rpt As New myReport
        Dim frm As New ReportViewer

        'As a side note ... this allows you to pass a string into a text field on the report at runtime that is not a part of the recordset
        Dim oText As CrystalDecisions.CrystalReports.Engine.TextObject
        oText = rpt.Section2.ReportObjects.Item("txtTitle")

        oText.Text = "History Report For " & Name

        'This passes records to the report object
        rpt.SetDataSource(Table)

        'This sets the report to a viewer, so instead you want to put your export code here
        frm.crvViewer.ReportSource = rpt
        frm.crvViewer.DisplayGroupTree = False
        frm.Show()

0
 
DotNetLover_BaanCommented:
"Code" means.. your program code... I use this term a lot here. You see, just like.. Code behind. :))
0
 
mppetersAuthor Commented:
When I add the Crystal Report file to the solution, it wants me to add the tables and the fields directly, and define the links for those tables ahead of time. How can I represent the following query in the rpt file?

SELECT Users.UserKey, Rules.RuleDescr
FROM Users
INNER JOIN userRules ON Users.UserKey = userRules.UserKey
INNER JOIN Rules ON userRules.RuleKey = Rules.RuleKey
UNION
SELECT ur.UserKey, Rules.RuleDescr
FROM
  (SELECT Users.UserKey, Roles.RoleKey, Roles.RoleDesc
   FROM Roles
   INNER JOIN UserRole ON Roles.RoleKey = UserRole.RoleKey
   INNER JOIN Users ON UserRole.UserKey = Users.UserKey) ur
INNER JOIN rolerules ON ur.RoleKey = rolerules.RoleKey
INNER JOIN Rules ON rolerules.RuleKey = Rules.RuleKey
0
 
RacinRanCommented:
I use a xsd file to define the structure of the data first.  Then you can browse for that "strongly-typed" dataset and use it as the source in design mode.

... nice definition DotNetLover ... lol
0
 
mppetersAuthor Commented:
Ok thanks RacinRan. So how would I represent the above query in the xsd file?
0
 
RacinRanCommented:
The complexity of query has nothing to with setting up the data structure in the report.  The only thing it needs to know is what the data is that is being returned by the query.  So, in your case, you will set up an element for UserKey and an element for RuleDescr because those are the only 2 fields being returned.

Here is the xml code for the xsd (which is a dataset):

<?xml version="1.0" encoding="utf-8" ?>
<xs:schema id="Dataset1" targetNamespace="http://tempuri.org/Dataset1.xsd" elementFormDefault="qualified"
      attributeFormDefault="qualified" xmlns="http://tempuri.org/Dataset1.xsd" xmlns:mstns="http://tempuri.org/Dataset1.xsd"
      xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
      <xs:element name="Dataset1" msdata:IsDataSet="true">
            <xs:complexType>
                  <xs:choice maxOccurs="unbounded">
                        <xs:element name="myDatatable">
                              <xs:complexType>
                                    <xs:sequence>
                                          <xs:element name="UserKey" type="xs:string" minOccurs="0" />
                                          <xs:element name="UserDescr" type="xs:string" minOccurs="0" />
                                    </xs:sequence>
                              </xs:complexType>
                        </xs:element>
                  </xs:choice>
            </xs:complexType>
      </xs:element>
</xs:schema>

The dataset has two modes in design view one is a drag and drop type interface and a xml view.  This is the xml view.
0
 
RacinRanCommented:
btw...my code assumes the data type is string.  If it's not then replace string with whatever in the <xs:Element name = "UserKey" type = "xs:<insert here>"

0
 
mppetersAuthor Commented:
I've tried several different ways and can't seem to get past the following error:

CrystalDecisions.CrystalReports.Engine.DataSourceException: Query Engine Error: 'C:\DOCUME~1\MARK\ASPNET\LOCALS~1\Temp\temp_c863172c-5377-4256-82ed-1543fca7634f.rpt'

XSD schema is:

<?xml version="1.0" encoding="utf-8" ?>
<xs:schema id="Dataset1" targetNamespace="http://tempuri.org/Dataset1.xsd" elementFormDefault="qualified"
      attributeFormDefault="qualified" xmlns="http://tempuri.org/Dataset1.xsd" xmlns:mstns="http://tempuri.org/Dataset1.xsd"
      xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
      <xs:element name="Dataset1" msdata:IsDataSet="true">
            <xs:complexType>
                  <xs:choice maxOccurs="unbounded">
                        <xs:element name="UserRules">
                              <xs:complexType>
                                    <xs:sequence>
                                          <xs:element name="UserID" type="xs:string" />
                                          <xs:element name="RuleDescr" type="xs:string" minOccurs="0" />
                                    </xs:sequence>
                              </xs:complexType>
                        </xs:element>
                  </xs:choice>
            </xs:complexType>
      </xs:element>
</xs:schema>

Button click code is:

    Private Sub GenerateReportBTN_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles GenerateReportBTN.Click

        Dim rptReportDocument As CrystalReport1
        Dim rptExportOptions As ExportOptions
        Dim rptDiskFileDestinationOptions As DiskFileDestinationOptions
        Dim PdfFilename As String
        Dim dsUserRules As DataSet
        Dim dt As DataTable
        Dim sqlCmd As SqlCommand
        Dim sqlString As String

        PdfFilename = "C:\Report.pdf"

        sqlString = "SELECT Users.UserID, Rules.RuleDescr" _
            & " FROM Users" _
            & " INNER JOIN userRules ON Users.UserKey = userRules.UserKey" _
            & " INNER JOIN Rules ON userRules.RuleKey = Rules.RuleKey" _
            & " UNION" _
            & " SELECT ur.UserID, Rules.RuleDescr" _
            & " FROM " _
            & "   (SELECT Users.UserID, Roles.RoleKey, Roles.RoleDesc" _
            & "    FROM Roles" _
            & "    INNER JOIN UserRole ON Roles.RoleKey = UserRole.RoleKey" _
            & "    INNER JOIN Users ON UserRole.UserKey = Users.UserKey) ur" _
            & " INNER JOIN rolerules ON ur.RoleKey = rolerules.RoleKey" _
            & " INNER JOIN Rules ON rolerules.RuleKey = Rules.RuleKey"

        sqlCmd = New SqlCommand(sqlString)
        sqlCmd.CommandType = CommandType.Text
       
        Dim sda As SqlDataAdapter

        sda = New SqlDataAdapter(sqlCmd)
        sqlCmd.CommandTimeout = 90
        sqlCmd.Connection = GetConnection()
        sqlCmd.Connection.Open()
        dsUserRules = New DataSet
        sda.Fill(dsUserRules)
        sqlCmd.Connection.Close()

        rptReportDocument = New CrystalReport1
        rptReportDocument.SetDataSource(dsUserRules)

        rptDiskFileDestinationOptions = New DiskFileDestinationOptions
        rptDiskFileDestinationOptions.DiskFileName = PdfFilename
        rptExportOptions = rptReportDocument.ExportOptions
        With rptExportOptions
            .DestinationOptions = rptDiskFileDestinationOptions
            .ExportDestinationType = ExportDestinationType.DiskFile
            .ExportFormatType = ExportFormatType.PortableDocFormat
        End With
        rptReportDocument.Export()
    End Sub
0
 
ahmadfuwadCommented:
'first add crystal reports and then put CrystalReportViewer into the form

Imports System.Data.SqlClient

'Declaration
 Public cn As New SqlConnection
    Public cmd1 As New SqlCommand
    Public oda1 As New SqlDataAdapter
    Public ods As New DataSet

'write the following code in button
        cn.ConnectionString = "Server=eteam08;Database=dbTestGrid;user id=sa;password=imran"
        cn.Open()
        'MsgBox("Connection Established")

        cmd1.Connection = cn
        cmd1.CommandType = CommandType.Text
        cmd1.CommandText = "select * from tblCustomer"  'write querry
        oda1.SelectCommand = cmd1
        oda1.Fill(ods, "Customer")  'you can write any name instead of Customer

        Dim rpt As New CrystalReport1
        rpt.SetDataSource(ods.Tables("Customer"))
        CrystalReportViewer1.ReportSource = rpt
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.