TM1 Forum

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

Hi friends, I wanted to share this information that I learned recently, to hopefully help you avoid some heartache (maybe it is old news to many, but it was new news to us :shock: ). If you are considering an upgrade to PA (TM1) Server, and your user community has Excel Office 365 deployed, IBM does...

I think these are all the valid TM1 related VBA commands, using the legacy perspectives (Tm1p.xla) add-in. https://www.ibm.com/docs/en/planning-analytics/2.0.0?topic=reference-macro-functions If you are using PaFE, there is a VBA API: https://ibm.github.io/paxapi/#introduction I'm not sure if either...

Very cool, thank you friend.

Hoping some may find this helpful. I still use VBA for a lot of legacy processes, so having a direct API connection to TM1 has been helpful, especially for running Turbo Integrator processes, I've been able to essentially bypass the need for connecting to TM1 via PAfE and using the proprietary TI la...

ABC1 intersection @ (Total Year, A1) is not being fed, so the value will return 0.

B1 will consolidate across month dimension = 16
C1 same = 15

+1 tried to get this working today, but no avail, will keep plugging away at it and report back any success

I clearly need some help, so if someone can share a powershell script that does something similar, i would really appreciate it I'm willing to dig into this to see what programmatic solution is possible. Can you give me a definition of "hot promote" in this context, that's one that's not ...

Hey, just throwing it out there, I was in the same boat as you about a year ago, stepping into a new company + position that was TM1 / Planning Analytics heavy and I had zero TM1 experience, imagine :o :lol: I've had to swim or drown, and I've learned a lot since (PAW UI / Excel Add-In, using the Re...

Mention of TM1py, also open source, is missing from this discussion, but imho it's one of the key developments (hand in hand with the introduction of the REST API by Hubert) in TM1 for a long time and kudos to Cubewise for resourcing and championing it - and I find it hard to imagine another way of...