title: Moving "Dynamic Worksheets" between CRM Online environments
body: <p>My understanding of how this works and the reality appear to be different.</p>
  
  <h2>Goal</h2>
  
  <p>Export a Dynamic Worksheet from one CRM Online tenant. Upload it as an "Existing Report" in a solution. Export the <em>solution</em> to another tenant. Similar to SSRS reports, I expect the datasource to be updated to the new tenant. I then want to open the Dynamic Worksheet via Reports in the new tenant and see the data in Excel coming from that second tenant.</p>
  
  <h2>Environment</h2>
  
  <p>I am using CRM Online (now with UR12) and Excel 2013. I am able to replicate this on another machine with Excel 2007.</p>
  
  <h2>Walk-through</h2>
  
  <p>I have a Dynamic Worksheet that I have exported from a CRM Online tenant ("T1"). I did this by creating an Advanced Find, viewing the results then clicking Export to Excel. I then chose the "Dynamic Worksheet" option and saved the resulting <code>.xml</code> file. If I open the file locally (and accept all the usual security warnings), I see data from T1 in the worksheet.</p>
  
  <p>I then created a new solution in T1, called "My Report". From the solution, I went to Reports, Add New and added an "Existing Report" by browsing to my newly saved <code>.xml</code> file.</p>
  
  <p>I then exported my solution as unmanaged (not that it matters).</p>
  
  <p>Next I signed out of T1 and signed into to another CRM Online tenant ("T2") and imported the solution. I can then see my new report under Reports in T2. If I click the report, I am prompted to download the spreadsheet. If I open the downloaded spreadsheet (and agree to the security warnings etc as before) I do not see any data. If I click <code>Refresh from CRM</code> then nothing appears to happen. If I click on <code>Data &gt; Connections &gt; Refresh All</code> then the content in my spreadsheet changes to:</p>
  
  <blockquote>
    <p>Microsoft Online Services requires JavaScript to sign in. This web
    browser either does not support JavaScript, or scripts are being
    blocked.</p>
    
    <p>To find out whether your browser supports JavaScript, or to allow
    scripts, see the browser's online help.</p>
  </blockquote>
  
  <h2>My Theory</h2>
  
  <p>It seems like, not unreasonably, the target datasource is being stored in the worksheet (I can see this by viewing the XML and I see the path of my "T1" tenant). I expected that this would be changed during the solution import phase but it isn't. If I change the XML manually, it will look at my T2 environment and work as expected.</p>
  
  <p>I had hoped that one of the following options would cause Excel to look to the right place:</p>
  
  <ul>
  <li>I have the Outlook client installed and configured. T1 and T2 are both configured as organisations that I can connect to. I hoped this might cause Excel to prompt to connect to one of the two environments.</li>
  <li>I reconfigured Outlook client to only connect to T2 (deleted T1 from config) but seemingly had impact on where Excel connects to</li>
  <li>I signed into T2 via my default browser (IE9) and opened the worksheet but again this did not effect a change. </li>
  </ul>
  
  <p>It will be no surprise that this example simplifies a more complex case (a workbook with many linked worksheets such as the one above). The workbook is part of a solution which is being deployed between DEV and UAT environments. Manually frigging the XML each time I export the solution is not a scalable option.</p>
  
  <h2>Question(s)</h2>
  
  <ul>
  <li>Is this behaviour expected or yet another post-UR12 / post-O365 bug?</li>
  <li>What haven't I tried that might allow this to "just work"?</li>
  </ul>