Displaying data in CrossGroupHeaderBand from dynamicSQL cols

[JackC23]

I have a datasource with the following SQL code. (Test data included)

Code: Select all

CREATE TABLE #yt 
(
  [PupilID] int, 
  [WeekNo] int, 
  [IsInAttendance] int
);

INSERT INTO #yt
(
  [PupilID], 
  [WeekNo],
  [IsInAttendance]
)
VALUES
    (102, 1, 1),
    (102, 1, 1),
    (102, 1, 1),
    (102, 1, 1),
    (102, 1, 0),
    (102, 1, 1),
    (102, 1, 0),
    (102, 1, 1),
    (102, 2, 1),
    (102, 2, 1),
    (102, 2, 1),
    (102, 2, 1),
    (102, 2, 1),
    (102, 2, 1),
    (102, 2, 0),
    (102, 3, 1),
    (101, 3, 0),
    (101, 1, 1),
    (101, 1, 1),
    (101, 1, 1),
    (101, 1, 1),
    (101, 1, 1),
    (101, 6, 1),
    (101, 2, 1),
    (101, 4, 1),
    (101, 2, 1),
    (101, 3, 1),
    (101, 2, 0)

DECLARE @WkCols AS NVARCHAR(MAX),
        @WkQuery AS NVARCHAR(MAX)

select @WkCols = STUFF((SELECT ',' + QUOTENAME(WeekNo) 
                    FROM #yt
                    GROUP BY WeekNo
                    ORDER BY WeekNo
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @WkQuery = 'SELECT PupilID,' + @WkCols + ' FROM 
            (
                SELECT
                    T.PupilID,
                    T.WeekNo,
                    AttendancePercentage = SUM(T.IsInAttendance) * 100.0 / COUNT(1)
                FROM
                    #yt AS T
                GROUP BY
                    T.PupilID,
                    T.WeekNo
            ) x
            PIVOT 
            (
                MAX(AttendancePercentage)
                FOR WeekNo IN (' + @WkCols + ')
            ) p '

execute(@WkQuery);

How do i get a crossgroup or crossdata band to iterate over dynamically set columns?

Thank you.

[Lech Kulikowski]

Hello,

Sorry, maybe we did not exactly understand your question. Could you explain your issue in more details?

Thank you.

[JackC23]

Hi Lech,

I have attached the report template I am working on.

The datasource in question is 'WeeklyPct'.

I wish to display the results of this SQL query in GroupFooterBand1 in my CrossFooterBand but am unsure of the best way to do this with dynamically set columns.

Obviously I can't use the retrieve columns command because I don't know the column names ahead of time and I get the error 'Invalid object name '#CombinedAttClSe'.'

Thank you.

[Edward]

Hi Jack,

Thank you for the sample report and sample data. Please check the following topic, that describes how to get the columns for your datasource from code :
viewtopic.php?f=8&t=345&p=1195

Once the columns are obtained in the DataSource dynamically, you would need to build the required report template's components manually, like in the following project:
https://www.stimulsoft.com/en/samples/w ... in-runtime

Thank you,
Edward