I am chasing my tail for quite few days now trying to answer a simple and resonable business question using MS SQL Server 2005 Analysis Services:
I have two independent dimensions: Category and Status
I need to produce an output that will display total count of records for each Category and Status, count for each Category for all Statuses, and ratio of the current Status count to all Statuses count within the same category.
I.e.” CategoryA” has two records one with status “Active” and one with status “Inactive” the output I need to produce
Category, Status, CountForCategory, CoutForStatus, Status Ratio
CategoryA, Active, 2, 1 , 50%
CategoryA, Inactive, 2, 1 , 50%
Any Ideas, suggestions (please)?
Hi Vlad,
here is the query in terms of AdventureWorks. I think it is very similar your one.
Code Snippet
with
member [Measures].[Internet Order Quantity AllCategories] as
([Measures].[Internet Order Quantity], [Product].[Category].[All Products])
member [Measures].[Quantity Ratio] as [Measures].[Internet Order Quantity]/[Measures].[Internet Order Quantity AllCategories], FORMAT_STRING="Percent"
select {[Measures].[Internet Order Quantity AllCategories], [Measures].[Internet Order Quantity], [Measures].[Quantity Ratio]} on 0,
[Customer].[Education].[Education].members *
[Product].[Category].[Category].members
on 1
from [Adventure Works]
here is the result
Internet Order Quantity AllCategories
Internet Order Quantity
Quantity Ratio
Bachelors
Accessories
18.144
10.302
56,78%
Bachelors
Bikes
18.144
5.112
28,17%
Bachelors
Clothing
18.144
2.730
15,05%
Bachelors
Components
18.144
(null)
(null)
Graduate Degree
Accessories
10.603
6.227
58,73%
Graduate Degree
Bikes
10.603
2.722
25,67%
Graduate Degree
Clothing
10.603
1.654
15,60%
Graduate Degree
Components
10.603
(null)
(null)
High School
Accessories
10.320
6.409
62,10%
High School
Bikes
10.320
2.345
22,72%
High School
Clothing
10.320
1.566
15,17%
High School
Components
10.320
(null)
(null)
No comments:
Post a Comment