Solved

Dynamic crystal report from a datatable and export to PDF

Posted on 2004-09-14
14
893 Views
Last Modified: 2008-02-07
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
0
Comment
Question by:mppeters
  • 5
  • 5
  • 3
  • +1
14 Comments
 
LVL 18

Expert Comment

by:DotNetLover_Baan
ID: 12054092
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
 
LVL 8

Author Comment

by:mppeters
ID: 12054173
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
 
LVL 18

Expert Comment

by:DotNetLover_Baan
ID: 12054221
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
 
LVL 8

Author Comment

by:mppeters
ID: 12054315
Thanks DotNetLover.

I understand the concept, but what is the code?
0
 
LVL 3

Expert Comment

by:RacinRan
ID: 12054606
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
 
LVL 18

Expert Comment

by:DotNetLover_Baan
ID: 12054645
"Code" means.. your program code... I use this term a lot here. You see, just like.. Code behind. :))
0
 
LVL 8

Author Comment

by:mppeters
ID: 12054715
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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 3

Expert Comment

by:RacinRan
ID: 12054769
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
 
LVL 8

Author Comment

by:mppeters
ID: 12054805
Ok thanks RacinRan. So how would I represent the above query in the xsd file?
0
 
LVL 3

Expert Comment

by:RacinRan
ID: 12054985
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
 
LVL 3

Expert Comment

by:RacinRan
ID: 12055006
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
 
LVL 8

Author Comment

by:mppeters
ID: 12055175
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
 
LVL 3

Accepted Solution

by:
RacinRan earned 500 total points
ID: 12055241
Try rptReportDocument.SetDataSource(dsUserRules.tables(0))
0
 
LVL 2

Expert Comment

by:ahmadfuwad
ID: 12063372
'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

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

1.0 - Introduction Converting Visual Basic 6.0 (VB6) to Visual Basic 2008+ (VB.NET). If ever there was a subject full of murkiness and bad decisions, it is this one!   The first problem seems to be that people considering this task of converting…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This video discusses moving either the default database or any database to a new volume.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now