Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 348
  • Last Modified:

Calculated Measure in Cube

Hi. Use to belong to Experts-Exchange for years and now have re-subscribed.
I am trying to create a calculated measure in a cube for an average.

What I want it to do is first determine if the count of services is 0 then 0
else divide the total turn around days by the count of services. My turn around time
is based on certain statuses.  I included a field in my status table to indicate if it is one of the statuses that the turn around time is calculated on.

WITH MEMBER [Measures].[AverageTurnAroundTime] AS
 
      [Measures].[Turn Around Days]/[Measures].[SAR Services]
      select [Measures].[AverageTurnAroundTime] on columns
      from [SAR Management]
      where [Sar Status Code].[Turn Around Time Indicator] = 1
0
LauraLynne
Asked:
LauraLynne
  • 4
  • 4
2 Solutions
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>first determine if the count of services is 0 then 0
>else divide the total turn around days by the count of services

Can't help you with any SSAS aspects of this question, but raw T-SQL would be <air code>..
SELECT CASE 
    WHEN COALESCE(COUNT(services),0) > 0 THEN 0 
    ELSE SUM(Turn around days) / CAST(COUNT(Services) as numeric(19,4))  END as column_name
FROM [SAR Management]
WHERE [Sar Status Code].[Turn Around Time Indicator] = 1 

Open in new window

btw the CAST..as Numeric is so that the result has decimal values.
0
 
LauraLynneAuthor Commented:
Appreciate the help, jimhorn! Still need the solution for the cube average though. Changed the logic above to read  

case when COALESCE(COUNT(services),0) = 0 THEN 0 rather than > 0.

Now I just have to figure out how that code gets modified to MDX. Thanks
0
 
LauraLynneAuthor Commented:
I will be awarding points for your help, btw, because it is helping me in my solution. Still seeking assistance for the cube calculation. Laura
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Yep.  I'll back away gracefully now to encourage SQL - SSAS experts to respond.
0
 
LauraLynneAuthor Commented:
We were able to find the answer in house. I am posting the solution. I am awarding jimhorn points for helping with the divide by zero logic. Thanks.

 
WITH MEMBER [Measures].[AverageTurnAroundDays] AS
IIF([Measures].[Turn Around Days]= 0,0,[Measures].[Turn Around Days]/[Measures].[SAR Services])
select {[Measures].[Turn Around Days],[Measures].[SAR Services],[Measures].[AverageTurnAroundDays] } ON COLUMNS,
         [Initial Submit Date].[Month].&[2013-10-01T00:00:00]ON ROWS
from [SAR Management]
where [Sar Status Code].[Turn Around Time Indicator].&[1]
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Thanks for the accept, and thanks for posting your ultimate solution.
Good luck with your project.

Jim
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
btw I've noticed that there's not a lot of us SSIS experts on EE, and probably fewer SSAS experts, so you can probably score a lot of points by answering questions here.
0
 
LauraLynneAuthor Commented:
We figured it out inhouse.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now