none
RDLC LocalReport Render with SubReports and nested subreports RRS feed

  • Question

  • I am migrating a legacy ASP.NET web application + SSRS reports, to MVC 3 and I have elected to use "LocalReport.Render(" to produce PDF output which works very well.

    The first problem report is a job list - jobs between dates with staff and the equipment they need for the job. In SSRS the old system has a report "jobs", with "staff" sub-report and inside that an "equipment" sub-report, joined by parameters.

    Up until now I have created a oleDb TableAdaptor dataset and passed that to the reports I render.

       '
            ' POST: /Print/ReportCriteria
            <Authorize()> <HttpPost()>
            Function ReportCriteria(ByVal criteria As FormCollection) As ActionResult
        
                '# validate criteria first, based on soft-coded parameters
                DoValidation(CInt(criteria("ReportNumber")), criteria)
    
                '# go no further if criteria is invalid
                If ModelState.IsValid = False Then
                    '# get report criteria viewdata for view, but pass posted values, so data entered on page is kept... " , criteria) "
                    GetCriteria(CInt(criteria("ReportNumber")), criteria)
                    Return View()
                End If
    
                Dim deviceInfo As String
                Dim localReport As New LocalReport()
                Dim reportType As String = "PDF"
                Dim mimeType As String
                Dim encoding As String
                Dim fileNameExtension As String
                Dim pagelayout As String = String.Empty
                Dim templatename As String = String.Empty
                Dim ds As New DataSet
                Dim sourcename As String = String.Empty
                Dim warnings() As Warning
                Dim streams() As String
                Dim renderedBytes() As Byte
                Dim reportDataSource As ReportDataSource
    
                '# lookup report number and return pagelayout, template name and datasource from OleDbDataAdapter taken from soft-coded source
                RunReport(criteria, pagelayout, templatename, ds, sourcename)
    
                '# no data in table? Game over
                If ds.Tables("report").Rows.Count = 0 Then
                    ModelState.AddModelError("", "The report found no data - either the criteria had no match or there is no data for this report.")
                    GetCriteria(CInt(criteria("ReportNumber")), criteria)
                    Return View()
                End If
    
                localReport.ReportPath = Server.MapPath("~/Content/reports/" & templatename)
    
                reportDataSource = New ReportDataSource(sourcename, ds.Tables("report"))
    
                localReport.DataSources.Add(reportDataSource)
    
                Select Case pagelayout
                    Case "A4Landscape"
                        deviceInfo = _
                           "<DeviceInfo>" +
                           "  <OutputFormat>PDF</OutputFormat>" +
                           "  <PageWidth>297mm</PageWidth>" +
                           "  <PageHeight>210mm</PageHeight>" +
                           "  <MarginTop>20mm</MarginTop>" +
                           "  <MarginLeft>10mm</MarginLeft>" +
                           "  <MarginRight>10mm</MarginRight>" +
                           "  <MarginBottom>20mm</MarginBottom>" +
                           "</DeviceInfo>"
    
                    Case Else '# A4Portrait
                        deviceInfo = _
                           "<DeviceInfo>" +
                           "  <OutputFormat>PDF</OutputFormat>" +
                           "  <PageWidth>210mm</PageWidth>" +
                           "  <PageHeight>297mm</PageHeight>" +
                           "  <MarginTop>20mm</MarginTop>" +
                           "  <MarginLeft>10mm</MarginLeft>" +
                           "  <MarginRight>10mm</MarginRight>" +
                           "  <MarginBottom>20mm</MarginBottom>" +
                           "</DeviceInfo>"
                End Select
    
    
                ' //Render the report
                Try
                     renderedBytes = localReport.Render(reportType, deviceInfo, mimeType, encoding, fileNameExtension, streams, warnings)
    
                     Return File(renderedBytes, mimeType)
    
                Catch ex As Exception
    		'# report failures in friendly fashion...
                    Session("Error") = ex.Message
                    Session("ErrorInnerException") = ex.InnerException.ToString
                    Session("ErrorSource") = "Print preview report #" & criteria("ReportNumber").ToString
                    Session("ReturnUrl") = Url.Action("Index", "Print")
                    Return RedirectToAction("Problem", "Home")
    
                End Try
            End Function
    "RunReport", "GetCriteria" and "DoValidate" are private functions - the remarks spell out what they do.

    The questions I cannot find an answer to are:-

    (1) how to test if a report has sub-reports and

    (2) how to set the datasource on those sub-reports.

    Because this is a generic report handler it has to be soft-coded or at least easily adaptable. There doesn't appear to be a "localReport.SubReports" collection or any other obvious way of managing sub-reports.


    Microsoft User since DOS 3 and programmer of MS since Access 1.0

    Thursday, October 18, 2012 3:00 PM

