of the
If you are facing the login window appearing in the Excel sheet after creating a dynamic Excel export from an IFD-enabled CRM organization, then there’s still a way to get the data into the Excel sheet:
- Export the Dynamic Worksheets (or Pivot Tables) from Microsoft Dynamics CRM.
- Open the Dynamic Worksheets with Excel.
- Excel will display the ADFS sign in page or a Script is disabled error in the worksheet.
- Click on Data tab.
- Click on Connections.
- Within Workbook Connections click on Properties.
- Within Connection Properties click on tab Definition.
- Click on button Edit Query.
- This will redirect to ADFS login, enter in the user’s credentials and click on Sign In.
- YOU WILL GET AN ERROR, THIS IS OK! So, click on OK.
- Within Edit Web Query, click on Cancel.
- Within Connection Properties, click on Cancel.
- Within Workbook Connections, click on Close.
- Within Excel go to the Data tab
- Click on Refresh All.
Cheers!
This tip is brought to you by Ronald “I’ll be back” Lemmen.Tipp Jarr’s Double Dipp
To my astonishment, these instructions do work! The painful part is the need to repeat the process every time worksheet is opened but this is infinitely better than no exported data at all!
Hey,
when you have CRM for Outlook installed, you can use “Data > CRM > Refresh from CRM” in CRM.
Works fine in CRM 2013 and Office 2013.
Regards
Has anyone found a way to modify the number of records returned within the workbook.
Prior to the IFD implementation I could go to the connection properties, select the definition tab and change the number of records from top 10000 to a larger quantity. With the IFD implementation I am unable to make this modification now.
Heather,
this is now an organization setting. If you download the Orgdb settings tool, you can change the number of rows for excel. http://orgdborgsettings.codeplex.com/
Thank you – do we have to take these steps each time we re-open the workbook? Or has anyone found a way around this?
Also, We are unable to export connection files which is critical to the reports we need to maintain and create using dynamic crm data.
Hi Vicki,
There is no way around this currently using the out-of-the-box dynamic worksheet. You can’t export the connection, The connection is in the Outlook client. Keep in mind that this is not your only option for dynamic Excel spreadsheet. I can embed a SQL query in a spreadsheet as long as somebody has access to the SQL database, it will load the data. Power bi is another option for that. Also, this gets a major overhaul in the spring update
Hi Joel – any new ideas for 2016 On prem for exporting the connection or being able to edit the connection file? From my understanding, the Excel template only uses one view. We are pulling data today from many entities into reports into Excel and when we upgrade to 2016 soon we think this will no longer work. We don’t have access to Power BI. Our developers can get to the SQL, but our CRM Support team doesn’t know SQL, and we don’t want to give this to a developer every time we need to change something. Am I just hosed?
Thanks!
Vicki
Hi Vicki,
Save one of your current dynamic spreadsheets and modify the connection string to point to the 2016 server. Your spreadsheet will still work. Users in 2016 still have the ability to query the sql views
The excel export just doesn’t use some as the data connection type anymore. But you can still build spreadsheets based on CRM awl queries
Thank you for your response, Joel. I apologize but I don’t understand why a feature in MS Excel would be a connection for Outlook?
When you go to the dynamic spreadsheet in Excel (after the steps taken above) and go to Connections – Properties – Definition and then Export Connection File – is this the same thing you are discussing? This is what we are using today in 2011 to maintain the dynamic data into a different spreadsheet in Excel (using the connection file in a table in a separate workbook)
We don’t have access to the SQL db directly and don’t have Power BI so we are hoping to make this work somehow.
Thanks for taking the time to respond – it is greatly appreciated!
Vicki, I’m not sure. This approach can work, but it is not documented. The outlook client adds things like the CRM refresh button. I know exporting the connection can work, but it is not documented (and slightly unsupported). Not saying that I wouldn’t do it.
I tried following your instructions above because I keep getting the following error when trying to set up the OData connection:
DataFormat.Error: OData: The given URL neither points to an OData service or a feed: ‘https://swank.crm.dynamics.com/XRMServices/2011/OrganizationData.svc’.
When I tried your suggestion I got to your step:
“Click on button Edit Query.
This will redirect to ADFS login, enter in the user’s credentials and click on Sign In.”,
I got the following error message:
An error has occurred in the script on this page.
Line 7
Char 233538
error Unspecified error
code: 0
Any suggestions? I have uninstalled power query and reinstalled it but keep getting the same error message.
Hi Ellen,
the instructions have nothing to do with Power Query, I’m not sure you’re trying to edit in the right place.
Cheers
George
Hi
we some issue with pivot table,i.e. when we export an data from me crm 4.0 to pivot table it shows an error as “To view and refresh dynamic data, Microsoft Dynamics CRM for Outlook must be installed.” even the out look is installed , if i click on refesh all button i will get an data but not in a pivot table format , the data will come in the excel format , u people have any idea about this
dynamic pivot table export from mscrm. following the above steps it’s working fine but every time user close and open excel sheet it asking adfs login for that user following again all the steps. but user not interest doing all this everytime. so what we need to solve the everytime not follow those steps and adfs login.
Note: not yet we are not supported mscrm outlook client. without this how can solve.