Cell Formatting

Unknown

   Rich Wojcik, Paola Mason and I are working on a scorecard solution. We would like to refine the formatting to be cell-specific. Consider the following scorecard as our starting point, and I'll describe our challenge.

  On the above scorecard, we want to be able to identify percentages with a (%), dollars with a ($) and other KPIs without a symbol. Furthermore, we want to be able to set the precision of the number displayed to 1 or 2 decimal places. We've played with the Row Template and Layout Block Formatting features, but we can't seem to get a combination that work together as we want. We seem to be able to have some, but not all of the formatting we want to present.

 

   Focusing on the EBITDA(N) per Life Served, we want to be able to see the first column (Weight) with a % symbol and one decimal place precision, but the second and third column should use a $ symbol and two decimal places of precision. Finally, the last two columns should use a % symbol and one decimal place of precision. Since Row Template apply formatting to each cell of a row, Row Templates haven't worked for us yet. Next we tried using the Layout Block Formatting too. If we try to add a $ to the second and third column formatting, it applies it to every cell in those columns. We do not want to see a $ on the Roll out of CUMULOUS line. Is there a way to be able to specify cell-specific formatting for the symbol and precision to be applied?

 

Thank you for your help,

Helmut Heimann

Hi Bob Gill!

Although I'm not quite sure whether you want to be able to do this formatting in WinClient or in HTML5 (have not tried this in HTML5, yet)--I'd propose using a rule in conjunction with a cell template. This might lead to having to change your model, as it seems that you are using different cubes / algorithms in the columns.

Another thing is that I'm pretty sure that you can apply layout block formatting to specific levels (such as the grand total, only)--depending on the cells you highlight...

Another idea would be to use Nexel ;-)

 

Kind regards,

Helmut

Paul Wyatt

Hi Bob, based on your question and diagram, you can achieve this through the block formatter as Helmut has said.  Below is the formatting, in red boxes, I set up to show a custom block, remembering to enable the custom block editor first.  Highlight the required level of cell Value, Total, Grand Total or all of them as I have.  Once highlighted, you can specify how it should look in options and Font boxes. 

Below is how I formatted a random dataview to meet your specifications (NB: I centred the % values to emphasise that the symbols before and after appear at the extreme end of the cell - this is not always desirable)

 

A good feature to use is the Copy and Paste which allows you to copy the completed format and paste it to the blocks that you want without having to format each one individually - useful for when you realise that you forgot to copy 'drag' the block with formatting, to create the subsequent blocks, at the beginning of the dataview creation - doh!

 

Was this what you're looking for?

Unknown

Hi Helmut Heimann,

 Thank you for your help. We'll be using the WinClient for now, but I'm not sure how a rule would help? Can you elaborate around how a rule can be used for formatting?

Unknown

Hi Paul Wyatt - Avison Young UK,

  Thank you for your help. This is close, but still runs into the same challenge. Using your example grid, we would want to see just Freehold and Long Leasehold lines show 85 and 114, without decimals and without a dollar sign, while the other lines, and other columns, stay as they are now. Any other ideas?

Helmut Heimann

Hi Bob Gill!

 

First of all a rule would mean that you might have to change your model from KPI-based to an account-based one; thus you'd have an entity describing your KPI (you might want to have a look at this document: How To: Choose whether to define an information as an Entity or Infocube ). With the rule applied to the entity members you'd be able to format each entry (every KPI) as it suits your needs. The rule itself would describe how different members of your KPI will be calculated (have a look into this one: Alternative approaches to modelling Finance Reporting in BOARD  

You can then use the row template for the KPI entity to format each cell according to your needs -- like this:

 

The next thing is that the block you are using in your layout should make use of this rule--"Functions->Options->Rule":

 

(this example is from our german A&R-Training, but I think you'll get the hint ;-)

 

Kind Regards,

Helmut

Unknown

Thank you again Helmut Heimann. I’ll study your response and hopefully that does the trick. 

Matthew Sobon

How do you do this on the most current version of Board?

------------------------------
Matthew Sobon
Data Analyst
M.T. Maritime Management (usa) LLC
United States
------------------------------
-------------------------------------------

Helmut Heimann

Hi @Matthew Sobon,

you can find both, the entity format and the rule definition in your database:
Kind regards
Helmut


------------------------------
Helmut Heimann
Senior Consultant
Board Community
Germany
------------------------------
-------------------------------------------