Sign in
Microsoft.com
 
Microsoft
 
 
 

locked
Exporting data to Excel RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    This may be more an Excel-related question but all ideas are welcomed. I'm trying to create reports by taking data from CRM and exporting it to Excel to create PivotTables. Problem is that I need data from multiple entities such as opportunities and activities and therefore I can't get them into one search. So I need to perform multiple searches and each search then creates a separate Excel-file. Problem arises when I try to collect data from different Excel-files and put into one that would work dynamically. It works  well as long as all these Exce files are open but as soon as these "sub-files" are closed the data cannot be collected. I would like to have just one dynamic Excel-file that would present data from multiple entities from CRM. Does anyone have a suggestion how a dynamic Excel-report presented above could be created?

    I thank for any advice in advance.

    Friday, August 7, 2009 7:56 AM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote
    The dynamic query sits on the worksheet so you should be able to right-click the label at the bottom of the worksheet and move the sheet to another Excel file (that's open). this will give you an Excel workbook with 2 sheets, each with their own query back to CRM. If you're not running of a hosted system you can also edit the query's SQL to bring in which ever records you want from whichever filtered views.


    Leon Tribe
    Want to hear me talk about all things CRM? Check out my blog
    http://leontribe.blogspot.com/
    or hear me tweet @leontribe
    Want to hear me talk about all things CRM? Check out my blog http://leontribe.blogspot.com/ or hear me tweet @leontribe
    Sunday, August 9, 2009 3:03 AM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote
    using advanced find or views will allow you to get realeted enity information.
    Then export that to excel
    Tiaan van Niekerk http://crmdelacreme.blogspot.com Skype:tiaan.van.niekerk1
    Friday, August 7, 2009 9:22 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    I've tried without satisfying results. Could you give more specified advice how to do that. For example if I want to list all opportunities plus all activities who has the same owner so what do I put to search criteria fields? I appreciate your help.
    Friday, August 7, 2009 11:57 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Try following steps to to get data from multiple entities (assuming requirement is to get Activities and Opportunity)

     1. Click Advanced Find
     2. Select Look For "Activities"
     3. Click on Edit Columns, new screen will open
     4. Click on Add Columns
     5. on Record Type Drop Down select Regarding (Opportunity) and it will display all the opp. columns , select the ones which are required
     6. Now apply the required filter ( make sure apply filter which select on;y required data)
     7. Click on Find and then on next screen click on Export to Excel icon
     8. Select Dynamic Pivotal table option  ( you will have one more option to select columns from related entities) and click Export...


    hope this helps !!
     

    Saturday, August 8, 2009 12:03 PM
    Answerer
  • Question
    Sign in to vote
    0
    Sign in to vote
    The dynamic query sits on the worksheet so you should be able to right-click the label at the bottom of the worksheet and move the sheet to another Excel file (that's open). this will give you an Excel workbook with 2 sheets, each with their own query back to CRM. If you're not running of a hosted system you can also edit the query's SQL to bring in which ever records you want from whichever filtered views.


    Leon Tribe
    Want to hear me talk about all things CRM? Check out my blog
    http://leontribe.blogspot.com/
    or hear me tweet @leontribe
    Want to hear me talk about all things CRM? Check out my blog http://leontribe.blogspot.com/ or hear me tweet @leontribe
    Sunday, August 9, 2009 3:03 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Thanks for your advice, it worked well, the only problem now is that the file containing the worksheets doesn't refresh the data. E.g. if I make changes in CRM they don't update to the Excel-file. They do update in the original file but not in the one where I have moved them as sheets, do you happen to have a solution for this? I appreciate your help.
    Monday, August 10, 2009 7:22 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Hi Leon

    I forgot to mention one thing, if I open up the Microsoft query in Excel and there run the query it updates the data but not if I run it in Excel.
    Monday, August 10, 2009 7:52 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    It sounds like its copied the data but not the query. If you right-click on cell A2 do you get the 'edit query' option?

    If not, you might want to look at moving it by simply highlighting all the original cells and doing a copy-paste to the new workbook.

    Leon Tribe
    Want to hear me talk about all things CRM? Check out my blog
    http://leontribe.blogspot.com/
    or hear me tweet @leontribe
    Want to hear me talk about all things CRM? Check out my blog http://leontribe.blogspot.com/ or hear me tweet @leontribe
    • Proposed as answer by Leon TribeMVP Sunday, August 16, 2009 8:11 PM
    Sunday, August 16, 2009 8:11 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Thanks, Leon!

    Austin Limousine

    Monday, July 30, 2012 9:51 PM