Answers

  • Solution Found!

    Firstly I created three datasets for jobs, staff and equipment and bound all three to top-level report.

    I then created the two sub-reports. In each one I bound the dataset with the name "subreport1" and "subreport2" respectively.

    On the "jobs" report I opened the sub-report properties and added the parameter "job" bound to the job ID field.

    Then, in the properties of the Tablix on the "staff" sub-report I added a filter for "[job_id] = Parameters!Job.Value" and on the sub-report embedded in "staff" I added parameter for "staff" bound to the staff ID field.

    The filter for the Tablix in the "equipment" sub-report was added, so "[staff_id] = Parameters!Staff.Value".

    That done, on to the code...

         localReport.ReportPath = Server.MapPath("~/Content/reports/" & templatename)
    
                '# get page layout from template and convert to device info string...
                deviceInfo = PageSize(localReport.GetDefaultPageSettings)
    
                reportDataSource = New ReportDataSource(sourcename, ds.Tables("report"))
              
                localReport.DataSources.Add(reportDataSource)
    
                Select Case CInt(criteria("ReportNumber"))
                    Case 3 '# has two subreports, nested..
                        reportDataSource = New ReportDataSource("subreport1", ds.Tables("subreport1"))
                        localReport.DataSources.Add(reportDataSource)
                        reportDataSource = New ReportDataSource("subreport2", ds.Tables("subreport2"))
                        localReport.DataSources.Add(reportDataSource)
    
                        AddHandler localReport.SubreportProcessing, AddressOf subReportHandling
    
                End Select
    
                ' //Render the report
                Try
                    renderedBytes = localReport.Render(reportType, deviceInfo, mimeType, encoding, fileNameExtension, streams, warnings)
    
                    ' //Response.AddHeader("content-disposition", "attachment; filename=NorthWindCustomers." + fileNameExtension);
                    Return File(renderedBytes, mimeType)
    
                Catch ex As Exception
                    Session("Error") = ex.Message
                    Session("ErrorInnerException") = ex.InnerException.ToString
                    Session("ErrorSource") = "Print preview report #" & criteria("ReportNumber").ToString
                    Session("ReturnUrl") = Url.Action("Index", "Print")
                    Return RedirectToAction("Problem", "Home")
    
                End Try

    I replaced the hard-coded pages sizes with:-

       Private Function PageSize(ByVal pagesettings As ReportPageSettings) As String
                Dim devinfo As New StringBuilder
    
                '# page units are in 1/100 inch (0.254mm)
    
                devinfo.Append("<DeviceInfo>")
                devinfo.Append("  <OutputFormat>PDF</OutputFormat>")
                If pagesettings.IsLandscape Then
                    devinfo.Append("  <PageWidth>" & CInt(pagesettings.PaperSize.Height * 0.254).ToString & "mm</PageWidth>")
                    devinfo.Append("  <PageHeight>" & CInt(pagesettings.PaperSize.Width * 0.254).ToString & "mm</PageHeight>")
                Else
                    devinfo.Append("  <PageWidth>" & CInt(pagesettings.PaperSize.Width * 0.254).ToString & "mm</PageWidth>")
                    devinfo.Append("  <PageHeight>" & CInt(pagesettings.PaperSize.Height * 0.254).ToString & "mm</PageHeight>")
                End If
                 devinfo.Append("  <MarginTop>" & CInt(pagesettings.Margins.Top * 0.254).ToString & "mm</MarginTop>")
                devinfo.Append("  <MarginLeft>" & CInt(pagesettings.Margins.Left * 0.254).ToString & "mm</MarginLeft>")
                devinfo.Append("  <MarginRight>" & CInt(pagesettings.Margins.Right * 0.254).ToString & "mm</MarginRight>")
                devinfo.Append("  <MarginBottom>" & CInt(pagesettings.Margins.Bottom * 0.254).ToString & "mm</MarginBottom>")
                devinfo.Append("</DeviceInfo>")
    
                Return devinfo.ToString
            End Function

    The sub-report processing event procedure is also soft-coded, by looping the data source names in the report (which is why the parent report has all three) the loop can refresh them all.

     Private Sub subReportHandling(ByVal sender As Object, ByVal e As Microsoft.Reporting.WebForms.SubreportProcessingEventArgs)
                Dim rds As ReportDataSource
                '# refresh the datasources using their existing name for the datasource name and also the dataset tablename...
                '# ie. on report, dataset name is "subreport1" and table name in dataset is also "subreport1"
                For Each n As String In e.DataSourceNames
                    rds = New ReportDataSource(n, ds.Tables(n))
                    e.DataSources.Add(rds)
                Next n
            End Sub

    The dataset variable is shared

      Shared ds As New DataSet
    My "RunReport" procedure populates the necessary tables in the dataset, depending on the report being run.
           Private Sub RunReport(ByVal criteria As FormCollection, ByRef template As String, ByRef rs As DataSet, ByRef rptdatasource As String)
     
     	Dim sql As New StringBuilder
      	Dim cons As String = "Provider=SQLOLEDB;" & System.Configuration.ConfigurationManager.AppSettings.Get("MyConnString") '# get SQL connection string from web.config
      	Dim con As New OleDb.OleDbConnection(cons)
      	Dim dp As New OleDbDataAdapter
    
           	rs = New DataSet
    
    	Select Case CInt(criteria("ReportNumber"))
    		Case 3
    			rptdatasource = "report"
    			sql = New StringBuilder
        			sql.Append(" SELECT  id, job_id, forename, surname, job_title FROM staff") 		    
        			dp = New OleDbDataAdapter(sql.ToString, con)
        			dp.Fill(rs, "subreport1")
    
        			sql = New StringBuilder
        			sql.Append(" SELECT  id, staff_id, equipment_desc FROM equipment")
        			dp = New OleDbDataAdapter(sql.ToString, con)
        			dp.Fill(rs, "subreport2")
    
        			sql = New StringBuilder
        			sql.Append("SELECT  id, job_name, job_date FROM jobs")
        			sql.Append(" WHERE  job_date = " & criteria("date1").ToString)
                     
        			dp = New OleDbDataAdapter(sql.ToString, con)
        			dp.Fill(rs, "report")
    			
    			template = Server.MapPath("~/Content/reports/") & "003-job-report.rdlc"
    
    
    	End Select
        	dp.Dispose()
            dp = Nothing
            con.Dispose()
            con = Nothing
    
          End Sub



    Microsoft User since DOS 3 and programmer of MS since Access 1.0

    • Marked as answer by Anthony Hunt Friday, October 19, 2012 3:43 PM
    Friday, October 19, 2012 3:41 PM