SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Adam Aspin / Centralising Reporting Services Stylesheets / Latest Posts

RE: Centralising Reporting Services Stylesheets

nyquist212 — Thu, 05 Jul 2012 11:45:39 GMT

Unfortunately, I am encountering the same problem...It seems my namespace is not declared, despite setting up the Assembly Reference as instructed.

RE: Centralising Reporting Services Stylesheets

NashVegas — Thu, 10 May 2012 15:05:24 GMT

Hi,I've been trying to get this to work for days but I keep getting the permissions error that you all talked about. I've added all the suggested code (modified the rssrvpolicy.config file, updated the DynamicStyleLibrary and gave it "Full Trust"). Nothing works. Still get this error:Request for the permission of type 'System.Security.Permissions.SecurityPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.Can anyone help? Thanks,BethP.S.I am NOT a developer. I know nothing about assemblies, but have had a developer look at this with me...we're both stumped.

RE: Centralising Reporting Services Stylesheets

Adam Aspin — Mon, 26 Mar 2012 08:53:00 GMT

Very nice! Thank you for suggesting this. It resolves the core weakness of the technique.Regards,Adam

RE: Centralising Reporting Services Stylesheets

szumkhawala — Mon, 26 Mar 2012 08:20:31 GMT

Thanks a lot for tis code. I pretty much used as is, apart from one issue - This was the fact that the the database was hit too many times. for example for a matrix of substantial cells the database will be hit everytime a cell needs to be formatted.To overcome that I used a a shared list of reportStyles that could be used between instances of the datadrivenstyle library. See below (following 2 lines are key - Private Shared reportStyles As List(Of ReportStyle) & If reportStyles Is Nothing Then):Imports System.DataImports System.Data.SqlClientImports System.SecurityPublic Class DataDrivenStyleLibrary 'Declare shared item that can be shared between instances Private Shared reportStyles As List(Of ReportStyle) Public Shared Function dbStyle(ByVal ReportSection As String, ByVal StyleType As String) As String Dim sStyle As String sStyle = "" 'Check to see if reportstyles list exists already created and if it does not then create it If reportStyles Is Nothing Then reportStyles = New List(Of ReportStyle) ' Set up the command and connection objects Dim MyConnection As SqlConnection MyConnection = New SqlConnection("Data Source=IMGSERVER17; Initial Catalog=ReportServer; Integrated Security=SSPI;") Dim cmd As New SqlCommand cmd.Connection = MyConnection cmd.Connection.Open() cmd.CommandType = CommandType.StoredProcedure cmd.CommandText = "pr_GetStyle" ' Read the data, and populate report style list Dim ddlValues As SqlDataReader ddlValues = cmd.ExecuteReader() While ddlValues.Read Dim rStyle = New ReportStyle() rStyle.ReportSection = ddlValues.GetString(0) rStyle.StyleType = ddlValues.GetString(1) rStyle.StyleDefinition = ddlValues.GetString(2) reportStyles.Add(rStyle) End While 'Close any open connections cmd.Connection.Close() ' return the selected value to the calling proceure End If 'Loop up particular style from object For Each rs In reportStyles If rs.ReportSection = ReportSection And rs.StyleType = StyleType Then sStyle = rs.StyleDefinition Exit For End If Next Return sStyle End FunctionEnd ClassFriend Class ReportStyle Private sType As String Private rSection As String Private sDefinition As String Public Property StyleType() As String Get Return sType End Get Set(ByVal value As String) sType = value End Set End Property Public Property ReportSection() As String Get Return rSection End Get Set(ByVal value As String) rSection = value End Set End Property Public Property StyleDefinition() As String Get Return sDefinition End Get Set(ByVal value As String) sDefinition = value End Set End PropertyEnd Class

RE: Centralising Reporting Services Stylesheets

Sinh Dinh — Mon, 18 Apr 2011 23:05:50 GMT

Is anyone experiencing slow export to excel performance using this? Exporting to csv is very fast, but it can take minutes to export to excel.

RE: Centralising Reporting Services Stylesheets

gin4lyfe — Wed, 08 Dec 2010 12:27:45 GMT

A blog post I just wrote about how to set up the DLL to use an external XML file:http://thinkdevcode.blogspot.com/It goes over all of the security settings you need to get it to work. Comment on it if you have any problems/questions.

RE: Centralising Reporting Services Stylesheets

M Chabot — Sun, 30 May 2010 16:18:01 GMT

Does anyone know of a good way to avoid hard-coding the database connection string?MyConnection = New SqlConnection("server=localhost;uid=USERNAME;pwd=PASSWORD;database=RSStyles")It would be preferable to make use of an existing shared database connection, or have this database connection information stored in a config file, since it could well be the case that the connection string is different in dev and production.

