Insert blank lines as separators?

[Mark H]

Folks

Would like to separate sections of measure dimension using blank lines.

clearly i can create the 'separators' using dash/underscores etc but ....

Any tips?

Thanks

Newbie

[Steve Rowe]

Mark,
You night want to give more information about where you are trying to do this in the TM1 world, it is pretty difficult to answer as currently phrased....

[Mark H]

Fair enough Steve - will try again.

On my measure dimension i have different groups such as 3 expense lines then 3 revenue lines or simply consider the sections of a PnL. To avoid one straight list I wish to make it a little easier on the eye:

revenue_1
revenue_2
revenue_3
blank line
expense_1
expense_2
expense_3
blank line

In absence of this technique i am doing this:
revenue_1
revenue_2
revenue_3
--
expense_1
expense_2
expense_3
--------

I have seen this achieved in other technologies by using the pipe '|' symbol

I could use a hierrchy to group expenses and revenue but there are times when i would just prefer a blank.


Thanks

[tomok]

It would be an exceedingly bad idea to add any type of "dummy" element or pseudo measure to a dimension in order to make the presentation easier on the eye. Each element must be denoted by a valid character string consisting of letters, numerals, and the other valid characters like underscore, etc. TM1 ignores blank spaces in element names so I'm pretty sure it won't work. This is not to mention how bad an idea this is from a data modeling standpoint. You should never bastardize your model just for presentation purposes. This type thing is better done in the reports you will create in the tool. I'm pretty sure you don't have any options in the cube viewer or subset editor to achieve this but it can be done in Excel or TM1 Web reports with a little creativity with VBA or Active Forms. You should use the search function in this web site for that topic. there's probably a post or two about this very thing.

[Steve Rowe]

I can see where you are coming from but which environment are you going to view this in?

If it is any of the dynamic views of TM1, like the cube viewer, dynamic slice etc then your users will nearly always have zero suppression on and so won't see the gap any way.

If you are in a static Excel report I'd just be inserting some blanks rows in the report.

As far as bastardising your model goes, I think that you should build whatever you need that gives your users the best experience subject to the constraints you have in the support you can offer. I just can't see how you could build what you want in a useful way...

Cheers

[lotsaram]

I wouldn't go as far as Tomok on this one. If the use interface is going to be cube view, contributor or active form then you can make a case for having blank or header elements that hold no data. If it something has a purpose in a model then it has a purpose, I wouldn't be so purist to insist that that purpose must be to hold data or perform a calculation.

Note that if proper use is made of subsets then you don't actually need multiple blanks as you can reuse the same element multiple times. However if you are talking section headers as opposed to blanks (which I think is more useful) then this is normally better handled by a consolidation as this allows users to expand and collapse sections. If the purpose of the element is purely organizational not as a genuine consolidation point then just make the element weightings all 0 and use element formatting to display a 0 as a blank cell.

[Wim Gielis]

If the purpose of the element is purely organizational not as a genuine consolidation point then just make the element weightings all 0 and use element formatting to display a 0 as a blank cell.

and give up on zero-suppressed rows, which seems to be the prerequisite throughout the discussion.

[lotsaram]

and give up on zero-suppressed rows, which seems to be the prerequisite throughout the discussion.

True, where I have used this has been for web input forms using active forms. As the forms have been to capture data from a blank base zero supression wasn't on and so this was a non issue. If you wanted to get tricky you could have the element weights as standard 1 not 0 and use element formatting to make all values display as blank (i.e. ;;;)

[Mark H]

Thanks guys. We have covered headers/groupings without data but seems there is not way to insert a blank line then.

Cheers

[pmakulski]

I have often wanted this. (Well, actually, my management wants this. Formatting to senior management seems to be as important as the numbers themselves, but, I digress.)
Reports just look better if there is a spacer line between one section and the next.
As was pointed out, this is easy enough to do with a Slice report (just add a blank line where you want it.)
But I use a lot of Active Forms.
I thought you might be able to have the section total line in the format section have a bigger row height. But, alas, row height is not one of the format elements carried from the format section to the data section. (At least, not in 9.5.2. IBM ... how about adding this in 10.2)

[tomok]

While you can't add blank lines as elements in a dimension, you can add other "dummy" elements that can be treated as blank lines in an active form report. For example, you could add items called Blank_1, Blank_2, Blank_3, etc., placing them wherever you want in the dimension hierarchy, and giving them an attribute that indicates they are dummies. Then in the active form, looking at the attribute, set the format to make the text the same color as the background when that attribute is present and then voila!, a blank line.

[lotsaram]

I have often wanted this. (Well, actually, my management wants this. Formatting to senior management seems to be as important as the numbers themselves, but, I digress.)
Reports just look better if there is a spacer line between one section and the next.
As was pointed out, this is easy enough to do with a Slice report (just add a blank line where you want it.)
But I use a lot of Active Forms.
I thought you might be able to have the section total line in the format section have a bigger row height. But, alas, row height is not one of the format elements carried from the format section to the data section. (At least, not in 9.5.2. IBM ... how about adding this in 10.2)

It is actually very easy to achieve the look of a blank row in an active form via adjusting row heights dynamically via a very simple trick. No need for blank formatted elements if you don't want them.
- create an attribute for some kind of row formatting for the line item dimension (presumably IS accounts)
- insert some additional rows in the format area and concatenate whatever the usual 0,1...N with the different combinations of expected attribute values (you aren't limited to the standard active form format keys, you can use whatever you like)
- edit the formulas in column A to also concatenate the report level with the attribute you created
- insert an additional column which you will subsequently HIDE. In this column for your attribute indication the accounts that need a space before them format with a really large font like 30, make all the other columns formatted to align to bottom of cell on vertical alignment
- done.

[Mark H]

Thanks All. Will be trying the colour coding text i think....

M

[pmakulski]

lotsaram ... that's brilliant.

So the row height follows the largest font in the line.
I don't even need the attribute lookup in most cases (but that's a good idea too).
Just a conditional format on a hidden column that sets the font size based on level.

I'm going to start using that.

[iansdigby]

In case anyone is still reading this (I was) after 2 years.......

You can get balnk rows by adding another column to the right of the active form and filling it with wrapped text.

- Supposing:
1. you want Active Form level '1' lines to appear with a space below;
2. the first row of your active form is row 20 (the row with the TM1RPTROW formula in).

So, put a formula in your added column, in row 20:
=IF(A19=1,"xxxxxxxxxxxxxxxx","")

- format the column as wrapped text and make it small enough for the xxxx's to wrap.

Rebuild the Active Form.