Programming SSRS Reports

Programmatically open a SSRS Report in Report Builder

2011-10-18T10:28:00.000-07:00

To open a report [which is deployed on report server] in report builder you can use the following URL

http://servername/ReportServer/reportbuilder/reportbuilder_2_0_0_0.application?http://servername/ReportServer/path to your report/reportname

eg. If i have a report TestReport.rdl which is deployed on root of report server then to open this report in report builder I will use following URL

http://localhost:8080/ReportServer/reportbuilder/reportbuilder_2_0_0_0.application?http:// localhost:8080/ReportServer/ TestReport

To open the same report in Report Builder 3, I will use following URL

http://localhost:8080/ReportServer/reportbuilder/reportbuilder_3_0_0_0.application?http:// localhost:8080/ReportServer/ TestReport

Please note that this technique is for Report Server configured in "native mode"

Print SSRS Report Programmatically

2011-10-07T03:24:00.001-07:00

This post explains about programmatically rendering a report and then sending that report directly to a local or network printer using C# and the Reporting Services Web service.
PreRequisite : You must have SSRS(SQL Server Reporting Services) installed so that you can use Reporting web services provided by SSRS.

Step 1: Add Reference to Web Service()
The first thing you need to do is add a Web reference to the Reporting Services Web service in your development project that points to your report server.
To do so right-click on your project in Visual Studio and choose Add Service reference... .Then Click on Advanced and then "Add Web Reference.." as shown in below image..