RE: Centralising Reporting Services Stylesheets

jcrawf02 — Thu, 11 Feb 2010 08:22:37 GMT

For those of you struggling, I was excited about Adam's solution, but then I ended up going with an alternate approach found here ([url=http://www.simple-talk.com/sql/reporting-services/reporting-services-with-style/]http://www.simple-talk.com/sql/reporting-services/reporting-services-with-style/[/url])mainly because it looked simpler from an "I don't know what I'm doing" standpoint.:-DSeems to work well also.

RE: Centralising Reporting Services Stylesheets

rene-500237 — Fri, 13 Nov 2009 02:27:08 GMT

Hi chikako wakishima,have you done the things I told , add the SQLClientpermission and codegroup. You have to do all of these to get the assambly running on RS.Regards

RE: Centralising Reporting Services Stylesheets

chikako wakishima — Thu, 12 Nov 2009 10:43:25 GMT

Hi, Thank for this article. It was very helpful for understanding adding assemblies to reporting services.However, I have run into a problem. Everything worked like charm, in a report, I changed background-color of to the value I got from the the assembly created..etc until I tried to connect to a database and get the value from there. And the reporting services just displays #ErrorThe function that's connect to database is working fine. The reason is b/c I created Windows App that calls this function actually receives value from DB by calling this function.The problem happens only when I call this from Reporting Services report. I'm thinking it's some kind of security issue?I hard-coded connection string to db in the function. basically, I used the exact same code as you posted in this article. I just changed values of connection string.I appreciate any help or any response.

RE: Centralising Reporting Services Stylesheets

rene-500237 — Mon, 02 Nov 2009 06:30:16 GMT

:-D Hi all,I could fix my permission problem. I add the following code to the assembly:[code="vb"]Imports System.Security.PermissionsPublic Class DataDrivenStyleLibrary Public Shared Function dbStyle(ByVal inputStyle As String, ByVal InputStyleType As String) As String Dim permission As SqlClientPermission = New SqlClientPermission(PermissionState.Unrestricted) permission.Assert() sStyle = ""[/code]I found the trick here [url]http://softwareexpertsolutions.blogspot.com/2007/06/creating-and-deploying-reporting.html[/url]I added in rssrvpolicy.config my code group like:[code="xml"]<CodeGroup class="UnionCodeGroup" version="1" PermissionSetName="FullTrust" Name="DynamicStyleLibrary" Description="Dynamic Reports"> <IMembershipCondition class="UrlMembershipCondition" version="1" Url="C:\Program Files\Microsoft SQL Server\MSSQL.X\Reporting Services\ReportServer\bin\DynamicStyleLibrary.dll"/></CodeGroup>[/code]I restarted IIS & RS .I hope this will solve the other issue as well.Many thanks for this great blog.Regards,René

RE: Centralising Reporting Services Stylesheets

rene-500237 — Fri, 30 Oct 2009 09:56:50 GMT

Hi all,I do have the same problem with the security of the dll in ReportServer. :crazy:Are there some good resource who know the solution?RegardsRené

RE: Centralising Reporting Services Stylesheets

ed.godshaw — Thu, 29 Oct 2009 04:53:17 GMT

Just a word of advice (after two days of trial and error!) you need to edit C:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies\RSPreviewPolicy.config to grant the assembly the correct security permisions in order to get the code in the tutorial to work correctly.Just add in a new code group with the url of your custom assembly and give it 'FullTrust' permissions. The System.Security.Permissions failed error.

RE: Centralising Reporting Services Stylesheets

ChromaticRanger — Tue, 21 Jul 2009 08:49:40 GMT

Hi.Did you ever get this to work? I am pulling my hair out changing the various policy configuration files adding 'Code Groups' and 'Permissions Sets'. I've followed the MS Knowledge base articles and spends 2 days trawling various resource trying to find the magic that will make this work, but right now its a big NO NO.What probably worse is I don't actually get any errors. Deployed report runs fine. I just don't get any of my db defined styles. As many other people on here have said, it all works beautifully in BIDS preview mode.Any light you could shed on this would be great.Martin

RE: Centralising Reporting Services Stylesheets

Adam Aspin — Thu, 18 Jun 2009 03:32:21 GMT

OOPs- My apologies to all who hit permissions problems, you need to add something like:"CodeGroup class="UnionCodeGroup" version="1" PermissionSetName="FullTrust" Name="MyCodeGroup" Description="Code group for my data processing extension"> "To the rssrvpolicy.config file (probably in Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportServer\)This is adapted from : http://support.microsoft.com/kb/920769Where you can find more details.

