SSRS - column headings as values

Below is an example of a table that controls the security settings. There are about 100 options in total.

TABLE1    -  ( 0 = Denied,   1 = Read Only,  2 = Full Access )
-----------------------------------------------------------------------------
Employee    Option1     Option2     Option3     Option4     Option5
John                 1               2                0               2               1
Tim                   2               2                2               2               2
Rick                  0               0                0               1               1
Steve                1               1               1               1               1
-----------------------------------------------------------------------------

I need to display the report like the example below:
REPORT
-----------------------------------------------------------------------------
Employee      FullAccess       ReadOnly        Denied
John             Option2             Option1            Option3
                     Option4             Option5        
Tim                Option1
                      Option2
                      Option3
                      Option4
                      Option5
Rick                                       Option4               Option1
                                              Option5               Option2
                                                                         Option3
Steve                                     Option1
                                              Option2
                                              Option3
                                               Option4
                                               Option5
-----------------------------------------------------------------------------

I have a view setup that is like the following:
-----------------------------------------------------------------------------

SELECT Employee,
      CASE WHEN ISNULL(Option1, 0) = 0 THEN 'Option1' + char(13) + char(10) ELSE '' END
      + CASE WHEN ISNULL(Option2, 0) = 0 THEN 'Option2' + char(13) + char(10) ELSE '' END
      + CASE WHEN ISNULL(Option3, 0) = 0 THEN 'Option3' + char(13) + char(10) ELSE '' END
      + CASE WHEN ISNULL(Option4, 0) = 0 THEN 'Option4' + char(13) + char(10) ELSE '' END
      + CASE WHEN ISNULL(Option5, 0) = 0 THEN 'Option5' + char(13) + char(10) ELSE ''
  END As Denied,

      CASE WHEN ISNULL(Option1, 0) = 1 THEN 'Option1' + char(13) + char(10) ELSE '' END
      + CASE WHEN ISNULL(Option2, 0) = 1 THEN 'Option2' + char(13) + char(10) ELSE '' END
      + CASE WHEN ISNULL(Option3, 0) = 1 THEN 'Option3' + char(13) + char(10) ELSE '' END
      + CASE WHEN ISNULL(Option4, 0) = 1 THEN 'Option4' + char(13) + char(10) ELSE '' END
      + CASE WHEN ISNULL(Option5, 0) = 1 THEN 'Option5' + char(13) + char(10) ELSE ''
  END As ReadOnly,

      CASE WHEN ISNULL(Option1, 0) = 2 THEN 'Option1' + char(13) + char(10) ELSE '' END
      + CASE WHEN ISNULL(Option2, 0) = 2 THEN 'Option2' + char(13) + char(10) ELSE '' END
      + CASE WHEN ISNULL(Option3, 0) = 2 THEN 'Option3' + char(13) + char(10) ELSE '' END
      + CASE WHEN ISNULL(Option4, 0) = 2 THEN 'Option4' + char(13) + char(10) ELSE '' END
      + CASE WHEN ISNULL(Option5, 0) = 2 THEN 'Option5' + char(13) + char(10) ELSE ''
  END As FullAccess
FROM TABLE1
-----------------------------------------------------------------------------

The problem is that when I create it the report like this, it exports as all one cell. This means that when you export it to something like PDF, it does not format correctly when it comes to page breaks. It always wants to start the table itself on a new page leaving a tremendous amount of white space within this report.

Any suggestions on how to change the view or the report so that it will be continuous?

thanks,
LVL 11
aelliso3Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

aelliso3Author Commented:
Hopefully not to be too confusing, but I've attached file, you'll notice the following:
 >> Page 1 is correct.
>> Page 2 started with the Group By field, and then skipped to the next page to start the cells.  This is becuase the field is so large, that it seems to want to start only on  a new page.
>> Page 3 is the remainder of Page 2
>> Page 4 shows some of the values from page 2 on the first 2 columns, then the remainder of the 3rd column
 

Example.pdf
0
ZberteocCommented:
Try the query bellow. Basically you need to union a select statement for each option you have. In the report you will use Employee to group your lines and show the Denied, Read Only and Full Access columns in details line.
select 
	employee,
	isnull(dn,'')	as Denied,
	isnull(ro,'')	as [Read Only],
	isnull(fa,'')	as [Full Acces]
