Model Item Security and Identifying Attributes in Report Builder

October 6, 2011

no comments

I found myself needing to define Model Item Security on a Report Builder 1.0 Model I did some time ago. I followed the guidelines detailed in the Model Item Security page (you can read more also about Securing Models). First of all, I would like to call to your attention that Model Item Security works only if your user isn’t a Content Manager or Publisher (as noted in this article about Security in Report Builder 1.0. Please note that it describes the process in the way it worked in SQL Server 2005, which has changed for SQL Server 2008 R2).  Also, as stated in the same article, you cannot create a subscription to a report which has Model Item Security in it (also mentioned in a post about Report Subscriptions by Bob Meyers).


Still, I found I had a problem with implementing the item security. I needed to show my users the total of files, but not the file ID. File ID is one of the Identifying Attributes of the entity.  The user interface in the Report Manager would only let me hide File ID (and subsequently all its derivatives) without stating that Total file ID should be shown. So, I thought that maybe if I make the automatically generated Total of files as an attribute all of its own (and not as a variation of file ID) then that should work. Not so. After I turned the property of VariationOf from “File ID” to “None” for the Total files I tried to deploy the Model and got:


The attempt to connect to the report server failed.  Check your connection information and that the report server is a compatible version.


Now the server was fine, but apparently disconnecting an automate aggregate from its source attribute can cause such an error message. I made it a variation of file ID again.
I also found that hiding an identifying attribute of an entity hides the entire entity(!) from the relevant user.


So as you may gather from the post, this caused me some woe… so the solution? As offered to me by our wonderful DBA Boris, I set the property of Hidden = True for the file Id and the Total of files. I then created two independent and unconnected expressions: one equal to file ID and one equal to Total of files. I could then hide each one independently, without causing me to hide the entire entity.



Now, I don’t know if you’ve noticed but this is me writing in 2011 about Report Builder 1.0 which was released around 2006. Report Builder 1.0 isn’t really supported nowadays by Microsoft as far as I know. But I keep working with it, and by blog statistics, you’re still reading about it quite a bit here!


So, it’s not my Birthday, but I still wanted to make a wish… if Microsoft is reading this (and I’m under the impression you are), then I would very much appreciate it if you could start developing us a wizard. You made a very simple wizard that converted SSAS cubes from 2005 to 2008 or 2008 R2. I would REALLY appreciate such a wizard from Report Builder 1.0 to a PowerPivot Model (or a Report Builder 4.0 if planned).

Add comment
facebook linkedin twitter email

Leave a Reply

Your email address will not be published.

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*