Creating Report Template in Visual Studio

Note: I have moved to blog at my own domain, so kindly visit this post over there for recent updates or comments. http://www.imranbalouch.com/blog/index.php/2010/04/creating-report-template-in-visual-studio/

We have been doing report development using SQL Server 2008 Reporting Services and all of reports normally followed a standard pattern, so I thought to design a report template so that we can have all standards while developing a new report. Following are the steps that I found from net for it:

  • Add a new report to your project
  • Using the report designer place any objects (text boxes, tables, lines, etc) on the report that you want as a template.
  • Give the report a name (anything that you can remember in the future, for instance if you want landscape reports and you create a landscape template you should then call the report LandscapeTemplate.rdl)
  • Save this .rdl file to: C:Program FilesMicrosoft Visual Studio 9.0Common7IDEPrivateAssembliesProjectItemsReportProject

The next time you add a report item you will notice this item in the Templates section.

For older version of SSRS use the Microsoft Visual Studio 8 directory:C:Program FilesMicrosoft Visual Studio 8Common7IDEPrivateAssembliesProjectItemsReportProject

And here you go, ALL IZ WELL… (Y)

Advertisements

Calling SQl2008 Reporting Server WebService Programmatically

Note: I have moved to blog at my own domain, so kindly visit this post over there for recent updates or comments. http://www.imranbalouch.com/blog/index.php/2010/04/calling-sql2008-reporting-server-webservice-programmaticall/

In our web application we have a lot of reports and many of them take hell lot of time, so we decided to make a page from where user can select report and provide parameters and save the request, later on our windows service can get report from sql reporting server’s web service and email the report in desired format to the user.

While doing a test, I created a windows service and used following code to get the report:

ReportExecutionService.ReportExecutionServiceSoapClient rsExec = new ReportingServiceTesting.ReportExecutionService.ReportExecutionServiceSoapClient();

      System.Net.NetworkCredential clientCredentials = new System.Net.NetworkCredential("me", "me", "me");
      rsExec.ClientCredentials.Windows.AllowedImpersonationLevel = System.Security.Principal.TokenImpersonationLevel.Impersonation;
      rsExec.ClientCredentials.Windows.ClientCredential = clientCredentials;

      string historyID = "";
      string reportPath = "Reports/MyOrders";

      ReportExecutionService.ExecutionInfo execInfo = new ReportExecutionService.ExecutionInfo();
      ReportExecutionService.TrustedUserHeader trusteduserHeader = new ReportingServiceTesting.ReportExecutionService.TrustedUserHeader();

      ReportExecutionService.ExecutionHeader execHeader = new ReportExecutionService.ExecutionHeader();
      ReportExecutionService.ServerInfoHeader serviceInfo = new ReportingServiceTesting.ReportExecutionService.ServerInfoHeader();
      rsExec.LoadReport(trusteduserHeader, reportPath, historyID, out serviceInfo, out execInfo);

      ReportExecutionService.ParameterValue[] executionParams;
      executionParams = new ReportExecutionService.ParameterValue[5];
      executionParams[0] = new ReportExecutionService.ParameterValue();
      executionParams[0].Name = "@pCompanyId";
      executionParams[0].Value = "1";
      executionParams[1] = new ReportExecutionService.ParameterValue();
      executionParams[1].Name = "@pUserId";
      executionParams[1].Value = "1";
      executionParams[2] = new ReportExecutionService.ParameterValue();
      executionParams[2].Name = "@pLanguageId";
      executionParams[2].Value = "1";
      executionParams[3] = new ReportExecutionService.ParameterValue();
      executionParams[3].Name = "@pOrganizationId";
      executionParams[3].Value = "1";
      executionParams[4] = new ReportExecutionService.ParameterValue();
      executionParams[4].Name = "@pThemeId";
      executionParams[4].Value = "1";

      rsExec.SetExecutionParameters(execHeader,trusteduserHeader, executionParams, "en-us",out execInfo);

      //string historyID = null;
      string deviceInfo = null;
      string extension;
      string encoding;
      string mimeType;
      ReportExecutionService.Warning[] warnings = new ReportingServiceTesting.ReportExecutionService.Warning[1];
      warnings[0] = new ReportingServiceTesting.ReportExecutionService.Warning();
      string[] streamIDs = null;
      string format = "EXCEL";
      Byte[] result;
      rsExec.Render(execHeader,trusteduserHeader, format,
        deviceInfo,out result, out extension,
        out mimeType, out encoding,
        out warnings, out streamIDs);
      String fileName = "me.xls";
      FileStream stream = File.OpenWrite(fileName);
      stream.Write(result, 0, result.Length);
      stream.Close();

