5

How can i round a calculated mdx measure up to the nearest integer without having Excel on the server? The Excel-function is CEILING(number, significance), but it is not possible to install Excel on the production ssas-server.

Espo
  • 41,399
  • 21
  • 132
  • 159

2 Answers2

7

If this is a Microsoft situation, you can use any VBA functions in your MDX to fiddle with strings or numbers. So Round(xxxxxx, 2) would work.

Magnus Smith
  • 5,895
  • 7
  • 43
  • 64
  • How do I use VBA functions in my MDX without Excel on the MSAS server? – Espo Jun 11 '10 at 22:01
  • You don't need Excel. If you are using Analysis Services, you just enter the VBA keywords into the MDX. No setup is required. – Magnus Smith Jun 14 '10 at 21:03
  • 1
    You may need to use VBA!round() to signify you are not using the built-in MDX Round() function which accepts only one argument. – Magnus Smith Aug 28 '13 at 12:44
1

For the Floor function try Int function like:

Int([Measures].[Store Sales])

For the Ceiling function try Int + 1 

IIF(([Measures].[Store Sales]) - Int([Measures].[Store Sales]) = 0, [Measures].[Store Sales], Int([Measures].[Store Sales]) + 1)
Igor Krupitsky
  • 787
  • 6
  • 9