Simpler Excel export without generating report
Hi; I embeddeda reporting services report into an aspx page and it works really well. Here's the deal, most of the time, my clients are interested in the excel export more than the generated report. So that's why generating the report and clicking excel export is a big waste of time for them. So I decided to have a button on the page, so I thought on click event I could use some reporting services web service methods andreturn the user excel export, but as far as I made research, I couldn't see a method that can achieve that. I guess I can use some other thingsbesides reporting services but in a multitier environment, I am going to lose all the advantages(fastness, lack of timeouts)of being close to the reporting server. Another problem is, do we have any ways of modifying the excel export format without changing the report format. My clients are interested in something little bit fancier than the csv format, I want to have something really simple there without all the tables and coloring since they are going to use the exports in the financial softwares. Erinc
August 26th, 2008 5:05pm

You can use Reporting Service Webservice to export the report into Excel. You can use the render the method to any particular format . http://msdn.microsoft.com/en-us/library/aa258073(SQL.80).aspx For the outlining you can do that using code (C#) to format the cells of the rendered excel. http://support.microsoft.com/kb/302084 http://www.csharpfriends.com/Forums/ShowPost.aspx?PostID=24185 The way the code will work is : Using Reporting Servivce Webservice class,it will first export the report into Excel. The second part of teh code will be responsible for formatting the rendered excel. Hope this helps... Regards... Girija Shankar
Free Windows Admin Tool Kit Click here and download it now
August 26th, 2008 6:08pm

I bumped into another problem here, I am trying to use Render method as you suggested. I can't build when I have ReportingService object in my code, it says are you missing a reference, so I went forward and tried to add the Microsoft.ReportingService reference, but unfortunately it wasn't on the list, so I went under C:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services\ReportServer\bin even though thereare lots of dlls there, I thought that ReportingServicesLibrary.dll is the one that I want, but unfortunately it didn't help either. Any ideas on What should I do to make it work?
August 26th, 2008 6:47pm

You have to add a Web Reference to : http://<serverName>:<Port Number>/reportserver/reportservice.asmx?wsdl for example if server is : localhost and port is 80: http://localhost/reportserver/reportservice.asmx?wsdl This is the link which tells how to adda reference : http://msdn.microsoft.com/en-us/library/aa237438(SQL.80).aspx Additional Link: These links will help : http://msdn.microsoft.com/en-us/library/cc282207.aspx For render method : http://msdn.microsoft.com/en-us/library/aa258532%28SQL.80%29.aspx Regards.. Girija Shankar
Free Windows Admin Tool Kit Click here and download it now
August 26th, 2008 7:01pm

Thanks a lot; I think that I am really close to making it work, I added reference and I am able to build the project this time. I had some security errors, I added a role for network service, it solved that. And here's the latest problem, I can't seem to solve it, I ve been dealing with it for 4 hours, here's what Ive been doing, ReportingServices is the name of the web reference. Code Snippet //reportpath, parameter array is set somehow I didn't include it ReportingServices.DataSourceCredentials[] credentials = new ReportingServices.DataSourceCredentials[1]; credentials[0] = new CMT.ReportingServices.DataSourceCredentials(); credentials[0].UserName = "login"; credentials[0].Password = "passwd"; credentials[0].DataSourceName = "Transaction_RptDS"; string showHideToggle = null; string encoding; string mimeType; ReportingServices.Warning[] warnings = null; ReportingServices.ParameterValue[] reportHistoryParameters = null; string[] streamIDs = null; ReportingServices.SessionHeader sh = new ReportingServices.SessionHeader(); rservice.SessionHeaderValue = sh; rservice.Url = GetGlobalConfig("ReportingServiceURL"); rservice.Timeout = 180000; try { rservice.Render(ReportPath, "EXCEL", null, @"<DeviceInfo><OmitDocumentMap>False</OmitDocumentMap><OmitFormulas>False</OmitFormulas><RemoveSpace>0.125</RemoveSpace></DeviceInfo>", parameters, credentials, showHideToggle, out encoding, out mimeType, out reportHistoryParameters, out warnings, out streamIDs); sh.SessionId = rservice.SessionHeaderValue.SessionId; } catch (SoapException ex) { Console.WriteLine(ex.Detail.OuterXml); } but when It executes Render method it throws the following SOAP exception, whatever I do, I can't seem to fix it: Server did not recognize the value of HTTP Header SOAPAction: http://schemas.microsoft.com/sqlserver/2003/12/reporting/reportingservices/Render. Does anybody have idea about how to fix that?
August 27th, 2008 10:00pm

