TM1 Forum

Hi Guys,

Facing weird thing with my VBA Macro. Here's the story.

I have a chore cho_Generate that has one TI process in it, ti_Generate. And in the prolog of this ti_Generate are two more TIs.

In order of execution:

1. upd_dim_Ref Month To Be Reported
2. util_call_Excel Report Generator

ti_Generate has one parameter that is pMonthYear. This is passed to the upd_dim_Ref Month To Be Reported TI. The value from here is stored as an alias for "Report Month" element in the Ref Month To Be Reported dimension.

What the second TI does is it opens a Macro-driven Excel file that would loop thru' all elements combinations, would place them in their proper cells, recalculate and save these Excel files to specific paths.

The thing is, the entered month year attribute value in the dimension Ref Month To Be Reported is not retrieved by the Macro, it seems to always get the previous attribute value. Always the previous.

The way I pass this month year to a cell in the Excel report generator is by equating the attrib value to cell as its value. And not a DBRA in a cell. Code below.

Sheet("Main").Range("MonthYear").Value = Application.Run ("DBRA", "Instance:Ref Month To Be Reported", "Report Month", "Month Year")

But the Excels end up with always the previous attribute value for that element.

I tried inserting an AsciiOutput before and after the util_call_Excel Report Generator process, and I get the correct attribute value.

I find it weird.

Anyone who has faced this issue before? Would really appreciate it.

Thanks is advance.

Art

Hi Guys.

I found the problem. The solution was just always in front me waiting for the kill.

Since there's only a single TI in the chore that runs two other TIs, all data updates in the model are committed after the chore finishes, whether my chore is set to Single Commit Mode or Multiple Commit mode. That's why the macro couldn't get the latest value.

Which, in a way, makes me confused why my ASCIIOuput after the util_call TI returns the new attrib value.

Anyways, so what I did is I removed the upd_dim_Ref Month To Be Reported process from being executed inside ti_Generate process and added that instead in the cho_Generate as the first process to be executed.

Additionally, I set the chore to Multiple Commit Mode so the newest month year updated by the first process will be retrieved in the second.

Hope I discussed it well for others to read.

Thanks,

Art