RE: Centralising Reporting Services Stylesheets

aveerabadran — Wed, 27 May 2009 11:50:22 GMT

But i face a strange problem. In SQLServer 2005 environment (enterprise&standard edition) when i use this DLL , the solution works gr8 if we preview the report using BIS. But if we deploy the report and view via report manager, the report comes up , but WITHOUT applying the style.i have placed the DLL in the locations mentioned in the article... but no luckAny ideas?Thanks,ArunvijayPS : Please ignore my previous post , where i have told that this solution works in enterprise edition , but doesnt work in standard edition .

RE: Centralising Reporting Services Stylesheets

Adam Aspin — Wed, 27 May 2009 06:28:16 GMT

Hi Steve & Sinh,You could need to look at:adding a code group to the rssrvpolicy.config - http://support.microsoft.com/kb/842419 (explains this particular error)or:http://msdn.microsoft.com/en-us/library/ms154466(SQL.90).aspx- for an overview of custom assembly security.Let us all know what exactly you have to do!Regards,Adam

RE: Centralising Reporting Services Stylesheets

sbygrave — Wed, 27 May 2009 04:35:09 GMT

Hi SinnI have no idea about C# but when I did this in vb.net I had to play with security issues: Dim oPerm As New Data.SqlClient.SqlClientPermission(System.Security.Permissions.PermissionState.Unrestricted) oPerm.Assert()May be worth you checking this out?CheersSteve

RE: Centralising Reporting Services Stylesheets

Adam Aspin — Wed, 27 May 2009 04:28:46 GMT

Hi Sinh,I have no idea off the top of my head about this one. I will have to spend some time looking at the deeper security implications of this process - but finding the time at the moment is difficult.Did you try signing the assembly using a strong key file?Regards,Aam

RE: Centralising Reporting Services Stylesheets

Sinh Dinh — Tue, 26 May 2009 16:27:33 GMT

I created a dll using C#. I'm getting this "warning":[code]Warning 2 [rsRuntimeErrorInExpression] The Value expression for the textrun ‘Textbox4.Paragraphs[0].TextRuns[0]’ contains an error: Request for the permission of type 'System.Data.SqlClient.SqlClientPermission, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed. C:\Documents and Settings\... 0 0 [/code]Here's the expression: =RSStyleLibrary.RSStyleSheet.ReportStyleGet("Color", "Accounting", "Header") Class file code[code]using System;using System.Collections.Generic;using System.Text;using System.Data;using System.Data.SqlClient;using System.Collections;namespace RSStyleLibrary{ public class RSStyleSheet { public static string ReportStyleGet(string StyleType, string StyleFamily, string StyleCategory) { //StyleType = "Color"; //StyleFamily = "Accounting"; //StyleCategory = "Header"; string RetVal; SqlConnection conn = new SqlConnection("server;database;User ID;Password); SqlCommand USP_ReportStyleGet = new SqlCommand("USP_ReportStyleGet", conn); USP_ReportStyleGet.CommandType = CommandType.StoredProcedure; USP_ReportStyleGet.Parameters.AddWithValue("@StyleType", StyleType); USP_ReportStyleGet.Parameters.AddWithValue("@StyleFamily", StyleFamily); USP_ReportStyleGet.Parameters.AddWithValue("@StyleCategory", StyleCategory); conn.Open(); RetVal = USP_ReportStyleGet.ExecuteScalar().ToString(); conn.Close(); return (string)RetVal; } }}[/code]Any help would be greatly appreciated.

RE: Centralising Reporting Services Stylesheets

Michael Shorkey — Thu, 30 Apr 2009 07:14:31 GMT

Hopefully Adam, or someone else can answer this one.Everything works wonderfully on my local workstation with the compiled DLL style library.The issue is when I copy the DLL for deployment into the reportServer folder on the development IIS server (where SSRS is installed) it is definitely NOT working.One issue may be the DLL (SSRS in general) is in a completely different folder than on my workstation, and VisualStudio is not installed on the server, so putting the DLL in there not even possible.What to do? Do the development DLL and the deployed on have to have the same reference paths? If they do, I'm sunk as the C: drives on company servers are a "no apps" zone (available only to system admins) and I don't have a "D" drive (which IS the user apps drive on company servers).

RE: Centralising Reporting Services Stylesheets

sbygrave — Mon, 27 Apr 2009 02:16:45 GMT

