I am very new to Analysis Services and have a problem that I don't have any idea on the direction to go or where to look for the answer.
I have included a very simple example of the tables to hopefully explain what I am trying to do. I have a WIP billing table that is used as my Fact Table. I am using the Adjustment column for my measure. I have three dimensions "Region by Employee", "Region by Client", "Service Code". The different region dimensions are because the work may have been done by an employee in a different office or region than what the client is assigned to.
Everything is fine when filtered by the "Region by Client" dimension.
My Problem is because for "Expense" type of Service codes management wants the adjustment to show up against the client assigned region. Even when looking at the data filtered by the "Region by Employee" dimension.
If you have any ideas or suggestions I would be very grateful.
Thanks
Tom
--------TABLES WITH DATA-----------
*WIP Table*
EmpID CltID Adj SCCode
100 300 -$20.00 Bill200
100 310 -$20.00 Bill220
120 330 -$40.00 Bill235
135 355 -$100.00 Exp100
*Employee Table*
EmpID EmpOffID
100 10
120 12
135 14
*Client Table*
CltID CltOff
300 10
310 12
330 14
355 18
*Office Table*
OffID RegID
10 20
12 22
14 24
18 28
*Region Table*
RegID
20
22
24
28
NOTE: Client table actually points to a view of the Office & Region tables due to the loop error you receive if you don't
--------CURRENT RESULTS------------
*Filtered By Emp Region* (incorrect)
Region 20 -$40.00
Region 22 -$40.00
Region 24 -$100.00
Region 28
*Filtered By Client Region* (correct)
Region 20 -$20.00
Region 22 -$20.00
Region 24 -$40.00
Region 28 -$100.00
--------DESIRED RESULTS------------
*Filtered By Emp Region* (correct)
Region 20 -$40.00
Region 22 -$40.00
Region 24
Region 28 -$100.00
*Filtered By Client Region* (correct)
Region 20 -$20.00
Region 22 -$20.00
Region 24 -$40.00
Region 28 -$100.00
Problem with non-standard dimension
ChoclatB0y
Not sure whether you're using AS 2000 or AS 2005 (presumably it's AS 2000, since the Regions seem to be set up as separate dimensions, rather than role-playing). But maybe a calculated cell solution will work (or MDX Scripts in AS 2005):
- Assume that [Service Code] dimension has [Billing] and [Expense] categories to group the individual SCCodes by.
- Create a cell calculation with scope like ([Emp Region Level], [All Client Region], [All Service Code], [Adjustment])
- [Adjustment] is computed as the sum of 2 [Service Code] contributions:
-- ([Billing])
-- ([Expense], [All Emp Region],
LinkMember([Emp Region].CurrentMember, [Client Region]))
So, when reporting by [Emp Region], the [Billing] component will be added to the [Expense] component from the corresponding [Client Region] via LinkMember().
Daniel Stolt
I am using AS 2000.
I appreciate your response. Being a huge beginner in MDX I don't currently understand what you are trying to suggest. I will try to look up LinkMember() and see if it provides me any insight into the possible solution you suggested.
Thanks
Tom
yibeltalisme
This article may help explain calculated cells - but they're only supported in AS 2000 Enterprise Edition. Otherwise a calculated measure could be used:
http://www.databasejournal.com/features/mssql/article.php/3106671
>>
Introduction to MSSQL Server 2000 Analysis Services: Using Calculated Cells in Analysis Services, Part I
By William Pearson
...
In this article, we will explore calculated cells, which enable us to apply functionality previously reserved for calculated members, custom members, and custom rollup formulas (all of which we have explored in previous articles) to a specific range of cells--or even to a single cell. We will describe the construction of a calculated cell, touching upon the basic properties that make it up.
...
>>