We help IT Professionals succeed at work.
Get Started

SSRS - column headings as values

521 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,
Comment
Watch Question
CERTIFIED EXPERT
Commented:
This problem has been solved!
Unlock 1 Answer and 6 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE