TM1 Forum

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:
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.

Worth a try with the row unhidden?

There are some actions that aren't possible versus a hidden cell row.

I would think that the reference for GetCurrentReport needs to be a cell within the report rather than a cell somewhere on the sheet.

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.

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.

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!)

Good luck!

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!)

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?

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