February 7, 2011 at 9:09 am
Hi
I have created a report within BIDS to show a list of users and which folder they have permissions to on the SSRS site.
Here is the coding I have
select
dbo.Users.UserName,dbo.Roles.RoleName,substring(dbo.catalog.path,2,LEN(dbo.catalog.path)) FolderName
from dbo.PolicyUserRole
left join dbo.Users on dbo.Users.UserID=dbo.PolicyUserRole.UserID
left join dbo.Roles on dbo.Roles.RoleID=dbo.PolicyUserRole.RoleID
inner join dbo.Catalog on dbo.Catalog.PolicyID=dbo.PolicyUserRole.PolicyID
where TYPE=1
I have grouped on UserName which gives me the infomation I need but it shows me the UserName,then a list of folders they have access which is fine its the role which is giving me problems, for each folder it is showing me each permission as in content manager, my reports, report builder etc, I would prefer it to be a list across seperated by commas rather than a list down as it shows me for example.
User       Folder       Role
JBloggs   Accounts Report builder
          Accounts Content Manager
          Accounts My Reports
Where I want it to show :
User     Folder     Role
JBloggs Accounts   Report Builder, Content Manager, My Reports
          Production  Report Builder, Content Manager
February 7, 2011 at 9:24 am
Look at Pivot or search for crosstab queries. That's what you are trying to do.
Note that these aren't very efficient, so as long as this isn't a large set of data, it works well. Otherwise, it can be a problem.
February 9, 2011 at 2:05 am
Thanks
There isnt that much data, put Im not sure how to do Crosstab queries.
Ive had a google on how to create them but they are all for summing number values rather than text values.
Any advice on how I would write this??
Thanks
R
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply