SSRS load image from external assembly

This has been a bit of and adventure to get this to work.

 

Scenario
I had a standalone C# assembly for creating data matrixes (2D barcodes) and it exported as a bitmap. This is really no different from loading a bmp from disk.

 

I wanted to create a report that displayed a data matrix per part in a table.

 

All sounds plausable!

 

Issues
There are a few issues with this:

 

1. The custom assemblies need to be installed and referenced. This needs to be set on the development PC and the server that the report is published to.

 

2. The Image control in SSRS (2005) needs to have the Value poperty set.

 

3. The Image Image expects a Byte array.

 

Configuration
Assuming that you have already created your assembly...

 

1. Copy the assembly to the following locations:
Development environment (may also be reporting server):
Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblie

 

Reporting Server:
Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportServer\bin

 

Note that these may be different depending on your installation.

 

2. Add reference to the assembly in your report.

• Open the report that will reference the custom assembly.
• On the Report menu, click Report Properties.
• In the Report Properties dialog box, click the References tab.
• Under References, click the ellipsis (...) button that is next to the Assembly name column header.
• In the Add References dialog box, click Browse. (In SQL Server 2005, click the Browse tab.)
• Locate and then click the custom assembly. Click Open. (In SQL Server 2005, click Add instead of Open.)
• In the Add References dialog box, click OK.
• In the Report Properties dialog box, click OK.

 

That's the reference installed.

 

3. Create custom code to use the assembly
This custom code will also need to convert the bitmap to a byte array...

• Right click the report and choose properties. Select the Code tab and enter code similar to the following :

Public Function GetMatrixImage(ByVal code As String) As Byte()

 

Dim photo as System.Drawing.Image
photo = myAssembly.GetTheImage(code)

 

Dim ms AS System.IO.MemoryStream = new System.IO.MemoryStream()
photo.Save(ms, System.Drawing.Imaging.ImageFormat.Bmp)
Dim imagedata as byte()
imagedata = ms.GetBuffer()

 

return imagedata

 

End Function

 

4. Go to Layout tab and drop your Image control. Set it up as follows:

• Set Source = Database
• Set MIMEType = image/bmp (from the pulldown)
• Choose Expression from the Value drop down. In the Expression editor enter :

=Code.GetMatrixImage(Fields!ID.Value)

 

Where ID.Value is the key to creating/finding the image.

 

 

And that's it. Pretty easy really, however the documentation on how to do it is a bit thin. Hopefully this will help people out !

Save/default parameters for SSRS

The following link is a rather useful way to save parameters and recall by user in SSRS.

Handling multi value parameters from SSRS in a stored procedure

Actually quite easy, using this function :

CREATE FUNCTION [dbo].[fn_SplitIN]
/* This function is used to split up multi-value parameters */
(
@ItemList VARCHAR(4000),
@delimiter CHAR(1)
)
/* ========================================================================
fn_SplitIN

Function to return a table for joining for a multi valued parameter from SSRS

How to use :

SELECT cols
FROM mytab
WHERE cols IN (SELECT Item FROM fn_SplitIN(@multiValueParam,','))

===========================================================================
Version Date Author Comment
------- ---- ------ -------
v0.00 23/03/2009 Brian Jones Initial Version
=========================================================================== */

RETURNS @IDTable TABLE (Item VARCHAR(50))
AS
BEGIN
DECLARE @tempItemList VARCHAR(4000)

SET @tempItemList = @ItemList

DECLARE @i INT
DECLARE @Item NVARCHAR(4000)

SET @tempItemList = REPLACE (@tempItemList, @delimiter + ' ',@delimiter)
SET @i = CHARINDEX(@delimiter, @tempItemList)

WHILE (LEN(@tempItemList) > 0)
BEGIN
IF @i = 0
SET @Item = @tempItemList
ELSE
SET @Item = LEFT(@tempItemList, @i - 1)

INSERT INTO @IDTable(Item) VALUES(@Item)

IF @i = 0
SET @tempItemList = ''
ELSE
SET @tempItemList = RIGHT(@tempItemList, LEN(@tempItemList) - @i)

SET @i = CHARINDEX(@delimiter, @tempItemList)
END

RETURN
END

Reporting Services Performance Monitoring

Useful technet article on how to monitor SSRS performance report by report.

http://technet.microsoft.com/en-us/library/aa964131.aspx

Custom Report Items in SSRS 2005

I was recommended the following by Jon as a possible solution to a calendar painting issue that Owen has. SSRS can be extended using custom report items (CRI) which should allow us to do jsut about anything. Jazz Up Your Data Using Custom Report Items In SQL Server Reporting Services runs through an example of this and is worth a look.

TFS : The permissions granted to user are insufficient for performing this operation. (rsAccessDenied)

I got the error message :
The permissions granted to user are insufficient for performing this operation. (rsAccessDenied) on a user today when running the reports from Team Foundation Server.

This was an existing user who had not experienced this particular error before. I had just created a new project, and had assigned the AD user group (using the excellent Pwoer Tool - Team Foundation Server Administration Tool) as a contributor/publisher. I even created them as a standalone user and still no joy.

After some web crawling I found that there is an interesting anomoly in Reporting Services. The role "Publisher" by default does not have the ability to view reports. OK....

To resolve the issue :

1. Open SSMS and connect to the Reporting Services instance.
2. Plus open security..roles
3. Open the Properties window for the Publisher role and check :
View Reports
View Resources
View Folders
View Models
4. Click OK.

For more information see :
Lesson 1: Setting System-level Permissions on a Report Server
and
Lesson 2: Setting Item-level Permissions on a Report Server