Checkboxes for "flag" measures in TM1

Ideas and tips for enhancing your TM1 application
Post Reply
User avatar
Elessar
Community Contributor
Posts: 331
Joined: Mon Nov 21, 2011 12:33 pm
OLAP Product: PA 2
Version: 2.0.9
Excel Version: 2016
Contact:

Checkboxes for "flag" measures in TM1

Post by Elessar »

The best I could implement for now, is using the Excel checkboxes:

Assume we have a 2-dim cube. We need to replace the numeric values of a flag measure with a checkbox.
  1. First we need to enable Developer ribbon (right-click on ribbon, "customise the ribbon", enable "developer" ribbon)
  2. On the "Developer" ribbon, select Insert, Checkbox. Place it somewhere
    Insert CB.png
    Insert CB.png (29.54 KiB) Viewed 41559 times
  3. Right-click on the created checkbox, Format control, assign the cell link to a cell near your target flag cell
    Format control.png
    Format control.png (24.89 KiB) Viewed 41559 times
  4. Write a DBS formula in your target flag cell, pointing to the desired "flag" cell, to send 1 or 0 to the server depending on the checkbox status: "=DBS(IF(CheckBoxCellLink=TRUE;1;0);Server;coordinates)"
    Formula.png
    Formula.png (13.07 KiB) Viewed 41559 times
Viola, it works, including websheets.

Note that this does NOT work with dynamic views (like active forms), only with static. So, in the example provided, you need to repeat 12 times to add checkboxes to every month.

There is an RFE for adding a checkbox format in PAW. Please vote: https://ibm-data-and-ai.ideas.aha.io/ideas/PAOC-I-517
Best regards, Alexander Dvoynev

TM1 and Data Science blog: 6th article - PAfE + VBA: Commit each cell without pressing “Commit” button.
User avatar
PavoGa
MVP
Posts: 616
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 FP7, PA2.0.9.1
Excel Version: 2013 PAW
Location: Charleston, Tennessee

Re: Checkboxes for "flag" measures in TM1

Post by PavoGa »

Very cool!!!
Ty
Cleveland, TN
User avatar
Alan Kirk
Site Admin
Posts: 6606
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: PA2.0.9.18 Classic NO PAW!
Excel Version: 2013 and Office 365
Location: Sydney, Australia
Contact:

Re: Checkboxes for "flag" measures in TM1

Post by Alan Kirk »

Elessar wrote: Tue Mar 16, 2021 6:44 pm There is an RFE for adding a checkbox format in PAW. Please vote: https://ibm-data-and-ai.ideas.aha.io/ideas/PAOC-I-517
I've done my civic duty, bringing it up to 7 votes.
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
Mark RMBC
Community Contributor
Posts: 292
Joined: Tue Sep 06, 2016 7:55 am
OLAP Product: TM1
Version: 10.1.1
Excel Version: Excel 2010

Re: Checkboxes for "flag" measures in TM1

Post by Mark RMBC »

Hi,

I use checkboxes a lot in TM1 web for filtering views, confirmations before running TI's and sending data back to cubes.

I was also struck that in PAW this was not available.

I was thinking along the lines of a new set of shape objects called Control Shapes or something, but actually a data format option seems like a great idea and something that never occurred to me.

regards,

Mark
tomok
MVP
Posts: 2831
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

Re: Checkboxes for "flag" measures in TM1

Post by tomok »

I use checkboxes in TM1 sometimes to indicate to a user whenever something is turned ON or OFF but I use a font to do it. In Wingdings 2, uppercase P is a check mark. I can then use an IF statement in the form to "turn" the value in the flag field to "P" if it is on or nothing if off. In the active form I will pull back all the flag values from the cube, hide those columns, and then have columns to the right with formulas like =IF((N8="WRITE"),"P",""). In this example I am pulling back values from an }ElementSecurity cube in column N and displaying a check mark if the group has WRITE and nothing if not. I also make the background green by conditional formatting if the value in the cell is "P". To turn the flag on or off I have an action button that runs a TI process that uses a pair of Excel OFFSET formulas to determine the row and column where the cursor is currently located to pass the selected element and group as parameters to the TI process to toggle the flags. The offset formulas are (where Start is a cell one row before the first value in the rows and one column to the left of the starting column)

Code: Select all

Row - OFFSET(Start,(@CELL("Row")-ROW(Start)),0)
Column - =OFFSET(Start,0,CELL("Col")-COLUMN(Start))
2021-03-17_8-06-03.jpg
2021-03-17_8-06-03.jpg (217.25 KiB) Viewed 41481 times
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/
MarenC
Regular Participant
Posts: 346
Joined: Sat Jun 08, 2019 9:55 am
OLAP Product: Planning Analytics
Version: Planning Analytics 2.0
Excel Version: Excel 2016

Re: Checkboxes for "flag" measures in TM1

Post by MarenC »

Hi Tomok,

how does your formula work? I don't really get it.

I would understand if you had used something like:

Code: Select all

Indirect(Address(Cell("row"),Cell("col")))
But I don't see how your formula tells you what cell you are on and then gets the value?

Maren
tomok
MVP
Posts: 2831
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

Re: Checkboxes for "flag" measures in TM1

Post by tomok »

MarenC wrote: Wed Mar 17, 2021 4:08 pm Hi Tomok,

how does your formula work? I don't really get it.

I would understand if you had used something like:

Code: Select all

Indirect(Address(Cell("row"),Cell("col")))
But I don't see how your formula tells you what cell you are on and then gets the value?

Maren
The purpose of the OFFSET formulas is not to get the value in the cell, it is to get the value in the dimension element row (the values in the first column on the left) and the value in the security groups column (the values in the country row at the top). So, for example, if my cursor is in the cell corresponding to the intersection of 002-592-U01 and Netherlands then my Row indirect formula resolves to "002-592-U01" and my Column indirect formula resolves to "Netherlands". I pass both of these as parameters to the TI formula called by the action button so the TI process knows which Note# and Country to either take away or give access to. If the button clicked is "Grant Access" then the TI process is going to put a value of "WRITE" in the }ElementSecurity_ICNote cube for the note# and group. If the button clicked is "Revoke Access" then it is going to clear the value in the }ElementSecurity cube. If you want, you can just have a single action button called "Toggle" and you can have the TI process determine the current state of the Note#/Group intersection and then reverse it every time the button is clicked.

The point of all of this is so a user doesn't have to key in the Note# and Group they want to modify security for, they can just put the cursor in the appropriate intersection and click on the action button.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/
MarenC
Regular Participant
Posts: 346
Joined: Sat Jun 08, 2019 9:55 am
OLAP Product: Planning Analytics
Version: Planning Analytics 2.0
Excel Version: Excel 2016

Re: Checkboxes for "flag" measures in TM1

Post by MarenC »

Hi Tomok,

I get it now and I have learned something, thanks!

Do you wrap an iferror around your formula or how do you handle where a user may click on an invalid cell?

Maren
tomok
MVP
Posts: 2831
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

Re: Checkboxes for "flag" measures in TM1

Post by tomok »

MarenC wrote: Thu Mar 18, 2021 10:42 am Hi Tomok,

I get it now and I have learned something, thanks!

Do you wrap an iferror around your formula or how do you handle where a user may click on an invalid cell?
This is primarily aimed at TM1Web so you can't stop someone from clicking on an action button, regardless of where the cursor is located. Therefore, I have validity checks in the prolog of the TI process that make sure the parameters passed are valid before executing the remainder of the process.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/
Post Reply