Provide report server URL (in my case http://localhost/reportserver/ReportExecution2005.asmx). If you have a remote report server, simply change the URL of the Web reference. The end point for any Reporting Services Web service is http://servername/reportserver/reportservice.asmx.

Provide service reference name(i provided "ExecutionService" as reference name) and click on Add.

now service reference gets added to user solution as shown below

Step 2: Using ReportingService.Render() method to Render report in desired format:

Once you have added the appropriate Web reference, create a proxy object for the Web service, so that you can access the its methods.

The method that we are most interested in for the purpose of this article is the ReportingService.Render method. This is the primary method for rendering reports that have been published to the report server.

The syntax for the Render method is as follows:

public Byte[] Render(string Report,string Format,string HistoryID,string DeviceInfo,

[Namespace].ParameterValue[] Parameters,[Namespace].DataSourceCredentials[] Credentials,

string ShowHideToggle,out string Encoding,out string MimeType,

out [Namespace].ParameterValue[] ParametersUsed, out [Namespace].Warning[] Warnings,

out string[] StreamIds);

In code, you need to render to the Image output format designated by the Format argument. The value of this parameter is a string, simply "IMAGE". To get Enhanced Meta File (EMF) output, the kind of output you will need for printing, you also need to specify device information for the call to Render. That device information should be passed as an XML string for the DeviceInfo argument and should look like "<DeviceInfo><OutputFormat>EMF</OutputFormat></DeviceInfo>". The Render method returns the report as a base 64-encoded byte array. This array of bytes can be used to perform a number of functions including saving the output to a file on the hard drive, or more importantly, sending the bytes as a stream to a printer.

Step 3:Printing the Report Programmatically
Here we use the classes of the System.Drawing.Printing namespace in order to send EMF output to a printer
From a Reporting Services standpoint, the key challenge is determining how many pages there are in the report. The SOAP API for Reporting Services lacks the ability to evaluate a report's number of printed pages through any exposed report properties. So the trick is to determine how many pages there are through some other means.
The MSDN documentation for Render method says that it returns the results of the rendered report as a byte array, but it only sends back the first page. Subsequent pages are associated with the report as streams with accompanying stream IDs. By counting the number of StreamIDs in the resultant string array, you can determine how many pages are in the report.
But i think we get this StreamIDs as empty array everytime .....so we have to use another logic to find number of pages and their stream .....you can find this logic in my below code :
Once you know how many pages you are dealing with you can call the Render method for each page in the report and send that page to the printer. You can render specific pages using device information. The device information for this is StartPage.
i have used PrintDocument class to print the report...
Following is the code to achieve this functionality...

using System;
using System.Web;
using System.Configuration;
using System.Drawing.Printing;
using System.Drawing;
using System.IO;
using System.Drawing.Imaging;
using System.Runtime.InteropServices;
using DirectlyPrintReport.ExecutionService;
using System.Security.Principal;

namespace DirectlyPrintReport
{
public class PrintManager
{// class members
private ReportExecutionService rs = new ReportExecutionService();
private byte[][] m_renderedReport;
private Graphics.EnumerateMetafileProc m_delegate = null;
private MemoryStream m_currentPageStream;
private Metafile m_metafile = null;
private int m_numberOfPages;
private int m_currentPrintingPage;
private int m_lastPrintingPage;

public PrintManager()
{
rs.Credentials = System.Net.CredentialCache.DefaultNetworkCredentials;
} public PrintManager(System.Net.NetworkCredential credentials)
{
rs.Credentials = credentials;
}

public bool PrintReport(string reportPath, ExecutionService.ParameterValue[] parameters, int fromPage, int toPage)

{

this.m_renderedReport = this.RenderReport(reportPath, parameters);

try

{

// Wait for the report to completely render.

if (m_numberOfPages < 1)

{

return false;

}

//You can set the Required Printer Settings(Paper Size, Page Source, Orientation etc) in printerSettings object defined below

PrinterSettings printerSettings = new PrinterSettings();

printerSettings.MaximumPage = m_numberOfPages;

printerSettings.MinimumPage = 1;

printerSettings.PrintRange = PrintRange.AllPages;//PrintRange.SomePages;

PrintDocument pd = new PrintDocument();

if (toPage != -1 && fromPage != -1)

{

m_currentPrintingPage = fromPage;

m_lastPrintingPage = toPage;

if (m_numberOfPages < toPage)

{

toPage = m_numberOfPages;

m_lastPrintingPage = toPage;

}

if (m_numberOfPages < fromPage)

{

fromPage = m_numberOfPages;

m_currentPrintingPage = fromPage;

}

printerSettings.FromPage = fromPage;

printerSettings.ToPage = toPage;

Console.WriteLine("Printing report... Start Page:{0} End Page:{1} Total Page(s):{2}", fromPage, toPage, m_numberOfPages);

}

else

{

m_currentPrintingPage = 1;

m_lastPrintingPage = m_numberOfPages;

Console.WriteLine("Printing report...");

}

using (WindowsImpersonationContext wic = WindowsIdentity.Impersonate(IntPtr.Zero))

{

//code to send printdocument to the printer

//Set Your Printer Name here

printerSettings.PrinterName = "Microsoft XPS Document Writer";

pd.PrinterSettings = printerSettings;

pd.PrintPage += this.pd_PrintPage;

pd.Print();

}

catch (Exception ex)

{

//System.Windows.Forms.MessageBox.Show(ex.ToString());

Console.WriteLine(ex.Message);

}

return true;

}

/// <summary>

/// This method renders the report as multidimentional byte array.

/// </summary>

private byte[][] RenderReport(string reportPath, ExecutionService.ParameterValue[] parameters)

{

// Private variables for rendering

string historyId = null;

ExecutionService.ExecutionHeader execHeader = new ExecutionService.ExecutionHeader();

try

{

rs.Timeout = 300000;

rs.ExecutionHeaderValue = execHeader;

rs.LoadReport(reportPath, historyId);

if ((parameters != null))

{

rs.SetExecutionParameters(parameters, "en_us");

}

byte[][] pages = new Byte[0][];

string format = "IMAGE";

int numberOfPages = 1;

byte[] currentPageStream = new byte[1] { 0x00 }; // put a byte to get the loop started

string extension = null;

string encoding = null;

string mimeType = null;

string[] streamIDs = null;

ExecutionService.Warning[] warnings = null;

while (currentPageStream.Length > 0)

{

string deviceInfo = String.Format(@"<DeviceInfo><OutputFormat>EMF</OutputFormat><PrintDpiX>200</PrintDpiX><PrintDpiY>200</PrintDpiY>" + "<StartPage>{0}</StartPage></DeviceInfo>", numberOfPages);

//rs.Render will render the page defined by deviceInfo's <StartPage>{0}</StartPage> tag

currentPageStream = rs.Render(format, deviceInfo, out extension, out encoding, out mimeType, out warnings, out streamIDs);

if (currentPageStream.Length == 0 && numberOfPages == 1)

{

//renderException = EnumRenderException.ZERO_LENGTH_STREAM;

break;

}

//Add the byte stream of current page in pages[] array so that we can have complete report in pages[][] array

if (currentPageStream.Length > 0)

{

Array.Resize(ref pages, pages.Length + 1);

pages[pages.Length - 1] = currentPageStream;

numberOfPages++;

}

m_numberOfPages = numberOfPages - 1;

return pages;

}

catch (System.Web.Services.Protocols.SoapException ex)

{

Console.WriteLine(ex.Detail.InnerXml);

}

catch (Exception ex)

{

Console.WriteLine(ex.Message);

}

finally

{

// Console.WriteLine("Number of pages: {0}", pages.Length);

}

return null;

}

/// <summary>

/// Handle the Printing of each page

/// </summary>

/// <param name="sender"></param>

/// <param name="ev"></param>

private void pd_PrintPage(object sender, PrintPageEventArgs ev)

{

ev.HasMorePages = false;

if (m_currentPrintingPage <= m_lastPrintingPage && MoveToPage(m_currentPrintingPage))

{

// Draw the page

ReportDrawPage(ev.Graphics);

// If the next page is less than or equal to the last page,

// print another page.

if (System.Threading.Interlocked.Increment(ref m_currentPrintingPage) <= m_lastPrintingPage)

{

ev.HasMorePages = true;

}

// Method to draw the current emf memory stream

private void ReportDrawPage(Graphics g)

{

if (m_currentPageStream == null || 0 == m_currentPageStream.Length || m_metafile == null)

{

return;

}

lock (this)

{

// Set the metafile delegate.

int width = m_metafile.Width;

int height = m_metafile.Height;

m_delegate = new Graphics.EnumerateMetafileProc(MetafileCallback);

// Draw in the rectangle

//Point destPoint = new Point(0, 0);

Point[] points = new Point[3];

Point destPoint = new Point(0, 0);

Point destPoint1 = new Point(width/2 , 0);

Point destPoint2 = new Point(0, height/2);

points[0] = destPoint;

points[1] = destPoint1;

points[2] = destPoint2;

g.EnumerateMetafile(m_metafile, points, m_delegate);

// Clean up

m_delegate = null;

}

private bool MoveToPage(int page)

{

// Check to make sure that the current page exists in

// the array list

if (m_renderedReport[m_currentPrintingPage - 1] == null)

{

return false;

}

// Set current page stream equal to the rendered page

m_currentPageStream = new MemoryStream(m_renderedReport[m_currentPrintingPage - 1]);

// Set its postion to start.

m_currentPageStream.Position = 0;

// Initialize the metafile

if (m_metafile != null)

{

m_metafile.Dispose();

m_metafile = null;

}

// Load the metafile image for this page

m_metafile = new Metafile((Stream)m_currentPageStream);

return true;

}

private bool MetafileCallback(EmfPlusRecordType recordType, int flags, int dataSize, IntPtr data, PlayRecordCallback callbackData)

{

byte[] dataArray = null;

// Dance around unmanaged code.

if (data != IntPtr.Zero)

{

// Copy the unmanaged record to a managed byte buffer

// that can be used by PlayRecord.

dataArray = new byte[dataSize];

Marshal.Copy(data, dataArray, 0, dataSize);

}

// play the record.

m_metafile.PlayRecord(recordType, flags, dataSize, dataArray);

return true;

}

Call this “PrintReport function” from any event(eg.A button click) from where you want to print the Server Report and pass the “report path as parameter” to the function.

Also you need to pass report’s parameters as parameter to this function…..in my case my report do not have any parameter so I am passing null

In my case my server reportPath is "/EmpInfoReport/EmpDetails"

I will call this function on my Print button click as follows:
private void btnPrint_Click(object sender, EventArgs e)
{
DirectlyPrintReport.PrintManager objPrintManager = new PrintManager();
objPrintManager.PrintReport(@"/EmpInfoReport/EmpDetails", null, -1, -1);
}

Kindly let me know whether this was helpful....When i was tryng for this, It took my complete week to achieve this functionality.

Copy SSRS Report on Report Server Programatically

2011-10-06T10:28:00.000-07:00

This post explains about programmatically copying a SSRS report(deployed on report server) using Reporting Services Web service.

Prerequisite : You must have SSRS(SQL Server Reporting Services) installed so that you can use Reporting web services provided by SSRS.

Follow the steps provided below:

Step 1: Add Reference to Web Service()
The first thing you need to do is add a Web reference to the Reporting Services Web service in your development project.
To do so right-click on your project in Visual Studio and choose Add Service reference... .Then Click on Advanced and then "Add Web Reference.." as shown in below image..

Provide report service URL (in my case http://localhost/reportserver/ReportService2010.asmx). If you have a remote report server, simply change the URL of the Web reference. The end point for any Reporting Services Web service is http://servername/reportserver/ReportService2010.asmx.

Provide service reference name(i provided "ExecutionService" as reference name) and click on Add.

Step 2: Using ReportingService.CreateCatalogItem method To copy a existing Report (which is deployed on Report Server)

Following code copies a SSRS named “EmployeeReport” located inside “TestReports” folder as “CopiedEmployeeReport” which will be located inside “TestSSRSProject” project

//Create Proxy object for ReportingService

ReportingService.ReportingService2010 rs;

rs = new ReportingService.ReportingService2010();

//Path of source report(i.e. Report that need to be copied)

//Set the Credentials for authentication of reportingService

rs.Credentials = Net.CredentialCache.DefaultNetworkCredentials;//Above you can also provide the credentials as below

//System.Net.NetworkCredential Mycredentials = new System.Net.NetworkCredential("yourUserName", "yourPassword", "yourDomainName");

// rs.Credentials = Mycredentials;

rs.Url = http://localhost/ReportServer/ReportService2010.asmx;

string _reportPath = null;

_reportPath = "/TestReports/EmployeeReport";

//Get source report definition as Byte array

Byte[] reportDefinition = null;

reportDefinition = rs.GetItemDefinition(_reportPath);

//Get report Data source of source report

ReportingService.DataSource[] ds = null;

ds = rs.GetItemDataSources(_reportPath);

ReportingService.Warning[] warnings = null;

//Copies “EmployeeReport” report as "CopiedEmployeeReport" inside "/TestSSRSProject" folder

ReportingService.CatalogItem copiedReport = rs.CreateCatalogItem("Report", "CopiedEmployeeReport", "/TestSSRSProject", false, reportDefinition, null, warnings);

//Set Data Source of copied report

rs.SetItemDataSources("/TestSSRSProject/CopiedEmployeeReport", ds);

Please let me know whether this was helpful , so that i can post more related topics????

Create a New SSRS report Programmatically

2011-10-06T03:35:00.000-07:00

This post explains about programmatically creating a new SSRS report using Reporting Services Web service.

Prerequisite : You must have SSRS(SQL Server Reporting Services) installed so that you can use Reporting web services provided by SSRS.

Follow the steps provided below:

Provide service reference name(i provided "ExecutionService" as reference name) and click on Add.

Step 2: Using ReportingService.CreateCatalogItem method To create a new Report

Following code will create a SSRS report with name "NewCreatedReport" at Root of Report Server

//Create Proxy object for ReportingService

ReportingService.ReportingService2010 rs;

rs = new ReportingService.ReportingService2010();

//Set the Credentials for authentication of reportingService

rs.Credentials = Net.CredentialCache.DefaultNetworkCredentials;

//Above you can also provide the credentials as below

//System.Net.NetworkCredential Mycredentials = new System.Net.NetworkCredential("yourUserName", "yourPassword", "yourDomainName");

// rs.Credentials = Mycredentials;

rs.Url = "http://localhost/ReportServer/ReportService2010.asmx";

//_reportPath is the path where new report will get created “/” represents report will get created at root of report server. If you have some folder say “Myreports” on report server and you want to create new report inside this folder then you need to provide _reportPath=”/Myreports”

string _reportPath;

_reportPath = "/";

Byte[] reportDefinition = null;

ReportingService.Warning[] warnings = null;

ReportingService.CatalogItem newCreatedReport = rs.CreateCatalogItem("Report", "NewCreatedReport", _reportPath , false, reportDefinition, null, warnings);

After executing this code a new report will get created with report name/title as “NewCreatedReport” on root of reportServer i.e http://localhost/Reports

Was this helpful ????
Kindly let me know if you need more clarification.