Format Attribute not being applied in Active Form

[ardi]

Hi,

I have built a websheet with an Active Form from a cube for which I want the formatting defined under Format Attribute of each measure to be applied in websheet, but it not being applied. The active Form has been built with measures dimensions ( last dimension in the cube ) in columns. Also I am not doing any formatting in the hidden area of websheet.

The columns of the active form are dynamic, based on a SUBM Selection, I have a MDX subset on back end that defines the measure that should apply. Because the measures are dynamic, I cannot use the hidden area to apply the formatting, but instead I'm trying to define formatting in the backend.

Any ideas why this is happening?

Thanks

[deepakjain2020]

Hi Ardi,

Hope it is a SUBNM function.
Will like to know whether you comparing between Perspective and Web?

Regards,
Deepak Jain

[ardi]

The formatting is not happening in Perspectives neither.

So my requirement is to have an active form with a List of Customers, and depending on Product Selection which is a Title dimension, to show only applicable Measures in the Columns. So the number of Measures and the Order changes depending on the Product Selection. To achieve this, I have built a cube in the backend, with 2 dimensions: Product and Measures, and Populate this cube with value=1 if a measure applies to a product and zero vice versa. Then I have an MDX subset that for a particular Product, retrieves all the measures that apply.
Then in my Active Form, in Column heading, I have = SUBNM(DimName,SubsetName,MeasureIndex), which gives me the measure names and everything works fine, except the formatting that is applied in backend is not applying.

I hope this explains better my issue

[tomok]

Active forms do not read the element attributes cube to get their formatting. The only way to modify formatting in an active form is through the formatting section at the top. Why do you think you can't use the formatting section at the top? You can do almost anything you want, given the right amount of creativity. You are not bound by those default formulas put in the first column. You can create any formula you want and then apply specific formatting for rows that have that formula in column A.

[ardi]

Thanks Tomok,

My problem is that the columns in the Active Form are dynamic, so depending on Product selection, different Measures should show, so in hidden area I dont know how to set the formatting for a such active form.

[tomok]

Thanks Tomok,

My problem is that the columns in the Active Form are dynamic, so depending on Product selection, different Measures should show, so in hidden area I dont know how to set the formatting for a such active form.

The value in column A is intended to be nothing more than a string code that is then used as a lookup against the formatting section. So, if you have 12 products then you could have twelve different codes that define the formatting for the columns based on which product is chosen. You could even use the product element names if you wanted in the formatting section and then in the body of the report, in column A, just have a formula that points to the Product element. Then the active form will read the product and apply the formatting for that product that you defined in the formatting section.

[declanr]

I'm gathering the issue is more about the columns being dynamic than the rows but you can use conditional formatting in the format area which is dependent on what that column's row header is derived as. You are a little limited if you are using 10.1 as to the amount of conditional statements you can use, to combat this you can have a hidden row that says "if header is A B C or D then format X else format Y" then put your conditional formatting to assess against the hidden row... assuming you don't need more than 3 types of conditional plus the base default. That gives plenty of scope for %s, numbers, currency and text.

[lotsaram]

Thanks Tomok,

My problem is that the columns in the Active Form are dynamic, so depending on Product selection, different Measures should show, so in hidden area I dont know how to set the formatting for a such active form.

You know the saying "a picture is worth a thousand words."? Well there's a reason for it. It seems you are meaning the formatting of columns, in which case i) make sure that TM1RPTFMTRNG is wide enough to cover all possible columns for the maximum subset size and ii) use conditional formatting to set all columns to sheet background fill and font colour same as fill for all columns where the header driven by the SUBNM is blank.

But maybe post a picture??

[ardi]

Thanks Tomok,

My problem is that the columns in the Active Form are dynamic, so depending on Product selection, different Measures should show, so in hidden area I dont know how to set the formatting for a such active form.

You know the saying "a picture is worth a thousand words."? Well there's a reason for it. It seems you are meaning the formatting of columns, in which case i) make sure that TM1RPTFMTRNG is wide enough to cover all possible columns for the maximum subset size and ii) use conditional formatting to set all columns to sheet background fill and font colour same as fill for all columns where the header driven by the SUBNM is blank.

But maybe post a picture??

Yes this is exactly what I'm doing so the Active Form is working fine, my only challange now is applying different format to different columns. I tried adding a text attribute to each measure ( named Measure Type ), with following possible values: Dollar, Percent, Rate, Text. I am trying to apply conditional formatting on each cell in the Format Area in the sheet, by looking at the Measure Type value. It seems to be working in Perspectives but not in TM1Web. We have Excel2003 in the TM1Server and probably that is the reason, eventhough I tried the conditional formatting in Excel 2003 and it's working.

[declanr]

Is your format measure being pulled by a dbrw? If so try changing it to a dbr as it could be an issue with the order of calculation.

[ardi]

It looks like the issue seems to be related to conditional formatting in TM1Web. If I apply some number formatting with conditional formatting, it does not work in TM1Web. Other types of formatting ( such as font color or border color ) are working, but not the number formatting. I checked conditional formatting in Excel 2003 and it looks like it's not possible to apply number formatting with conditional formatting.

I will see if I can find another workaround for this, but as I said earlier, it seems to be hard to handle this.

Thank you for your help

[declanr]

Is it an input sheet or is it just to be used for reporting? If it's for reporting you can do things like concatenating with a $ or % symbol by an if statement wrapped around the DBRWs.