Checkboxes for "flag" measures in TM1

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

Checkboxes for "flag" measures in TM1

Post by Elessar » Tue Mar 16, 2021 6:44 pm

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 626 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 626 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 626 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

User avatar
PavoGa
MVP
Posts: 508
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: Cleveland, Tennessee

Re: Checkboxes for "flag" measures in TM1

Post by PavoGa » Tue Mar 16, 2021 6:53 pm

Very cool!!!
Ty
Cleveland, TN

User avatar
Alan Kirk
Site Admin
Posts: 6348
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: PA2.0.6 Classic(PAW-free zone)
Excel Version: 2010 and Office 365
Location: Sydney, Australia
Contact:

Re: Checkboxes for "flag" measures in TM1

Post by Alan Kirk » Tue Mar 16, 2021 10:44 pm

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: 246
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 » Wed Mar 17, 2021 9:12 am

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: 2800
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 » Wed Mar 17, 2021 12:08 pm

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 548 times
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/

MarenC
Posts: 142
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 » 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

tomok
MVP
Posts: 2800
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 » Wed Mar 17, 2021 5:46 pm

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
Posts: 142
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 » 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?

Maren

tomok
MVP
Posts: 2800
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 » Thu Mar 18, 2021 11:11 am

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