cubeview - FORMAT_STRING MDX question

5. RE: cubeview - FORMAT_STRING MDX question

Like

Denis Barchukov

Posted Fri January 07, 2022 12:08 AM

Hi Ryan, thanks for the respond.

The idea is to replace the web sheet reports with CubeView in PAW. The version I use is PA on the cloud. I am not aware of cell formatting options there. Please, let me know if I miss something.

Having them in separate columns has crossed my mind, and maybe that is how I will approach it, but it makes the report too noisy. So instead of just 13 columns, it will have 17 columns. And with the luck of rich visual formatting in view, it makes it harder to use.

------------------------------
Denis Barchukov
------------------------------

Original Message

Original Message:
Sent: Thu January 06, 2022 11:06 AM
From: Ryan Clapp
Subject: cubeview - FORMAT_STRING MDX question

What are you using to render the mdx? Cell wise formatting is much easier in the UI layer rather than the query layer.

An easy compromise that would honestly make the report easier to read would be to add another column that shows the varience in bps for every row. I know this isn't always possible but it had to be said.

------------------------------
Ryan Clapp

Original Message:
Sent: Wed January 05, 2022 06:27 PM
From: Denis Barchukov
Subject: cubeview - FORMAT_STRING MDX question

Thanks, James!
Indeed, static formatting in MDX expression works well. And you are right, my issue is not in MDX itself but in an inability to find an option to change dynamically (conditionally) the data type format so I can replicate the web sheet-like in an example below.
GP Margin in this case has 2 different types: percent and customer one (bps). I tried all the options and MDX is my last resort.

------------------------------
Denis Barchukov

Original Message:
Sent: Wed January 05, 2022 06:22 AM
From: James McCarthy
Subject: cubeview - FORMAT_STRING MDX question

Hi Denis,

I have been using the static version above for some time but a dynamic version would clearly be much better. Have tried syntax similar to the above and has never worked for me either.

I assume there is a good reason you have not used rules and formatting using PA rather than MDX.

Rgds

James

------------------------------
James McCarthy

Original Message:
Sent: Tue January 04, 2022 12:26 AM
From: Denis Barchukov
Subject: cubeview - FORMAT_STRING MDX question

Hello, Planning analytics and MDX enthusiast folks!

I am very excited about cube view enhancements and customisation using MDX direct queries. This allows the development of dynamic and custom experiences for users.

The ability to create view specific calculations such as version variances percentage and absolute values is especially useful.

For instance, you can create a conditional calculation where you can define that if the current element of a row dimension is a ratio (ec. COGS%), then apply different variable calculations (bps instead of % variance).
It works splendidly on calculation. However, I have trouble with the correct formatting of the cell. I can not find how to display forms dynamically based on an account.

After googling the issue, it seems that manipulating FORMAT_STRING value is the key. I can see that people approach the issue in other products using the custom format attribute on rows. However, it seems that it doesn't work in PA. See the working and attempted dynamic example.

//Option 1 Returns the conditional calculation with static format
MEMBER [WxVersion].[WxVersion].[Reporting Versions].[calculation test] AS 
CASE 
WHEN [WxAccount].[WxAccount].CURRENTMEMBER.PROPERTIES("MEMBER_NAME") = "COGS%" THEN 1 ELSE NULL END, SOLVE_ORDER = 4, FORMAT_STRING = "#0.00000;(#0.000000)" 

//Option 2 Returns the conditional calculation with dynamic format
MEMBER [WxVersion].[WxVersion].[Reporting Versions].[calculation test2] AS 
CASE 
WHEN [WxAccount].[WxAccount].CURRENTMEMBER.PROPERTIES("MEMBER_NAME") = "COGS%" THEN 1 ELSE NULL END, SOLVE_ORDER = 4, 
FORMAT_STRING = [WxFinancialMeasure].[WxFinancialMeasure].CURRENTMEMBER.PROPERTIES("Report Format") 

Another option is to use MDX Cell Calculations, but I don't believe it is supported yet.

Could anyone advice?

------------------------------
Denis Barchukov
------------------------------
#PlanningAnalyticswithWatson

7. RE: cubeview - FORMAT_STRING MDX question

Like

Simon Saul

Posted Sat January 08, 2022 08:52 AM

Just wanted to add to my last message,
I don't think you can get the format to change in a single column so it displays % or BPS at least not in PAW. Previous message will show you how to get the values you require in the right place without symbols % or BPS. perhaps you could have an additional column to display % or BPS after, it could be left aligned to look like it's in place?

The only other option I can think of would be to convert the calculated value into a string and then tack on the required format

