Creating a Truly Dynamic Crystal Report in .NET


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


Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.

FastEddie___Author Commented:
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.


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

Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

FastEddie___Author Commented:
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.


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.

FastEddie___Author Commented:
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....

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


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
How is that the answer?

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.