and our configuration file has settings like:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <system.serviceModel>
    <bindings>
      <basicHttpBinding>
        <binding name="ReportExecutionServiceSoap" closeTimeout="00:01:00"
          openTimeout="00:01:00" receiveTimeout="00:10:00" sendTimeout="00:01:00"
          allowCookies="false" bypassProxyOnLocal="false" hostNameComparisonMode="StrongWildcard"
          maxBufferSize="65536" maxBufferPoolSize="524288" maxReceivedMessageSize="65536"
          messageEncoding="Text" textEncoding="utf-8" transferMode="Buffered"
          useDefaultWebProxy="true">
          <readerQuotas maxDepth="32" maxStringContentLength="8192" maxArrayLength="16384"
            maxBytesPerRead="4096" maxNameTableCharCount="16384" />
          <security mode="TransportCredentialOnly">
            <transport clientCredentialType="Ntlm" proxyCredentialType="None"
              realm="" />
            <message clientCredentialType="UserName" algorithmSuite="Default" />
          </security>
        </binding>
      </basicHttpBinding>
      <customBinding>
        <binding name="ReportingService2005Soap12">
          <textMessageEncoding maxReadPoolSize="64" maxWritePoolSize="16"
            messageVersion="Soap12" writeEncoding="utf-8">
            <readerQuotas maxDepth="32" maxStringContentLength="8192" maxArrayLength="16384"
              maxBytesPerRead="4096" maxNameTableCharCount="16384" />
          </textMessageEncoding>
          <httpTransport manualAddressing="false" maxBufferPoolSize="524288"
            maxReceivedMessageSize="65536" allowCookies="false" authenticationScheme="Anonymous"
            bypassProxyOnLocal="false" hostNameComparisonMode="StrongWildcard"
            keepAliveEnabled="true" maxBufferSize="65536" proxyAuthenticationScheme="Anonymous"
            realm="" transferMode="Buffered" unsafeConnectionNtlmAuthentication="false"
            useDefaultWebProxy="true" />
        </binding>
      </customBinding>
    </bindings>
    <client>
      <endpoint address="http://myServer/ReportServerSQL2008/ReportExecution2005.asmx"
        binding="basicHttpBinding" bindingConfiguration="ReportExecutionServiceSoap"
        contract="ReportExecutionService.ReportExecutionServiceSoap"
        name="ReportExecutionServiceSoap" />
    </client>
  </system.serviceModel>
</configuration>

When I run my windows service I came across folloing error:

System.ServiceModel.FaultException: System.Web.Services.Protocols.SoapException: An internal error occurred on the report server. See the error log for more details. —> Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException: An internal error occurred on the report server. See the error log for more details. —> System.ArgumentNullException: Value cannot be null.
Parameter name: value
at Microsoft.ReportingServices.WebServer.ReportExecutionService.Initialize(TrustedUserHeader userHeader, String item)
at Microsoft.ReportingServices.WebServer.ReportExecutionService.LoadReport(String Report, String HistoryID, ExecutionInfo& executionInfo)

