Freigeben über

Refreshing EWA Automatically when Sharepoint Filters are Changed

  • Artikel

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.

Comments

  • Anonymous
    November 09, 2006
    The comment has been removed

  • Anonymous
    November 09, 2006
    This is most likely an issue with your authentication settings for the PivotTable connection. You need to set up SSO on the connection in order to properly authenticate with the AS server. Check out this article, focusing on setting up SSO sections: http://technet2.microsoft.com/Office/en-us/library/a49883a7-de84-4a66-8fa0-7c7d125f237b1033.mspx?mfr=true If you're not the server admin you'll need to have the admin set this up for you, and let you know which SSO ID to use. Let me know if this doesn't do it.

  • Anonymous
    November 10, 2006
    The comment has been removed

  • Anonymous
    November 10, 2006
    Part of setting up SSO includes choosing the SSO ID and accounts to use with it.  This ID needs to be used in the Connection for the PivotTable under Data -> Connections -> Properties -> Definition -> Authentication Settings... Why don't you shoot me an e-mail and we can work this out offline.  If you're not internal at MS you can use the Email link under the title for this blog.

  • Anonymous
    January 22, 2007
    SSO solution that worked for me with same issue: http://msmvps.com/blogs/obts/archive/2007/01/05/471495.aspx "you must make sure the account used to run the SSP (SharedServices running Excel Services) is either the exact same account as the SSO admin account (defined in "manage server settings for SSO" page) or, if both SSP and SSO admin accounts are different, these must be members of the same group (eg MySSOAdmins). '

  • Anonymous
    January 22, 2007
    Thanks for the link tronn! I've realized recently that the "email me and we'll work it out offline" method isn't very useful to the community as a whole because the resolution and investigation of the problem aren't public & searchable. I'll try and work through issues online and make sure the resolution is posted as well, that way if anyone runs into the same issue and searches for it they'll get the answer as well. I'm planning on a whole post with the sole purpose of making common issues/resolutions from our internal alias public and searchable.  There's quite a few threads out there but as soon as I can parse through them and publish them I will.

  • Anonymous
    July 01, 2007
    I'm getting error: The data source may be unreachable, may not be responding, or has denied access when I'm trying to use a sharepoint list as a datasource. baffled by that I'm afraid. in excel it's fine, in EWA not. EWA renders the sheet, but if I refresh it generates that error.

  • Anonymous
    July 02, 2007
    SharePoint Lists are not a supported data source this release.  I'm assuming you're using a PivotTable though as you would have gotten a different error with QueryTables? If it was another data source as opposed to a SharePoint list your problem would probably be the authentication method expiring over the hop to the server.  You can read more about that here: http://technet2.microsoft.com/Office/en-us/library/7e6ce086-57b6-4ef2-8117-e725de18f2401033.mspx?mfr=true