Help on User Defined Formats for the Measures in OLAP cube

[RSB]

I am trying to apply a user defined format on a measure in my cube.After creating the format, I have added this to my proc olap code.

libname pr 'D:\Program Files\SAS\Config\Lev1\SASAPP\SASEnvironment\SASFormats';
options fmtsearch=(pr);


However this does not seem to have any affect on the measures in my cube , after some investigation I have came across this SAS Document.

http://support.sas.com/kb/11/390.html

Assuming that my format is similiar to the one that is shown in the document

proc format ;
value NACOMMA . = 'NA'
other=[comma15.]
;
run;

I Have created a calculated member as per the document, but I am getting an error message


"Formula error - The format specified in the PUT function is invalid or not found - in the "PUT" function"


Since I have placed the format in the specified directory this should be available to all the applications, Please let me know what could be the issue.

[AngelaHall]

You mentioned that the libname pr statement is only being added to the proc olap code? This only makes the format catalog available during OLAP cube creation. You still need this format available to users (and the OLAP Server) when creating measures, accessing/viewing/quering the cube. Similar to my post on custom formats for the Information Maps (http://sas-bi.blogspot.com/2010/01/using-custom-formats-in-information-map.html) you will need to assign this catalog to the server's SASv9.cfg.

~ Angela

[RSB]

I have copied the Formats to D:\Program Files\SAS\Config\Lev1\SASAPP\SASEnvironment\SASFormats

and added these lines to the D:\Program Files\SAS\Config\Lev1\SASAPP\sasv9.usermods.cfg and
D:\Program Files\SAS\Config\Lev1\SASAPP\OLAPServer\sasv9.usermods.cfg


-set APFMTLIB "D:\Program Files\SAS\Config\Lev1\SASAPP\SASEnvironment\SASFormats"
-insert fmtsearch APFMTLIB


after adding these I have restarted the OLAP and object spawner services.


now, I can use these format in my programs without using the fmtsearch option.

However the measures in the cube are still not able to recognize the format.

[AngelaHall]

The calculated member GUI will not display the custom formats in the dropdown list.

Have you been attempted to edit an existing calculated member and type in the user defined format manually?

Create the calculated member with a standard format (such as BEST.).
Click ok. Then click 'Edit' on the calculated member to see the MDX string. Manually change the BEST. format portion of the string to your user defined format.

Example:
[MEASURES].[WeightAVG]/[MEASURES].[TotalWeightAVG], FORMAT_STRING="BEST15."

[MEASURES].[WeightAVG]/[MEASURES].[TotalWeightAVG], FORMAT_STRING="userdef15."

~ Angela

[RSB]

I have created a custom measure as

[Measures].[ORIGINAL_VALUESUM]-[Measures].[HAIRCUT_AMTSUM], FORMAT_STRING="nacomma."

there is no error but the format is being ignored.

[AngelaHall]

We've gotten past the initial error, but at this point I think you should give SAS Technical Support a ring. http://support.sas.com/ctx/supportform/createForm

You will need to provide the information in this discussion as well as information on where the format is being ignored (EG? IMap? etc).

~ Angela

[RSB]

The Tech. support has suggested to create the calculated measure as

DEFINE Member '[COLL_ADDITIVE].[Measures].[ddf]'
AS 'IIF([Measures].[ORIGINAL_VALUE]+[Measures].[ORIGINAL_VALUE],
PUT([Measures].[ORIGINAL_VALUESUM]+[Measures].[ORIGINAL_VALUESUM], "COMMA15."), "Not Available")';

And this has done the trick. Although this is similiar to the one defined in the document , the difference is not using the user defined format in the PUT Function. Message was edited by: RSB

[tommy81]

Can you explain the above code a bit. Where are you now using the custom format in the code now.

iif([Measures].[Value] or [Measures].[Value] = 0,
    PUT([Measures].[Value],"myfmt."),  "NO VALUE")

[twocanbazza]

You may have to restart your olap server as well, to enable the new format to be seen...