Solved

Dynamic crystal report from a datatable and export to PDF

Posted on 2004-09-14
14
904 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
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.

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Get the selected ValueMember of Combobox 5 39
VB.NET 2008 Populate DataModel with DataTable 44 38
Complex SQL statement in VB.NET 7 31
get combo value in class 5 14
Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

789 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