20 Ton Squirrel wrote: ↑Tue Apr 12, 2022 7:39 pm
I'm curious if there is something like:
Code: Select all
Reporting.QuickReports.Get(qrIDNumber).Refresh
There is, but it's not well documented in the PAX API documentation. The below code will refresh whichever current report the cell "B2" finds itself in. You could of course use named ranges, ActiveCell, or specify other ranges
Code: Select all
Reporting.GetCurrentReport(Range("B2")).Refresh
20 Ton Squirrel wrote: ↑Tue Apr 12, 2022 7:39 pm
The worksheet I'm trying to update doesn't have any report objects on it. It just has TM1ELLIST. There's no specific report to refresh, it just needs to update the results of that function for that particular range. The steps in the code are like this:
- Update the MDX
- Turn off calculation and screen updating
- Clear out range where values existed before
- Throw down TM1ELLIST at top of range with updated MDX
- Calculate that worksheet ONLY, also includes calls to Cognos API for Reporting.RefreshSheet
- Translate results of TM1ELLIST to values
- Turn on calc/screen updating
If I don't include the API call for RefreshSheet, the TM1ELLIST just returns "RECALC_0_0" which is a placeholder the function uses while the API is fetching data.
Addressing your last point first, the reason you're seeing "Recalc..." when your macro runs without the RefreshSheet is because I think the worksheets().recalculate vba is not the same as hitting F9 on your keyboard. When you F9 or shift-F9, the PAfE add-in is automatically refreshing the TM1 function TM1Ellist at the same time. When you try to do a simple recalc in VBA, the PAfE add-in automatic piggyback on the recalc is blocked due to the execution of the VBA which is why you need the RefreshSheet. I'm not an excel calculation order expert or have any knowledge of how the PAfE works under the hood, so careful quoting my interpretation.
I don't fully grasp your intended use of the array returned by TM1ELLIST or if/how it relates to your quick reports on another sheet, but in looking over your list, what you've laid out should work. I set up a sample file that had 3 quick reports on 1 worksheet "Sheet2". This worksheet also has a dropdown list with Actual and Budget as available options. I then have a formula reference the selection to create an MDX statement that will be used in a TM1ELLIST formula. On another sheet "Sheet1", I have space for a TM1ELLIST formula that is updated by button click on the sheet with the quick reports. I can toggle/rerun any number of times and the quick reports NEVER refresh unexpectedly. I even set the context filter values for a couple quick reports to reference the Actual/Budget dropdown selection and they will not refresh from the macro. Based on this, I'm inclined to believe that there's something wrong with your code that needs further debugging. I'll paste my quick and dirty macro at bottom for reference. Do you by change have "type-in-refresh" enabled for quick reports in your PAfE options?
20 Ton Squirrel wrote: ↑Mon Apr 11, 2022 8:08 pm
I need to selectively update data. Is this possible?
Yes, by using RefreshSelection instead of RefreshSheet, see both options in macro at bottom
20 Ton Squirrel wrote: ↑Tue May 10, 2022 3:27 pm
I bounced this issue against someone with far more experience than myself, I will share this sage advice…
The Guru LeRoux wrote:Do not use TM1ELLIST, use the PAfE List object instead.
Did they give a reason or did they just say not to use it? Advice or direction proffered without supporting evidence is to be met with skepticism in my opinion. If they gave you good reason not to use TM1ELLIST and you didn't share it with the rest of us, shame on you. I've used TM1ELLIST for a few different solutions and found it to work just fine for my needs. I'm genuinely curious how a PAfE list object would be the preferred solution in your specific use case considering the
issues you're having making that fit your needs.
my macro that worked without issue:
Code: Select all
Sub tm1ellist_test()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim vMDX As String
' recalc for mdx formula update based on selection
Application.Calculate
' mdx formula is on same worksheet as 3 quick reports
vMDX = Range("C2").Value
' activate sheet that has the TM1ELLIST range
Worksheets("Sheet1").Activate
' clear existing values in range
Range("K2").Select
Do Until ActiveCell = ""
ActiveCell.Delete
ActiveCell.Offset(1, 0).Select
Loop
Application.CutCopyMode = False
' insert TM1ELLIST formula
Range("k2").Formula2R1C1 = _
"=TM1ELLIST(""<server>:<dimension>"",,,,,""" + vMDX + """)"
' refresh sheet with TM1ELLIST formula
'Application.COMAddIns("CognosOffice12.Connect").Object.AutomationServer.Application("COR", "1.1").RefreshSheet
' OR select cell formula pasted into and refresh just that selection (excel spill will still return full array)
Range("k2").Select
Application.COMAddIns("CognosOffice12.Connect").Object.AutomationServer.Application("COR", "1.1").RefreshSelection
' paste values and clear clipboard
Range("K2").Select
Do Until ActiveCell.Offset(1, 0) = ""
ActiveCell.Offset(1, 0).Select
Loop
vStop = ActiveCell.Address
Range("K2", vStop).Copy
Sheets("Sheet1").Range("K2").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
' return to worksheet with quick reports
Worksheets("Sheet2").Activate
Application.ScreenUpdating = True
Application.Calculation = xlAutomatic
End Sub