?
Solved

SSRS - column headings as values

Posted on 2008-11-07
6
Medium Priority
?
507 Views
Last Modified: 2012-05-05
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,
0
Comment
Question by:aelliso3
  • 3
  • 3
6 Comments
 
LVL 11

Author Comment

by:aelliso3
ID: 22908122
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
 
LVL 27

Expert Comment

by:Zberteoc
ID: 22926546
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
 
LVL 27

Accepted Solution

by:
Zberteoc earned 2000 total points
ID: 22926561
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
Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

 
LVL 11

Author Comment

by:aelliso3
ID: 22926742
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
 
LVL 27

Expert Comment

by:Zberteoc
ID: 22926843
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
 
LVL 11

Author Comment

by:aelliso3
ID: 22929929
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question