Creating a Truly Dynamic Crystal Report in .NET

Posted on 2006-05-26
Last Modified: 2010-01-13

I would like to create a crystal report while a datareader loop is being executed.
Does anybody know how to do this? Or if it is even possible since crystal requires a dataset object and they can be memory intensive.

I want to run a stored procedure and for each row returned I want to run a second stored procedure, populate a dataset, build the report and export the report in PDF format to a directory on the server.

I have all the code required for all of the processing but now I need to be able to put that code in a datareader loop. In a "reader.Read()".

Can someone tell me how I may get the second code block to work inside the reader loop?

I'm in a tight time crunch. Any advice is greatly appreciated.

Here is the first stored procedure call:


                                                string CONN = ConfigurationSettings.AppSettings["DB_CONN"];

                  // get list of neighborhood codes
                  SqlConnection conn = new SqlConnection(CONN);
                  SqlCommand command = new SqlCommand("MLS_HCAD", conn);
                  command.CommandTimeout = 0;
                  command.CommandType = CommandType.StoredProcedure;
                  SqlDataReader reader = command.ExecuteReader();

                  DataSet dsTemp   = new DataSet();
                  DataTable Tables = new DataTable();
                  dsTemp.Tables[0].Columns.Add( "val", System.Type.GetType("System.String" ) );
                  while (reader.Read())
                        string currentNeighborhoodCode = reader["txroll_NeighborhoodCode"].ToString();

                                                                /// process the other stuff here




Now, here is the other stuff to be processed:


                                // EXPORT CR Variables                    
            ExportOptions crExportOptions;
            DiskFileDestinationOptions crDiskFileDestinationOptions;

                                protected CrystalDecisions.Web.CrystalReportViewer CrystalReportViewer1;

                                                oRpt = new rptSales (); //instantiate report object

                  // use list to fill a dataset then fill a datagrid.
                  string myList = Session["strCadNumbersList"].ToString();

                  string CONN = ConfigurationSettings.AppSettings["DB_CONN"];
                  SqlConnection connObj = new SqlConnection(CONN);
                  SqlCommand cmdObj = new SqlCommand("MLS_HCAD_ProcessPerNeighborhood", connObj);
                  cmdObj.CommandType = CommandType.StoredProcedure;
                  cmdObj.Parameters.Add(new SqlParameter("@strNeighborhoodCode", string currentNeighborhoodCode));
                  SqlDataAdapter da = new SqlDataAdapter ( cmdObj ) ;

                  DataSet dataSet = new DataSet();
                  da.SelectCommand.CommandTimeout = 0;
                  da.Fill(dataSet, "MLS");

                  /* Use Report Engine object model to pass populated dataset to report */
                  oRpt.SetDataSource (dataSet);
                  /* bind report object containing data to the Crystal Web Forms Viewer */
                  CrystalReportViewer1.ReportSource = oRpt;

                  oRpt.PrintOptions.PaperOrientation = PaperOrientation.Landscape;
                  oRpt.PrintOptions.PaperSize = PaperSize.PaperLetter;

                                                               // ****************************************************
                        //   start export report of pdf to local file system
                        // ****************************************************
                        //Check to see if the application directory has a subdirectory called "Export".
                        //If not, create the directory since exported files will be placed here.
                        //This uses the Directory class of the System.IO namespace.
                        string ExportPath;
                        ExportPath = ConfigurationSettings.AppSettings["PAPERWISE_PATH"];
                        if (Directory.Exists(ExportPath) == false) Directory.CreateDirectory(ExportPath);

                        // First we must create a new instance of the diskfiledestinationoptions class and
                        // set variable called crExportOptions to the exportoptions class of the reportdocument.
                        crDiskFileDestinationOptions = new DiskFileDestinationOptions();
                        crExportOptions = oRpt.ExportOptions;
                        //Export to PDF

                        //append a filename to the export path and set this file as the filename property for
                        //the DestinationOptions class
                        string today = DateTime.Now.ToString("MMMMddyyyy_HHmm");
                        string fullFileName = ExportPath+today+"_NeighborhoodReport.pdf";
                        crDiskFileDestinationOptions.DiskFileName = fullFileName ;

                        //set the required report ExportOptions properties
                        crExportOptions.DestinationOptions = crDiskFileDestinationOptions;
                        crExportOptions.ExportDestinationType = ExportDestinationType.DiskFile;
                        crExportOptions.ExportFormatType = ExportFormatType.PortableDocFormat;

                        //Once the export options have been set for the report, the report can be exported.
                        //The Export command does not take any arguments
                              // Export the report
                        catch (Exception err)

                        // ****************************************************
                        //  end export report of pdf to local file system
                        // ****************************************************