from
(
	select 
		employee, 
		case when isnull(Option1,0)=0 then 'Option1' else NULL end as dn, 
		case when isnull(Option1,0)=1 then 'Option1' else NULL end as ro,
		case when isnull(Option1,0)=2 then 'Option1' else NULL end as fa 
	from 
		permiss union
	select 
		employee, 
		case when isnull(Option2,0)=0 then 'Option2' else NULL end as dn, 
		case when isnull(Option2,0)=1 then 'Option2' else NULL end as ro, 
		case when isnull(Option2,0)=2 then 'Option2' else NULL end as fa 
	from 
		permiss union
	select 
		employee, 
		case when isnull(Option3,0)=0 then 'Option3' else NULL end as dn, 
		case when isnull(Option3,0)=1 then 'Option3' else NULL end as ro, 
		case when isnull(Option3,0)=2 then 'Option3' else NULL end as fa 
	from 
		permiss union
	select 
		employee, 
		case when isnull(Option4,0)=0 then 'Option4' else NULL end as dn, 
		case when isnull(Option4,0)=1 then 'Option4' else NULL end as ro, 
		case when isnull(Option4,0)=2 then 'Option4' else NULL end as fa 
	from 
		permiss union
	select 
		employee, 
		case when isnull(Option5,0)=0 then 'Option5' else NULL end as dn, 
		case when isnull(Option5,0)=1 then 'Option5' else NULL end as ro, 
		case when isnull(Option5,0)=2 then 'Option5' else NULL end as fa 
	from 
		permiss --union
	-- the rest of the option here
) prm

Open in new window

0
ZberteocCommented:
I added the order by clause:
select 
	employee,
	isnull(dn,'')	as Denied,
	isnull(ro,'')	as [Read Only],
	isnull(fa,'')	as [Full Acces]
from
(
	select 
		employee, 
		case when isnull(Option1,0)=0 then 'Option1' else NULL end as dn, 
		case when isnull(Option1,0)=1 then 'Option1' else NULL end as ro,
		case when isnull(Option1,0)=2 then 'Option1' else NULL end as fa 
	from 
		permiss union
	select 
		employee, 
		case when isnull(Option2,0)=0 then 'Option2' else NULL end as dn, 
		case when isnull(Option2,0)=1 then 'Option2' else NULL end as ro, 
		case when isnull(Option2,0)=2 then 'Option2' else NULL end as fa 
	from 
		permiss union
	select 
		employee, 
		case when isnull(Option3,0)=0 then 'Option3' else NULL end as dn, 
		case when isnull(Option3,0)=1 then 'Option3' else NULL end as ro, 
		case when isnull(Option3,0)=2 then 'Option3' else NULL end as fa 
	from 
		permiss union
	select 
		employee, 
		case when isnull(Option4,0)=0 then 'Option4' else NULL end as dn, 
		case when isnull(Option4,0)=1 then 'Option4' else NULL end as ro, 
		case when isnull(Option4,0)=2 then 'Option4' else NULL end as fa 
	from 
		permiss union
	select 
		employee, 
		case when isnull(Option5,0)=0 then 'Option5' else NULL end as dn, 
		case when isnull(Option5,0)=1 then 'Option5' else NULL end as ro, 
		case when isnull(Option5,0)=2 then 'Option5' else NULL end as fa 
	from 
		permiss --union
	-- the rest of the option here
) prm
order by 
	employee,
	case when dn is null then 'zzz' else dn end,
	case when ro is null then 'zzz' else dn end,
	case when fa is null then 'zzz' else dn end

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

aelliso3Author Commented:
That about the same that I was able to come up with today and I believe that it's about the best that I am going to get ... I wish that I was able to put them side by side, but I know that this is more difficult becuase of the relationship of the columns vs. rows.
I'll use this one and see if I can format the report with 3 subreports to get it finished up.
Thanks a million,
 
0
ZberteocCommented:
You could do that but only using temptables so you would need to put everything in a stored procedure and use it as datasource for the report.
0
aelliso3Author Commented:
Well, I ended out with a sproc that started with finding all employee name and using them to create 4 temp tables, (AllPermissions, FullAccess, ReadOnly, and Denied) with these 4 tables I used them to update the employee table using a cursor line by line to fill in the 3 permission type columns.
The query has gone from <1 second to about 6 seconds, but it only has to be run once a month for auditing ... (I guess it's a take your pick ... pretty or fast)
Thanks for all your help
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
DB Reporting Tools

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.