asked on

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

DotNetLover_Baan

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

mppeters

ASKER

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

DotNetLover_Baan

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

mppeters

ASKER

Thanks DotNetLover.

I understand the concept, but what is the code?

RacinRan

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()

DotNetLover_Baan

"Code" means.. your program code... I use this term a lot here. You see, just like.. Code behind. :))

mppeters

ASKER

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

RacinRan

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

mppeters

ASKER

Ok thanks RacinRan. So how would I represent the above query in the xsd file?

RacinRan

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.

RacinRan

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>"

mppeters

ASKER

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

ASKER CERTIFIED SOLUTION

RacinRan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ahmadfuwad

'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