Hack for multicolumn sub reports.

[Henry Derstine]

I was working on a report that had a sub report that displayed in multiple columns. From what I understand Telerik Reports do not currently support multiple columns in sub reports so I came up with this hack.

1)      Create master report

2)      Create a sub report for as many columns you need

3)      For the sub reports you will need to return a rownumber value. From that you can use the modulus function to filter what is displayed. For example:

 Report1 filter would be

Me.Filters.AddRange(New Telerik.Reporting.Data.Filter() {New Telerik.Reporting.Data.Filter("= Fields.RowNumber Mod 2", Telerik.Reporting.Data.FilterOperator.Equal, "0")})

        Report2 filter would be

Me.Filters.AddRange(New Telerik.Reporting.Data.Filter() {New Telerik.Reporting.Data.Filter("= Fields.RowNumber Mod 2", Telerik.Reporting.Data.FilterOperator.NotEqual, "0")})

This splits the sub report columns to odds and even based on row count.

I wanted to make the solution a little more robust and move some processing to the server so I ended up using the stored procedure below. You pass in the total number of columns and which column the call is being made from. For example for a two columns-

subreport1 would pass @NumberofColumns=2, @ColumnNumber=1, @MultiColumn=True
subreport2 would pass @NumberofColumns=2, @ColumnNumber=2, @MultiColumn=True

I hope this helps and if anyone has ideas on improving this please post them.

CREATE PROCEDURE [dbo].[NET_sp_PullBuildingSubDept2]

(

      @BuildingIdnt int=0,

      @NumberofColumns int=0,

      @ColumnNumber int=0,

      @MultiColumn bit =0

)

AS

BEGIN

      SET NOCOUNT ON;

If (@MultiColumn =0)

BEGIN

      SELECT    REPLACE(REPLACE(EntityName,'(COM)',''),'(RES)','') as EName, EntityName as EntityName, DepartmentType, PoliticalSubdivision, ApprovalDate, BuildingIdnt,Row_Number() OVER (ORDER BY EntityName) as RowNumber

      from dbo.NET_vw_Building

      where PrimaryBuildingIDnt=@BuildingIdnt

      order by entityname

END

ELSE

BEGIN

      If (@ColumnNumber =@NumberofColumns)

      BEGIN

            Set @ColumnNumber =0

      END

      ;With MyCTE as

      (

      SELECT    REPLACE(REPLACE(EntityName,'(COM)',''),'(RES)','') as EName, EntityName as EntityName, DepartmentType

      , PoliticalSubdivision, ApprovalDate, BuildingIdnt

      ,Row_Number() OVER (ORDER BY EntityName) as RowNumber

      from dbo.NET_vw_Building

      where PrimaryBuildingIDnt=@BuildingIdnt

      )

      SELECT EName, EntityName, PoliticalSubdivision, ApprovalDate, BuildingIdnt, RowNumber

      FROM MyCTE

      WHERE RowNumber %  @NumberofColumns = @ColumnNumber

      order by entityname

END

END

GO

[David A.]

Henry, this was extremely helpful as I needed the ability to display multiple columns in a subreport.  Using your logic, I adapted your logic to come up with this:

srChecklist1.ReportSource = new SRAppointmentChecklist() { Name= "Checklist1", DataSource = ds.TaskChecklistItems.Select("Index % 2 = 1", "Index")};

srChecklist2.ReportSource = new SRAppointmentChecklist() { Name = "Checklist2", DataSource = ds.TaskChecklistItems.Select("Index % 2 = 0", "Index") };

I am using two columns and populated a column named "Index" with a incremental value. 

Thank you for sharing this!
David.