RDLC Basics

So less than 6 hrs of time invested and I have basic RDLC reporting working for data displayed in a table / grid.  Also created several multi-column reports.

So here is my basic approach:

My data source is a MS Access MDB file but this would work for SQL server just as easy. Simply a different connection string.

Step #1 - Create a visual dataset with table adapters that can be bound to a report.

Step #2 Create basic RDLC report without using the wizard.

1.  Add new item, select reporting template, select Report.
2. In report data panel, select new dataset
3. Select data source 
4. Select table adapter
5. Add table / grid to body of the report.
6. Assign columns in the table / grid to fields from the tableAdapter field list
7. Right-click in the report designer below the report body and add page header or page footer as needed.
8. Add Report name, execution time, and page number from built-in fields

Step #3 Fetch data for report and return it as a data table

Here is  my design approach.  I only use table adapters to bind fields in the designer.  At runtime, I like to manually fetch the data and add it to report data source.  I do this so I can re-use report design and pass slightly different data sets to the same report.  This is similar to the approach I used 10 year ago with MS Access reports.  Not for everyone but it works for me.

I create a function where I can return the data I want by passing in the report name.

public static DataTable GetReportData(string reportName, string MDBfile)

{

try

{

EnterProc(MethodBase.GetCurrentMethod());

string sql = null;

DataTable table = null;

switch (reportName)

{

case "":

break;

case "CollectionSummaryByYear":

sql = "SELECT [Year], COUNT([Year]) AS SpecimenCount FROM tblMineral_Collection WHERE (Len([Year]) > 0) GROUP BY [Year]";

break;

case "FossilLabel_SmallCabinet":

case "FossilLabel_Thumbnail":

sql = "SELECT tblFossils.ID_Text, tblFossils.CommonName, tblFossils.ProperName, tblFossils.RockFormation, tblFossils.RockAge, tblFossils.Location, tblFossils.County, tblFossils.City, lstStates.State, lstCountries.Country, Str([tblFossils].[Year]) AS strYear FROM (tblFossils LEFT JOIN lstCountries ON tblFossils.CountryID = lstCountries.ID) LEFT JOIN lstStates ON (tblFossils.StateID = lstStates.ID) AND (tblFossils.CountryID = lstStates.CountryID);";                        

break;

case "MineralLabel_Cabinet":

case "MineralLabel_SmallCabinet":

case "MineralLabel_Micromount":

case "MineralLabel_Thumbnail":

sql = "SELECT tblMineral_Collection.ID_Text, tblMineral_Collection.Variety, tblMineral_Collection.Mineral_Name, tblMineral_Collection.Mine, tblMineral_Collection.City, tblMineral_Collection.County, lstStates.State, lstCountries.Country, Str([tblMineral_Collection].[Year]) AS StrYear, tblMinerals.Formula_1, tblMinerals.Formula_2 FROM (((LabelQueue INNER JOIN tblMineral_Collection ON LabelQueue.Specimen_ID = tblMineral_Collection.Specimen_ID) LEFT JOIN lstCountries ON tblMineral_Collection.CountryID = lstCountries.ID) LEFT JOIN lstStates ON (tblMineral_Collection.CountryID = lstStates.CountryID) AND (tblMineral_Collection.StateID = lstStates.ID)) LEFT JOIN tblMinerals ON tblMineral_Collection.Mineral_Name = tblMinerals.Mineral";

break;

case "SpeciesList":

sql = "SELECT tblMineral_Collection.Mineral_Name, Count(tblMineral_Collection.Mineral_Name) AS SpecimenCount FROM  tblMineral_Collection WHERE LEN(Mineral_Name)>0 GROUP BY tblMineral_Collection.Mineral_Name";

break;

}

if (!string.IsNullOrEmpty(sql))

{

table = TableFromMdb(MDBfile, sql);

}

ExitProc(MethodBase.GetCurrentMethod());

return table;

}

catch (Exception ex)

{

ErrorLog(ex);

return null;

}

The only trick to this approach is to pass in the data using name of dataset originally used to setup report.

var rds = new ReportDataSource("DataSet1", args.ReportData);

var report = new LocalReport

{

ReportEmbeddedResource = string.Format("DRC.RDLC.{0}.rdlc", args.ReportName)

};

report.DataSources.Add(rds);

Creating Labels in RDLC

Next step in Reporting without Crystal reports using RDLC: Multicolumn labels.

See Wrox article on Multicolumn labels

Safety tip for those playing at home with VS2010.  ReportViewer control does not render report to show data spread across into multiple columns.  To save paper, user Convert to PDF as Print preview.

I was able to create a report in landscape layout with 3 columns using Tablix control to display two data fields.

Be mindful to manage column spacing and margins otherwise there will be column spill over to the next page.

Finally Replacing CrystalReports

I've had a long hate-hate relationship with crystal reports.  Latest version of Crystal Reports via SAP is a 72 MB install!?! Why....  CrystalReport 2008 was only 17 MB.  Leave it to SAP to take a poor solution and make it worse.

So, I've been on a quest to replace crystal Reports.  In VS 2010, I've found a workable client side reporting solution in RDLC.  Now, I know its going to be an uphill battle to produce Avery style labels with RDLC but I'm going uphill 75MB lighter.

The real motivation for this reporting switch is my next version of Digital Rockhound's Companion software is going to be a download users can buy.  I'll trimmed down the files my software uses.  I'm ditching my street level arcview shapefiles dated circa 1995 for Google/Bing/OSM online maps.  Last piece is really the reporting engine.

In a little under 3 hours, I was able to add in a basic grid RDLC report into DRC 3.0 and get the report to display in the report viewer, print directly to a local printer, and render to a PDF and word file formats.

Resources that helped me with RDLC reports:

Brian Hartman's code to manually print a RDLC report.  Code sample Here.

MSDN: Walkthrough: Printing a Local Report without Preview 

Get this error attempting to print a localreport without a RDLC source.
The report definition for report 'xxx' has not been specified.

A couple of useful stackoverflow posts:
Code to convert RDLC to a PDF
Code to convert RDLC to Word.

Article from dotnetsoldier blog

My code to render a simple RDLC report to a word Doc and then launch word:

var rds = new ReportDataSource("DataSet1", args.ReportData);
                        var report = new LocalReport
                            {
                                ReportEmbeddedResource = string.Format("DRC.RDLC.{0}.rdlc", args.ReportName)
                            };

                        report.DataSources.Add(rds);
                        // ReSharper disable RedundantAssignment
                        string encoding = String.Empty;
                        string mimeType = String.Empty;
                        string extension = String.Empty;
                        // ReSharper restore RedundantAssignment

                        Warning[] warnings = null;
                        string[] streamids = null;
                        string wordDocName = args.PDFFileName.ToLower().Replace(".pdf", ".doc");
                        byte[] bytes= report.Render("WORD", null, out mimeType, out encoding, out extension, out streamids, out warnings);
                        using (var fs = new FileStream(wordDocName, FileMode.Create))
                        {
                            fs.Write(bytes, 0, bytes.Length);
                        }
                        if (File.Exists(wordDocName))
                            System.Diagnostics.Process.Start(wordDocName);

//My code to print RDLC locally:

                        var rds = new ReportDataSource("DataSet1", args.ReportData);
                        var report = new LocalReport
                            {
                                ReportEmbeddedResource = string.Format("DRC.RDLC.{0}.rdlc", args.ReportName)
                            };

                        report.DataSources.Add(rds);
                        var reportPrintDoc = new ReportPrintDocument(report)
                            {
                                PrinterSettings = {PrinterName = Properties.Settings.Default.DRC_Printer}
                            };

                        reportPrintDoc.Print();