No more Crystal Reports

More RDLC Basics

The final piece of the puzzle to solve is how to use multiple report data sets in a report.  

Things to know:

• You do not need to use sub reports to use more than one data set in  report.
• Report data set != .net C# Data set.  (This is possibly the dumbest thing about RDLC reports).
• Report data set  is approximately equivalent to .net data table.
• Setting up multiple report data sets for a single report is easier than most examples so.

Here is the end point I want:  A report where most of the information comes from one query or view where there is a 1=1 relationship between the various data tables.  The next data set is 1 to many relationship listing multiple items associated a data record.  

My working example: Mine Summary report:

Mine Name, location description, map coordinates, and mineral deposit description comes from one data set.  Minerals found at the mine will come from another data set.

End result will be:


First, I used the Report designer in VS 2010:

Defined the data sets:

From December 1, 2012

And the created the report:

From December 1, 2012

You can add report controls Grids, lists, or text boxes and then assign the values to each.
As you assign the value for each control, you can specify the name of the data set the value is from.

From December 1, 2012

The report data sets map to table adapters I have set up in my DataSet in the project.

From December 1, 2012

I can this approach because I want to have the report be as close to a dumb view as possible.  Any data processing or business logic will be performed in C#. So I create a reference dataset(s) that I can design the report with and stored in the project as reference.

Now, all I have to do is create a couple data tables that match the data sets defined in the report.

Here is the code I use to fetch the data:  1st data table is the 1=1 data of mine name, location and map coordinates.  2nd table is list of mineral occurrences.  A 3rd table is used to concatenate the mineral occurrences to a single text column using a Linq aggregate function.

public static DataSet GetReportDataSet(string reportName, string MDBfile, object rowID)
        {

            try
            {
                EnterProc(MethodBase.GetCurrentMethod());
                var dataset = new DataSet();
                string[] sql = null;
                string[] tablenames = null;

                switch (reportName)
                {
                    case "MineSummary":
                        #region MineSummary
                        sql = new[]{string.Format("SELECT * FROM vwMineSummaryReport WHERE MineID={0}", rowID)
                                      , string.Format("SELECT * FROM vwMineSummary_MineralOcc WHERE ID={0}", rowID)
                                      };

                        tablenames = new[] {"Mine"
                                           ,"MineralOcc"
                                           };
                        #endregion
                        break;
                }

                if (sql != null && sql.Length > 0)
                {
                    FillDataSetFromMDB(MDBfile, sql, tablenames, dataset);
                }

                if (dataset.Tables["MineralOcc"].Rows.Count > 0)
                {
                    var table = new DataTable("Minerals");
                    var col = new DataColumn { ColumnName = "Minerals", DataType = typeof(string) };
                    table.Columns.Add(col);
                    string sMinerals = dataset.Tables["MineralOcc"].Rows.Cast<DataRow>().Aggregate("", (current, row) => current.Length > 0 ? current + string.Format(" , {0} ", row[0]) : string.Format(" {0} ", row[0]));
                    table.Rows.Add(sMinerals.Trim());
                    dataset.Tables.Add((table));
                }


                ExitProc(MethodBase.GetCurrentMethod());
                return dataset;

            }
            catch (Exception ex)
            {
                ErrorLog(ex);
                return null;
            }
        }

The next code block is used to assign the data to report data sets.
(A point a claification:  In my application, the user clicks on a button to print/view/or export a report to PDF, and I use events and arguments to pass the data and the report name from a user control hosting all of the functionality to the parent form which catches the event and calls a method to print / preview or export the report.)

In the code, a report event argument is passed to a method that creates a localreport and assigns multiple report data sets (one per data table in the dataset passed in using the argument.)

static LocalReport PrepareRDLCReport(ReportEventArgs args)
        {
            var rds = args.ReportData != null ? new ReportDataSource("DataSet1", args.ReportData)
                                            : new ReportDataSource("DataSet1", args.ReportDataSet.Tables[0]);
            var report = new LocalReport
            {
                ReportEmbeddedResource = string.Format("DRC.RDLC.{0}.rdlc", args.ReportName)
            };

            report.DataSources.Add(rds);
            if (args.ReportName == "MineSummary")
            {
                report.DataSources.Add(new ReportDataSource("DataSet2", args.ReportDataSet.Tables[1]));
                report.DataSources.Add(new ReportDataSource("DataSet3", args.ReportDataSet.Tables[2]));
            }
            return report;

        }

The final block of code is common code used to print a report.

public static void PrintReport(ReportEventArgs args, Form mdiParent)
        {
            try
            {
                if (mdiParent != null)
                     mdiParent.Cursor = Cursors.WaitCursor;

                DateTime startTime = DateTime.Now;
                switch (args.ReportType)
                {
                    case "CrystalReport":
                        ReportDocument reportobj = PrepareReport(args);
                        if (reportobj != null)            
                                reportobj.PrintToPrinter(1, false, 0, 0);
                        break;
                    case "RDLC":
                        var report = PrepareRDLCReport(args);
                        var reportPrintDoc = new ReportPrintDocument(report)
                            {
                                PrinterSettings = {PrinterName = Properties.Settings.Default.DRC_Printer}
                            };

                        reportPrintDoc.Print();
                        break;
                }
                TimeSpan duration = DateTime.Now - startTime;
                DisplayDuration(mdiParent, duration, string.Format("Print report - {0}", args.ReportName));
            }
            catch (Exception ex)
            {
                DRCCommon.Common.ErrorLog(ex);
            }
        }