Tuesday, December 17, 2019

SSRS: How to export multiple RDLC report in a single excel with multiple spreadsheet

In this blog, we will discuss how to generate a single word or excel file from multiple RDLC report by using sub report control.

A sub report is a report control which is used to display another report inside a main report, it helps to re-use the existing RDLC report

Here are few basic steps to create a single excel file with multiple spreadsheet from multiple RDLC reports

Steps:
1.    Create a main RDLC report – Summary.RDLC :

SSRS RDLC Report

2.    Add Rectangle Table Control :

SSRS Add  Rectangle Control

3.    Add Sub-Report control inside rectangle control and include the existing RDLC report ( Child1.rdlc & Child2.rdlc) :

SSRS  Add Sub-Report control


4.    Set Page Break Option for each rectangle control :
a.    Break Location  -  (End option as shown below )

SSRS - RDLC Report Page Break


b.    Page Name (Excel Spreadsheet Name : Child1)

SSRS - RDLC Report Page Name

5.    Set Data For Child1 and Child2 rdlc report :

There is a SubreportProcessingEventHandler event, which is getting fired for each sub reports, what Main RDLC report have. It means, if Main Report have two sub Report, it is getting called two times

In event handler, we can easily bind data source for each sub-reports.

Here is an example, how to bind the DataSource for two child reports :

        viewer.LocalReport.SubreportProcessing +=
        new SubreportProcessingEventHandler(exampleSubreportProcessingEventHandler);
        void exampleSubreportProcessingEventHandler(object sender, SubreportProcessingEventArgs e)
        {
           if (e.DataSourceNames.FirstOrDefault() == "dsChild1")
            {
               e.DataSources.Add(new ReportDataSource("dsChild1", itemCollection1));                    
            }
           if (e.DataSourceNames.FirstOrDefault() == "dsChild2")
           {                     
               e.DataSources.Add(new ReportDataSource("dsChild2", itemCollection2));
           }
       }

6.    Generate Summary report :

Run the below code to generate Summary.xlsx report, which contain two sheet (child1 and Child2 as shown below)

        static void Main(string[] args)
        {        
            MemoryStream memoryStream = GenerateReport();
            WriteFile("Summary.xlsx", memoryStream);   
        }

       public static MemoryStream GenerateReport()
        {
            // Variables
            Microsoft.Reporting.WinForms.Warning[] warnings;
            string[] streamIds;
            string mimeType = string.Empty;
            string encoding = string.Empty;
            string extension = string.Empty;

            string deviceInfo =
          "" +
          "  PDF" +
          "
";

            // Setup the report viewer object and get the array of bytes
            ReportViewer viewer = new ReportViewer();
            try
            {
                string fileDownloadType = "EXCELOPENXML";            

                string exeFolder = System.AppDomain.CurrentDomain.BaseDirectory;
                string reportPath = Path.Combine(exeFolder, string.Format(@"Summary.rdlc"));

                viewer.ProcessingMode = ProcessingMode.Local;
                viewer.LocalReport.ReportPath = reportPath;

                List itemCollection1 = new List
                {
                     new Item{ Name = "Rajeev"}
                };               

                List itemCollection2 = new List
                {
                     new Item{ Name = "Rajeev Tiwari"},
                     new Item{ Name = "Shyam Tiwari"}
                };

                // Set Sub-reports Dataset
                viewer.LocalReport.SubreportProcessing +=
                new SubreportProcessingEventHandler(exampleSubreportProcessingEventHandler);
                void exampleSubreportProcessingEventHandler(object sender, SubreportProcessingEventArgs e)
                {
                   
                    e.DataSources.Add(new ReportDataSource("dsChild1", itemCollection1));
                    e.DataSources.Add(new ReportDataSource("dsChild2", itemCollection2));
                }

                viewer.LocalReport.Refresh();

                byte[] bytes = viewer.LocalReport.Render(fileDownloadType, deviceInfo, out mimeType, out encoding, out extension, out streamIds, out warnings);
                return new MemoryStream(bytes);
            }
            catch (Exception ex)
            {
                throw;
            }
            finally
            {
                viewer.LocalReport.Dispose();
                viewer = null;
            }
        }

protected static void WriteFile(string filename, MemoryStream notice)
        {
            try
            {
                using (FileStream file = new FileStream(filename, FileMode.Create))
                {
                    notice.WriteTo(file);
                    file.Close();
                    notice.Dispose();
                    notice = null;
                }
            }
            catch (Exception ex)
            {

                throw ex;
            }

        } 

No comments:

SQL Server - Identify unused indexes

 In this blog, we learn about the index usage information (SYS.DM_DB_INDEX_USAGE_STATS) and analyze the index usage data (USER_SEEKS, USER_S...