Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > .NET > Other .NET > Crystal Reports
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Crystal Reports General discussion about Crystal Reports. For discussions specific to the book Professional Crystal Reports for VS.NET, please see the book discussion forum for that book.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Crystal Reports section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old January 21st, 2004, 07:42 PM
Registered User
 
Join Date: Jan 2004
Location: , , .
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default Recipe to change CR query at run time.

Hello,

I'm a newbie to CR. I'm using the version of CR that comes with Visual Studio .NET 2003 Enterprise Edition. I'm connecting to Microsoft SQL Server Version 2000. My interface language is C#.

My problem is that I can get "regular" Crystal Reports to work, i.e. if I specify all the tables and fields ahead of time, but I can't figure out how to programmatically change the query at run time.

That is, I have SQL query that can change dynamically at run time, (even the DB server and catalog can change), but the fields of the output will always be the same in number, name, and type.

So I would like a recipe that I could follow, from top to bottom, to get the results I want, without any external assumptions (like that I've created an appropriate *.rpt file first).

Lest you think I'm lazy and trying to get you to do my work for me, I have tried the following methods:

My first approach was to try to use a parameterized stored procedure. The problem with that was that I can add the SP, but that CR doesn't automatically discover what fields it returns, so I couldn't find any way to put fields on the report. Even if I could solve this problem. I foresee further problems:

#1 Since I'm using a parameter, the ReportViewer is going to pop up a parameter dialog box. I don't want this to happen, because I intend to populate the parameters programmatically at run time.

#2 I don't think the CR would allow me to change the database server or catalog.

I then tried use a "command" instead of a SP, but discovered I couldn't change the command text at run time (also, I think #2 from above will apply here).

Finally I discovered you can populate a CR at runtime with a DataSet. I believe this is the approach I should use, because then I'd be in complete control of what data got passed to the report. The problem is I can't figure out how to get the report to display this data!

I think the issue is that I'm not creating the report correctly; but I don't know how to do so. I've tried a blank report, a report with all the appropriate fields, but unbound, a report that contains a single table that looks just like the output of my SP, and finally a report created from an XSD file**.

I tried the example I found at http://support.businessobjects.com/c...dotnet.exe.asp , but I couldn't get it to work (yes, I pointed it at a real database).

For most of a week, I've been searching these and other forums, web pages, Intellisense, the online help, and the book "The Complete Reference: Crystal Reports 9". I've seen bits and pieces of the answer in many different places, but nowhere can I find the _complete_ answer.

So can someone give me a simple (but complete) method for doing this?

Thank you,

-D. Bron

* After enabling SPs by right clicking in the details field, then selecting Designer>Default Settings>Database and enabling "Stored Procedures". Took me a while to discover that.

** It took me even longer to figure out you could do this, and how.
Reply With Quote
  #2 (permalink)  
Old January 22nd, 2004, 06:11 PM
Registered User
 
Join Date: Jan 2004
Location: , , .
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Ok, I figured this out for myself. The answer is not obvious.

Steps to get a crystal report to display arbitrary data (i.e. roll your own queries):

#1 Create a description of the output of your SQL query as an XML Schema (i.e. as an XSD file).
    #1.a In the solution explorer, right click on your project and select "Add new item".
    #1.b Select "Local Project Items">Data>XML Schema
    #1.c Describe your data type in XML. This is very much like designing a database in SQL server; you have to specify the columns' names & types.
        #1.c.i Open the XSD file and right click on an empty area,
        #1.c.ii Select "View XML source".
        #1.c.iii Enter your XML schema. Remember, this schema should describe the columns you expect from the output of your SQL query.

                  I've included an example of the XML I used. You can copy and paste it, and change the important parts.
                  The comments (in ) explain what to change.

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

                      <!--
                                    Ok, the name= on the next line is important, because it will be used when Visual Studio generates
                                    your strongly-typed data set (see section #2). This means it _will_ appear in your C#/VB.Net/Whatever code.
                                    If your report is called "Foo", I recommend calling your schema "FooDataSet".
                      -->
                      <xs:element name="CurrencyDataSet" msdata:IsDataSet="true">
                          <xs:complexType>
                              <xs:choice maxOccurs="unbounded">


                                  <xs:element name="currency_rate">
                                      <xs:complexType>


                                          <xs:sequence>
                                              <xs:element name="currency_rate_id" type="xs:integer" />
                                              <xs:element name="rate" type="xs:double" />
                                              <xs:element name="effective_date" type="xs:date" />
                                              <xs:element name="from_currency_id" type="xs:integer" />
                                              <xs:element name="to_currency_id" type="xs:integer" />
                                          </xs:sequence>
                                      </xs:complexType>
                                  </xs:element>
                              </xs:choice>
                          </xs:complexType>


                          <xs:unique name="CurrencyDataSetKey1" msdata:PrimaryKey="true">
                              <xs:selector xpath=".//mstns:currency_rate" />
                              <xs:field xpath="mstns:currency_rate_id" />
                          </xs:unique>
                      </xs:element>
                  </xs:schema>

#2 Generate the strongly-typed DataSet object from this schema.

    #2.a Right click in your XSD file.
    #2.b Select "View Dataset". You will see a graphic representation of your dataset (tip: if you need to change your dataset, the graphic representation is easier to manipulate than the XML text).
    #2.c Right click again
    #2.d Make sure "Generate Dataset" is enabled.

#3 Generate the crystal-report and the strongly-typed CrystalReport object from this schema
    #3.a In the solution explorer, right click on your project and select "Add new item".
    #3.b Select "Local Project Items">Utility>Crystal Report
    #3.c Name the Crystal Report whatever you want your strongly-typed CrystalReport object named.
    #3.d When the Crystal Report wizard pops up, select "Using the Report Expert" and press "OK".
    #3.e In the report expert, select "Database Files" and navigate to your XSD file. Press "Open".
    #3.f From here, create the Crystal Report as you would if you were connecting to a database.
          As an example, using the XSD file I gave above:
        #3.f.i Click the '+' next to "CurrencyDataSet", highlight "currency_rate", press
                "Insert Table", then "Next>>".
        #3.f.ii Press "add all", then "Finish".

#4 Create your Crystal Report viewer
    #4.a Add references in your project to the Crystal Reports namespace
        #4.a.1 In solution explorer, right click on "References"
        #4.a.2 Select "Add Reference".
        #4.a.3 In the reference dialog, add a reference to CrystalDecisions.Windows.Forms
                and press "OK".
    #4.a Double-click on your form in Solution Explorer
    #4.b Open your Toolbox and press "Windows Forms".
    #4.c Drag a "CrystalReportViewer" from the toolbox to your form.

#5 Display your report
    #5.a Generate your data set (i.e. connect to the database).
        #5.a.1 Use the appropriate namespaces. You need
                System.Data and System.Data.OleDb.
                For example, in C#, add the following to the top of your Form code:
                  using System.Data;
                  using System.Data.OleDb;

        #5.a.2 Instantiate a connection to your database, using an OleDbConnection.
                For example, in C#, use:
                  System.Data.OleDb.OleDbConnection cxn = new System.Data.OleDb.OleDbConnection(CONNECTION STRING GOES HERE);

        #5.a.3 Instantiate a data adapter to execute your query.
                For example, in C# use:
                  System.Data.OleDb.OleDbDataAdapter adptr = new System.Data.OleDb.OleDbDataAdapter(SQL QUERY GOES HERE, cxn);

        #5.a.4 Instantiate and fill your custom DataSet (the strongly-typed DataSet object VS generated from your XSD file):
                For example, in C#, using the XSD I gave above, use:
                  CurrencyDataSet ds = new CurrencyDataSet();
                  adptr.Fill(ds,"currency_rate"); // Note that "currency_rate" is the name of the complex type I created in my XSD.

    #5.b Create your report.
        #5.b.1 Instantiate your custom CrystalReport (the strongly-typed Report object CR generated from your XSD file).
                For example, in C#, using the XSD I gave aboce, use:
                  CurrencyReport rpt = new CurrencyReport();

        #5.b.2 Provide your data set to your custom report object.
                For example, in C#, use:
                  rpt.SetDataSource(ds);

    #5.c Bind your report to the viewer
          For example, in C#, use:
            crystalReportViewer1.ReportSource = rpt;

That's it; build and run your project, and your report will be displayed. Note that, once you create your XML schema and have VS & CR generate your custom
DataSet & Report objects, there really isn't much code to write. For my XML example, using C#, the only code you'd need is:

    using System.Data;
    using System.Data.OleDb;

    ... // Assuming that the Windows Form Designer has added a Crystal Report viewer named "crystalReportViewer1" here.

    // Set up connection to database
    System.Data.OleDb.OleDbConnection cxn = new System.Data.OleDb.OleDbConnection(CONNECTION STRING GOES HERE);

        // Data adapater to actually retrieve data
    System.Data.OleDb.OleDbDataAdapter adptr = new System.Data.OleDb.OleDbDataAdapter(SQL QUERY GOES HERE, cxn);

    // Data set to hold data
    CurrencyDataSet ds = new CurrencyDataSet();
    adptr.Fill(ds,"currency_rate");

    // Format the data as a report.
    CurrencyReport rpt = new CurrencyReport();
    rpt.SetDataSource(ds);

    // Display the report
    crystalReportViewer1.ReportSource = rpt;

The DB connection and SQL query are entirely within your control; just make sure you keep your XML schema in sync with the output of your query. If the number, names, or types
of the columns of your output changes, you have to change your XML accordingly, then re-generate your DataSet & Report objects.

I hope this helps the next programmer frustrated by Crystal Reports.

-D. Bron
Reply With Quote
  #3 (permalink)  
Old February 25th, 2004, 10:13 AM
Registered User
 
Join Date: Feb 2004
Location: montreal, PQ, Canada.
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

You went to a lot of effort, thanks for the help. Your technique is very interesting but unfortunately I am not using a dataset as my data source.

My report gets its data from a stored procedure in an SQL Server database. My VB.Net application gets its connectionstring from the App.Config file.

I want my VB application to be able to share this connection string with my report. Currently the report uses a DSN which is less than ideal as this dsn wont exist on client machines.

I have spent a lot of time (as you did) trolling through forums and pages of Google results. I am not using a web service, the striongly typed report is included in my VB.Net Windows application. I don't want to make a deployment package for this application, it has been designed to run from a network drive - avoiding any installation.

If you have any more resources I would greatly appreciate the help.
Thanks,
Andrew.

Reply With Quote
  #4 (permalink)  
Old February 26th, 2004, 02:02 PM
Registered User
 
Join Date: Jan 2004
Location: , , .
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

One of my co-workers found this VB code that will create a DSN:

http://www.freevbcode.com/ShowCode.Asp?ID=502

I haven't actually used or reviewed it, but it might help you.

-D. Bron
Reply With Quote
  #5 (permalink)  
Old June 24th, 2009, 03:07 AM
Registered User
 
Join Date: Jun 2009
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default run time customization of report

Hi,

I have created an MFC application, in which i am using crystal report.

I have created one .rpt file which is my crystal report. I am using MsAccess for database.

I have several data in my database & each time report displays all the data which are in my MsAccess database. But i want to customize my report at run time.
i.e I have 100 records in one table. Now i want to show only some of the records from thos 100 records. Means i wnat to show only those records which are created in June-2009. Now this date should be selectd by my end-user.

Can anyone help me how to customize my report like this at run time??

Because each time my creastl report shows all records in database.
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
VB.NET - creating a CR report at run time jimbodeni Crystal Reports 4 March 21st, 2013 10:01 AM
How to change CR 11 database name at run time, ASP enghok Classic ASP Professional 3 September 26th, 2007 03:28 AM
change Datasource of a crystal report at run time tiyyob Pro VB 6 1 February 17th, 2007 06:05 AM
change database location on CR 10 at run time blu3_x30n Pro VB 6 0 March 30th, 2006 01:25 PM
Change picture at run-time with CR for .NET? NTurnbullJr Crystal Reports 2 March 31st, 2005 05:29 AM



All times are GMT -4. The time now is 04:22 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.