Server stack trace:
at System.ServiceModel.Channels.ServiceChannel.HandleReply(ProxyOperationRuntime operation, ProxyRpc& rpc)
at System.ServiceModel.Channels.ServiceChannel.Call(String action, Boolean oneway, ProxyOperationRuntime operation, Object[] ins, Object[] outs, TimeSpan timeout)

I checked the log reports but couldn’t find any clue, I did googling but couldn’t find any solid solution and after getting tips and tricks from different posts I found following things I needed to do in:

historyId = null;

If you are getting some error regarding SnapshotId, be sure that you have passed historyId as null.

After load report method:
execHeader.ExecutionID = execInfo.ExecutionID;

assign executionId of your current execution to header so that it can be used in other steps.

and while rendering the report through Render method pass TrustedUserHeader parameter as null.

This was the most hectic thing to find because System was throwing null value exception and it got fixed when I passed TrustedUserHeader as null. ­čśÇ

After doing these tweaks the windows service got the report from the web service.

ReportExecutionService.ReportExecutionServiceSoapClient rsExec = new ReportingServiceTesting.ReportExecutionService.ReportExecutionServiceSoapClient();

      System.Net.NetworkCredential clientCredentials = new System.Net.NetworkCredential("me", "me", "me");
      rsExec.ClientCredentials.Windows.AllowedImpersonationLevel = System.Security.Principal.TokenImpersonationLevel.Impersonation;
      rsExec.ClientCredentials.Windows.ClientCredential = clientCredentials;

      string historyID = "";
      string reportPath = "/V3/Operations/OrderssByCustomers";

      ReportExecutionService.ExecutionInfo execInfo = new ReportExecutionService.ExecutionInfo();
      ReportExecutionService.TrustedUserHeader trusteduserHeader = new ReportingServiceTesting.ReportExecutionService.TrustedUserHeader();

      ReportExecutionService.ExecutionHeader execHeader = new ReportExecutionService.ExecutionHeader();
      ReportExecutionService.ServerInfoHeader serviceInfo = new ReportingServiceTesting.ReportExecutionService.ServerInfoHeader();
      rsExec.LoadReport(trusteduserHeader, reportPath, historyID, out serviceInfo, out execInfo);

      ReportExecutionService.ParameterValue[] executionParams;
      executionParams = new ReportExecutionService.ParameterValue[5];
      executionParams[0] = new ReportExecutionService.ParameterValue();
      executionParams[0].Name = "@pCompanyId";
      executionParams[0].Value = "1";
      executionParams[1] = new ReportExecutionService.ParameterValue();
      executionParams[1].Name = "@pUserId";
      executionParams[1].Value = "1";
      executionParams[2] = new ReportExecutionService.ParameterValue();
      executionParams[2].Name = "@pLanguageId";
      executionParams[2].Value = "1";
      executionParams[3] = new ReportExecutionService.ParameterValue();
      executionParams[3].Name = "@pOrganizationId";
      executionParams[3].Value = "1";
      executionParams[4] = new ReportExecutionService.ParameterValue();
      executionParams[4].Name = "@pThemeId";
      executionParams[4].Value = "1";

      rsExec.SetExecutionParameters(execHeader,trusteduserHeader, executionParams, "en-us",out execInfo);

      //string historyID = null;
      string deviceInfo = null;
      string extension;
      string encoding;
      string mimeType;
      ReportExecutionService.Warning[] warnings = new ReportingServiceTesting.ReportExecutionService.Warning[1];
      warnings[0] = new ReportingServiceTesting.ReportExecutionService.Warning();
      string[] streamIDs = null;
      string format = "EXCEL";
      Byte[] result;
      rsExec.Render(execHeader,trusteduserHeader, format,
        deviceInfo,out result, out extension,
        out mimeType, out encoding,
        out warnings, out streamIDs);
      String fileName = "me.xls";
      FileStream stream = File.OpenWrite(fileName);
      stream.Write(result, 0, result.Length);
      stream.Close();