none
Flag or indicator that all queries (cubeformulas) have completed updating? RRS feed

  • Question

  • Hi all-

    I've got a workbook with a large number of cubeformulas that operate in the background based on user selections. I just ran into a situation where a user changed some selections, but then saved/closed the workbook before the queries had completed, so the dashboard metrics that pull from those cells were not updated and the data was not correct.

    I tried inserting a flag at the end of my macro (the one that triggers the cubeformulas to update), but the macro completes before the queries, so I end up with a race condition and any flags I set in my code (just based on the code itself being complete) are not a good indicator of whether the actual data refresh is complete.

    I would like to put some type of visual display on the dashboard that alerts the user to the update status- "updating" and "complete" or something like that, so if anyone opens the workbook and the updating message is there, they know they need to refresh the dashboard because the data shown isn't valid.

    I am using VBA for this project. Excel 2010, pulling data from AS cubes via cubeformulas. Any and all help would be appreciated!

    Can anyone tell me if there is a way to check the status of the queries?



    Thursday, August 28, 2014 4:24 PM

Answers

  • Hi Keith Ruck,

    >>So, as soon as A1 is changed, it triggers a cascade of cubevalue query updates that continue well after the macro has finished.<<

    Would you mind share some code snippet that how you update the cubevalue query? As far as I know, we can check the calculation state of the application via Application.CalculationState.

    Here is an example, Microsoft Excel checks to see if any calculations are being performed. If no calculations are being performed, a message  displays the calculation state as "Done". Otherwise, a message displays the calculation state as "Not Done".

    Sub StillCalculating() 
     
     If Application.CalculationState = xlDone Then 
     MsgBox "Done" 
     Else 
     MsgBox "Not Done" 
     End If 
     
    End Sub
    

    Hope it is helpful.

    Best regards

    Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, September 1, 2014 8:12 AM
    Moderator

All replies

  • Hi Keith Ruck,

    >>I tried inserting a flag at the end of my macro (the one that triggers the cubeformulas to update), but the macro completes before the queries<<

    Since you metioned the macro completes before the queries, did you use muiple-thread devloping? As far as I know, multithreading developing using VBA is a little hard. Can you share the code how you query the data?

    If you are  devloping with multithreading, I suggest that you wirte a loop to check the statues. And to let the other process to run, you we can use DoEvents function in the loop.

    Hope it is helpful.

    Best regards

    Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Friday, August 29, 2014 2:57 AM
    Moderator
  • Multi-threading is above my pay grade :)

    This is a workbook where the user makes a data validation (list) selection in cell A1, corresponding with a month. That cell is used as a reference in a bunch of cubevalue formulas on multiple pages of the report. So, as soon as A1 is changed, it triggers a cascade of cubevalue query updates that continue well after the macro has finished.

    What I'm hoping for is some application-level flag that will tell me if there are any active/pending queries, which I would check during workbook_close, and alert the user (or even cancel the close) until the cubevalue queries had completed.

    Thanks!

    Friday, August 29, 2014 4:40 PM
  • Hi Keith Ruck,

    >>So, as soon as A1 is changed, it triggers a cascade of cubevalue query updates that continue well after the macro has finished.<<

    Would you mind share some code snippet that how you update the cubevalue query? As far as I know, we can check the calculation state of the application via Application.CalculationState.

    Here is an example, Microsoft Excel checks to see if any calculations are being performed. If no calculations are being performed, a message  displays the calculation state as "Done". Otherwise, a message displays the calculation state as "Not Done".

    Sub StillCalculating() 
     
     If Application.CalculationState = xlDone Then 
     MsgBox "Done" 
     Else 
     MsgBox "Not Done" 
     End If 
     
    End Sub
    

    Hope it is helpful.

    Best regards

    Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, September 1, 2014 8:12 AM
    Moderator
  • Voted Fei's answer as helpful, MS seems to have changed the forum code and I'm not able to mark the thread as answered. I'm also getting weird artifacts where the user "picture" (symbols) change size dramatically (1 cm2 right-justified <->  6cm2 left justified) as I scroll up and down threads, reformatting the layout as it happens and making the forum very difficult to read and use. Left panel layout is also messed up.

    Moderators, please mark Fei's response as the correct answer, and please go 'frown sternly' at whoever is changing your site code. I'm using IE9, which admittedly is older, but I'm in a restricted IT environment so upgrading isn't an option for me <sigh>

    Tuesday, September 16, 2014 4:11 PM
  • Hi Keith Ruck,

    >>MS seems to have changed the forum code and I'm not able to mark the thread as answered.<<

    We can only mark the thread which type is a question. If it is a discussion, there is no answers.

    >> I'm also getting weird artifacts where the user "picture" (symbols) change size dramatically (1 cm2 right-justified <->  6cm2 left justified) as I scroll up and down threads, reformatting the layout as it happens and making the forum very difficult to read and use. Left panel layout is also messed up.<<

    Thanks for your feedback about the forum. If you have any suggestion or feedback, you can reopen a new thread in forum below:
    Suggestions and Feedback for the Forums

    Best regards

    Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, September 17, 2014 3:10 AM
    Moderator