Skip to Content
author's profile photo Former Member
Former Member

Connecting to Excel file posted on CR.COM

Post Author: Summer Frend

CA Forum: crystalreports.com

Hello,

Just wondering if there is a way to create a Crystal Report off an Excel document that is posted on CR.COM?

Both Report and Excel file will be uploaded to CR.COM and we would like to create the report off the uploaded Excel file for security reasons.

Regards,

Summer Frend

Add a comment
10|10000 characters needed characters exceeded

Related questions

10 Answers

  • author's profile photo Former Member
    Former Member
    Posted on Sep 07, 2007 at 06:02 PM

    Post Author: norton

    CA Forum: crystalreports.com

    Hi Summer,

    Yes, support for reporting off an excel file in cr.com was added last night.

    Just follow these steps:

    Create the excel file

    Create the report that uses the excel file on your local computer.

    Upload the excel file to cr.com.

    Upload the report to cr.com

    On the upload page there is a box labeled "Establish Data Connection" - check this

    Select the "Excel Spreadsheet" radio button

    Select the appropriate excel file from the drop down list

    You can leave the user name / password fields empty

    If your report has saved data, you will see that when you view it, but you will also have the option to refresh the report against your excel file.

    If your report has no saved data then it will automatically refresh itself against your excel file when you view it.

    Once you have this set up, you can upload new excel files periodically (to replace the existing one) using the desktop publisher. Any scheduled instances or viewer refreshes will then pick up the new data.

    Please let us know if you have any problems.

    Steve

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Sep 08, 2007 at 01:38 AM

    Post Author: Summer Frend

    CA Forum: crystalreports.com

    Hi Steve,

    Thanks so much. This works great if you have a report that is only created off the Excel spreadsheet. However, my report has a main report off the Excel Spreadsheet, then a subreport off Salesforce data. When I upload it to CR.COM it says: Only OLE DB (ADO) db connections are supported for use with file data sources.

    Will this only work if you have 1 datasource in your report? Is there a way to do this with my scenario?

    Thanks,

    Summer Frend

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Sep 10, 2007 at 05:45 AM

    Post Author: norton

    CA Forum: crystalreports.com

    Hi Summer,

    Yes, I am afraid that right now that we can only support reports with a single data connection.

    I will pass this along to our product manager for consideration in a future release.

    Steve

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Dec 20, 2007 at 02:48 PM

    Post Author: Dixie Folzenlogen

    CA Forum: crystalreports.com

    Hi Steve,

    I tried to follow the steps you gave Summer in your 09-07-2007 post. I have only 1 data connection (to an Excel spreadsheet) but I cannot make this work. I get the following error message:

    Only OLE DB (ADO) db connections are supported for use with file data sources.

    What am I doing incorrectly?

    Dixie

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Dec 20, 2007 at 03:38 PM

    Post Author: tymiller

    CA Forum: crystalreports.com

    Hi Dixie, Can you confirm that you are making a connection to your Excel file using OLE DB and not ODBC?

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Dec 23, 2007 at 10:59 AM

    Post Author: Dixie Folzenlogen

    CA Forum: crystalreports.com

    Hi Steve,

    Problem solved. I was connecting to my Excel spreadsheet using the wrong OLE DB driver. Pulled the Crystal Reports documentation online and found I have to connect using Microsoft OLE DB Driver for Jet 4.0. Works fine now.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Dec 23, 2007 at 05:29 PM

    Post Author: Dixie Folzenlogen

    CA Forum: crystalreports.com

    Hi Steve,

    A note for others who might encounter same issue in the future:

    Had a client who wanted to "hide" the Excel spreadsheet from users. I placed in a folder with No Access. However, reports would not update. Once folder permission for folder containing Excel spreadsheet was changed back to Viewer access -- reports worked fine. Excel report is "viewable" however.

    Maybe future software updates with allow for Administratively "hidden" folders but with Read access for the viewers or something along that line. . . .

    Perhaps I overlooked some other solution. If so -- please let me know.

    Dixie Folzenlogen

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jan 02, 2008 at 04:59 PM

    Post Author: norton

    CA Forum: crystalreports.com

    Hi Dixie,

    Yes, the behaviour you are seeing is a side effect of how the object rights work. You have not overlooked anything.

    I will forward this request on to our product manager. Keep the suggestions coming!

    Thanks

    Steve

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jan 03, 2008 at 07:59 PM

    Post Author: smckay

    CA Forum: crystalreports.com

    In the meantime, you can accomplish this by having a subfolder with view rights in a folder set to 'no access', i.e.: 1. Create a new folder, called 'hidden'2. Create a subfolder in 'hidden', called 'excel'3. Set the permissions to 'hidden/excel' to 'View'4. Set the permissions to 'hidden' to 'No Acess' Now the guest user will have access to the files in 'hidden/excel', but will not be able to see the 'hidden' folder or any of its subfolders in his folder tree.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jan 04, 2008 at 12:11 PM

    Post Author: Dixie Folzenlogen

    CA Forum: crystalreports.com

    Works like a charm. Thanks for your help!!

    Dixie Folzenlogen

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.