I know it's against Contractor principles but, I was thinking about this at the weekend and it occurred to me that I had missed the point that the dll is copied to the local server. Therefore to make this code work, no matter the name or location of the server, is to simply use local server. Thanks againSteve

RE: Centralising Reporting Services Stylesheets

Adam Aspin — Fri, 24 Apr 2009 08:41:56 GMT

Again, off the top of my head, how about "localhost" - I have used this in SSIS scripts, it might work here - if I have understood! Of course, you will need the same database name in each location.

RE: Centralising Reporting Services Stylesheets

sbygrave — Fri, 24 Apr 2009 07:54:28 GMT

Yes I was thinking that we could use replication for the Style table(s) but... How do I access the tables from within the CAS? At the moment I am hard coding the server name in there (using integrated security) and I do not want to have to create a different CAS for each location. Being able to use the shared data source from within the CAS would do it but, as I said before, I can't see how to do this.I suppose I could have a config file, but that seems a retrograde step to me?Thanks againSteve

RE: Centralising Reporting Services Stylesheets

Adam Aspin — Fri, 24 Apr 2009 07:37:43 GMT

Hi sbygrave,I can only suggest (untried and untested) that SQL Server replication from a central point to the departmental servers could possibly do the trick.After all, it is extremely lightweight and rarelychanging data.Regards,Adam

RE: Centralising Reporting Services Stylesheets

Adam Aspin — Fri, 24 Apr 2009 07:35:31 GMT

Hi MissKittyT,I am using the Enterprise version of SQL Server - as you can see from Arunvijay's post,the solution does not seem to work out of the box with Standard Edition.Which are you using?Regards,Adam

RE: Centralising Reporting Services Stylesheets

Adam Aspin — Fri, 24 Apr 2009 07:33:39 GMT

Hi Arunvj,I must admit that I have only used this in the Enterprise edition of SQL Server, and have not tested in standard edition. I will have to research this!Thanks!ADam

RE: Centralising Reporting Services Stylesheets

sbygrave — Fri, 24 Apr 2009 05:59:08 GMT

Brilliant article Adam but.......isn't there always a but?How would you go about centralising report styles in a global organisation? Where each jurisidiction/site has its own server? My initial thought was to use the datasource I am using within the reports which takes care of the differing server names already, but I can't work out/find a way to do this. Do you have any suggestions please?Thanks again

RE: Centralising Reporting Services Stylesheets

aveerabadran — Fri, 24 Apr 2009 04:22:51 GMT