I did try to make the Format_String parameter dynamic but in PAW the MDX is rejected with an IIF or CASE statement

------------------------------
Simon Saul
------------------------------

Original Message

Original Message:
Sent: Fri January 07, 2022 09:00 AM
From: Simon Saul
Subject: cubeview - FORMAT_STRING MDX question

How about something like this:
------------------
WITH MEMBER [Measures - Fee Rates].[Measures - Fee Rates].[Var] AS
IIF([Academic Year].CURRENTMEMBER.NAME = "2012/13"
, [Measures - Fee Rates].[Measures - Fee Rates].[Enhanced Fee Income] - [Measures - Fee Rates].[Measures - Fee Rates].[Standard Fee Income]
, [Measures - Fee Rates].[Measures - Fee Rates].[Enhanced Fee Income] / [Measures - Fee Rates].[Measures - Fee Rates].[Standard Fee Income]*100
)
SELECT
TM1IGNORE_BADTUPLES NON EMPTY{[Measures - Fee Rates].[Measures - Fee Rates].[Enhanced Fee Income],[Measures - Fee Rates].[Measures - Fee Rates].[Standard Fee Income],[Measures - Fee Rates].[Measures - Fee Rates].[Enhanced Fee Income / Standard Fee Income],[Measures - Fee Rates].[Measures - Fee Rates].[Var]} ON 0
, TM1IGNORE_BADTUPLES NON EMPTY {TM1FILTERBYLEVEL(TM1SUBSETALL([Academic Year].[Academic Year]) , 0)} ON 1 FROM [Fee Actuals] WHERE ([Programme].[Programme].[All Programme], [Fee Status].[Fee Status].[All Fee Status], [Mode].[Mode].[All Mode], [Programme Period].[Programme Period].[All Programme Period], [Start Year].[Start Year].[All Start Year])
----------------------

And then format values as a %. It should work for the sample you gave for gross margin %

The key is in the WITH statement I have an IIF condition which currently if the year is 2012/13 then do a subtraction, for everything else do a division and multiple by 100 to yield a percentage.

You could replace this
[Academic Year].CURRENTMEMBER.NAME = "2012/13"
so that instead of looking at a name it refers to a attribute which dictates if the value is a % or not already and so know which variance is required.

------------------------------
Simon Saul

Original Message:
Sent: Wed January 05, 2022 06:27 PM
From: Denis Barchukov
Subject: cubeview - FORMAT_STRING MDX question

Thanks, James!
Indeed, static formatting in MDX expression works well. And you are right, my issue is not in MDX itself but in an inability to find an option to change dynamically (conditionally) the data type format so I can replicate the web sheet-like in an example below.
GP Margin in this case has 2 different types: percent and customer one (bps). I tried all the options and MDX is my last resort.

------------------------------
Denis Barchukov

Original Message:
Sent: Wed January 05, 2022 06:22 AM
From: James McCarthy
Subject: cubeview - FORMAT_STRING MDX question

Hi Denis,

I have been using the static version above for some time but a dynamic version would clearly be much better. Have tried syntax similar to the above and has never worked for me either.

I assume there is a good reason you have not used rules and formatting using PA rather than MDX.

Rgds

James

------------------------------
James McCarthy

Original Message:
Sent: Tue January 04, 2022 12:26 AM
From: Denis Barchukov
Subject: cubeview - FORMAT_STRING MDX question

Hello, Planning analytics and MDX enthusiast folks!

I am very excited about cube view enhancements and customisation using MDX direct queries. This allows the development of dynamic and custom experiences for users.

The ability to create view specific calculations such as version variances percentage and absolute values is especially useful.

For instance, you can create a conditional calculation where you can define that if the current element of a row dimension is a ratio (ec. COGS%), then apply different variable calculations (bps instead of % variance).
It works splendidly on calculation. However, I have trouble with the correct formatting of the cell. I can not find how to display forms dynamically based on an account.

After googling the issue, it seems that manipulating FORMAT_STRING value is the key. I can see that people approach the issue in other products using the custom format attribute on rows. However, it seems that it doesn't work in PA. See the working and attempted dynamic example.

//Option 1 Returns the conditional calculation with static format
MEMBER [WxVersion].[WxVersion].[Reporting Versions].[calculation test] AS 
CASE 
WHEN [WxAccount].[WxAccount].CURRENTMEMBER.PROPERTIES("MEMBER_NAME") = "COGS%" THEN 1 ELSE NULL END, SOLVE_ORDER = 4, FORMAT_STRING = "#0.00000;(#0.000000)" 

