locked
Most Unusual Excel Problem RRS feed

  • Question

  •  

    I'm having a small problem when I export records over to excel. I'm the system admin working in CRM 4.0 on the client. I'm trying to export Opportunities over to excel. If I do an advanced find to find the ones I'm looking for (say all Opportunities that are open and over 20K) I get back my results (say 200) and everything exports to excel fine.

     

    The problem lies whenever I try and use the Estimated Close Date (field name estimatedclosedate) anywhere in the advanced find I still get my results, but both the Dynamic Pivot Table and Dyanmic Worksheet both show blank worksheets with just the headers no data. I've made sure the data connection is active and not disabled.

     

    What's weird is the static worksheet has all the data. So it appears to only happen when the records are filtered on Est. Close date for the Dynamic excel options but not the Static option while all 3 work without the Est. Close Date field filtered on.

     

     

    It's just an odd problem that is causing a headache cause there's a lot of reporting we plan to use with the Est. Close Date field. any help is appreciated.

    Tuesday, September 16, 2008 9:52 PM

Answers

  • Ok, I tested the issue and received the same result.  Here is a workaround that will give you the result you need:

     

    Open Excel

    Select Data from the top menu

    Select the Get External Data button under the Windows Icon (all the way to the left)

    Select From Other Sources

    Select From Microsoft Query

    Select your CRM SQL database server

     

    Go through the Wizard and connect to the FilteredOpportunity table.  Select the fields you want and then begin building & filtering on only the records you want to see in the Report. 

    Select Return data to Microsoft Excel and select Pivot Table as the type.

     

    You can change your query by selecting Data from the top Menu

    Select Connections

    Select Properties

    Select Definition

    Select Edit Query

     

    Using this method, you should be able to do just about anything you want.  You can teach users to update the data by selecting the Data tab and selecting the Refresh from CRM button.  You can also change the Data Connection properties to refresh the data when the file is opened.  However, it is always best to select the Refresh Data button just to ensure you have the latest and greatest information.

     

    This should get you moving in the right direction. 

     

    Best Regards,

     

     

    Thursday, September 18, 2008 7:29 PM

All replies

  • There will be a 'button' on the Excel worksheet that you have to enable when you export the records to Excel using Dynamic worksheet or Pivot Table.  You should see a Security Warning in the Excel file just under your menu ribbon that states Data connections have been disabled.  Select Options and select Enable Content.

     

    You may need to do this each time  you open the Excel file.  The Excel file will always update with the latest data.

     

    Best Regards,

    Wednesday, September 17, 2008 7:48 PM
  • Right I have enabled the data connection before and each time the data just isn't there each time. Actually, I found a new problem having to do with the search in Advanced Find. If I use any of the 'In the Next/Last 'x' criteria (ie Last 'X' months, Next 'X' days, etc) the data doesn't transfer over to Excel. But if I use any other criteria (ie On and Before) then the data exports over to excel without a problem.

     

    It seems to be that whenever I use the filter criteria of Next/Last 'X' operator none of my search results export to Excel even when the data connection is enabled. It's so weird and utterly frustrating cause that's how most of our reports are generated now.

     

    Wednesday, September 17, 2008 9:44 PM
  • That is very odd and you have Sys Admin rights I assume so it is not a rights issue?  Have you tried creating a worksheet using a data connection to the filtered views in CRM?  Can you import the data you want using that type of connection?

     

    Best Regards,

    Thursday, September 18, 2008 1:07 AM
  • I set up the data connection to the filtered Views and can see the data...except for when I use the 'Next 'x'... operator for the Est. Close Date. Literally makes no sense, it's just when I use the Next 'x' days/weeks/months/years and no other operator. If I use Last 'x' days for the Est. Close Date field the data exports just fine.

     

    What I'm trying to do is make a dynamic report in excel for a weekly meeting called Hot 60 day Opportunities. So the filter has to be set on Est. Close Date for 'X' time period (60 days, 8 weeks, 2 months, etc). I can get the data to export to a static spreadsheet, but it needs to be dynamic so the VP can just open it every week. Literally, it's like CRM is just saying 'f' you I'm not going to let you run a filter on the one thing you need.

     

    I greatly appreciate your help as I swear I'm losing my mind.

     

    Thursday, September 18, 2008 7:25 PM
  • Ok, I tested the issue and received the same result.  Here is a workaround that will give you the result you need:

     

    Open Excel

    Select Data from the top menu

    Select the Get External Data button under the Windows Icon (all the way to the left)

    Select From Other Sources

    Select From Microsoft Query

    Select your CRM SQL database server

     

    Go through the Wizard and connect to the FilteredOpportunity table.  Select the fields you want and then begin building & filtering on only the records you want to see in the Report. 

    Select Return data to Microsoft Excel and select Pivot Table as the type.

     

    You can change your query by selecting Data from the top Menu

    Select Connections

    Select Properties

    Select Definition

    Select Edit Query

     

    Using this method, you should be able to do just about anything you want.  You can teach users to update the data by selecting the Data tab and selecting the Refresh from CRM button.  You can also change the Data Connection properties to refresh the data when the file is opened.  However, it is always best to select the Refresh Data button just to ensure you have the latest and greatest information.

     

    This should get you moving in the right direction. 

     

    Best Regards,

     

     

    Thursday, September 18, 2008 7:29 PM
  • Another option is too use the view Open Opportunities, export all to Excel using the Dynamic Pivot Table option, then create your pivot table using the Grouping option or applying the filters based on the criteria you want to see.

     

    Best Regards,

     

    Friday, September 19, 2008 1:35 PM
  • Thanks for the work around it worked like a charm...headache averted. Thanks again.

     

    Friday, September 19, 2008 11:51 PM
  •  

    We are having the same issue.  Is this a Microsoft problem and will they be correcting it so the 'work around' wont be necessary forever?
    Wednesday, October 15, 2008 7:02 PM
  • Is this a problem that Microsoft can fix or will the 'work around' always be necessary?

    Wednesday, October 15, 2008 7:04 PM