Link to PDF Document in SSRS Report

Requirement: Include a PDF document in SSRS Report.

Problem: SSRS Reports do not support embedded PDF documents, yet.

Workarounds:

1. Use custom code and 3rd party solution to convert documents into an image as described in

• Reporting Services 2008 — Embedding a PDF DocumentNot appropriate for me!

1. Include a PDF icon in the report that links to the PDF document.

The next problem: The PDF document is stored as a blob in an SQL DB. Actually, we are using a K2 SmartObject (i.e. SmartBox) to write and read data from the SQL table.

Solution: Develop an aspx page that takes the process instance id (from the K2 process) as a parameter, checks the user’s permissions for the process, reads the data from the SQL table (via SmartObject) and displays it within the web browser.

protected void Page_Load(object sender, EventArgs e)
        {
// get the Process Instance ID
string procInstID = string.Empty;
            procInstID = Request["ProcInstID"] as string;
// only process if Process Instance ID is provided
if (!string.IsNullOrEmpty(procInstID))
            {
// K2 Connection String
                SCConnectionStringBuilder cs = new SCConnectionStringBuilder();
                cs.Host = "localhost";
                cs.Port = 5555;
                cs.Integrated = true;
                cs.IsPrimaryLogin = true;
// K2 Smart Object
                SmartObjectClientServer soServer = new SmartObjectClientServer();
                soServer.CreateConnection();
                soServer.Connection.Open(cs.ToString());
// K2 Process Instance SMO --> check permissions for the process instance
                SmartObject soProcInst = soServer.GetSmartObject("Process_Instance");
// fill in the SMO properties
                soProcInst.Properties["ProcessInstanceID"].Value = procInstID;
// execute SMO
                soProcInst.MethodToExecute = "List";
                SmartObjectList result = soServer.ExecuteList(soProcInst);
// proceed only if the user gets results for that Process Instance
if (result.SmartObjectsList.Count > 0)
                {
// K2 Reporting SMO
                    SmartObject soReporting = soServer.GetSmartObject("your_SMO");
// fill in the SMO properties
                    soReporting.Properties["ProcInstID"].Value = procInstID;
// execute SMO
                    soReporting.MethodToExecute = "Load";
                    soReporting = soServer.ExecuteScalar(soReporting);
// return property containing the file
                    SmartFileProperty f = (SmartFileProperty)soReporting.Properties["PDF"];
// extract the blob
byte[] data = Convert.FromBase64String(f.Content);
// present the PDF
                    Response.ContentType = "application/pdf";
                    Response.BinaryWrite(data);
                }
// otherwise he has no permission to view the process
else
                    Response.Write("no permission.");
Response.End();
            }
        }

The PDF image report then simply redirects the user to the aspx page and displays the document.

#SSRS