vb.net 如何从 SQL Server Express 中的动态查询创建 RDLC 报告

How to create RDLC report from dynamic query in SQL Server Express

提问by gaurav somani

I can't create the rdlc report from this procedure, I'm using report viewer but when I select the procedure, it doesn't show any column names but my procedure returns 7 columns. How can I create the report please help ? I'm creating the report in vb.net

我无法从此过程创建 rdlc 报告，我正在使用报告查看器，但是当我选择该过程时，它不显示任何列名称，但我的过程返回 7 列。我如何创建报告请帮忙？我正在 vb.net 中创建报告

ALTER PROCEDURE Pr_getAcceptedOnDateReport
@date date
AS
SET FMTONLY OFF
DECLARE  @SQL varchar(MAX)
DECLARE  @antiHIV bit
DECLARE  @HBsAg bit
DECLARE  @IGMHBCore bit
DECLARE  @NAT bit
DECLARE  @Malaria bit
DECLARE  @TotalHBCore bit
DECLARE  @Syphilis bit
DECLARE  @HCV bit
DECLARE  @ICT bit
DECLARE  @DCT bit
DECLARE  @Antibody bit

 Select @antiHIV=[HIV1/2 screen Reactive],
@IGMHBCore=[IgM HBcore Reactive], 
@HBsAg=[HBsAg Screen Reactive],
@NAT= [NAT Reactive],
@Malaria=[Malaria Screen Reactive],
@TotalHBCore=[Total HBcore Reactive],
@Syphilis=[Syphilis Screen Reactive],
@HCV=[HCV screen Reactive],
@ICT=[ICT Positive],
@DCT= [DCT Positive],
@Antibody= [Antibody Screen Positive]
 from m_rejectionRules where deleted=0

 DECLARE @sql1 nvarchar(4000)    

 Select @sql1='Select t.donorid, t.donorname, t.sampleid, t.customid,t.bagtype,t.bagnumber, t.segmentno from ttidetail t, m_donor m 
where t.donorid=m.donorid 
AND  CONVERT(date, m.RDate) =''' + cast(@date as varchar(100)) + ''''

IF @antiHIV='True' 
BEGIN
SELECT @sql1 = @sql1 + ' AND t.antiHIV like ' + ''''+ 'Non%Reactive'+''''
END
IF @HBsAg='True' 
BEGIN
SELECT @sql1 = @sql1 + ' AND t.HBsAg like '+ ''''+ 'Non%Reactive'+''''
END
IF @IGMHBCore='True' 
BEGIN
SELECT @sql1 = @sql1 + ' AND t.IGM_antiHBC like '+ ''''+ 'Non%Reactive'+''''
END
IF @NAT='True' 
BEGIN
SELECT @sql1 = @sql1 + ' AND t.NAT_HIV1 like '+ ''''+ 'Non%Reactive'+''''
END
IF @Malaria='True' 
BEGIN
SELECT @sql1 = @sql1 + ' AND t.malariaScreen like '+ ''''+ 'Non%Reactive'+''''
END
IF @TotalHBCore='True' 
BEGIN
SELECT @sql1 = @sql1 + ' AND t.totalAnti_HBC like '+ ''''+ 'Non%Reactive'+''''
END

IF @Syphilis='True' 
BEGIN
SELECT @sql1 = @sql1 + ' AND t.SyphilisScreen like '+ ''''+ 'Non%Reactive'+''''
END


EXEC sp_executesql @sql1

回答by Juzer

Create a class which maps to your fields returned by your stored procedure

创建一个映射到您的存储过程返回的字段的类

Public Class ReportData

Property donorid AS Integer = 0
Property donorname as string = string.empty
Property sampleid as integer = 0 
Property customid as integer = 0
Property bagtype as string = string.empty
Property bagnumber as integer = 0
Property segmentno as integer = 0

End Class

Assuming that you have a function which returns a dataset using the above query, I will refer to it as ds

假设您有一个使用上述查询返回数据集的函数，我将其称为 ds

* MOST IMPORTANT *When creating your rdlc report instead of mapping to the storedprocdure map it to an object datasource and select the class you just created. Use the fileds in this class as fields for your report.

* 最重要 *创建 rdlc 报告而不是映射到存储过程时，将其映射到对象数据源并选择您刚刚创建的类。使用此类中的字段作为报告的字段。

In the button show report you can use the following code to display your report

在按钮显示报告中，您可以使用以下代码来显示您的报告

Private Sub ShowReport()
    Dim dsL As DataSet = New DataSet()
    dsL = GetReportData()  ' Function which will get the data from the SQL Query

    Dim rds As ReportDataSource = New ReportDataSource()
    rds.Name = "ReportDS" ' Change to what you will be using when creating an objectdatasource
    rds.Value = dsL.Tables(0)
    With rvReport   ' Name of the report control on the form
        .Reset()
        .ProcessingMode = ProcessingMode.Local
        .LocalReport.DataSources.Clear()
        .Visible = True
        .LocalReport.ReportPath = reportPath
        .LocalReport.DataSources.Add(rds)


        ' If you have any parameters you can pass them here
        Dim rptParameters As New List(Of ReportParameter)()
        With rptParameters
            .Add(New ReportParameter("Title",
                String.Format("{0} Grid For Period {1} To {2}",
                    gridType,
                    FormatDateTime(startDate, DateFormat.ShortDate),
                    FormatDateTime(endDate, DateFormat.ShortDate))))
            .Add(New ReportParameter("SubTitle", String.Format("Program: {0}", ucProgram.Text)))
            .Add(New ReportParameter("StartDate", startDate))
            .Add(New ReportParameter("EndDate", endDate))
        End With
        .LocalReport.SetParameters(rptParameters)

        .RefreshReport()
    End With

End Sub