Hi there,
I have a requirement that requires a cell specific formatting. Formatting the whole row or column in a specific format doesn't work here as across rows and columns, there is a blend of percentage and thousand separated decimals.
Is there a solution in TM1 for this purpose?
Thanks in advance.
Cell Formatting
-
Alan Kirk
- Site Admin
- Posts: 6667
- 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: Cell Formatting
From the Request For Assistance Guidelines:tm1green wrote: I have a requirement that requires a cell specific formatting. Formatting the whole row or column in a specific format doesn't work here as across rows and columns, there is a blend of percentage and thousand separated decimals.
Is there a solution in TM1 for this purpose?
2) Ensure that you specify the component of the software that you're having a problem with. If someone states that they have a problem with number formatting, people need to know whether that's in Cube Viewer, Web, EV, the API, or wherever.
"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.
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
-
tm1green
- Posts: 22
- Joined: Tue Mar 31, 2015 9:54 am
- OLAP Product: TM1
- Version: 10.2.2
- Excel Version: 2010
Re: Cell Formatting
Sorry for not being clear in my post. Thanks Alan for pointing out. I am developing in Architect 10.2.2 running on Windows 7 64bit. I don't any issue with the tool itself. It is more of a requirement rather than issue.
Thank you.
Thank you.
-
Alan Kirk
- Site Admin
- Posts: 6667
- 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: Cell Formatting
What are you using in your measures dimension? Normally the formatting would be done on those elements as you wouldn't typically have a single element that would have different formatting depending on the other elements.tm1green wrote:Sorry for not being clear in my post. Thanks Alan for pointing out. I am developing in Architect 10.2.2 running on Windows 7 64bit. Thank you.
"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.
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
-
tm1green
- Posts: 22
- Joined: Tue Mar 31, 2015 9:54 am
- OLAP Product: TM1
- Version: 10.2.2
- Excel Version: 2010
Re: Cell Formatting
Thanks Alan for the reply. I have a cube with Measure (Revenue and Nights), Category and Period dimensions.
In cube viewer,
As you can see, the Occupancy supposed to be in % in the Nights column while the rest should be in thousand separated decimals.
Thank you.
In cube viewer,
| Revenue | Nights | ||
| Jan 2015 | Groups | $2,000,000 | 5,000 |
| Complimentary | $1,000 | 200 | |
| Occupancy % | $0 | 90% |
Thank you.
-
tm1green
- Posts: 22
- Joined: Tue Mar 31, 2015 9:54 am
- OLAP Product: TM1
- Version: 10.2.2
- Excel Version: 2010
Re: Cell Formatting
Update. I managed to achieve the requirement by selecting the cells in cube viewer and select Options then Format from the menu. However, the formatting does not persist in performance modeler and web contributor. 
-
paulsimon
- MVP
- Posts: 808
- Joined: Sat Sep 03, 2011 11:10 pm
- OLAP Product: TM1
- Version: PA 2.0.5
- Excel Version: 2016
- Contact:
Re: Cell Formatting
Hi
The problem is due to a clash - I am guessing that you have a % Format on Occupancy % and a $ Format on Revenue? If so the issue is that you have formats in two dimensions in a Cube Viewer. Given that, any cell is going to potentially have two formats and which wins will depend on what is displayed on columns and rows etc, and can vary.
One option is to push this out to Excel and then you can format on a cell by cell basis.
Another is to apply formatting in only one dimension. However, I cannot see a way to do that, since Revenue and Nights need $ and integer formats, but occupancy needs a %. A compromise might be to omit the $ format on Revenue, and instead just have a % format on Occupancy %
My only other suggestion would be to change your model to make Occupancy % a Measure alongside Nights.
In fact, if I remember my hotel modelling correctly, Occupancy % would normally be solely based on Number of Nights, and not on Revenue. There might be another measure which would be the theoretical revenue given 100% occupancy vs the Revenue achieved, but from what I have heard most hotels just focus on % rooms occupied over total rooms.
Regards
Paul Simon
The problem is due to a clash - I am guessing that you have a % Format on Occupancy % and a $ Format on Revenue? If so the issue is that you have formats in two dimensions in a Cube Viewer. Given that, any cell is going to potentially have two formats and which wins will depend on what is displayed on columns and rows etc, and can vary.
One option is to push this out to Excel and then you can format on a cell by cell basis.
Another is to apply formatting in only one dimension. However, I cannot see a way to do that, since Revenue and Nights need $ and integer formats, but occupancy needs a %. A compromise might be to omit the $ format on Revenue, and instead just have a % format on Occupancy %
My only other suggestion would be to change your model to make Occupancy % a Measure alongside Nights.
In fact, if I remember my hotel modelling correctly, Occupancy % would normally be solely based on Number of Nights, and not on Revenue. There might be another measure which would be the theoretical revenue given 100% occupancy vs the Revenue achieved, but from what I have heard most hotels just focus on % rooms occupied over total rooms.
Regards
Paul Simon