Question by:FastEddie___
    LVL 100

    Expert Comment

    I think the only way to do this is with a subreport.

    Crystal expects a single dataset so I don't think you can get it to open a new dataset for each record.


    Author Comment

    Hi Mlmcc,

    Thanks for the quick response.

    The subreport might be a good option but it seems to me that creating a report for each iteration of the loop should be possible.

    The loop starts, a stored procedure is run, a single dataset is created, a single report is created and sent to the file system as a pdf.

    When the first iteration finishes you still have the dataset in memory and that is ok if you either create the dataset for the second iteration with a different name (perhaps by appending the currentNeighborhoodCode to the name) or by keeping the same name and overriding the first dataset. This seems logical to me but I don't know the intricacies of memory management for datasets. I may just be blowing hot air.  :-)

    The point is that with every iteration you are indeed using a single dataset and that is what Crystal Reports expects.

    If this problem can be solved I'm sure it could be used as a great tool for Crystal Reports developers out there because the result would be truly dynamic reports.

    I appreciate your comments. Please let me know if I'm way off base on this one.


    LVL 100

    Expert Comment

    If you are trying to do the logic in an application then that is possible.  If you mean from Crystal then no it cannot be done that way.

    I believe there is an example in here using an ADO dataset.

    VB.Net web examples

    VB.Net Windows examples


    Author Comment

    Hi mlmcc,

    Thanks for the links.

    I have previously viewed similar sample code using C#. It is what I used as a basis for this application.
    The problem is not the use of a dataset. The problem is building the dataset inside a loop.

    For the time being lets forget about all the crystal reports stuff.
    Let say all we wanted to do is create a counter from 1 to 10.
    Within the counter we wanted to create a dataset and fill a datagrid.
    So for each iteration through the counter we end up with a populated datagrid.
    Since there are 10 iterations we will end up with 10 datagrids.

    Is this process possible? If so could you please provide some sample code as to what it might look like.

    I'm confident that if it is possible then I could add complexity with the crystal reports piece and make it work.

    If not then I'm up the creek.  :-)

    Thanks mlmcc, I do appreciate your help.


    LVL 100

    Expert Comment

    It is possible.  Do you need all the s=datasets at one time or can you build one then print the report then build the next?

    I am not familiar with C# datasets but can you have an array of datasets?  I assume you can

    Something like this

    DataSet dataSet [10];
                   DataSet dataSet = new DataSet();
                   da.SelectCommand.CommandTimeout = 0;
                   da.Fill(dataSet, "MLS");

    for (i=1; i < 10; i++)
        dataset[i] = New DataSet();
        da.SelectCommand.CommandTimeout = 0;
         da.Fill(dataSet[i], "MLS");

    When the loop finishes you should heave 10 datasets.


    Author Comment

    Hi mlmcc,

    I'm glad to hear that it is possible.

    Right, what I need is for each iteration, to build the dataset and print the report.
    The idea of creating a dataset array sounds very interesting but I would have use it in the context of a datareader loop.
    The for loop:   (i=1; i < 10; i++)   will acutally be a datareader.

    I do have the code working partially and I hope the dataset array is the missing piece.

    Let me give you a quick breakdown of what is happening in psudo code:

      Run a stored procedure.
      Put the results of the stored procedure in a datreader.
              For each iteration of the datareader
                     Run a second stored procedure.
                     Use the current row value of the first stored procedure as the parameter of the second stored procedure.
                     Build a dataset.
                     Build a report object.
                     Export the report object to the local file system as a PDF.

    This is acutally working except that it is currently only processing the last iteration of the first stored procedure.

    For example if the first stored procedure returned this:


    The second stored procedure would only process CKP3872 and not the first two (AYX8567 nor BRG1958).

    The results of the second stored procedure would be something like this:

       CKP3872   123  Main St.      Houston    TX
       CKP3872   222  Bravo St      Houston    TX
       CKP3872   794  Charlie Rd   Houston    TX
       CKP3872   999  Zulu Ct.       Houston    TX

    Which is the correct data but I need it for the first two iterations as well as the last iteration of the first stored procedure.

    It seems like something is being overriden on the:     while (reader.Read())   method.

    Got any ideas...

    Anyone...  Anyone...   BUELLER....

    LVL 100

    Accepted Solution

    Can you show the actual code?  I am not familiar with data readers so not sure how they work.

    LVL 100

    Expert Comment

    How is that the answer?


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
    Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
    Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

    760 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

    11 Experts available now in Live!

    Get 1:1 Help Now