//Option 2 Returns the conditional calculation with dynamic format
MEMBER [WxVersion].[WxVersion].[Reporting Versions].[calculation test2] AS 
CASE 
WHEN [WxAccount].[WxAccount].CURRENTMEMBER.PROPERTIES("MEMBER_NAME") = "COGS%" THEN 1 ELSE NULL END, SOLVE_ORDER = 4, 
FORMAT_STRING = [WxFinancialMeasure].[WxFinancialMeasure].CURRENTMEMBER.PROPERTIES("Report Format") 

Another option is to use MDX Cell Calculations, but I don't believe it is supported yet.

Could anyone advice?

------------------------------
Denis Barchukov
------------------------------
#PlanningAnalyticswithWatson

8. RE: cubeview - FORMAT_STRING MDX question

Like

Denis Barchukov

Posted Mon January 10, 2022 12:07 AM

Thanks, Simon to put the effort into it!

I confirm that MDX is powerful enough to get the right number using IIF statement or CASE WHEN .. THEN.

The idea of using STRING element is just brilliant! Unfortunately, PA interpretation seems to remove CSTR() function. I am also not sure if It is the right thing to do, thinking of future developers who decide to enhance the query in future. May be conditional formatting will soon be enhanced in a view widget, it's been unchanged since the beginning of time. Cheers everyone!

------------------------------
Denis Barchukov
------------------------------

Original Message

Original Message:
Sent: Sat January 08, 2022 08:51 AM
From: Simon Saul
Subject: cubeview - FORMAT_STRING MDX question

Just wanted to add to my last message,
I don't think you can get the format to change in a single column so it displays % or BPS at least not in PAW. Previous message will show you how to get the values you require in the right place without symbols % or BPS. perhaps you could have an additional column to display % or BPS after, it could be left aligned to look like it's in place?

The only other option I can think of would be to convert the calculated value into a string and then tack on the required format

I did try to make the Format_String parameter dynamic but in PAW the MDX is rejected with an IIF or CASE statement

------------------------------
Simon Saul

Original Message:
Sent: Fri January 07, 2022 09:00 AM
From: Simon Saul
Subject: cubeview - FORMAT_STRING MDX question

How about something like this:
------------------
WITH MEMBER [Measures - Fee Rates].[Measures - Fee Rates].[Var] AS
IIF([Academic Year].CURRENTMEMBER.NAME = "2012/13"
, [Measures - Fee Rates].[Measures - Fee Rates].[Enhanced Fee Income] - [Measures - Fee Rates].[Measures - Fee Rates].[Standard Fee Income]
, [Measures - Fee Rates].[Measures - Fee Rates].[Enhanced Fee Income] / [Measures - Fee Rates].[Measures - Fee Rates].[Standard Fee Income]*100
)
SELECT
TM1IGNORE_BADTUPLES NON EMPTY{[Measures - Fee Rates].[Measures - Fee Rates].[Enhanced Fee Income],[Measures - Fee Rates].[Measures - Fee Rates].[Standard Fee Income],[Measures - Fee Rates].[Measures - Fee Rates].[Enhanced Fee Income / Standard Fee Income],[Measures - Fee Rates].[Measures - Fee Rates].[Var]} ON 0
, TM1IGNORE_BADTUPLES NON EMPTY {TM1FILTERBYLEVEL(TM1SUBSETALL([Academic Year].[Academic Year]) , 0)} ON 1 FROM [Fee Actuals] WHERE ([Programme].[Programme].[All Programme], [Fee Status].[Fee Status].[All Fee Status], [Mode].[Mode].[All Mode], [Programme Period].[Programme Period].[All Programme Period], [Start Year].[Start Year].[All Start Year])
----------------------

And then format values as a %. It should work for the sample you gave for gross margin %

The key is in the WITH statement I have an IIF condition which currently if the year is 2012/13 then do a subtraction, for everything else do a division and multiple by 100 to yield a percentage.

You could replace this
[Academic Year].CURRENTMEMBER.NAME = "2012/13"
so that instead of looking at a name it refers to a attribute which dictates if the value is a % or not already and so know which variance is required.

------------------------------
Simon Saul

Original Message:
Sent: Wed January 05, 2022 06:27 PM
From: Denis Barchukov
Subject: cubeview - FORMAT_STRING MDX question

Thanks, James!
Indeed, static formatting in MDX expression works well. And you are right, my issue is not in MDX itself but in an inability to find an option to change dynamically (conditionally) the data type format so I can replicate the web sheet-like in an example below.
GP Margin in this case has 2 different types: percent and customer one (bps). I tried all the options and MDX is my last resort.

------------------------------
Denis Barchukov

Original Message:
Sent: Wed January 05, 2022 06:22 AM
From: James McCarthy
Subject: cubeview - FORMAT_STRING MDX question