what version of SSRS are you using? Another thing I noticed in your Code . You should the return of render method in a byte array : byte[] <byteArrayName> = rservice.Render(ReportPath, "EXCEL", null, @"<DeviceInfo><OmitDocumentMap>False</OmitDocumentMap><OmitFormulas>False</OmitFormulas><RemoveSpace>0.125</RemoveSpace></DeviceInfo>", parameters, credentials, showHideToggle, out encoding, out mimeType, out reportHistoryParameters, out warnings, out streamIDs); Regards... Girija Shankar
Free Windows Admin Tool Kit Click here and download it now
August 27th, 2008 10:05pm

I have SQL Server 2005. And I am using: Microsoft SQL Server Reporting Services Version 9.00.1406.00 , I am thinking that there might be something wrong in security settings, Network Service user is assigned browser role, should I modify something else? Thanks for the warning, I didn't assign the return value to a variablebecause right now my focus isto make it work without the SoapException.
August 27th, 2008 10:15pm

I added the web reference by using: http://localhost/reportserver/reportservice.asmx?wsdl I use the following url when I try to bindthe proxyto an endpoint: http://localhost/ReportServer/ReportExecution2005.asmx which seems to be correct. I am not really sure how to solve this. Erinc
Free Windows Admin Tool Kit Click here and download it now
August 27th, 2008 10:35pm

Add the Reporting ExecutionWeb reference also to the project.. Regards.. Girija Shankar
August 27th, 2008 10:37pm

Thanks Girija; Excuse my newbieness in Reporting Services web service. But I added the web service reference for Execution Web by searching for http://localhost/reportserver/ReportExecution2005.asmx and after that I imported thatto my aspx file by using Project.Reporting.ExecutionWeb, but adding that didn't fix my problem at all. I am little bit confused about this: rservice has url property which has a value of http://localhost/reportserver/ReportExecution2005.asmx, We want to execute rservice.render() but rservice is a web reference proxy created by url of http://localhost/reportserver/reportservice.asmx?wsdl, In the meanwhile Executionweb proxy doesn't have reportingservice class in it. So rservice can't be instantiated from that proxy, but when I look into web service definition, ExecutionWeb has a render method of its own. So if the reference that has been created with http://localhost/reportserver/reportservice.asmx?wsdlis named ReportingServices and if the reference that has been created with http://localhost/reportserver/ReportExecution2005.asmxhas been named as ExecutionWeb this is what Iam doing to make it work: Code Snippet using ReportingServices; using ExecutionWeb; //included ReportingServices prefix just to illustrate where it is originating from ReportingServices.ReportingService rservice = new ReportingServices.ReportingService(); //parameters, reporthpaths are set didn't go into them they are //pretty straight forward ReportingServices.DataSourceCredentials[] credentials = new ReportingServices.DataSourceCredentials[1]; credentials[0] = new CMT.ReportingServices.DataSourceCredentials(); credentials[0].UserName = "login"; credentials[0].Password = "passwd"; credentials[0].DataSourceName = "Transaction_RptDS"; string showHideToggle = null; string encoding; string mimeType; ReportingServices.Warning[] warnings = null; ReportingServices.ParameterValue[] reportHistoryParameters = null; string[] streamIDs = null; ReportingServices.SessionHeader sh = new ReportingServices.SessionHeader(); rservice.SessionHeaderValue = sh; rservice.Url = GetGlobalConfig("ReportingServiceURL"); rservice.Timeout = 180000; try { rservice.Render(ReportPath, "EXCEL", null, @"<DeviceInfo><OmitDocumentMap>False</OmitDocumentMap><OmitFormulas>False</OmitFormulas><RemoveSpace>0.125</RemoveSpace></DeviceInfo>", parameters, credentials, showHideToggle, out encoding, out mimeType, out reportHistoryParameters, out warnings, out streamIDs); sh.SessionId = rservice.SessionHeaderValue.SessionId; } catch (SoapException ex) { Console.WriteLine(ex.Detail.OuterXml); } Even though executionweb has been added as a web reference, it is not used anywhere. So should I make the render call from Execution.Render web instead of using ReportingServices.ReportingService.Render? Regards
Free Windows Admin Tool Kit Click here and download it now
August 27th, 2008 11:21pm

