Looking for Excel Macro code to auto refresh JRS Live Data data
How would i get the JRS Excel Live Data Update Refresh to execute and auto refresh the JRS-Exported data on a periodic basis?
|
Accepted answer
If you open the Excel View tab and click the macros button, you can examine and edit the JRS macros. Select the RunQuery macro and click Edit. The macro editor should display. In the left panel you should see Modules and Class Modules. Expand Class Modules and double-click on QTEvents. This corresponds to 2 event handler for the QueryTable refresh events. (The JRS query is inserted into Excel as a QueryTable object.)
The raw results that comes back from the JRS query have extra rows and columns and the event handler qt_AfterRefresh() shifts the results to remove those columns/rows. You could insert the REST API calls at the end of this macro. Robert Myers selected this answer as the correct answer
|
3 other answers
|
When you export a "live" Excel spreadsheet from JRS you must first click Enable Editing (button near top of Excel editor) and then click Enable Content (which enables the macros). After that, on the RunQuery tab click the button Make the results dynamic. This creates an external data source in the Excel workbook that will run the query.
To refresh the results in the QueryResults tab, either click Refresh All in the Data command section or click the button Refresh the query results in the RunQuery tab. If you want to automatically update the results periodically (after you have clicked Make the results dynamic), go to the Data section and click on Connections. By default there should only be a single connection for the JRS query. Select the connection and click Properties. You should be able to schedule a refresh at regular intervals. Comments @Kevin Thank you for this information, it is part of what I need. But beyond that, I am looking for a way to automatically click the Refresh button. We have customers who want to periodically on a regular basis, refresh te data and send it to their data warehouse.
Asked Anagha (anaghagala@in.ibm.com) and Shane (shane.hoey@ie.ibm.com) for their testing of your answer regarding automatic, periodic refresh of the JRS Exported data and I await their response. I will ask the question in the comment above as a separate question.
Kevin: I tested this (setting up a schedule in the connections property)....I have Office 2003, and cannot find this option. Which version have you ? The other functionality is fine...(Make results dynamic, Refresh query results)
|
|
The Excel workbooks exported by JRS have macros that require Microsoft Office 2010. After you "Enable Editing" and "Enable Content" in a live workbook exported from the Jazz Reporting Service, click the button to Make the results dynamic.
The in the Data tab, click Connections, select the "Connection" entry and click Properties. In the connection properties, set the refresh interval as you desire. I set the interval for 1 minute and every minute a request was sent to JRS to refresh the query results. See the attached screen shot of the Excel dialogs. 
|
|
@Robert As for your question about using REST API to send the data to their data warehouse, are you referring to the data warehouse populated by the IBM Rational CLM applications, or a user's custom data warehouse.
For a user's custom data warehouse, I am not an expert in Excel so I do not know how you would extract the data from the updated Query Results tab and make a REST call to their warehouse. Comments @Kevin I am asking where in the JRS Excel macros, should our customer insert her Restful API calls. I agree that their DWH is custom and not something you should know about, but where in the JRS macro code should we insert the RESTful API calls to send the data to their warehouse? When that data is periodically refreshed by setting the parameters in Excel, we want the data which comes refreshed from RRDI through JRS to be sent right up to the customer's data warehouse. So we want that to be part of the refresh loop too.
|
Your answer
Dashboards and work items are no longer publicly available, so some links may be invalid. We now provide similar information through other means. Learn more here.