Hi Denis,

I have been using the static version above for some time but a dynamic version would clearly be much better. Have tried syntax similar to the above and has never worked for me either.

I assume there is a good reason you have not used rules and formatting using PA rather than MDX.

Rgds

James

------------------------------
James McCarthy

Original Message:
Sent: Tue January 04, 2022 12:26 AM
From: Denis Barchukov
Subject: cubeview - FORMAT_STRING MDX question

Hello, Planning analytics and MDX enthusiast folks!

I am very excited about cube view enhancements and customisation using MDX direct queries. This allows the development of dynamic and custom experiences for users.

The ability to create view specific calculations such as version variances percentage and absolute values is especially useful.

For instance, you can create a conditional calculation where you can define that if the current element of a row dimension is a ratio (ec. COGS%), then apply different variable calculations (bps instead of % variance).
It works splendidly on calculation. However, I have trouble with the correct formatting of the cell. I can not find how to display forms dynamically based on an account.

After googling the issue, it seems that manipulating FORMAT_STRING value is the key. I can see that people approach the issue in other products using the custom format attribute on rows. However, it seems that it doesn't work in PA. See the working and attempted dynamic example.

//Option 1 Returns the conditional calculation with static format
MEMBER [WxVersion].[WxVersion].[Reporting Versions].[calculation test] AS 
CASE 
WHEN [WxAccount].[WxAccount].CURRENTMEMBER.PROPERTIES("MEMBER_NAME") = "COGS%" THEN 1 ELSE NULL END, SOLVE_ORDER = 4, FORMAT_STRING = "#0.00000;(#0.000000)" 

//Option 2 Returns the conditional calculation with dynamic format
MEMBER [WxVersion].[WxVersion].[Reporting Versions].[calculation test2] AS 
CASE 
WHEN [WxAccount].[WxAccount].CURRENTMEMBER.PROPERTIES("MEMBER_NAME") = "COGS%" THEN 1 ELSE NULL END, SOLVE_ORDER = 4, 
FORMAT_STRING = [WxFinancialMeasure].[WxFinancialMeasure].CURRENTMEMBER.PROPERTIES("Report Format") 

Another option is to use MDX Cell Calculations, but I don't believe it is supported yet.

Could anyone advice?

------------------------------
Denis Barchukov
------------------------------
#PlanningAnalyticswithWatson

9. RE: cubeview - FORMAT_STRING MDX question

Like

Simon Saul

Posted Mon January 10, 2022 07:04 PM

If the client was desperate for it or your cube already had this order, you could move the wxaccount dim as the last dimension and then use the attribute format to define the format for each account? Don't know if this is feasible or not given the work that may entail

It was fun trying to get the mdx to work, but I agree I think we're stuck otherwise until more formatting options are added

------------------------------
Simon Saul
------------------------------

Original Message

Original Message:
Sent: Mon January 10, 2022 12:07 AM
From: Denis Barchukov
Subject: cubeview - FORMAT_STRING MDX question

Thanks, Simon to put the effort into it!

I confirm that MDX is powerful enough to get the right number using IIF statement or CASE WHEN .. THEN.

The idea of using STRING element is just brilliant! Unfortunately, PA interpretation seems to remove CSTR() function. I am also not sure if It is the right thing to do, thinking of future developers who decide to enhance the query in future. May be conditional formatting will soon be enhanced in a view widget, it's been unchanged since the beginning of time. Cheers everyone!

------------------------------
Denis Barchukov

Original Message:
Sent: Sat January 08, 2022 08:51 AM
From: Simon Saul
Subject: cubeview - FORMAT_STRING MDX question

Just wanted to add to my last message,
I don't think you can get the format to change in a single column so it displays % or BPS at least not in PAW. Previous message will show you how to get the values you require in the right place without symbols % or BPS. perhaps you could have an additional column to display % or BPS after, it could be left aligned to look like it's in place?

The only other option I can think of would be to convert the calculated value into a string and then tack on the required format

I did try to make the Format_String parameter dynamic but in PAW the MDX is rejected with an IIF or CASE statement

------------------------------
Simon Saul

Original Message:
Sent: Fri January 07, 2022 09:00 AM
From: Simon Saul
Subject: cubeview - FORMAT_STRING MDX question

