Results 1 to 13 of 13
  1. #1
    Join Date
    Aug 2010
    Posts
    26

    Unanswered: Reporting Services Excel Unreadable

    Hi Everyone,

    Using 2008 R2.

    I have an interesting problem with excel rendering (rendering may not be the proper term here, but I dont know what else to call it)

    when exporting to excel, I get junk. Pages upon pages of:
    ...
    ࡱ>   
    ...

    In the midst of the junk I can see elements of the report, so I know it is reading it properly - just not 'rendering' it properly.

    This is my URL:
    http://SERVER/DBNAME/SQLReport.aspx?ReportName=Payment Verification Report&ReportPath=/ODVS Reports/Reports/Reports/PVR&ParamtartapprovedDate=11/1/2010&Param:EndApprovedDate=1/5/2011&Format=EXCEL

    If I change "&Format=EXCEL" to "&Format=PDF" then it works perfectly.

    Anyone else having this issue, or have any insight as to why this is happening?

  2. #2
    Join Date
    Jun 2005
    Posts
    319
    please PM blindman, he's an expert

  3. #3
    Join Date
    Aug 2010
    Posts
    26
    Quote Originally Posted by Gagnon View Post
    please PM blindman, he's an expert
    Thanks for the tip. I'll post again if it is resolved off-post.

    In the meantime, anyone else is welcome and encouraged to still reply! *hint hint*

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I haven't used Reporting Services much, but what version of Excel are you using to open the file?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Aug 2010
    Posts
    26
    Quote Originally Posted by blindman View Post
    I haven't used Reporting Services much, but what version of Excel are you using to open the file?
    well thats the thing - and I should have clarified this more in the OP - it opens up in an internet explorer window, and displays the data in byte form... it never prompts to open up in Excel. I have a hunch that if it did present the prompt (the open or save dialog box), then I would be able to do this just fine.

    though FYI, I have excel 2007 installed on the machine in question.

    EDIT:

    Here is a screenshot of what I'm talking about:
    http://www.flickr.com/photos/dustinhouck/5347010468/
    Last edited by dah97765; 01-11-11 at 15:02. Reason: add link

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Well, you are sending it to the browser, not to Excel.
    So, a PDF file looks fine, because your browser has an Adobe plugin. But the browser chokes on the Excel format.

    Are you trying to output to an Excel file, or are you trying to output to a browser-based report?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    I can replicate the issue by exporting an SSRS report to excel and then try to open it without using excel. I use notepad and get the same junk. I don't have excel on the server where my reporting services are located. When I use an URL to the reporting server then I get prompted for which presentation object to use. I select excel and the extract is opened in excel without any problems. As blindman said, the excel plugin should be on the server where the xls is located - if opening the file local. I don't have MS Office suite installed on the reporting server.

    P.S. If I keep up this pace of reply, I'll hit 100 by 2013.
    Last edited by corncrowe; 01-12-11 at 07:26.

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    It sounds like Reporting Services is correctly exporting the data in Excel format. So the problem lies with whatever application you are using to view the Excel file. If it is not Excel, or does not have a plug-in to read Excel files, then you are going to get the gobbledy-gook.

    Can't really help you more with this. Sorry.

    blindman
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    I've had something similar, but ironically with exporting to PDF. Now I am not saying that the process is the same, because we all know exporting to PDF requires implicit or explicit converter. It also may set up a file-based printer driver, and actually "print" to the file in PDF format. I can't fully speak for Excel on your box, but maybe there is an installation issue with your Office?
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  10. #10
    Join Date
    Aug 2010
    Posts
    26
    Quote Originally Posted by rdjabarov View Post
    I've had something similar, but ironically with exporting to PDF. Now I am not saying that the process is the same, because we all know exporting to PDF requires implicit or explicit converter. It also may set up a file-based printer driver, and actually "print" to the file in PDF format. I can't fully speak for Excel on your box, but maybe there is an installation issue with your Office?
    Its a good thought - a coworker suggested something similar, but it is a universal issue. It happens the same on every machine that I have attempted so far.

    I also installed office on the reporting server, just in case the excel generation was dependent on some included files in office. Not surprisingly, it made no difference.

    I am in agreement that it is an issue on the application level, but I am not sure how to resolve that yet.

  11. #11
    Join Date
    Jun 2005
    Posts
    319
    I would think you need Excel installed on the server (possibly).

    Failing that, you probably need the HTML to explicitly state the content type, I vaguely remember doing this about 10 years ago using code similar to this in ASP:

    Code:
    <% The main feature of this technique is that %>
    <% you have to change Content type to ms-excel.%>
    
    Response.ContentType = "application/vnd.ms-excel"
    <TABLE>
    <TR><TD>2</TD></TR>
    <TR><TD>3</TD></TR>
    <TR><TD>=SUM(A1:A2)</TD></TR>
    </TABLE>

  12. #12
    Join Date
    Aug 2010
    Posts
    26
    Quote Originally Posted by Gagnon View Post
    I would think you need Excel installed on the server (possibly).

    Failing that, you probably need the HTML to explicitly state the content type, I vaguely remember doing this about 10 years ago using code similar to this in ASP:

    Code:
    <% The main feature of this technique is that %>
    <% you have to change Content type to ms-excel.%>
    
    Response.ContentType = "application/vnd.ms-excel"
    <TABLE>
    <TR><TD>2</TD></TR>
    <TR><TD>3</TD></TR>
    <TR><TD>=SUM(A1:A2)</TD></TR>
    </TABLE>

    I installed Excel on the Server (see last post) but to no avail. I could be wrong, but I think that SSRS has .xls generation built in, so there's no need to have office installed on the server.

    I'll give the html a shot and see how it goes.

  13. #13
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Quote Originally Posted by dah97765 View Post
    I installed Excel on the Server (see last post) but to no avail. I could be wrong, but I think that SSRS has .xls generation built in, so there's no need to have office installed on the server.

    I'll give the html a shot and see how it goes.
    I mentioned earlier that this issue was easily replicated. Reason is that I used a different api (notepad) to open the xls file since Excel wasn't installed on server. There is no api on the server to handle this type of file. I don't want excel on my server.

    I use SSRS to create reports and export them to a separate folder on the reporting server where they can be opened from my desktop or using an api (owc11) in a bespoken application.

    I have MS Office 2003 installed on my desktop and when I go to the remote serve (file:///Q:/CallDetails.xls) using IE 6.0 it is easily enough to open my exported SSRS report. The report is opened in a new excel window.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •