I've never used Web-service calls using Delphi and wondered if ANYONE has any sample code out there that shows how to interface with Microsoft SQL Server Reporting Services (SSRS) Web-service? I did a WSDL import of the local service on our servers here into my Delphi app, but now I'm stumped as to how i interface with the service. Anyone? Bueller? Bueller?
Vote for best question.
Score: 0
# Vote: 0
Date Posted: 28-Sep-2009, at 11:38 AM EST
From: James Grace
Re: Any sample code calling Sql Server Reporting Services (S
> {quote:title=James Grace wrote:}{quote}
> I've never used Web-service calls using Delphi and wondered if ANYONE has any sample code out there that shows how to interface with Microsoft SQL Server Reporting Services (SSRS) Web-service? I did a WSDL import of the local service on our servers here into my Delphi app, but now I'm stumped as to how i interface with the service. Anyone? Bueller? Bueller?
You can use the endpoint to create a WSDL from MSSQL Server.
Please read about the endpoint in the Book Online.
I hope this can help your problem.
To create a WSDL from MSSQL there are some rules that you should know.
Please join in http://www.sqlservercentral.com to get more specific.
cheers,
Eko Indriyawan
--
Blog: http://ekoindri.com/
Specialization Expertise:MS SQL Server, Web Service, Accounting
Vote for best answer.
Score: 0
# Vote: 0
Date Posted: 28-Sep-2009, at 4:20 PM EST
From: Eko Indriyawan
Re: Any sample code calling Sql Server Reporting Services (S
Thanks for the quick response, Eko. However, I have successfully generated a WSDL import of the SSRS Web-service on our local reporting server and can address that namespace, however I don't have any code as to how to interact with the SSRS web-service and wondered if perhaps someone may have interacted with the service in Delphi to generate reports (in my case, a PDF into a file folder on the server).
James
> {quote:title=Eko Indriyawan wrote:}{quote}
> > {quote:title=James Grace wrote:}{quote}
> > I've never used Web-service calls using Delphi and wondered if ANYONE has any sample code out there that shows how to interface with Microsoft SQL Server Reporting Services (SSRS) Web-service? I did a WSDL import of the local service on our servers here into my Delphi app, but now I'm stumped as to how i interface with the service. Anyone? Bueller? Bueller?
>
> You can use the endpoint to create a WSDL from MSSQL Server.
> Please read about the endpoint in the Book Online.
> I hope this can help your problem.
>
> To create a WSDL from MSSQL there are some rules that you should know.
> Please join in http://www.sqlservercentral.com to get more specific.
>
> cheers,
>
>
> Eko Indriyawan
>
> --
> Blog: http://ekoindri.com/
> Specialization Expertise:MS SQL Server, Web Service, Accounting
Vote for best answer.
Score: 0
# Vote: 0
Date Posted: 29-Sep-2009, at 8:13 AM EST
From: James Grace
Re: Any sample code calling Sql Server Reporting Services (S
Hello James,
Now, I'm learning to SSRS, if successful I'll let you know.
If not I reply to this thread, then I still fail.
Best Regards,
Eko Indriyawan
--
Blog: http://ekoindri.com/
Specialization Expertise:MS SQL Server, Web Service, Accounting
Vote for best answer.
Score: 0
# Vote: 0
Date Posted: 1-Oct-2009, at 12:15 PM EST
From: Eko Indriyawan
Re: Any sample code calling Sql Server Reporting Services (S
> {quote:title=Jean-Marie Babet wrote:}{quote}
> Hello James,
>
> Unless someone has specifically worked with that service... it's best to
> post any info you have. For example, if you have a WSDL and any
> documentation... we can figure out whether/how it will work with Delphi SOAP
> (I've done for many many services that I never use).
>
> And additionally, Eko reported that our importer has problems with WSDLs
> generated by MSSQL. I'm particularly interested in investigating that issue.
> So I would be grateful to have a copy of an MSSQL WSDL to look into that
> issue - and help also explain how the interface exposed by the service could
> be invoked with Delphi.
>
Hello Bruneau,
Please check your email, I've sent you an email
Best Regards,
Eko Indriyawan
--
Blog: http://ekoindri.com/
Specialization Expertise:MS SQL Server, Web Service, Accounting
Vote for best answer.
Score: 0
# Vote: 0
Date Posted: 8-Oct-2009, at 3:06 PM EST
From: Eko Indriyawan
Re: Any sample code calling Sql Server Reporting Services (S
Bruneau,
Apologies for the delayed reply... I worked my way around this problem by using the RS.EXE (via Delphi ShellExecute) to create and store my PDF's. It looks like RS.EXE is smart enough to transact with the Web-service to generate my reports. Not quite as efficient as a direct Delphi call to the SSRS web-service but at least it works.
The version of Delphi is Borland Developer Studio 2006 Win.
Jim
> {quote:title=Jean-Marie Babet wrote:}{quote}
> Hello,
>
> Thanks for the Delphi binding generated by the importer. What version of
> Delphi are you using? I'm asking because I find the binding generated by the
> importer a little strange. It's lacking something that I would expect.
> Typically, in the initialization section I would expect to see the
> following:
>
>
> InvRegistry.RegisterInvokeOptions(TypeInfo(ReportExecutionServiceSoap),
> ioDocument);
>
>
> Without that line, the SOAP runtime will treat the service as an rpc|encoded
> service - which is an older standard that I doubt SSRS is using. I know that
> earlier versions of our importer had problems detecting newer document style
> services. So I'm suspecting that maybe you have an earlier version of the
> importer. It's possible also that I'm wrong and that the WebService is an
> rpc|encoded service but I would be very very surprised if that were the
> case.
>
> That said looking at the methods in interface in the Delphi binding (i.e.
> ReportExecutionServiceSoap) I assume that the first one does is call the
> 'LogonUser' method. I then went online to look for LogonUser and found this:
>
>
> http://msdn.microsoft.com/en-us/library/aa225885(SQL.80).aspx
>
>
> The online matches the Delphi one:
>
> VB
> Public Sub LogonUser( _
> ByVal userName As String _
> ByVal password As String _
> ByVal authority As String _
> )
>
>
> Delphi
> procedure LogonUser(const userName: WideString; const password:
> WideString; const authority: WideString); stdcall;
>
>
> The typical Delphi code to invoke the above would read as follows:
>
>
> procedure TForm1.Button2Click(Sender: TObject);
> var
> Service: ReportExecutionServiceSoap;
> begin
> Service := GetReportExecutionServiceSoap(False, '', HTTPRIO1);
> Service.LogonUser('Put Your Username Here',
> 'Put the Password Here',
> '');
>
> end;
>
>
>
> Can you confirm whether the above works for you? And if possible, could you
> email me a copy of the WSDL - I'm curious about my suspicion of a document
> style service not being correctly identified? Thank you.
>
> Cheers,
>
> Bruneau
Vote for best answer.
Score: 0
# Vote: 0
Date Posted: 23-Oct-2009, at 8:59 AM EST
From: James Grace
Re: Any sample code calling Sql Server Reporting Services (S
> {quote:title=James Grace wrote:}{quote}
> I've never used Web-service calls using Delphi and wondered if ANYONE has any sample code out there that shows how to interface with Microsoft SQL Server Reporting Services (SSRS) Web-service? I did a WSDL import of the local service on our servers here into my Delphi app, but now I'm stumped as to how i interface with the service. Anyone? Bueller? Bueller?
I know this is very late in this thread, but I searched for ages on the web and couldn't find any example code for interfacing with Reporting Services. Here's some I hacked together, hope someone finds it useful:
{code}
uses ReportService2005, ReportExecution2005, SoapHTTPClient;
// get reportservice2005 and reportexecution2005 by importing the appropriate wsdl
procedure SaveSSRSReportToFile(const APDFFileName: string);
const
ServiceURL = 'http://SQLSERVER2005/reportserver/ReportService2005.asmx';
ExecURL = 'http://SQLSERVER2005/reportserver/ReportExecution2005.asmx';
ReportName = '/Folder 1/Folder 2/Folder 3/A Long Report Name';
var
args: array[0..4] of string;
rs: ReportService2005.ReportingService2005Soap;
rsExec: ReportExecution2005.ReportExecutionServiceSoap;
GRParams: ReportService2005.GetReportParameters;
RepParamsResponse: ReportService2005.GetReportParametersResponse;
LRParams: ReportExecution2005.LoadReport;
LoadParamsResponse: ReportExecution2005.LoadReportResponse;
i: Integer;
setParams: ReportExecution2005.SetExecutionParameters;
setParamValues: ReportExecution2005.ArrayOfParameterValue;
renderParams: ReportExecution2005.Render;
renderResponse: ReportExecution2005.RenderResponse;
setParamsResponse: ReportExecution2005.SetExecutionParametersResponse;
fileStream: TFileStream;
execHeader, renderHeader: ReportExecution2005.ExecutionHeader;
HTExec: THttpRIO;
begin
args[0] := 'Param1'; // note params need to be correct length (I had one that was 3 characters, but was only passing two, needed a trailing space to make it work)
args[1] := 'Param2';
args[2] := 'Param3';
args[3] := 'Null'; // "Null" is just a bit of text that is used below to create a "Null" parameter
GRParams := GetReportParameters.Create;
setParams := SetExecutionParameters.Create;
LRParams := LoadReport.Create;
renderParams := Render.Create;
execHeader := ExecutionHeader.Create;
renderHeader := ExecutionHeader.Create;
HTExec := tHttpRio.Create(nil);
try
rs := GetReportingService2005Soap(False, ServiceURL);
rsExec := GetReportExecutionServiceSoap(False, ExecURL, HTExec);
try
GRParams.Report := ReportName;
GRParams.ForRendering := False;
RepParamsResponse := rs.GetReportParameters(GRParams);
// Fill the Parameters to call in the SetExecutionParameters later
SetLength(setParamValues, Length(RepParamsResponse.Parameters));
for i := 0 to Length(RepParamsResponse.Parameters)-1 do
begin
setParamValues[i] := ParameterValue.Create;
setParamValues[i].Name_ := RepParamsresponse.Parameters[i].Name_;
if args[i] <> 'Null' then // do not set the param if you want it to be null
setParamValues[i].Value := args[i];
end;
setParams.ParameterLanguage := 'en-us';
setParams.parameters := setParamValues;
// Load the selected report.
LRParams.Report := ReportName;
LoadParamsResponse := rsExec.LoadReport(LRParams);
// Send ExecutionHeader
execHeader.ExecutionID := LoadParamsResponse.executionInfo.ExecutionID;
HTExec.SOAPHeaders.Send(execHeader);
// Set the Parameters
setParamsResponse := rsExec.SetExecutionParameters(setParams);
renderParams.Format := 'PDF';
// Send RenderHeader
renderHeader.ExecutionID := setParamsResponse.executionInfo.ExecutionID;
HTExec.SOAPHeaders.Send(renderHeader);
renderResponse := rsExec.Render(renderParams);
fileStream := TFileStream.Create(APDFFileName, fmCreate);
try
fileStream.Write(renderResponse.Result[0], Length(renderResponse.Result));
finally
FreeAndNil(fileStream);
end;
except
on E: Exception do
begin
ShowMessage(E.Message);
end;
end;
finally
GRParams.Free;
setParams.Free;
LRParams.Free;
renderParams.Free;
renderHeader.Free;
execHeader.Free;
RepParamsResponse.Free;
LoadParamsResponse.Free;
setParamsResponse.Free;
renderResponse.Free;
for i := 0 to Length(setParamValues) - 1 do
setParamValues[i].Free;
SetLength(setParamValues, 0);
//FreeAndNil(HTExec);
rs := nil;
rsExec := nil;
end;
end;
{code}
Edited by: Pat Buxton on Mar 2, 2011 7:50 AM
Vote for best answer.
Score: 0
# Vote: 0
Date Posted: 2-Mar-2011, at 11:52 AM EST
From: Anonymous
Re: Any sample code calling Sql Server Reporting Services (S
> {quote:title=Pat Buxton wrote:}{quote}
>
> I know this is very late in this thread, but I searched for ages on the web and couldn't find any example code for interfacing with Reporting Services. Here's some I hacked together, hope someone finds it useful:
>
> {code}
> uses ReportService2005, ReportExecution2005, SoapHTTPClient;
> // get reportservice2005 and reportexecution2005 by importing the appropriate wsdl
>
> procedure SaveSSRSReportToFile(const APDFFileName: string);
> const
> ServiceURL = 'http://SQLSERVER2005/reportserver/ReportService2005.asmx';
> ExecURL = 'http://SQLSERVER2005/reportserver/ReportExecution2005.asmx';
> ReportName = '/Folder 1/Folder 2/Folder 3/A Long Report Name';
> var
> args: array[0..4] of string;
> rs: ReportService2005.ReportingService2005Soap;
> rsExec: ReportExecution2005.ReportExecutionServiceSoap;
> GRParams: ReportService2005.GetReportParameters;
> RepParamsResponse: ReportService2005.GetReportParametersResponse;
> LRParams: ReportExecution2005.LoadReport;
> LoadParamsResponse: ReportExecution2005.LoadReportResponse;
> i: Integer;
> setParams: ReportExecution2005.SetExecutionParameters;
> setParamValues: ReportExecution2005.ArrayOfParameterValue;
> renderParams: ReportExecution2005.Render;
> renderResponse: ReportExecution2005.RenderResponse;
> setParamsResponse: ReportExecution2005.SetExecutionParametersResponse;
> fileStream: TFileStream;
> execHeader, renderHeader: ReportExecution2005.ExecutionHeader;
> HTExec: THttpRIO;
> begin
> args[0] := 'Param1'; // note params need to be correct length (I had one that was 3 characters, but was only passing two, needed a trailing space to make it work)
> args[1] := 'Param2';
> args[2] := 'Param3';
> args[3] := 'Null'; // "Null" is just a bit of text that is used below to create a "Null" parameter
> GRParams := GetReportParameters.Create;
> setParams := SetExecutionParameters.Create;
> LRParams := LoadReport.Create;
> renderParams := Render.Create;
> execHeader := ExecutionHeader.Create;
> renderHeader := ExecutionHeader.Create;
> HTExec := tHttpRio.Create(nil);
> try
> rs := GetReportingService2005Soap(False, ServiceURL);
> rsExec := GetReportExecutionServiceSoap(False, ExecURL, HTExec);
> try
> GRParams.Report := ReportName;
> GRParams.ForRendering := False;
> RepParamsResponse := rs.GetReportParameters(GRParams);
>
> // Fill the Parameters to call in the SetExecutionParameters later
> SetLength(setParamValues, Length(RepParamsResponse.Parameters));
> for i := 0 to Length(RepParamsResponse.Parameters)-1 do
> begin
> setParamValues[i] := ParameterValue.Create;
> setParamValues[i].Name_ := RepParamsresponse.Parameters[i].Name_;
> if args[i] <> 'Null' then // do not set the param if you want it to be null
> setParamValues[i].Value := args[i];
> end;
> setParams.ParameterLanguage := 'en-us';
> setParams.parameters := setParamValues;
>
> // Load the selected report.
> LRParams.Report := ReportName;
> LoadParamsResponse := rsExec.LoadReport(LRParams);
>
> // Send ExecutionHeader
> execHeader.ExecutionID := LoadParamsResponse.executionInfo.ExecutionID;
> HTExec.SOAPHeaders.Send(execHeader);
>
> // Set the Parameters
> setParamsResponse := rsExec.SetExecutionParameters(setParams);
> renderParams.Format := 'PDF';
>
> // Send RenderHeader
> renderHeader.ExecutionID := setParamsResponse.executionInfo.ExecutionID;
> HTExec.SOAPHeaders.Send(renderHeader);
> renderResponse := rsExec.Render(renderParams);
> fileStream := TFileStream.Create(APDFFileName, fmCreate);
> try
> fileStream.Write(renderResponse.Result[0], Length(renderResponse.Result));
> finally
> FreeAndNil(fileStream);
> end;
> except
> on E: Exception do
> begin
> ShowMessage(E.Message);
> end;
> end;
> finally
> GRParams.Free;
> setParams.Free;
> LRParams.Free;
> renderParams.Free;
> renderHeader.Free;
> execHeader.Free;
> RepParamsResponse.Free;
> LoadParamsResponse.Free;
> setParamsResponse.Free;
> renderResponse.Free;
> for i := 0 to Length(setParamValues) - 1 do
> setParamValues[i].Free;
> SetLength(setParamValues, 0);
> //FreeAndNil(HTExec);
> rs := nil;
> rsExec := nil;
> end;
> end;
> {code}
>
Thanks Pat, for posting the code. It was unbelievably helpful.
I grabbed the code did some minor debugging and it worked like a charm.
I know this is even later in the post but I am hoping to try to takes this a little further.
I need to change the Datasource of the report depending on which Database I want to use.
I then want to display the report to the screen instead of export to PDF.
Has anyone accomplished this kind of thing or perhaps someone can point in the right direction as to where to look for more information.
I am new to SSRS and Delphi XE.
Thanks,
Leonard
Vote for best answer.
Score: 0
# Vote: 0
Date Posted: 27-Jun-2011, at 4:18 PM EST
From: Leonard Mann
Re: Any sample code calling Sql Server Reporting Services (S
> {quote:title=Leonard Mann wrote:}{quote}
>
> I need to change the Datasource of the report depending on which Database I want to use.
Just as an update if anyone is interested.
I was able to dynamically change the Database by using an Expression as the Data Connection string for the datasource.
="Data Source=(ServerName);Initial Catalog=" & Parameters!Database.Value
I then passsed my Database as a parameter and I accomplished what I needed.
I could pass the Server using the same method
="Data Source=" & Parameters!Server.Value & ";Initial Catalog=" & Parameters!Database.Value
Still looking into how to send the report stright to the browser from within Delphi.
Mega Search
Sign Up 