How about something like this:
------------------
WITH MEMBER [Measures - Fee Rates].[Measures - Fee Rates].[Var] AS
IIF([Academic Year].CURRENTMEMBER.NAME = "2012/13"
, [Measures - Fee Rates].[Measures - Fee Rates].[Enhanced Fee Income] - [Measures - Fee Rates].[Measures - Fee Rates].[Standard Fee Income]
, [Measures - Fee Rates].[Measures - Fee Rates].[Enhanced Fee Income] / [Measures - Fee Rates].[Measures - Fee Rates].[Standard Fee Income]*100
)
SELECT
TM1IGNORE_BADTUPLES NON EMPTY{[Measures - Fee Rates].[Measures - Fee Rates].[Enhanced Fee Income],[Measures - Fee Rates].[Measures - Fee Rates].[Standard Fee Income],[Measures - Fee Rates].[Measures - Fee Rates].[Enhanced Fee Income / Standard Fee Income],[Measures - Fee Rates].[Measures - Fee Rates].[Var]} ON 0
, TM1IGNORE_BADTUPLES NON EMPTY {TM1FILTERBYLEVEL(TM1SUBSETALL([Academic Year].[Academic Year]) , 0)} ON 1 FROM [Fee Actuals] WHERE ([Programme].[Programme].[All Programme], [Fee Status].[Fee Status].[All Fee Status], [Mode].[Mode].[All Mode], [Programme Period].[Programme Period].[All Programme Period], [Start Year].[Start Year].[All Start Year])
----------------------

And then format values as a %. It should work for the sample you gave for gross margin %

The key is in the WITH statement I have an IIF condition which currently if the year is 2012/13 then do a subtraction, for everything else do a division and multiple by 100 to yield a percentage.

You could replace this
[Academic Year].CURRENTMEMBER.NAME = "2012/13"
so that instead of looking at a name it refers to a attribute which dictates if the value is a % or not already and so know which variance is required.

------------------------------
Simon Saul

Original Message:
Sent: Wed January 05, 2022 06:27 PM
From: Denis Barchukov
Subject: cubeview - FORMAT_STRING MDX question

Thanks, James!
Indeed, static formatting in MDX expression works well. And you are right, my issue is not in MDX itself but in an inability to find an option to change dynamically (conditionally) the data type format so I can replicate the web sheet-like in an example below.
GP Margin in this case has 2 different types: percent and customer one (bps). I tried all the options and MDX is my last resort.

------------------------------
Denis Barchukov

Original Message:
Sent: Wed January 05, 2022 06:22 AM
From: James McCarthy
Subject: cubeview - FORMAT_STRING MDX question

Hi Denis,

I have been using the static version above for some time but a dynamic version would clearly be much better. Have tried syntax similar to the above and has never worked for me either.

I assume there is a good reason you have not used rules and formatting using PA rather than MDX.

Rgds

James

------------------------------
James McCarthy

Original Message:
Sent: Tue January 04, 2022 12:26 AM
From: Denis Barchukov
Subject: cubeview - FORMAT_STRING MDX question

Hello, Planning analytics and MDX enthusiast folks!

I am very excited about cube view enhancements and customisation using MDX direct queries. This allows the development of dynamic and custom experiences for users.

The ability to create view specific calculations such as version variances percentage and absolute values is especially useful.

For instance, you can create a conditional calculation where you can define that if the current element of a row dimension is a ratio (ec. COGS%), then apply different variable calculations (bps instead of % variance).
It works splendidly on calculation. However, I have trouble with the correct formatting of the cell. I can not find how to display forms dynamically based on an account.

After googling the issue, it seems that manipulating FORMAT_STRING value is the key. I can see that people approach the issue in other products using the custom format attribute on rows. However, it seems that it doesn't work in PA. See the working and attempted dynamic example.

//Option 1 Returns the conditional calculation with static format
MEMBER [WxVersion].[WxVersion].[Reporting Versions].[calculation test] AS 
CASE 
WHEN [WxAccount].[WxAccount].CURRENTMEMBER.PROPERTIES("MEMBER_NAME") = "COGS%" THEN 1 ELSE NULL END, SOLVE_ORDER = 4, FORMAT_STRING = "#0.00000;(#0.000000)" 

//Option 2 Returns the conditional calculation with dynamic format
MEMBER [WxVersion].[WxVersion].[Reporting Versions].[calculation test2] AS 
CASE 
WHEN [WxAccount].[WxAccount].CURRENTMEMBER.PROPERTIES("MEMBER_NAME") = "COGS%" THEN 1 ELSE NULL END, SOLVE_ORDER = 4, 
FORMAT_STRING = [WxFinancialMeasure].[WxFinancialMeasure].CURRENTMEMBER.PROPERTIES("Report Format") 

Another option is to use MDX Cell Calculations, but I don't believe it is supported yet.

Could anyone advice?

------------------------------
Denis Barchukov
------------------------------
#PlanningAnalyticswithWatson

Planning Analytics

Planning Analytics