2012年2月16日星期四

Battling to programmatically load a report

Hi friends,

I am struggling to programmatically render my report to a pdf without displaying it in a viewer and would appreciate a pointer in the right direction.

My code seems to work and the report is found and everything is honkey-dorey until I try to actually load the report.. lemme show: (please excuse the bit of a mess since this is just a test form)

rs = new rs2005.ReportingService2005();
rsExec = new rsExecService.ReportExecutionService();
// Prepare Render arguments
string historyID = null;
string deviceInfo = null;
string format = "PDF";
Byte[] results;
string encoding = String.Empty;
string mimeType = String.Empty;
string extension = String.Empty;
rsExecService.Warning[] warnings = null;
string[] streamIDs = null;

// Define variables needed for GetParameters() method
string _historyID = null;
bool _forRendering = false;
rs2005.ParameterValue[] _values = null;

// Authenticate to the Web service using Windows credentials
rs.Credentials = System.Net.CredentialCache.DefaultCredentials;
rsExec.Credentials = System.Net.CredentialCache.DefaultCredentials;

rs2005.DataSourceCredentials[] _credentials = null;
rs2005.ReportParameter[] _parameters = null;

FindReport();
//Create a variable containing the selected item
selItem = MyCatalogItem.Item;
try
{
// Get if any parameters needed.
_parameters = rs.GetReportParameters(selItem.Path , _historyID, _forRendering, _values, _credentials );

// Load the selected report.
rsExecService.ExecutionInfo ei = rsExec.LoadReport(selItem.Path, historyID);

// Prepare report parameter.
// Set the parameters for the report needed.
rsExecService.ParameterValue[] parameters = new rsExecService.ParameterValue[5];

// Place to include the parameter..
if (_parameters.Length > 0)
{
parameters[0] = new rsExecService.ParameterValue();
parameters[0].Value = pQuoteID.ToString();
parameters[0].Label = "Quote ID";
parameters[0].Name = "cor_Quote_id";

parameters[1] = new rsExecService.ParameterValue();
parameters[1].Value = DateTime.Now.ToString();
parameters[1].Label = "Quote Start Period";
parameters[1].Name = "QuoteStartPeriod";

parameters[2] = new rsExecService.ParameterValue();
parameters[2].Value = DateTime.Now.ToString();
parameters[2].Label = "Quote End Period";
parameters[2].Name = "QuoteEndPeriod";

parameters[3] = new rsExecService.ParameterValue();
parameters[3].Value = "Client";
parameters[3].Label = "Client";
parameters[3].Name = "ClientName";

parameters[4] = new rsExecService.ParameterValue();
parameters[4].Value = "Ralph Contact";
parameters[4].Label = "Ralph Contact";
parameters[4].Name = "RaljacContact";

}
rsExec.SetExecutionParameters(parameters, "en-us");
results = rsExec.Render(format, deviceInfo, out mimeType, out encoding, out warnings, out streamIDs);

// Create a file stream and write the report to it
using (FileStream stream = File.OpenWrite(fileName))
{
stream.Write(results, 0, results.Length);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}

Right, It bombs out at the section I marked in blue and gives me this HUGE message :

"System.Web.Services.Protocols.SoapException: Server did not recognize the value of HTTP Header SOAPAction: http://schemas.microsoft.com/sqlserver/2005/01/12/reporting/reportingservices/LoadReport.\n at System.Web.Services.Protocols.Soap11ServerProtocolHelper.RouteRequest()\n at System.Web.Services.Protocols.SoapServerProtocol.RouteRequest(SoapServerMessage message)\n at System.Web.Services.Protocols.SoapServerProtocol.Initialize()\n at System.Web.Services.Protocols.ServerProtocolFactory.Create(Type type, HttpContext context, HttpRequest request, HttpResponse response, Boolean& abortProcessing)"

Could this be related to credentials or something? Any advice welcome.
Thanks Mates

I remember using streams to do this many years ago (2004), but as I am a sloth, I have included this link for your perusal

http://www.codeproject.com/sqlrs/PDFUsingSQLRepServices.asp|||LOL It's a funny coincidence that you posted that link since I was working of that code to get my code to where it is. It's almost exactly the same except that my report doesn't load because of some sort of code or server setting problem I'm sure.

Thanks for the reply still. I appreciate it.

Regards
Mike
|||dumb question time....
you are using 2005, aren't you?|||Yes sir.
|||Can you prove that it is def using the 2005asmx and not the 2003asmx?

Am guessing the '2005' object at the top will automatically do this, but you never know.

I had a problem here with some users who had a soap header error and had to downgrade them to using the 2003 service. They were calling it from some java unix *** which couldn't seem to cope with the new definition.|||Maybe try setting the URL property of both web services. I had a similar issue before I did that.

rs.Url = "http://" & SERVER_NAME & "/ReportServer/ReportService2005.asmx?wsdl"

|||Ok I will try that and also make sure that and see what it comes up with.

Thanks for the reply

|||Hi,

I tried setting the rs manually and it still gives me the same Error: server did not recognize the http soap header...

I wonder why it just seems to bomb out on the following line of code:
rsExecService.ExecutionInfo ei = rsExec.LoadReport(selItem.Path, historyID);

Regards
Mike
|||

Hey guys,

It would probably be a lot easier if you did this *without* SOAP. You can programmatically instantiate a reportviewer without showing it, btw. You will find instructions here... http://gotreportviewer.com/ (see Print a report from a console app -- you can render the thing to a PDF instead of page EMFs if you want the PDF instead of printing pages).

But you don't even need to do that. Here is an example I wrote for somebody else as part of a larger thing he was trying to solve. (Ignore the comments about package vars, he was doing this in SSIS). Basically the idea is to use URL access specifying a PDF in your case (see the comments about report parameters and URL querystring).

I think I should have used System.Net.WebRequest instead of the class I used in this code. But you might be using completely different classes in a completely different environment. This is straight HTTPRequest stuff, you can do it in javascript if you need to <g>.

"Lisa Slater Nicholls" <lisa@.spacefold.com> wrote in message news:%23xdLwmwoHHA.4400@.TK2MSFTNGP03.phx.gbl...

OK, it wasn't that bad. And lucky you I've been kinda working on integrating SSIS with some reporting stuff anyway... so I was curious about how this would work...

Below is some sample script that you could use inside SSIS**. Just remember that, where I've hard-coded a sample report URL , you will actually be building it up based on package variables. And that includes your params.

** Caveat: it works as-is in SSIS except that I had to do something that looked really silly as an explicit cast, and I've included that as a comment because I'm not sure it's necessary.Notice how the URL includes a query string representing the path of the report and two options (in this case, rs:Command and rs:Format). These are arguments that belong to the report engine, and you can see it because of their rs: prefix. But if you had (say) a parameter for this report such as PopulationGreaterThan (the report in my example URL is based on a table of cities in the MySQL standard sample database), you could add this right onto the end of the URL:

&PopulationGreaterThan=50000

... got that? I give users URLs that include report params all the time... So, here's the code... Have fun.. >L<

' substitute the following line when working in SSIS
' for the instantiation of the HttpWebRequest object you see below:

' Dim ox As System.Net.HttpWebRequest = CType(System.Net.HttpWebRequest.Create _
' ("http://localhost/reportserver/?/Report+Project1/TestCity&rs:Command=Render&rs:format=EXCEL"), HttpWebRequest)

Dim ox As System.Net.HttpWebRequest = _

System.Net.HttpWebRequest.Create _
("http://localhost/reportserver/?/Report+Project1/TestCity&rs:Command=Render&rs:format=EXCEL")

'just substitute &rs:Format=PDF for &rs:format=EXCEL in the line above for a PDF


ox.UseDefaultCredentials = True

' the line above may not work for you, you may have to provide credential information with more work


Dim oy As System.Net.HttpWebResponse = ox.GetResponse()
Dim raw As System.IO.FileStream = New System.IO.FileStream("c:\temp\x.xls", IO.FileMode.Create)

' for a PDF, change the extension in the line above, along with changing its

' name and folder based on (obviously!) more package vars
Dim buffer(1024) AsByte
Dim rs As System.IO.Stream = oy.GetResponseStream()
Dim read AsInteger = rs.Read(buffer, 0, buffer.Length)
While (read > 0)
raw.Write(buffer, 0, read)
read = rs.Read(buffer, 0, buffer.Length)
EndWhile
oy.Close()
raw.Close()
raw.Dispose()
rs.Close()
rs.Dispose()
oy = Nothing
ox = Nothing
raw = Nothing
rs = Nothing

|||Thanks Lisa,

I'm definitely going to give this a bash.

Will report back soon.

Regards
Mike
|||

Well, it definitely works <rofl>. I'll take REST over SOAP, for anything like this, any day.

>L<

|||

This code above works great for me as long as I don't attempt to add any report parameters to the querystring. To test the above code I created 2 reports:

report1 has no parameters

report2 has the parameter test

report1 runs fine:

http://localhost/reportserver/?/myreports/report1&rs:Command=Render&rs:format=EXCEL

report2 fails:

http://localhost/reportserver/?/myreports/report2&rs:Command=Render&rs:format=EXCEL&test=1

I get a 500 error at:

Dim oy As System.Net.HttpWebResponse = ox.GetResponse()

Any ideas?

thanks!

|||

Two things:

1) Remember I said it was better to use System.Net.WebRequest and System.Net.WebResponse (at least I think I remembered to say that when I reposted the example )? You really should... I don't think it's involved with your error, but do yourself a favor...