Try using the render method of ReportExecutionService ReportExecutionService reportExecutionService; reportExecutionService = new ReportExecutionService(); reportExecutionService.Url = "http://localhost/ReportServer/ReportExecution2005.asmx"; reportExecutionService.Credentials = System.Net.CredentialCache.DefaultCredentials; byte[] rdlDefinition; byte[] rdlDefinition; reportExecutionService.LoadReport(<ReportName>,null); rdlDefinition = reportExecutionService.Render("EXCEL",null,null,null,null,null,Warnings,null) FileStream fileStream = new FileStream(@"F:\Girija Tool\All Extra\To Deploy Reports\Files\ReportRDL.rdl",FileMode.Create); fileStream.Write(rdlDefinition, 0, rdlDefinition.Length); fileStream.Close(); MessageBox.Show("Completed The Render"); ** Here you will have to load the report before calling the render method. I have used some null, but you can set parameter accordingly in render method Hope this helps.. Regards.. Girija Shankar
August 27th, 2008 11:30pm

Thanks a lot Girija; Ijust tried what you suggested and when I specify my parameters it again throw an exception saying it can not access datasource. My datasource uses windows authentication so I thought it would be a better idea if I setcredentials with setcredentials method after reportExecutionServ.Credentials = System.Net.CredentialCache.DefaultCredentials; line. But this time when setting credentials I receive the following error, An attempt was made to set credentials for a data source 'Transaction_RptDS' associated with the report. Report data source settings are specified in a way that prevents credentials from being submitted to the report server, it looks like I might struggle with this one for a while. Erinc
Free Windows Admin Tool Kit Click here and download it now
August 28th, 2008 12:36am

Do you have proper access to Report Server. Can you check what this credentials retun. I have never faced this error. I searched and forund a link with error like you have mentioned. May want to have a look at that. http://forums.asp.net/p/1232213/2236346.aspx#2236346 You can try this code on a sample reort on your local machine also to verify wether it is a issue with code or some other settings. Hope this helps... Regards... Girija Shankar
August 28th, 2008 12:53am

Today I started working on this problem again,I guess I solved all of the problems that I've bumped into. So my post will be like a memo, if I have to go through the same process again If you see401 Unauthorized access error, first thing you have to do ischecking report security management browser, you can access it from http://<reportingserver>/reports url.Go to the second tab and click on security link.If you dont see the user in your credentials then add it by clicking new role assignment button. Set it asbrowser. If it doesn't solve your problemcheck if theusername that's being specified in the credentials added in iis_wpg group, I really don't understand why it is necessary, it solved my problem once. If you get an exception saying that one mor fields of the datasource has not been set, probabluy you configured your dataset as "prompt username and password", check if the datasource connection settings are correct. If not I really don't know what else might be wrong. Another thing to note: reportExecutionService.Credentials = System.Net.CredentialCache.DefaultCredentials; In my case It was using "NT AUTHORITY\NETWORK SERVICE" as user which might be undesired at some occasions So if you create a service account with the correct permissions you can simply use: reportExecutionService.Credentials = new System.Net.NetworkCredential(<username>,<password>,<domain>); if you add the username in security manager of reporting services as a browser(as mentioned above) And also if you need to render the report a lot, and if you have a aim to make it totally different(things like getting rid of excel formattings(because extract is pretty much same thing that you see as a report on excel cells)), use render method to get the cml back and parse it. If you have additional fields that you have to display which exist on the dataset but not on the report, best approach will be creating another report and making a seperate call to that. Hope that helps to anyone who has to fight with it, and thanks to girija. Erinc
Free Windows Admin Tool Kit Click here and download it now
October 9th, 2008 12:48am

I've done all of this and I'm getting a security error: Server Error in '/ReportPortal' Application. Security Exception Description: The application attempted to perform an operation not allowed by the security policy. To grant this application the required permission please contact your system administrator or change the application's trust level in the configuration file. Exception Details: System.Security.SecurityException: Requested registry access is not allowed. We have given access to the specific user already. But it still won't work
October 15th, 2010 1:17am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics