May 10, 2008

Using Custom Assembly in Sql Server Reporting Services (SSRS).

There are times when one needs to have something that is not already available in Sql Server Reporting Services.

I too had one requirement to have custom Localization implementation for the static labels in a report. I had to fetch Localized data based on the UserID parameter (which is MUST for every report I had as all the reports were User specific.).

Let's look into details how I did that.

Give it a thought First !!

I did the same, I knew I had to use expression so I assumed I would use something like

=Code.RptUtils.GetValue("LabelKey",Parameters!UserID.Value,"DefaultText").

WHERE

Code = Globally defined member through which Instance methods from our assembly are available. (We would not have used this keyword if we had written static methods.)

RptUtils = Instance of the class in our assembly.(This one also is there because we had not written static method).

GetValue = method about which I mentioned above. ( Additional to it, this function returns a string)

LabelKey = Database field value. My database contains a Key, CountryID, LanguageID, Value pattern to have multilingual values.

Parameters!UserID.Value = UserID from the Report parameter.

DefaultText = If the value is not found in database then show this value.

Let's Start :

Create a class library project and name it as you desire. I named it ReportUtilities. And add a class that is going to help us getting multilingual values that is MLHelper.

Create a method that looks like following.

/// <summary>
///
This method is used to fetch the Resource value from the database.
/// The method will be used by the SSRS report.
/// </summary>
/// <param name="key">
Key of the resource</param>
/// <param name="userId">
specific UserID to fetch the resource
/// value for.</param>
/// <returns>
Resource value as string if found, Key enclosed
/// in square brackets otherwise.</returns>
[PermissionSet(SecurityAction.Assert, Unrestricted = true)]
public string GetValue(string key, string userId, string defaultText)
{
// Set initial value for the resource .
string returnValue = defaultText;
try
{
using (
SqlConnection
_con = new SqlConnection(
DatabaseHelper.ConnectionString
)
)
{
// DatabaseHelper is a class containing the constant string that holds the Sql connection string.
using (
SqlCommand _cmd = new SqlCommand(
"[Internationalization].[GetResourceValue]",
_con)
)
{
SqlParameter[] _params = new SqlParameter[2];
_params[0] =
new SqlParameter();
_params[0].ParameterName =
"@Key";
if (!string.IsNullOrEmpty(key))
{
_params[0].Value = key;
}
else
throw new
Exception("The Key must not be blank.");
_params[1] =
new SqlParameter();
_params[1].ParameterName =
"@UserID";
if (!string.IsNullOrEmpty(userId))
{
_params[1].Value = System.Data.SqlTypes.
SqlGuid.Parse(userId);
}
else
throw new
Exception("The UserID must not be blank.");
_cmd.Parameters.Add(_params[0]);
_cmd.Parameters.Add(_params[1]);
_con.Open();


// I have a stored proc to get multilingual value from the database
// based on the UserID and the Key passed to it.

_cmd.CommandType = System.Data.
CommandType.StoredProcedure;
object objResourceValue = _cmd.ExecuteScalar();
if (objResourceValue != null)
{
returnValue = objResourceValue.ToString();
}
_con.Close();
}
}
}
catch
{
// if anything goes wrong....the default text will be set on the label.
// Or you may want to handle it differently.
}
return returnValue;
}
Compile it to an assembly and it is ready to be hosted.

Host It

Hosting is just copy the DLL from your class library's Bin to
  • C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies ( for the developement machine on which you are designing and testing the report).

  • For SSRS 2005 (Reporting Host machine), the default is C:\Program Files\Microsoft SQL Server\MSSQL.n\Reporting Services\ReportServer\bin.

Use It

Goto ReportProperties >> References and select the assembly



rpt1



After the assembly is added we can use it in two ways depending upon the type of member we are using.


  • If we are using static member (if our method GetValue is static) we can directly access it using the full qualified member name from the root namespace like

=ReportUtilities.MLHelper.GetValue(.....)

  • For the Instance (non static) members we need to alias the class. For this look at the classes section in above image. Mention the fully qualified class name into the Class name section and alias it in the InstanceName section. And then it can be used as

=Code.RptUtils.GetValue(.....)

As we have Instance method "GetValue" we will follow the later one from above.

Right click the label you want to write expression for and put the following text

=Code.RptUtils.GetValue("LabelKeyYouHaveDefined",Parameters!UserID.Value,"First Name")

Does it really work ??

  • Yes, when you run the report in the Visual Studio's Reporting project ( means in the design environment).
  • No, on the report server when you host the assembly in the
  • C:\Program Files\Microsoft SQL Server\MSSQL.n\Reporting Services\ReportServer\bin. and it gives "#Error" instead of what you wanted.

Why?? Because of the security. When you run the report within the report designer (Visual Studio), the Visual Studio runs under the logged in user's context and as the logged in user has the rights it runs properly.

On the Reporting server the report is running under the ASP.NET's account so it requires the permission.

If the custom assembly needs permissions in addition to the Execution permission we need to mention it in the ReportServer's rssrvpolicy.config file located at C:\Program Files\Microsoft SQL Server\MSSQL.4\Reporting Services\ReportServer\. Open the file and put the following CodeGroup stuff

<CodeGroup class="UnionCodeGroup"
version
="1"
PermissionSetName
="FullTrust"
Name
="YourCustomCodeGroup"
Description
="For the Custom assembly of Demo">
<IMembershipCondition
class="UrlMembershipCondition"
version
="1"
Url
="C:\Program Files\Microsoft SQL Server\MSSQL.4\Reporting Services\ReportServer\bin\ReportUtilities.dll"
/>
</CodeGroup>

Now you are ready to go...!! Congratulations you have done it....!!!
Find more on SSRS on
Nirav Patel's Weblog

Submit this story to DotNetKicks

7 comments:

Nirav Patel said...

Hey Paresh,
Great article. Keep coming.

Anonymous said...

Very nice article!
It answers all my questions and resolves all my problems.
Thanks

dylan_baxter said...

Paresh, thank you for this article!
I had doubts about trying to query a SQL server in a custom assembly before I read this. I realize now that some of the worst limitations in SSRS can be overcome simply by leveraging functionality provided by the framework.
Cheers!

KY said...

Hi Paresh,

After reading your article and follow your instruction, my SSRS rather show "#Error" then display the correct result from Custom Assembly, would you give me some advise to solve this problem?? I deeply appreciated your help, thanks.

Yan

Paresh Jagatiya said...

Hi KY,
this error might be due to the security problems.. please read the post again. specially after the "Does it really work ??" stuff. see how to set the security in the config file. Give it a try.

Bhupendra said...

Hi Paresh,
I tried doing the same thing as you only diffrence is i am using a static class.

My Assembly name is SSRS.ResourcesEx.
name of the class is LocalizedValue
and the nam of function is GetString

but when i put the same in expr for text box it says GetString is undefined.

Now, I am still playing with this idea in Visual Studio not yet deployed to SSRS.

Cheers,
Bhupendra Patel

Custom Paper Writing said...

Many institutions limit access to their online information. Making this information available will be an asset to all.