I have a stored procedure which returns Dynamic columns(using Pivot) I want to bind stored procedure with RDLC report

Question

I want to create RDLC report ,but the problem is that Columns in a table returned by the stored procedure are Dynamic, means Columns can be increased or decrease depends on the condition. I have Created procedure.

alter proc sp_GetEmpBranAndDesigWise(
@BranchId nvarchar(200),
@ZoneId nvarchar(200)
)

as
if 1=0 Begin
set FMTONLY  OFF End 
select  b.BranchName,b.BranchCity,z.ZoneName,b.BranchID,d.DesigName
,Count(d.DesigName) as TotalCount 
    INTO #RepTablTemp
    from Emp_File emp 
    INNER join Branch b on emp.BranchID=b.BranchID
    left join ZoneFile z on z.ZoneID=b.ZoneID
    INNER join SWHouse_Production.dbo.emp_DesigFile  d on emp.DesigID=d.DesigID

    where Convert(nvarchar(250), b.BranchID) like @BranchId
        and   Convert(nvarchar(250), b.ZoneID) like @ZoneId
    group by b.BranchName,z.ZoneName,b.BranchCity,b.BranchID,d.DesigName
    order by b.BranchName,z.ZoneName,d.DesigName

--select * from #RepTablTemp order by BranchName,DesigName

--select * from Emp_File where BranchID=1055

Begin
    DECLARE @cols AS NVARCHAR(MAX),
        @query  AS NVARCHAR(MAX)

    select @cols = STUFF((SELECT DISTINCT ',' + QUOTENAME(DesigName) 
                        from #RepTablTemp
                        group by  DesigName
                        --order by  1
                FOR XML PATH(''), TYPE
                ).value('.', 'NVARCHAR(MAX)') 
            ,1,1,'')
            print @cols
    set @query = N'SELECT BranchName,ZoneName,BranchCity,' + ISNULL(@cols,'0') + N' from 
                 (
                    select BranchName,ZoneName,BranchCity,TotalCount, DesigName
                    from #RepTablTemp
                ) x
                pivot 
                (
                    max(TotalCount)
                    for DesigName in (' + @cols + N')
                ) p '

    exec sp_executesql @query;
    select 0 AS mycol1int
    print @query
drop table #RepTablTemp
End

here Designations are dynamic and can be increased,i want this kind of result in RDLC enter image description here

In attached file highlighted fields are Designations coming from Designation Table.

Leandro Bardelli Leandro Bardelli · Accepted Answer · 2019-08-01T15:01:47

You can't do that. RDLC expects a fixed "object" for a table. You can pass a dynamic object, but this object must be match with the dataset provided to the table.

But you can use several tablix and show them or not (visibility condition) for an specific condition loaded by a parameter (for example, a kind of table do you want to see or hide).

I have a stored procedure which returns Dynamic columns(using Pivot) I want to bind stored procedure with RDLC report

1 Answers