2) I definitely do this with parameters. However it is really easy to get it wrong, especially if you store the parts of the URL -- something may be escaping the "&", etc. I can't tell you exactly what is wrong with your requestURL but if you capture the URL you've built right before you do the WebRequest.Create(requestURL) you will probably see it. I have definitely seen that error when I screw this part up .

Below is an example from my code -- this is from inside an SSIS package, hence the DTS vars stuff. I know that the params are going through, because I can see the values in my results, but notice two interesting things: (a) I'm storing stuff separately, without including the ampersands in the values that I'm actually storing to package variables to avoid issues with their being escaped and (b) I'm using a kind of hokey trick to handle multiple params in one var rather than risking the ampersand there either.

3) Another thing that can give you the same error is if your deployed report doesn't actually have a parameter by that exact name <g>. So, did you just add that param into the designer, or did you remember to deploy it to the server? Sorry, but gotta ask...

HTH,

>L<

Code Snippet

requestURL = Dts.Variables("ExportRSURL").Value.ToString()

requestURL += "&rs:Command=Render"

requestURL += "&rs:Format=" & Dts.Variables("ExportRSType").Value.ToString()

If Len(Dts.Variables("ExportRSParams").Value.ToString()) > 0 Then

requestURL += "&" & Dts.Variables("ExportRSParams").Value.ToString().Replace("^", "&")

EndIf

|||Hi Lisa, once again, thank you for your input.

This might be a really dumb question but your method is awesome but a little new to me (coming from the SOAP method)
I seem to be having problems with a report with more than 1 parameter where some of the parameters can be of a null value. How do I pass a null value through the request? (I tried useing cor_Quote_id=null&.....
but that didn't seem to work.

Report code
System.Net.WebRequest ox = System.Net.WebRequest.Create("http://localhost/reportserver/?/Raljac/rptQuote&cor_Quote_id=null&QuoteStartPeriod=2005\\01\\01&QuoteEndPeriod=2006\\01\\01&ClientName=ABC&RaljacContact=Rodney&rs:Command=Render&rs:format=PDF");

I get your method to work awesomely for reports with only 1 parm though Smile

Regards

没有评论:

发表评论