Refreshing EWA Automatically when Sharepoint Filters are Changed

  • Article

If you played around with the post on using UDFs to write to SQL databases, you probably ended up creating a PivotTable connected to the SQL database the UDFs were writing to in order to see the new data showing up on the database.

 As you would have noticed if you took that route it took two steps to get it to work:

  1.  Send parameters to EWA, possibly through Sharepoint Filters
  2. Refresh the PivotTable once those parameters have been stored in SQL by the UDF

The point of this post is to get rid of step 2 above.  What we want to happen is have a user choose an option in a Sharepoint Filter, send that option to a parameter in EWA, and automatically refresh the PivotTable once the parameter is stored in SQL.

Unfortunately it is not possible to programmatically refresh a pivot this release but as you learned from my post on reloading EWA workbooks programmatically you can cause the entire workbook to reload.

 Couple the above functionality with the PivotTable option of "Refresh on Open" and you have accomplished your scenario!

 The one missing link is how to have the Javascript code to reload the workbook, detect when Sharepoint filters have been updated.  Here's the code I came up with (read below for info on how to use it):

CODE:

 <script type='text/javascript' language='javascript'>

 

function checkAndReload()

{ 

   <!-- Get real text filter ID -->

   var fullHiddenID = 'WebPartManager_g_' + 

              document.getElementById(hiddenID).WebPartID.replace(/-/g, "_")

              + '_SPTextSlicerValueTextControl';

 

   <!-- Check if a filter has been changed -->

   if (document.getElementById(fullHiddenID).value != '')

   {

      <!-- Reload workbook -->

      EwaReloadWorkbook(ewrID1);

 

      <!-- Reset text filter value -->

      document.getElementById(fullHiddenID).value = '';

   }

   else

   {

       <!-- No changes, try again in 1 second -->

       setTimeout('checkAndReload()', 1000);

   }

}

 

<!-- DEFINE WEB PART NAMES -->

var ewrID1 = 'WebPartWPQ5';

var hiddenID = 'WebPartWPQ10';

 

<!-- In 1 second, try to reload -->

setTimeout('checkAndReload()', 1000);

 

</script>

 USAGE:

To use the code above you need a web part page with at least four web parts:

  1. Excel Web Access with a workbook that has the following features: A parameter feeding into the SQL UDF from my previous post that will cause the parameter's value to be written to a SQL database, and a PivotTable connected to that same Database, with Refresh on Open checked in it's connection properties.
  2. Any Sharepoint Filter (Choice Filter for example)
  3. A Text Filter
  4. A Content Editor Web Part (This one should be below all other web parts, as it will contain Javascript that will reference the parts created above)

 Connect the Sharepoint Filter (Choice Filter) to send it's value to the EWA web part.  This will populate the parameter in the Excel workbook that is calling the SQL UDF with that value.

Connect the Text Filter web part to Get Default Value From the Sharepoint Filter.  This will cause the Text Filter to be updated whenever the Sharepoint Filter is changed.  Our Javascript will check the value of this filter to determine if it is time to reload EWA.

Once you publish this page you will need to get the following information from the page’s source (right click in IE and View Source).

1. EWA Web Part ID – Search for “ECSFrame” in the source and get the corresponding “WebPartWPQ#”.

2. Text Filter Web Part ID – Same as above, but search for “TextSlicer”.

 

Now that you have the values for the above ID’s you can create a text file which will contain your Javascript.  Here’s what I have (replace the IDs under “DEFINE WEB PART NAMES” with the values you got from above):

Now save this text file to a Sharepoint document library and remember the path to it (e.g. https://MYSERVER/Documents/Scripts.txt).  On your web part or dashboard page modify the Content Editor web part such that it’s Content Link points to your script file above.  That script will now run every time the page is loaded.

 

DONE!

If you did everything correctly, and I didn't forget any steps :o), you now have a web part page that will reload the EWR part whenever a user changes the values from the Sharepoint Filter, thus causing the PivotTable to Refresh On Open and get the data the the Sharepoint Filter just saved to SQL.

 There's a few levels of indirection here so don't be surprised if this is tough to follow, just try it step by step and you'll get there in no time.  The script can also be changed to reload multiple web part, or check multiple Sharepoint Filter values, etc... once you have this up it should be easy to modify/tweak it for your scenario.  I also usually hide my Text Filter once it's ready, that way users don't even know it's there.

 

Thanks: to Jenefer who's Real Word Solution called for this trick to be suggested by Tyson.