Microsoft Sql Reporting Services - Connect and Render from Delphi

[bruno-pere]

Hi!

 

If anyone wants I have successfully connected via SOAP to MSRS to render any report.

 

* Connection using SOAP and THttpRio component;

* Using ReportService2010 and ReportExecution2005 webservices;

* Basic authentication;

* Get list of reports of any folder;

* Pass parameters;

* Render the report in any format (PDF, EXCEL, MHTML, HTML4.0, HTML3.2, TIFF and every format MSRS supports);

* Send the stream (file) to the user of UniGUI.

 

The only thing I didn't do yet is get list of parameters, what is not difficult, but in my current frame I pass the parameters I know the report needs.

 

Bye!

 

Bruno

[stiaan]

Hi Bruno

 

Can you share this please. I'm interested in this.

 

Regards

Stiaan

[eduardosuruagy]

+1

[Harry Rogers]

Sounds interesting, please share

 

Many thanks

[fredycc]

Hello Bruno.

 

We wish you could share.

 

regards

[rsanford]

Hi Bruno,

I'm also interested. A demo app would be great if possible. 

 

Thanks!

[bruno-pere]

Hi!

 

Sorry for the delay. And I don't have time to make a sample and the procedure to Render the report is used for one of my reports only. If you want a generic procedure you should do changes like remove the two hardcoded parameters in the procedure. Test with a simple report first.

 

 

 

 

1) IMPORT CLASSES IN YOUR PROJECT (Delphi -> Menu Component -> Import WSDL)

        ReportExecution2005 (http://YOURSERVER/ReportServer/ReportExecution2005.asmx?wsdl)
        ReportService2010 (http://YOURSERVER/ReportServer/ReportService2010.asmx?wsdl)

 

 

 

 

 

2) DECLARE IN THE FORM YOU WILL CALL THE REPORT:

		procedure TYOURFORM.Rio1HTTPWebNode1BeforePost(
		  const HTTPReqResp: THTTPReqResp; Data: Pointer);
		var
		  user,pass: string;
		begin
		  user := 'srv-test\administrador';
		  pass := 'yourpassword'; // the same account / password you use when using the browser to view the reports

		 if not InternetSetOption(Data,
					   INTERNET_OPTION_USERNAME,
					   PChar(user),
					   Length(user)) then
			 ShowMessage(SysErrorMessage(GetLastError));

		  if not InternetSetOption(Data,
					   INTERNET_OPTION_PASSWORD,
					   PChar(pass),
					   Length (pass)) then
			 ShowMessage(SysErrorMessage(GetLastError));

		end;

		// PARAMETERS DataEnt and CodComprador YOU SHOULD CHANGE FOR YOUR REPORT PARAMETER
		function TYOURFORM.RenderReport1(stm: TStream; const ExtFormat: string;
		  DataEnt: TDateTime; CodComprador: integer):boolean;
		const
		  ServiceURL = 'http://YOURSERVER/reportserver/ReportService2010.asmx'; // change for YOUR SERVER 
		  ExecURL = 'http://YOURSERVER/reportserver/ReportExecution2005.asmx';
		var
		  //rs: ReportService2010.ReportingService2010Soap;
		  rsExec: ReportExecution2005.ReportExecutionServiceSoap;


		  setParams: ReportExecution2005.SetExecutionParameters;
		  setParamValues: ReportExecution2005.ArrayOfParameterValue;
		  LRParams: ReportExecution2005.LoadReport;
		  LoadParamsResponse: ReportExecution2005.LoadReportResponse;

		  renderParams: ReportExecution2005.Render;
		  renderResponse: ReportExecution2005.RenderResponse;
		  setParamsResponse: ReportExecution2005.SetExecutionParametersResponse;

		  execInfo: ReportExecution2005.ExecutionInfo;
		  execHeader, renderHeader: ReportExecution2005.ExecutionHeader;

		  I: integer;

		  //args: array [0..4] of string;
		  Rio1: THttpRio;
		begin
		  result := false;

		  LoadParamsResponse := nil;
		  setParamsResponse := nil;

		  Rio1 := THttpRIo.Create(nil);

		  Rio1.HTTPWebNode.OnBeforePost := Rio1HTTPWebNode1BeforePost;
		  Rio1.HTTPWebNode.UserName := 'extrafruti\administrador';
		  Rio1.HTTPWebNode.Password := 'yourpassword'; // the same account / password you use when using the browser to view the reports

		  {Rio2.HTTPWebNode.UserName := 'srv-teste\administrador';
		  Rio2.HTTPWebNode.Password := '*****';}

		  //rs := ReportService2010.GetReportingService2010Soap(False, ServiceURL, Rio2);
		  rsExec := ReportExecution2005.GetReportExecutionServiceSoap(False, ExecURL, Rio1);

		  execInfo := ReportExecution2005.ExecutionInfo.Create;
		  execHeader := ReportExecution2005.ExecutionHeader.Create;

		  LRParams := ReportExecution2005.LoadReport.Create;
		  setParams := ReportExecution2005.SetExecutionParameters.Create;
		  renderHeader := ReportExecution2005.ExecutionHeader.Create;
		  renderResponse := ReportExecution2005.RenderResponse.Create;
		  renderParams := ReportExecution2005.Render.Create;

		  try
			  try
				  setlength(setParamValues,0);

				  // Load the selected report.
				  LRParams.Report := '/teste2'; // the report name in the root folder in this case
				  LoadParamsResponse := rsExec.LoadReport(LRParams);

				  // Send ExecutionHeader
				  execHeader.ExecutionID := LoadParamsResponse.executionInfo.ExecutionID;
				  Rio1.SOAPHeaders.Send(execHeader);


				  // SET LENGTH EQUAL TO THE NUMBER OF PARAMETERS OF THE REPORT
				  // AND PASS IN THE ARRAY LIKE I DID
				  SetLength(setParamValues, 2);
				  // parâmetro 1 (FIRST PARAMETER)
				  setParamValues[0] := ReportExecution2005.ParameterValue.Create;
				  setParamValues[0].Name_ := 'DATA_ENT';
				  setParamValues[0].Value := FormatDateTime('dd/MM/yyyy',DataEnt);
				  // parâmetro 2 (SECOND PARAMETER)
				  setParamValues[1] := ReportExecution2005.ParameterValue.Create;
				  setParamValues[1].Name_ := 'COD_COMPRADOR';
				  setParamValues[1].Value := IntToStr(CodComprador);


				  // change to your language
				  setParams.ParameterLanguage := 'pt-BR';
				  setParams.parameters := setParamValues;

				  setParamsResponse := rsExec.SetExecutionParameters(setParams);

				  renderParams.Format := ExtFormat;

				  // Send RenderHeader
				  renderHeader.ExecutionID := LoadParamsResponse.executionInfo.ExecutionID;
				  Rio1.SOAPHeaders.Send(renderHeader);
				  renderResponse := rsExec.Render(renderParams);

				  stm.Write(renderResponse.Result[0], Length(renderResponse.Result));

				  result := true;

			  except
				  on E: Exception do
				  begin
					MessageDlg(
					  'Error creating report: ' + E.Message + #13#10 +
					  'Please contact.........',
					  mtError, []);
				  end;
			  end;
		  finally
			  for I := Low(setParamValues) to High(setParamValues) do
				if setParamValues[I]<>nil then setParamValues[I].Free;

			  if Assigned(setParamsResponse) then setParamsResponse.Free;
			  if Assigned(renderResponse) then renderResponse.Free;
			  if Assigned(renderParams) then renderParams.Free;
			  if Assigned(execHeader) then execHeader.Free;
			  if Assigned(execInfo) then execInfo.Free;
			  if Assigned(LRParams) then LRParams.Free;

			  Rio1 := nil;
			  //RS := nil;
			  RSExec := nil;

		//      setParams.Free;


		  end;
		end;

3) CALL LIKE THIS

	fname := 'c:\test.html'; // YOUR FILE NAME must be the same extesion you will pass

		stm := TMemoryStream.Create;
		try
		  if RenderReport1(stm,'HTML4.0',UniDateEnt.DateTime,StrToInt(cp)) then // two parameters of my report
		    UniSession.SendStream(stm,ExtractFileName(fname));
		  
		  // you can mail the report too (SEE MY OTHER EXAMPLE IN UNIGUI FORUM)
		finally
		  stm.Free;
		end;

