SSRS Dynamic columns for different Year groups

Question

SSRS Dynamic columns for different Year groups

spboutput

Old Hand

Points: 337
More actions
March 9, 2021 at 11:29 am

#3855437
Hi,

I have sample data, please can someone help me create the output based on the following data? So asically in SSRS when year 6 is selected it should populate the output below for Year 6, Year 7 populate the output for Year 7.
```
CREATE TABLE Data 
(
StudentId int, 

intYear int,
,subject varchar(50)
, Flag varchar(2)
)
INSERT INTO Data 
VALUES (1,6, 'Art', 'Y'),
(1, 6,'Chemistry','Y'),
(2,6,'Art','Y'),
(3, 6,'Art','Y'),
(3, 6,'Chemistry','y'),
(3, 7,'Art','Y'),
(1, 7,'Art','Y'),
(2,7,’Bio’,’Y’)
```
Output for Year 6:

I will have to generate the columns for Year 7 too, which has different subjects?

Please help.

Thank you

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

callen-557064 SSC-Addicted Points: 460 More actions · Answer 1

This question may be oversimplifying your need but why not use the matrix report in SSRS which automatically creates the column headings based on the data?

Also, what is the point of having the categories as rows and columns?

bfoster-764881 Mr or Mrs. 500 Points: 549 More actions · Answer 2

I think this will produce the summary numbers that you seek. Maybe there's a better way that I haven't thought of yet because this solution uses bit masking.

WITH cteSubject AS (

-- give each subject a bit value so bit masking can be applied

select
[subject],
power(2, row_number() over (order by [subject]) - 1) as SubjectBitMask
from
[Data]
group by
[subject]
)
, cteSubjectCombinations AS (

-- get a bit mask for all the possible two-subject combinations

select
s1.[subject] as [row],
s2.[subject] as [col], 
s1.SubjectBitMask | s2.SubjectBitMask as ComboSubjectBitMask
from
cteSubject as s1
cross join cteSubject as s2
)
, cteStudent AS (

-- get a subject bit mask for each student-year 

select 
d.StudentId,
d.intYear,
sum(s.SubjectBitMask) as StudentSubjectBitMask
from 
dbo.[Data] as d
inner join cteSubject as s on s.[subject] = d.[subject]
group by
d.StudentId,
d.intYear
)
select
d.intYear,
s.[row],
s.[col],
count(*) as total
from
cteStudent as d
inner join cteSubjectCombinations as s on s.ComboSubjectBitMask = (s.ComboSubjectBitMask & d.StudentSubjectBitMask)
group by 
d.intYear,
s.[row],
s.[col]