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.
+-+Microsoft+Visual+Studio+(Administrator)_2012-12-29_16-34-09.png)
Step #2 Create basic RDLC report without using the wizard.
- Add new item, select reporting template, select Report.
- In report data panel, select new dataset
- Select data source
- Select table adapter
- Add table / grid to body of the report.
- Assign columns in the table / grid to fields from the tableAdapter field list
- Right-click in the report designer below the report body and add page header or page footer as needed.
- 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);
