Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
delpiero
Yak Posting Veteran
98 Posts |
 Posted - 2005-04-13 : 12:48:08
|
| Hi all, I have a cube with one measure and several dimensions. One of the dimensions has data from one column of a table. This column only has 3 distinct values in the table, namely, 'Paid','Pending','Cancelled'. What I want to do is that, I want to group 'Pending' and 'Cancelled' values as one group, namely 'Not Paid', while the value 'Paid' is in another group, namely 'Paid'. How can we make this grouping? When I process the cube and browse its data, I wish to view the cube's measure values in 2 columns, one is 'Paid' and the other is 'Not Paid', with the latter actually coming from 2 distinct values in the dimension. For example:Order type Paid Not PaidBread 17 35Wine 21 77Ice-cream 6 21Tissue 27 36Detergent 22 21 Do we need to use MDX or just modify some member properties?Thanks for your help,delpiero |
|
|
jojo_jacob
Starting Member
24 Posts |
Posted - 2005-04-14 : 01:04:40
|
| The simplest solution would be to use a CASE statement instead of the column name in the member name column of the dimension level.CASE WHEN (Table_Name.Column_Name = 'Pending' ) THEN 'Not Paid' WHEN (Table_Name.Column_Name = 'Cancelled') THEN 'Not Paid'WHEN (Table_Name.Column_Name = 'Paid') THEN 'Paid'ENDGo to the dimension editor, click on the level and edit the Member Name Column property. You may also need to type the same in the Member Key Column as well.Hope this helps .. lemme know.Jojo |
 |
|
|
muntu
Starting Member
11 Posts |
Posted - 2005-04-14 : 08:22:24
|
| what front end are you using to access the data? |
|
|
|
jojo_jacob
Starting Member
24 Posts |
Posted - 2005-04-15 : 00:04:26
|
| This CASE statement is in Analysis Manager ... instead of the column name, do it with a CASE statement ... Analaysis Services just get the data from the database using this statement instead of the column name. |
|
|
|
delpiero
Yak Posting Veteran
98 Posts |
Posted - 2005-04-15 : 00:48:34
|
| Hi Jojo, The CASE statement works fine. I put it in the member property column and now I get what I need. Many thanks!delpiero |
|
|
|
|
|
|
|
|
A question about cube dimension