PAX RestAPI VBA error

Post Reply
luizg2019
Posts: 41
Joined: Thu Sep 12, 2019 11:02 pm
OLAP Product: TM1 - PAX-PAW-Perspectiv-Arc
Version: PA 2.0.9 - PAW 2.0.73
Excel Version: office 2016

PAX RestAPI VBA error

Post by luizg2019 »

Hi.

We have a quick report with some custom formatting that hasn't been working properly after updating to PAX 73.

After updating the dimension filters (subnm), the user press a button that calls a VBA code that test some things, rebuilds the sheet, build the MDX, and query the data. But we've been getting an VBA runtime error '91 object variable or with block variable not set" on this line:

Code: Select all

Reporting.GetCurrentReport(Range("Sandbox")).UseServerFormats = False
Range("Sandbox") points to a hidden row where we have "[Base]" stored.

Any ideas what's going on? This wasn't an issue before, no changes were made other than updating PAX. The hidden row and the named variable Sandbox are still there.
User avatar
Steve Rowe
Site Admin
Posts: 2410
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: TM1 v6,v7,v8,v9,v10,v11+PAW
Excel Version: Nearly all of them

Re: PAX RestAPI VBA error

Post by Steve Rowe »

Worth a try with the row unhidden?

There are some actions that aren't possible versus a hidden cell row.
Technical Director
www.infocat.co.uk
User avatar
gtonkin
MVP
Posts: 1192
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: Latest and greatest
Excel Version: Office 365 64-bit
Location: JHB, South Africa
Contact:

Re: PAX RestAPI VBA error

Post by gtonkin »

I would think that the reference for GetCurrentReport needs to be a cell within the report rather than a cell somewhere on the sheet.
Adam
Posts: 94
Joined: Wed Apr 03, 2019 12:10 am
OLAP Product: IBM PA
Version: 2.0.9.x
Excel Version: Microsoft 365 x64

Re: PAX RestAPI VBA error

Post by Adam »

luizg2019 wrote: Tue Jul 05, 2022 9:32 pm After updating the dimension filters (subnm), the user press a button that calls a VBA code that test some things, rebuilds the sheet, build the MDX, and query the data. But we've been getting an VBA runtime error '91 object variable or with block variable not set" on this line:
Hi Luiz,

Wanted to laser focus on "rebuilds the sheet". Are you using PAFE API for this? If so, what function are you using?
Have some thoughts and can help but don't want to go down a path you're not on.
Take care.
Adam
luizg2019
Posts: 41
Joined: Thu Sep 12, 2019 11:02 pm
OLAP Product: TM1 - PAX-PAW-Perspectiv-Arc
Version: PA 2.0.9 - PAW 2.0.73
Excel Version: office 2016

Re: PAX RestAPI VBA error

Post by luizg2019 »

I did try to unhide the row but nothing happened.
The range is in the same sheet as the report.
Adam wrote: Thu Jul 07, 2022 5:00 pm Hi Luiz,

Wanted to laser focus on "rebuilds the sheet". Are you using PAFE API for this? If so, what function are you using?
Have some thoughts and can help but don't want to go down a path you're not on.
sorry, I got confused with another report, this macro doesn't rebuild the sheet.


What I did notice is that Quick Reports created on PAX 73 have an extra 3 rows at the top:

Host
TM1 Server
Cube

This is not the case with QR created with PAX 62. I think this is causing the issue, will investigate more when I have time.
Adam
Posts: 94
Joined: Wed Apr 03, 2019 12:10 am
OLAP Product: IBM PA
Version: 2.0.9.x
Excel Version: Microsoft 365 x64

Re: PAX RestAPI VBA error

Post by Adam »

luizg2019 wrote: Wed Jul 13, 2022 12:04 am What I did notice is that Quick Reports created on PAX 73 have an extra 3 rows at the top:

Host
TM1 Server
Cube

This is not the case with QR created with PAX 62. I think this is causing the issue, will investigate more when I have time.
Indeed that is correct. We had to work around an issue with rebuilding(A) PAFEv66 quick reports in PAFEv76. Not just 3 additional slicers in the header, but also a defect with the QR shifting over to the right by one column.

(A): We define rebuild as PAXAPI function Reporting.QuickReports.ReplaceWithFormats(qrIDNumber, mdxStmt, True) where the mdxStmt contains dynamic subsets enabling elements to be added to the QR on the fly. (We've had dynamic quick reports years before anyone else!) :D

Good luck!
Take care.
Adam
luizg2019
Posts: 41
Joined: Thu Sep 12, 2019 11:02 pm
OLAP Product: TM1 - PAX-PAW-Perspectiv-Arc
Version: PA 2.0.9 - PAW 2.0.73
Excel Version: office 2016

Re: PAX RestAPI VBA error

Post by luizg2019 »

Adam wrote: Thu Jul 14, 2022 1:54 am Indeed that is correct. We had to work around an issue with rebuilding(A) PAFEv66 quick reports in PAFEv76. Not just 3 additional slicers in the header, but also a defect with the QR shifting over to the right by one column.

(A): We define rebuild as PAXAPI function Reporting.QuickReports.ReplaceWithFormats(qrIDNumber, mdxStmt, True) where the mdxStmt contains dynamic subsets enabling elements to be added to the QR on the fly. (We've had dynamic quick reports years before anyone else!) :D

Good luck!
oh that's a great way to do it, I just realized we are not using ReplaceWithFormats but using a lot of custom vba code to do the same thing :|

Also, sometimes we get a thread POST ExecuteMDX hanging, I believe its being caused by our QR. Do you have any ideas on what might be?
Adam
Posts: 94
Joined: Wed Apr 03, 2019 12:10 am
OLAP Product: IBM PA
Version: 2.0.9.x
Excel Version: Microsoft 365 x64

Re: PAX RestAPI VBA error

Post by Adam »

We’ve not seen that issue before. Would you post some VBA for us to look at?
Take care.
Adam
Post Reply