Looking for a format function in MDX??? | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Looking for a format function in MDX???

Hi,<br /><br />Is there a format function in MDX which will help me solve the following <br />scenario:<br /><br />One of my measures is Net Sales and I have tried all different formats <br />in Analysis Services so that it shows without the decimal places i.e. <br />instead of showing the Net Sales for any selected combination of <br />Dimensions as 123,456.04, I want to show it as 123,456<br /><br />When I browse the cube (in Analysis Services), I can meet my <br />requirement. However, in my front-end (Microsoft Data Analyzer), I am <br />still getting it as 123,456.04 (even when the same in Analysis services, <br />i.e. when I browse the cube, is being shown as 123,456).<br /><br />I do not have much choice at the moment and am stuck with Microsoft Data <br />Analyzer and unfortunately have not been able to solve this <img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ /><br /><br />Can someone think of a solution/workaround/use of MDX which will help me <br />get the results being displayed without the decimal.<br /><br />Many TIA<br />
If you are using MS Analysis Services to build the cubes you can change the measure’s properties Analysis Manager: Edit the cube
Select the measure
Show the properties (bottom pane on left)
on the advanced tab change the Display Properties to the format you require e.g. #,#
Save the cube. Regards, Robert.
RGKN – I think that Joozh has already done just that. We get the same problems with our cubes, however we use an ASP.Net front end and format the numbers on the client. I’m not to familiar with MS Data Analyzer but there should be some formatting options you could set on the client.
Hi Raulie and RGKN,<br /><br />Thanks for the replies.<br /><br />Yes Raulie is right that I have already tried the solution which Robert suggested. However I have recently found out that this is simply NOT AT ALL possible in Data Analyzer (regardless of how the format is defined in Analysis Services) <img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ /><br /><br />I however am very curious to find more about the ASP.NET solution which you (Raulie) havementioned. Please excuse my silly question but is this a ‘front-end OLAP tool’ or just like what used to be IIS (but with the new ".NET" flavor – whatever that is???)<br /><br />Will appreciate your reply.<br /><br />Many thanks once again and Regards.
The Solution we use is an in house ASP.Net application that uses OWC (office web components). The solution is used World Wide in our enterprise and currently meets our needs. Read up on office web components in MS http://support.microsoft.com/kb/q301456/
Raulie thanks for the reply but unfortunately I donot have much experience related to web development, ASP.NET,and how to go about developing a solution that you have mentioned <img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ /><br /><br />Ichecked theURL that you havegiven butthat talks more about solving some formattingproblem rather than helping me get started…<br /><br />Maybe thisisasking too much but is it possible to guide me to some URL from where I can get started and end up having a web based solution using ASP.NET and OWC as you have suggested.<br /><br />Thanks & Regards.
Yes I understand and sorry that the link did not help much, but I would recommend just browsing the MS/MSDN website and search for examples. Are you absolutely sure that there are not any formatting options in Data Analyzer it’s just hard to think that MS would include these capabilities. Can you access the code VBA in Data Analyzer? Check out this link.http://msdn.microsoft.com/library/d…/odc_2003_ta/html/odc_landDataAnalyzer_ta.asp
Hi Raulie, Well there are 2 answers to your questions. I just found out that there is a SINGLE indirect solution to getting rid of the decimal places. This can be done via the regional setting of the PC and can bedone by defining the number of decimal digits as zero. Unfortunately Data Analyzer itself does not offer ANY such option. On a side but related note (you might find this useful/interesting): I really like the interface of Data Analyzer and have not come across ANY product which allows slicing/dicing/drilling interface in the same manner which Data Analyzer does. I just wish that it had more options….with thisI think it would had been an excellent frot-end tool. Regards.