TM1 Forum

Hi there

In perspectives, numbers were always committed automatically. In Pax, it seems like you have to click on the Commit icon.
Is there a way to avoit this and have data commit automatically as soon as you hit you enter key?

Many thanks!!

Regards
Chris

Quick Reports have a type-in refresh option but that is only for automatically refreshing data after a metadata change. I'm not aware of type-in commit as a radial option. In the couple cases I've had where the end user asked for auto-commit after entry, I've used VBA to commit the active quick report on worksheet change. I put a toggle at the top of the page so they can turn the auto-commit on/off. The ActiveCell.Offset(-1,-1) in the code below assumes that the end user hits RETURN or TAB after making an input and this corrects for if the cursor is moved out range of the Quick Report where data was being modified. I also call a refresh sheet after commit regardless of end users' PAX settings for refresh after commit; the scope and necessity of the refresh could vary depending on needs.

PAX API documentation

Thanks for sharing.

ascheevel wrote: ↑Mon Jul 27, 2020 2:56 pm Quick Reports have a type-in refresh option but that is only for automatically refreshing data after a metadata change. I'm not aware of type-in commit as a radial option. In the couple cases I've had where the end user asked for auto-commit after entry, I've used VBA to commit the active quick report on worksheet change. I put a toggle at the top of the page so they can turn the auto-commit on/off. The ActiveCell.Offset(-1,-1) in the code below assumes that the end user hits RETURN or TAB after making an input and this corrects for if the cursor is moved out range of the Quick Report where data was being modified. I also call a refresh sheet after commit regardless of end users' PAX settings for refresh after commit; the scope and necessity of the refresh could vary depending on needs.

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("I2").Value = "On" Then
    On Error GoTo err_handler1:
        Reporting.GetCurrentReport(ActiveCell.Offset(-1, -1)).Commit True
        Application.COMAddIns("CognosOffice12.Connect").Object.AutomationServer.Application("COR", "1.1").RefreshSheet
End If
err_handler1:
Exit Sub

End Sub

PAX API documentation

Hi, I am trying to commit data through PAXAPI but always get run-time error '91'. The creation of quick report from api is fine. The Sheet1 below is the modified quick report and ready to commit data.

Worksheets("Sheet1").Activate
Reporting.GetCurrentReport(ActiveCell).Commit True

Appreciate your guide on this.

macsir wrote: ↑Thu Jan 28, 2021 2:20 am Hi, I am trying to commit data through PAXAPI but always get run-time error '91'. The creation of quick report from api is fine. The Sheet1 below is the modified quick report and ready to commit data.

Worksheets("Sheet1").Activate
Reporting.GetCurrentReport(ActiveCell).Commit True

Appreciate your guide on this.

Macsir, I think the issue is the ActiveCell when Sheet1 is activated by your macro is not a cell within the range of the Quick Report. If you know explicitly where the Quick Report is on the sheet, you could supply a specific cell within the range instead of using ActiveCell. I was able to replicate your '91' error when I passed a cell that was outside of the range of the Quick Report.

For my specific file, cell D2 is blank cell two columns to the right of the context filters of my quick report and B2 is a dimension value within the context filter of my quick report.


***Below is what I was originally going to post after some testing before I figured out how to replicate your exact error. I'll leave it here in case someone else stumbles on the issue of not first importing the CognosOfficeAutomationExample.

I tested a couple things this morning and was unable to get a run-time error 91, but did get a 424 error if I set up the worksheet change macro BEFORE I had imported the the CognosOfficeAutomationExample module.

My test script to resolve the 424 error:
1. open new excel file, create some quick report (stay connected to PAX)
2. paste my original worksheet change script (below with err_handler commented out) into code for worksheet with quick report, close VBA debugger
3. input "On" in cell I2 so code will execute
4. change any value in quick report, encountered object required error
5. delete worksheet specific code (will paste back in later)
6. import CognosOfficeAutomationExample module
7. paste back in the worksheet code for the worksheet with quick report
8. change some value within quick report, successful commit & refresh


worksheet code with error handling removed

Hi, ascheevel
Thank you very much for detailed testing and explanation.
Yes, I figured out later that I didn't point ActiveCell onto a Quick report area in the sheet.