TM1 Forum

Hi CRP0021, The code I posted previously will preserve the state of Application.Calculation, meaning if it was set to manual prior to running the sub, it will be manual after. If you want to force the Application.Calculation state to manual you can use Application.Calculation = xlCalculationManual T...

Hi there, If you have Planning Analytics for Excel installed, and you are logged into PAW etc. you can trigger branching recalculation by calling the .Dirty() method on a range of cells. Here is the VBA documentation: https://learn.microsoft.com/en-us/office/vba/api/excel.range.dirty This will not w...

If you have Planning Analytics for Excel installed, and you are logged into PAW etc. you can trigger branching recalculation by calling the .Dirty() method on a range of cells. Here is the VBA documentation: https://learn.microsoft.com/en-us/office/vba/api/excel.range.dirty This will not work if the...

Hi all, I have a process that takes a field from SQL Server and uses CellPutS to insert the value into a cube intersection. The limitation I've run up against is that if the string length in the field from SQL Server is > 256 characters it gets truncated. I've confirmed that the data from SQL Server...

Hi all, Below is a refinement to the VBA code I use to launch TI processes via the TM1 Rest API. Wanted to share to help our community in case someone finds it useful. The refinement uses the async polling method. I've found the older sync endpoint has undefined behavior, as far how long it will mai...

Error 438 in VBA generally means the object you are trying to invoke a property / method from is not a valid binding.

Do you have the Planning Analytics for Excel add-in loaded and fully enabled?

Sharing in case this is an option for you. We now use a TM1 library to query data into SQL Server, then use Power Query to load data into Excel / PowerBI. It is very performant. If you are a MS shop the C# library is linked below, or if you're a python dev tm1py should work in the same way. We like ...

"If you're always seeing 404, it may because you're polling from a separate session id. The async job is session specific, you can't check on the status of some other thread's job, even if it was executing under your ID." Oh, that's a good hint. I was trying to access the async object on a...

Hi all, I'm having trouble interacting with the TM1 rest API in the following way. I'm hoping maybe there's some obvious that I'm missing :) Kick-off TI process public async static Task<string> RunProcessWithPolingAsync(TM1SharpConfig tm1, string processName, Dictionary<string, string>? parameters =...

I've had success with three methods: 1.) Use VBA to re-calculate DBSS formulas, to push data into cube cells. (Slow) 2.) Use VBA to send JSON payloads to the TM1 Rest API (Slower) (Could only figure out how to send one cell at a time) 3.) Use VBA to export data to a flat .CSV file, in a location the...

You're welcome, glad I could help

Try using Application.EnableEvents = False before modifying the cell formula.
Then .Dirty method on the range.
Then trigger calculation.

@ascheevel, got it, thank you. In Architect there does seem to be the ability to edit the dimension (right click -> Edit Dimension Structure) with the indexing that is shown in the return from TM1SubSetAll. I'm guessing that PAW does not have the ability to edit the dimension in this way? Both Edit ...

Thank you ascheevel, I do see now in PAW where I can modify the MDX in the subset editor. Is there a way I can modify the ordering returned by TM1SUBSETALL? What is more confusing is that in the Dimension Editor (or MDX .Members return), all of the N level elements roll to the top level consolidatio...

Hi Maren, (Continuing with the previous example) DIMNM at index 1 return Element B (although it shows as the third item in the dimension editor in PAW) DIMIX passing in the string name "Element B" in the same dimension, returns 1. What would be the best way to test ElementName and ElementI...

Scenario: • Dimension was created in the "old world" Perspectives / Architect. • Using =DIMNM("server:dimensionName", index) in PaFE. • In PAW, under the dimension tree, I right click the dimension and select "Edit Dimension" (or "Edit Dimension Classic"). In ...

For posterity, the VBA code below will work: Why it works? Disabling events will prevent ExcelDNA batch recalculation (e.g. RECALC_XXXX) from triggering, otherwise once the batch calculation returns from the PAfE add-in, the cell will be overwritten with the original formula AFTER the cell.Value = c...

Also, you may find this helpful... Trigger batch recalculation of TM1 formulas on a range, or worksheet. Useful for force re-calc in automated scripts: Sub ReCalcRng(rng As Range) calc = Application.Calculation Application.EnableEvents = False Application.Calculation = xlCalculationAutomatic Applica...

Hi my friend, I hope you are having a nice day. Try this dim cell as range set cell = ActiveCell 'ActiveCell, for each, etc. Application.EnableEvents = False cell.Formula = cell.Value 'or .Value2 Application.EnableEvents = True It will prevent ExcelDNA batch recalculation (e.g. RECALC_XXXX) from tri...

I agree with 20 Ton Squirrel. I will caveat that you can use Msxml2.ServerXMLHTTP.6.0 (or some variant that lets you GET / POST to http) in VBA to interact with the ODATA v4 TM1 API directly, and it's a "fairly" modern implementation. Although working in VBA means single thread / no async,...