locked
ADO.NET Datatable as Crystal Report datasource

    Question

  • Folks,

     

    Background:  My customer cannot accept any additional installation of supporting software or modules for my system.  Therefore, I am not loading SQL Express or any other DB onto their system.  Instead, I am loading my db schema into an into a dataset with the respective datatables (small program, small db, so in memory works fine) and filling it with the corresponding xml data.

     

    I cannot find the appropriate documentation to load a crystal report from an in-memory datatable.  Most tutorials are geared towards connecting to SQL Server.  When I point a crystalreport object to my dataset, the designer picks up the tables at designtime but doesn't load data at runtime.

     

    So how do I build a reportdocument from code that then loads my in-memory datatable?  Better yet, how do I get a designer pre-built report that I can fill with the in-memory datatable (I'd hate to give up the benefits of the report designer)?

     

    Thanks,
    Mike

    Thursday, May 03, 2007 1:54 PM

Answers

  • I just had to do this and was looking for help before I found my problem.  Here is a snippet of a very simple example that I used to figure out the process.  A simple dataset is being formed when a button is clicked to call up a form named form2.  The dataset is then set as the datasource of the report.  The report is then set as the report source for the crystalreportviewer on the form.

     

    Code Snippet

    Option Explicit On

    Imports CrystalDecisions.CrystalReports.Engine

    Imports CrystalDecisions.Shared

    Public Class Form2

    Dim ds As New DataSet

    Dim dt As DataTable

    Dim dr As DataRow

    Private Sub Form2_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

    Dim rpt As cr1 = New cr1

     

    dt = New DataTable("Sandwiches")

    dt.Columns.Add("Bread", GetType(String))

    dt.Columns.Add("Meat", GetType(String))

    dt.Columns.Add("Condiment", GetType(String))

    dt.Columns.Add("#Veggies", GetType(Double))

    ds.Tables.Add("Sandwiches")

     

    dr = dt.NewRow()

    dr(0) = "White"

    dr(1) = "Ham"

    dr(2) = "Yellow Mustard"

    dr(3) = 2

    dt.Rows.Add(dr)

    dr = dt.NewRow()

    dr(0) = "Wheat"

    dr(1) = "Turkey"

    dr(2) = "Dijon Mustard"

    dr(3) = 2

    dt.Rows.Add(dr)

    dr = dt.NewRow()

    dr(0) = "Kaiser Roll"

    dr(1) = "Chicken Breast"

    dr(2) = "BBQ Sauce"

    dr(3) = 2

    dt.Rows.Add(dr)

     

    rpt.SetDataSource(dt)

    CrystalReportViewer1.ReportSource = rpt

     

    End Sub

    End Class

     

    If you have subreports then use:

    Code Snippet

    rpt.Subreports("YourSubReportNameHere").SetDataSource(WhichEverDataTableHere)

     

    It is crucial that your report is designed from a table with the same name and schema that your in-memory dataset is generating.  I used a .xsd in my report and made the datatables there.  I think you should also be able to use an xml file created from your dataset if you don't want to use the .xsd file as the source to design your reports from.  You could try throwing in a ds.writexml("PathHere.xml") command for one run and then form the report off that but the .xsd method was very quick and easy.  Hopefully this helps.

     

    On the offchance you run into the problem I had where the toolbar disappears, after the reportsource is made, toss in a:

    Code Snippet

    CrystalReportViewer1.DisplayToolbar = True

     

    Thursday, May 03, 2007 9:31 PM
  • You can find samples here : These must also be present somewhere in your CR Installation folder, theay are there in CR11 but not sure about previous versions.

     

    http://support.businessobjects.com/downloads/samples.asp

    Friday, May 04, 2007 7:59 AM

All replies

  • "how do I get a designer pre-built report that I can fill with the in-memory datatable "

     

    to do this, use a typed dataset,In your project add a dataset file, in that dataset create a table that you are going to use in  your report with same name and filed names as in your untyped dataset(in memory dataset).

     

    Now in your report Database FIelds> Add remove DataBase > A window will open, select more data sources and select ADO.NET(XML) and select the file that you added to your project , its will provide you with all tables/fields that you created in DataSet file.Now use them in your report and at runtime these fields will be replaced with data from your in memory DataSet.

     

    There is one more thing , actually its been long time since i used such thing.When you create your untyped(in memory) dataset use this

     

    DataSet objDs = new DataSet("Dataset1");

     

    Dataset1 is the name of DataSet file that you added to your project.

    Thursday, May 03, 2007 4:02 PM
  • Thank you for responding. 

     

    Unfortunatley, the approach you described is the one I tried.  At design time, I referenced my typed dataset, my typed datatable, and included my typed fields in the report design.  At runtime, the report prints the field headers but no data.  I think there is a disconnect between an empty design-time dataset (due to intentional exclusion of SQL Server) and the run-time dataset loaded at program init via xml.

     

    It is the <DataSet ojbDs = new DataSet("Dataset1");/> code sequence that doesn't seem to be refreshing the static report design with the dynamic data.  Where do I find the documentation to build a report from code?

     

    Thanks,
    Mike

     

     

    Thursday, May 03, 2007 5:45 PM
  • I just had to do this and was looking for help before I found my problem.  Here is a snippet of a very simple example that I used to figure out the process.  A simple dataset is being formed when a button is clicked to call up a form named form2.  The dataset is then set as the datasource of the report.  The report is then set as the report source for the crystalreportviewer on the form.

     

    Code Snippet

    Option Explicit On

    Imports CrystalDecisions.CrystalReports.Engine

    Imports CrystalDecisions.Shared

    Public Class Form2

    Dim ds As New DataSet

    Dim dt As DataTable

    Dim dr As DataRow

    Private Sub Form2_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

    Dim rpt As cr1 = New cr1

     

    dt = New DataTable("Sandwiches")

    dt.Columns.Add("Bread", GetType(String))

    dt.Columns.Add("Meat", GetType(String))

    dt.Columns.Add("Condiment", GetType(String))

    dt.Columns.Add("#Veggies", GetType(Double))

    ds.Tables.Add("Sandwiches")

     

    dr = dt.NewRow()

    dr(0) = "White"

    dr(1) = "Ham"

    dr(2) = "Yellow Mustard"

    dr(3) = 2

    dt.Rows.Add(dr)

    dr = dt.NewRow()

    dr(0) = "Wheat"

    dr(1) = "Turkey"

    dr(2) = "Dijon Mustard"

    dr(3) = 2

    dt.Rows.Add(dr)

    dr = dt.NewRow()

    dr(0) = "Kaiser Roll"

    dr(1) = "Chicken Breast"

    dr(2) = "BBQ Sauce"

    dr(3) = 2

    dt.Rows.Add(dr)

     

    rpt.SetDataSource(dt)

    CrystalReportViewer1.ReportSource = rpt

     

    End Sub

    End Class

     

    If you have subreports then use:

    Code Snippet

    rpt.Subreports("YourSubReportNameHere").SetDataSource(WhichEverDataTableHere)

     

    It is crucial that your report is designed from a table with the same name and schema that your in-memory dataset is generating.  I used a .xsd in my report and made the datatables there.  I think you should also be able to use an xml file created from your dataset if you don't want to use the .xsd file as the source to design your reports from.  You could try throwing in a ds.writexml("PathHere.xml") command for one run and then form the report off that but the .xsd method was very quick and easy.  Hopefully this helps.

     

    On the offchance you run into the problem I had where the toolbar disappears, after the reportsource is made, toss in a:

    Code Snippet

    CrystalReportViewer1.DisplayToolbar = True

     

    Thursday, May 03, 2007 9:31 PM
  • Are you ever passing the dataset to the report in code?  You need something similar to the line:

    myReport.SetDatasource(myDataset)

    Thursday, May 03, 2007 9:32 PM
  • You can find samples here : These must also be present somewhere in your CR Installation folder, theay are there in CR11 but not sure about previous versions.

     

    http://support.businessobjects.com/downloads/samples.asp

    Friday, May 04, 2007 7:59 AM
  • Perfect.  The rpt.SetDataSource(dt) is the part I couldn't find anywhere.

     

    All, thanks a bunch for the help and pointing me to related resources.

     

    Cheers,
    sdbMike

    Friday, May 04, 2007 12:53 PM
  • great yar , thanks for the help ... wonder code ... keep posting and helping the developers .


    i found the code to set the datasourse of a crystal report very helpfull in solving my problems in the project


    thanks a lot


    pramod.s
    Monday, September 01, 2008 10:05 AM