How to pass array to rdlc report?

debugcn Published at Dev

Joe Yan

I provided a radcombobox to let user select one or more than one department to generate a report (developed by asp.net report (RDLC)).

I had developeded the following dataset:

SELECT     c.DeptID, c.Department, b.Course_Name, b.Course_ID, b.Type, b.Ref_Code, b.Exam
FROM         dbo.db_Competency_List AS a INNER JOIN
                      dbo.db_Course AS b ON a.Course_ID = b.Course_ID INNER JOIN
                      dbo.db_Department AS c ON a.Dept_ID = c.DeptID
where a.Dept_ID in (@Para_DID)

enter image description here

It works if user select one department only.

but if user select more than one department, it seems that the dataset cannot get the parameter. e.g. 12,33,65,78...

Code:

    <telerik:RadComboBox ID="rcb_select_dept" runat="server"  DataSourceID="LDS_ddl_dept" DataTextField="Department" AutoPostBack="True" 
           DefaultMessage="Please Select" DataValueField="DeptID" Width="300" CheckBoxes="true" 
           AllowCustomText="true" >
    </telerik:RadComboBox>
       <asp:LinqDataSource ID="LDS_ddl_dept" runat="server" 
           ContextTypeName="dcLRDBDataContext" EntityTypeName="" TableName="db_Departments" OrderBy="Department">
       </asp:LinqDataSource>
       </td>
   </tr>
   </table>

   <br />

   <rsweb:ReportViewer ID="ReportViewer1" runat="server" Width="1200px" 
                       Height="800px" Font-Names="Verdana" Font-Size="8pt" 
                       InteractiveDeviceInfos="(Collection)" WaitMessageFont-Names="Verdana" 
                       WaitMessageFont-Size="14pt">
                       <LocalReport ReportPath="Reports\template\RequiredByDepartment.rdlc">
                         <DataSources>
                              <rsweb:ReportDataSource DataSourceId="ObjectDataSource1" 
                                    Name="DS_Content" />           
                              </DataSources>
                       </LocalReport>
                   </rsweb:ReportViewer>
<br />

<asp:ObjectDataSource ID="ObjectDataSource1" runat="server" 
    OldValuesParameterFormatString="original_{0}" SelectMethod="GetData" 
    TypeName="LRDBDataSetTableAdapters.vReqByDeptTableAdapter">
    <SelectParameters>
                <asp:Parameter DefaultValue="0" Name="Para_DID" Type="String" />                        
    </SelectParameters>
</asp:ObjectDataSource>

Code behind:

Protected Sub rtbMenu_ButtonClick(ByVal sender As Object, ByVal e As Telerik.Web.UI.RadToolBarEventArgs) Handles rtbMenu.ButtonClick
    If e.Item.Value = "Generate" Then

        'Get DID
        Dim strCOM_cmb As String = ""

        For i = 0 To rcb_select_dept.CheckedItems.Count - 1
            If rcb_select_dept.CheckedItems(i).Checked = True Then
                strCOM_cmb += rcb_select_dept.CheckedItems(i).Value.ToString & ","
            End If
        Next
        If Left(strCOM_cmb, 1) = "," Then
            strCOM_cmb = Right(strCOM_cmb, Len(strCOM_cmb) - 1)
        End If
        If Right(strCOM_cmb, 1) = "," Then
            strCOM_cmb = Left(strCOM_cmb, Len(strCOM_cmb) - 1)
        End If
        strCOM_cmb = TrimList(strCOM_cmb)

        Dim params(0) As Microsoft.Reporting.WebForms.ReportParameter
        params(0) = New Microsoft.Reporting.WebForms.ReportParameter("Para_DID", strCOM_cmb)
        ReportViewer1.LocalReport.SetParameters(params)

        ObjectDataSource1.SelectParameters("Para_DID").DefaultValue = strCOM_cmb
        ObjectDataSource1.DataBind()

        Preview()
    End If
End Sub

Sub Preview()

    ReportViewer1.Visible = True
    ReportViewer1.LocalReport.Refresh()

End Sub

How can i pass the department ID array (parameter) to the report ?

thanks.

Alice

I would suggest you to handle this issue in SQL implementation. Just the optional.

To pass text parameter and hope it works as a list, you should represent them in table, after then join your query result to that list in the table. See code below:

Create new table variable and insert each member in the list into the table:

DECLARE @SelectedDeptIds table
(
    DeptID int
)

DECLARE @deptIdTemp varchar(10), @Pos int

SET @Para_DID = LTRIM(RTRIM(@Para_DID))+ ','
SET @Pos = CHARINDEX(',', @Para_DID, 1)

IF REPLACE(@Para_DID, ',', '') <> ''
BEGIN
        WHILE @Pos > 0
    BEGIN
        SET @deptIdTemp = LTRIM(RTRIM(LEFT(@Para_DID, @Pos - 1)))
        IF @deptIdTemp <> ''
        BEGIN
            INSERT INTO @SelectedDeptIds (DeptID) VALUES (CAST(@deptIdTemp AS int))
        END
        SET @Para_DID = RIGHT(@Para_DID, LEN(@Para_DID) - @Pos)
        SET @Pos = CHARINDEX(',', @Para_DID, 1)
    END
END

Next code is to join table to the list:

SELECT     c.DeptID, c.Department, b.Course_Name, b.Course_ID, b.Type, b.Ref_Code, b.Exam
FROM       dbo.db_Competency_List AS a INNER JOIN
           dbo.db_Course AS b ON a.Course_ID = b.Course_ID INNER JOIN
           dbo.db_Department AS c ON a.Dept_ID = c.DeptID
INNER JOIN @SelectedDeptIds d ON a.Dept_ID = d.DeptID

Collected from the Internet

Please contact [email protected] to delete if infringement.