Thanks Adams. The solution looks gr8.But i face a strange problem. In SQLServer 2005 environment ([b][u]enterprise edition[/u][/b]) when i use this DLL , the solution works gr8 , ---->when we preview the report using VS and --->when we deploy the report and view in report manager . But in SQLServer 2005 environment ([b][u]standard edition[/u][/b]) when i use this DLL , --->the solution works gr8 when we preview the report using VS ,--->but the styling dont work when we deploy the report and view in report manager :( .is this due to the enterprise VS standard edition? any idea on why this issue?Thanks,Arunvijay

RE: Centralising Reporting Services Stylesheets

misskittyt — Mon, 30 Mar 2009 16:49:23 GMT

Adam,Was the intention of the article to have this only working locally in preview mode? There is no mention of modifying the config files or asserting permissions in the tutorial. From everything I've read online, it sounds like this is required, but it's never mentioned in this article. Sadly, I have modified these files and still can't get it to work :(Are you deploying yours to the RS and running it there or running it in debugLocal mode successfully? I cannot get this to work unless I'm in preview mode.Thank you.

RE: Centralising Reporting Services Stylesheets

s b dragoo — Thu, 26 Mar 2009 09:20:54 GMT

After many hours of google and msdn searches, I am still frustrated with this option. It works fine at the development machine in BIDS but at the server it never makes the call to the .dll and ultimately to the database.Here is what I have in place:In the rssrvpolicy.config at the Windows 2008 Server with SQL Server 2005 on it, I've added these lines:<CodeGroup class="UnionCodeGroup"version="1"PermissionSetName="FullTrust"Name="MyCodeGroup"Description="Code group for my data processing extension"><IMembershipCondition class="UrlMembershipCondition"version="1"Url="C:\program files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportServer\bin\DynamicReportStyle.dll" They were inserted immediately prior to the last /CodeGroup line.Within my custom assembly prior to any sql calls I've added the following according to another msdn posting: Dim pstate As PermissionState pstate = PermissionState.Unrestricted Dim psql As New SqlClientPermission(pstate) psql.Assert()As stated earlier, this works beautifully from the BIDS interface, but once deployed to the server - NO GO.I've re-checked all of my deployment locations for the dll to insure the proper location.Any help? Anybody?Thanks in advance,Scott B Dragoo

RE: Centralising Reporting Services Stylesheets

OlafD — Thu, 19 Mar 2009 10:52:00 GMT

ok, so there's no real advantage when choosing one method over the other?your article however has helped me to understand how easy it is to put code into an assembly instead of using the pesky Report Properties -> Code tab. but for a style based solution i think i'll work with stored procedures instead then.thanks a lot

RE: Centralising Reporting Services Stylesheets

Adam Aspin — Thu, 19 Mar 2009 10:40:19 GMT

Hi Olaf,You can indeed use a stored procedure to retrieve style information, and then use this in the report. It is a question of choice, you choose what you are happier with.Adam

RE: Centralising Reporting Services Stylesheets

misskittyt — Thu, 19 Mar 2009 09:30:17 GMT

I'm experiencing something similar to S B Dragoo (maybe Susan is running into this too). I haven't implemented the image part yet, only the styles. But, it works fine locally and deploys, but any styles I've set do not show up when I run the report in Report Manager. As a test, I tried to return the style values in a text box and it returns as #Error. I also ran a trace and don't see any activity.I read on some other posts that they updated the rssvpolicy.config file and add some assert statements to the code. But, we're not doing that here. I tried it just to see if that was somehow the missing piece. Report Manager threw an error that the entire site was down. So, I'm guessing we're not supposed to do this.Is there maybe some setup on the db side I'm overlooking?

RE: Centralising Reporting Services Stylesheets

OlafD — Thu, 19 Mar 2009 09:16:35 GMT

hi again adam,i have been playing a bit with the assembly and it works quite well.the more i play with it, the more i wonder if it's not simpler to create a stored procedure that retrieves all styles in a dataset that then can be referenced in the report wherever needed. that would reduce the overhead of executing the vb code and it would also read the entire dataset with all styles once instead of calling the database for each function call.i guess i must be missing something to not see the obvious advantage when using the assembly though...thanks,olafi accidentally created a new topic with the above content. tried to delete it but it wouldn't let me. so please feel free to delete this one:http://www.sqlservercentral.com/Forums/FindPost679286.aspx

RE: Centralising Reporting Services Stylesheets

s b dragoo — Thu, 19 Mar 2009 09:06:28 GMT

Hopefully someone can help out here. I've created, compiled, and distributed the .dll to the server and my local machine.When I create the report, it runs and looks great on my local machine. However, when I run it from the server, I get #Error back to the report. I've ran a trace on the database and it appears as though the application never even tries to access the database. It pulls an image from the same database and correctly displays it, but nothing on the formatting.The Reporting Services is running on the same physical machine as the database and IE (Test environment). The table holding the image and the table containing the formatting settings are in the same database.I'm sure I've missed something obvious, but haven't been able to figure it out.Any help is greatly appreciated.Scott B DragooDatabase AdministratorTrippLiteAddendum: I added a MessageBox() to the .dll. When the report is ran from my local workstation the MessageBox() appears as expected. When I run the report from the server (either at the server with IE or from my workstation using IE) no appearance of the box. Does the .dll need registration with the server? Is there a software dependancy for this? Help.

RE: Centralising Reporting Services Stylesheets

Adam Aspin — Wed, 18 Mar 2009 09:32:10 GMT

Hi there perlowin,Question 1:CREATE ASSEMBLY SQLCLRTestFROM 'C:\MyDBApp\SQLCLRTest.dll'WITH PERMISSION_SET = UNSAFE- or if using Visual Studio, it is in one of the project config tabs (I don't have access to my test rig at the moment & so can't be more precise - sorry!)Question2:You can - it's just a variation on a theme!Adam

RE: Centralising Reporting Services Stylesheets

perlowin — Wed, 18 Mar 2009 09:24:12 GMT

Okay, I'm going to show how little I know...How do I set UNSAFE?Another question -- why not send both fields, StyleType and StyleCode as parameters rather then only one and using a loop to find the other?

RE: Centralising Reporting Services Stylesheets

Adam Aspin — Wed, 18 Mar 2009 06:31:55 GMT

Hi Olaf,I am pretty sure tha the answer is yes, using a config file, but I have not done this yet.Adam

RE: Centralising Reporting Services Stylesheets

OlafD — Wed, 18 Mar 2009 06:08:21 GMT

hi adam,thanks for these articles, they totally rock!!!one question though: i have lots of separate databases, which have similar sets of reports pointing at them. each db will contain it's own set of style tables.i'd like to use the data source that the actual report is using instead of hard coding the db connection into the dll. is that possible at all?cheers,o