My uses:

 

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics,
  Controls, Forms, Dialogs, uniGUITypes, uniGUIAbstractClasses,
  uniGUIClasses, uniGUIFrame, uniEdit, uniBasicGrid, uniDBGrid, uniSplitter,
  uniPanel, uniTrackBar, uniButton, uniBitBtn, uniMultiItem, uniComboBox,
  uniLabel, uniGUIBaseClasses, uniGroupBox, uniDateTimePicker, Data.DB,
  Data.Win.ADODB, uniDBComboBox, uniDBLookupComboBox, DateUtils,
  Datasnap.DBClient, SOAPHTTPTrans, WinINet, Soap.InvokeRegistry, Soap.Rio,
  Soap.SOAPHTTPClient, uniScreenMask,

  IdExplicitTLSClientServerBase, IdMessage;

 

 

 

 

Please, share if you have success. It's working perfectly here.

 

 

 

 

Bye!

 

Bruno

[stiaan]

Much appreciated!

[bruno-pere]

Sample to get report parameters if needed:

http://msdn.microsoft.com/en-us/library/reportservice2005.reportingservice2005.getreportparameters.aspx

 

And use FindItems to get a list of reports:

http://msdn.microsoft.com/en-us/library/reportservice2005.reportingservice2005.finditems.aspx

 

I didn't use in this project but works because I have used it in another project.

As said, my project runs only one report in that function I shared and I pass the report name and parameters hard coded.

 

Bye!

 

Bruno