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
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
