• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 689
  • Last Modified:

Creating a Truly Dynamic Crystal Report in .NET

Hello,

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 reports.net 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;
                  conn.Open();
                  SqlDataReader reader = command.ExecuteReader();

                  DataSet dsTemp   = new DataSet();
                  DataTable Tables = new DataTable();
                  dsTemp.Tables.Add(Tables);
                  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
                                                               ///////////////////////////////////

                  }


                  conn.Close();

///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////



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
                        try
                        {
                              // Export the report
                              oRpt.Export();
                        }
                        catch (Exception err)
                        {
                              Response.Write("<BR>");
                              Response.Write(err.Message.ToString());
                        }

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

///////////////////////////////////////////////////////////////////////////////////////////////////////////////////






0
FastEddie___
Asked:
FastEddie___
  • 5
  • 3
1 Solution
 
mlmccCommented:
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.

mlmcc
0
 
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.

-Eddie







0
 
mlmccCommented:
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
http://support.businessobjects.com/communityCS/FilesAndUpdates/vbnet_web_samples.exe.asp

VB.Net Windows examples
http://support.businessobjects.com/communityCS/FilesAndUpdates/vbnet_win_samples.exe.asp

mlmcc
0
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

 
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.

-Eddie




0
 
mlmccCommented:
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.

mlmcc
0
 
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:

  AYX8567
  BRG1958
  CKP3872

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

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

mlmcc
0
 
mlmccCommented:
How is that the answer?

mlmcc
0

Featured Post

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.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now