?
Solved

SSAS conditional caculation

Posted on 2009-03-31
3
Medium Priority
?
306 Views
Last Modified: 2016-02-12
I am new to SSAS in SQL Server 2005.  I want to create a calculated value, that has the value of an amount measure or zero depending the value of a dimension.  To outline a simple example assume I have a dimension called AccountCode with values of "4000", "4001", "5000", "5001", "5002", "6000", "6001", "6002", "6003", etc.  and I have a measure called "Amount".

I want to create 3 calculated values as outlined below:

Sales = Amount where AccountCode >= "4000" and AccountCode <= "4999" else 0
CostOfSales = Amount where AccountCode >= "5000" and AccountCode <= "5999" else 0
GeneralExpenses = Amount where AccountCode >= "6000" and AccountCode <= "6999" else 0

So in the OLAP Cube, I would have the measures Amount plus the three named above.
0
Comment
Question by:bandreini
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 8

Accepted Solution

by:
tpi007 earned 2000 total points
ID: 24037500
Ideally, I would recommend you investigate parent child heirarchies in SSAS 2005.
http://msdn.microsoft.com/en-us/library/ms174846.aspx  
http://blogs.technet.com/paolt/archive/2005/02/17/375497.aspx
Bascially you can have a parentid column in accountcode source table and use parentid column to represent structure, This is usually done  in source data. Calculated memebers can be quite slow as calculations are done on fly as opposed to a meaure.    
Alternatively you can add sql below into datasource view of data for fact table and reference the columns as measure in their own right. Heiracrchies are preferreed option for your scenario but can take a little time to setup and a little complex to implement. They also require the parentid column to be maintained as chnages to your account structure change.  
 

CREATE TABLE [dbo].[#tblCosts](
	[AccountCode] [int] NOT NULL,
	[Amount] [decimal](18, 2) NOT NULL
) 
 
INSERT INTO #tblCosts (AccountCode,Amount) VALUES (4000,3.00)
INSERT INTO #tblCosts (AccountCode,Amount) VALUES (5000,4.00)
INSERT INTO #tblCosts (AccountCode,Amount) VALUES (5000,14.00)
INSERT INTO #tblCosts (AccountCode,Amount) VALUES (5800,3.00)
INSERT INTO #tblCosts (AccountCode,Amount) VALUES (4500,14.00)
INSERT INTO #tblCosts (AccountCode,Amount) VALUES (6700,24.00)
INSERT INTO #tblCosts (AccountCode,Amount) VALUES (6600,24.00)
 
select AccountCode,
(CASE WHEN AccountCode BETWEEN 4000 AND 4999 THEN Amount
      ELSE 0 END) AS Sales,
(CASE WHEN AccountCode BETWEEN 5000 AND 5999 THEN Amount
      ELSE 0 END) AS CostOfSales,
(CASE WHEN  AccountCode BETWEEN 6000 AND 6999 THEN Amount
      ELSE 0 END) AS GeneralExpenses
FROM #tblcosts

Open in new window

0
 

Author Closing Comment

by:bandreini
ID: 31565185
I do understand the reason why to this approach.  I have successfully implemented and tested it!  Thanks!  
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question