How to treat two different subcategories as one absolutely?

As the subject suggests , I encountered that problem when I want to treat two different subcategories as one absolutely!

Every product belongs to one subcategory , and every subcategory belongs to one category.Now in the category X there are two subcategorys : A and B,and I want to treat the merging of A and B as the new subcategory 'AB' logically absolutely , which means that , for example , the products belonging to A and B now belong to 'AB' , which meas the function 'descendants' works fine as before , but the total sales of all subcategories are right(so does "currentmember") when I choose subcategory.AllMembers on some axis.

Any reply are appreciated.

3X.

If you want to do it dynamically on the client, then it isn't easy. I beleive the closest option is to use session cubes with grouping level. Excel is one example of client tool which uses this technique for custom grouping. Below is the sample statement captured through Profiler with Excel 2007 going against AS2005 on Adventure Works cube, where I grouped together two product subcategories - Bike Racks and Bike Stands:

CREATE SESSION CUBE [Adventure Works_XL_GROUPING0] FROM [Adventure Works] ( MEASURE [Adventure Works].[Internet Sales Amount],MEASURE [Adventure Works].[Internet Order Quantity],MEASURE [Adventure Works].[Internet Extended Amount],MEASURE [Adventure Works].[Internet Tax Amount],MEASURE [Adventure Works].[Internet Freight Cost],MEASURE [Adventure Works].[Internet Total Product Cost],MEASURE [Adventure Works].[Internet Standard Product Cost],MEASURE [Adventure Works].[Internet Order Count],MEASURE [Adventure Works].[Customer Count],MEASURE [Adventure Works].[Reseller Sales Amount],MEASURE [Adventure Works].[Reseller Order Quantity],MEASURE [Adventure Works].[Reseller Extended Amount],MEASURE [Adventure Works].[Reseller Tax Amount],MEASURE [Adventure Works].[Reseller Freight Cost],MEASURE [Adventure Works].[Discount Amount],MEASURE [Adventure Works].[Reseller Total Product Cost],MEASURE [Adventure Works].[Reseller Standard Product Cost],MEASURE [Adventure Works].[Reseller Order Count],MEASURE [Adventure Works].[Order Quantity],MEASURE [Adventure Works].[Extended Amount],MEASURE [Adventure Works].[Standard Product Cost],MEASURE [Adventure Works].[Total Product Cost],MEASURE [Adventure Works].[Sales Amount],MEASURE [Adventure Works].[Tax Amount],MEASURE [Adventure Works].[Freight Cost],MEASURE [Adventure Works].[Order Count],MEASURE [Adventure Works].[Sales Amount Quota],MEASURE [Adventure Works].[Amount],MEASURE [Adventure Works].[Average Rate],MEASURE [Adventure Works].[End of Day Rate],MEASURE [Adventure Works].[Internet Sales Amount],MEASURE [Adventure Works].[Internet Unit Price] HIDDEN,MEASURE [Adventure Works].[Internet Transaction Count] HIDDEN,MEASURE [Adventure Works].[Sales Reason Count] HIDDEN,MEASURE [Adventure Works].[Reseller Unit Price] HIDDEN,MEASURE [Adventure Works].[Unit Price Discount Percent] HIDDEN,MEASURE [Adventure Works].[Reseller Transaction Count] HIDDEN,MEASURE [Adventure Works].[Unit Price] HIDDEN,MEASURE [Adventure Works].[Transaction Count] HIDDEN,DIMENSION [Adventure Works].[Account].[Account],DIMENSION [Adventure Works].[Account].[Account Number],DIMENSION [Adventure Works].[Account].[Account Type],DIMENSION [Adventure Works].[Account].[Accounts],DIMENSION [Adventure Works].[Customer].[Address],DIMENSION [Adventure Works].[Customer].[City],DIMENSION [Adventure Works].[Customer].[Commute Distance],DIMENSION [Adventure Works].[Customer].[Country],DIMENSION [Adventure Works].[Customer].[Customer],DIMENSION [Adventure Works].[Customer].[Customer Geography],DIMENSION [Adventure Works].[Customer].[Date of First Purchase],DIMENSION [Adventure Works].[Customer].[Education],DIMENSION [Adventure Works].[Customer].[Email Address],DIMENSION [Adventure Works].[Customer].[Gender],DIMENSION [Adventure Works].[Customer].[Home Owner],DIMENSION [Adventure Works].[Customer].[Marital Status],DIMENSION [Adventure Works].[Customer].[Number of Cars Owned],DIMENSION [Adventure Works].[Customer].[Number of Children At Home],DIMENSION [Adventure Works].[Customer].[Occupation],DIMENSION [Adventure Works].[Customer].[Phone],DIMENSION [Adventure Works].[Customer].[Postal Code],DIMENSION [Adventure Works].[Customer].[State-Province],DIMENSION [Adventure Works].[Customer].[Total Children],DIMENSION [Adventure Works].[Customer].[Yearly Income],DIMENSION [Adventure Works].[Date].[Calendar],DIMENSION [Adventure Works].[Date].[Calendar by Weeks],DIMENSION [Adventure Works].[Date].[Calendar Quarter of Year],DIMENSION [Adventure Works].[Date].[Calendar Semester of Year],DIMENSION [Adventure Works].[Date].[Calendar Week],DIMENSION [Adventure Works].[Date].[Calendar Year],DIMENSION [Adventure Works].[Date].[Date],DIMENSION [Adventure Works].[Date].[Day Name],DIMENSION [Adventure Works].[Date].[Day of Month],DIMENSION [Adventure Works].[Date].[Day of Week],DIMENSION [Adventure Works].[Date].[Day of Year],DIMENSION [Adventure Works].[Date].[Fiscal],DIMENSION [Adventure Works].[Date].[Fiscal Quarter of Year],DIMENSION [Adventure Works].[Date].[Fiscal Semester of Year],DIMENSION [Adventure Works].[Date].[Fiscal Year],DIMENSION [Adventure Works].[Date].[Month of Year],DIMENSION [Adventure Works].[Date].[Week of Year],DIMENSION [Adventure Works].[Date].[Year By Months],DIMENSION [Adventure Works].[Date].[Year By Quarters],DIMENSION [Adventure Works].[Date].[Year By Weeks],DIMENSION [Adventure Works].[Delivery Date].[Calendar],DIMENSION [Adventure Works].[Delivery Date].[Calendar by Weeks],DIMENSION [Adventure Works].[Delivery Date].[Calendar Quarter of Year],DIMENSION [Adventure Works].[Delivery Date].[Calendar Semester of Year],DIMENSION [Adventure Works].[Delivery Date].[Calendar Week],DIMENSION [Adventure Works].[Delivery Date].[Calendar Year],DIMENSION [Adventure Works].[Delivery Date].[Date],DIMENSION [Adventure Works].[Delivery Date].[Day Name],DIMENSION [Adventure Works].[Delivery Date].[Day of Month],DIMENSION [Adventure Works].[Delivery Date].[Day of Week],DIMENSION [Adventure Works].[Delivery Date].[Day of Year],DIMENSION [Adventure Works].[Delivery Date].[Fiscal],DIMENSION [Adventure Works].[Delivery Date].[Fiscal Quarter of Year],DIMENSION [Adventure Works].[Delivery Date].[Fiscal Semester of Year],DIMENSION [Adventure Works].[Delivery Date].[Fiscal Year],DIMENSION [Adventure Works].[Delivery Date].[Month of Year],DIMENSION [Adventure Works].[Delivery Date].[Week of Year],DIMENSION [Adventure Works].[Delivery Date].[Year By Months],DIMENSION [Adventure Works].[Delivery Date].[Year By Quarters],DIMENSION [Adventure Works].[Delivery Date].[Year By Weeks],DIMENSION [Adventure Works].[Department].[Departments],DIMENSION [Adventure Works].[Destination Currency].[Destination Currency],DIMENSION [Adventure Works].[Destination Currency].[Destination Currency Code],DIMENSION [Adventure Works].[Employee].[Base Rate],DIMENSION [Adventure Works].[Employee].[Birth Date],DIMENSION [Adventure Works].[Employee].[Department Name],DIMENSION [Adventure Works].[Employee].[Email Address],DIMENSION [Adventure Works].[Employee].[Emergency Contact Name],DIMENSION [Adventure Works].[Employee].[Emergency Contact Phone],DIMENSION [Adventure Works].[Employee].[Employee],DIMENSION [Adventure Works].[Employee].[Employee Department],DIMENSION [Adventure Works].[Employee].[Employees],DIMENSION [Adventure Works].[Employee].[End Date],DIMENSION [Adventure Works].[Employee].[Gender],DIMENSION [Adventure Works].[Employee].[Hire Date],DIMENSION [Adventure Works].[Employee].[Hire Year],DIMENSION [Adventure Works].[Employee].[Marital Status],DIMENSION [Adventure Works].[Employee].[Pay Frequency],DIMENSION [Adventure Works].[Employee].[Phone],DIMENSION [Adventure Works].[Employee].[Salaried Flag],DIMENSION [Adventure Works].[Employee].[Sales Person Flag],DIMENSION [Adventure Works].[Employee].[Sick Leave Hours],DIMENSION [Adventure Works].[Employee].[Start Date],DIMENSION [Adventure Works].[Employee].[Status],DIMENSION [Adventure Works].[Employee].[Title],DIMENSION [Adventure Works].[Employee].[Vacation Hours],DIMENSION [Adventure Works].[Geography].[City],DIMENSION [Adventure Works].[Geography].[Country],DIMENSION [Adventure Works].[Geography].[Geography],DIMENSION [Adventure Works].[Geography].[Postal Code],DIMENSION [Adventure Works].[Geography].[State-Province],DIMENSION [Adventure Works].[Internet Sales Order Details].[Carrier Tracking Number],DIMENSION [Adventure Works].[Internet Sales Order Details].[Customer PO Number],DIMENSION [Adventure Works].[Internet Sales Order Details].[Internet Sales Orders],DIMENSION [Adventure Works].[Internet Sales Order Details].[Sales Order Line],DIMENSION [Adventure Works].[Internet Sales Order Details].[Sales Order Number],DIMENSION [Adventure Works].[Organization].[Currency Code],DIMENSION [Adventure Works].[Organization].[Organizations],DIMENSION [Adventure Works].[Product].[Category],DIMENSION [Adventure Works].[Product].[Class],DIMENSION [Adventure Works].[Product].[Color],DIMENSION [Adventure Works].[Product].[Days to Manufacture],DIMENSION [Adventure Works].[Product].[Dealer Price],DIMENSION [Adventure Works].[Product].[End Date],DIMENSION [Adventure Works].[Product].[Large Photo],DIMENSION [Adventure Works].[Product].[List Price],DIMENSION [Adventure Works].[Product].[Manufacture Time],DIMENSION [Adventure Works].[Product].[Model Name],DIMENSION [Adventure Works].[Product].[Product],DIMENSION [Adventure Works].[Product].[Product Categories] HIDDEN AS _XL_GROUPING113,DIMENSION [Adventure Works].[Product].[Product Key],DIMENSION [Adventure Works].[Product].[Product Line],DIMENSION [Adventure Works].[Product].[Product Model Categories],DIMENSION [Adventure Works].[Product].[Product Model Lines],DIMENSION [Adventure Works].[Product].[Reorder Point],DIMENSION [Adventure Works].[Product].[Safety Stock Level],DIMENSION [Adventure Works].[Product].[Size],DIMENSION [Adventure Works].[Product].[Size Range],DIMENSION [Adventure Works].[Product].[Standard Cost],DIMENSION [Adventure Works].[Product].[Start Date],DIMENSION [Adventure Works].[Product].[Status],DIMENSION [Adventure Works].[Product].[Stock Level],DIMENSION [Adventure Works].[Product].[Style],DIMENSION [Adventure Works].[Product].[Subcategory],DIMENSION [Adventure Works].[Product].[Weight],DIMENSION [Adventure Works].[Promotion].[Discount Percent],DIMENSION [Adventure Works].[Promotion].[End Date],DIMENSION [Adventure Works].[Promotion].[Max Quantity],DIMENSION [Adventure Works].[Promotion].[Min Quantity],DIMENSION [Adventure Works].[Promotion].[Promotion],DIMENSION [Adventure Works].[Promotion].[Promotion Category],DIMENSION [Adventure Works].[Promotion].[Promotion Type],DIMENSION [Adventure Works].[Promotion].[Promotions],DIMENSION [Adventure Works].[Promotion].[Start Date],DIMENSION [Adventure Works].[Reseller].[Address],DIMENSION [Adventure Works].[Reseller].[Annual Revenue],DIMENSION [Adventure Works].[Reseller].[Annual Sales],DIMENSION [Adventure Works].[Reseller].[Bank Name],DIMENSION [Adventure Works].[Reseller].[Business Type],DIMENSION [Adventure Works].[Reseller].[First Order Year],DIMENSION [Adventure Works].[Reseller].[Last Order Year],DIMENSION [Adventure Works].[Reseller].[Min Payment Amount],DIMENSION [Adventure Works].[Reseller].[Min Payment Type],DIMENSION [Adventure Works].[Reseller].[Number of Employees],DIMENSION [Adventure Works].[Reseller].[Order Frequency],DIMENSION [Adventure Works].[Reseller].[Order Month],DIMENSION [Adventure Works].[Reseller].[Phone],DIMENSION [Adventure Works].[Reseller].[Product Line],DIMENSION [Adventure Works].[Reseller].[Reseller],DIMENSION [Adventure Works].[Reseller].[Reseller Bank],DIMENSION [Adventure Works].[Reseller].[Reseller Order Frequency],DIMENSION [Adventure Works].[Reseller].[Reseller Order Month],DIMENSION [Adventure Works].[Reseller].[Reseller Type],DIMENSION [Adventure Works].[Reseller].[Year Opened],DIMENSION [Adventure Works].[Reseller Sales Order Details].[Carrier Tracking Number],DIMENSION [Adventure Works].[Reseller Sales Order Details].[Customer PO Number],DIMENSION [Adventure Works].[Reseller Sales Order Details].[Reseller Sales Orders],DIMENSION [Adventure Works].[Reseller Sales Order Details].[Sales Order Line],DIMENSION [Adventure Works].[Reseller Sales Order Details].[Sales Order Number],DIMENSION [Adventure Works].[Sales Channel].[Sales Channel],DIMENSION [Adventure Works].[Sales Reason].[Sales Reason],DIMENSION [Adventure Works].[Sales Reason].[Sales Reason Type],DIMENSION [Adventure Works].[Sales Reason].[Sales Reasons],DIMENSION [Adventure Works].[Sales Summary Order Details].[Carrier Tracking Number],DIMENSION [Adventure Works].[Sales Summary Order Details].[Customer PO Number],DIMENSION [Adventure Works].[Sales Summary Order Details].[Sales Order Line],DIMENSION [Adventure Works].[Sales Summary Order Details].[Sales Order Number],DIMENSION [Adventure Works].[Sales Summary Order Details].[Sales Orders],DIMENSION [Adventure Works].[Sales Territory].[Sales Territory],DIMENSION [Adventure Works].[Sales Territory].[Sales Territory Country],DIMENSION [Adventure Works].[Sales Territory].[Sales Territory Group],DIMENSION [Adventure Works].[Sales Territory].[Sales Territory Region],DIMENSION [Adventure Works].[Scenario].[Scenario],DIMENSION [Adventure Works].[Ship Date].[Calendar],DIMENSION [Adventure Works].[Ship Date].[Calendar by Weeks],DIMENSION [Adventure Works].[Ship Date].[Calendar Quarter of Year],DIMENSION [Adventure Works].[Ship Date].[Calendar Semester of Year],DIMENSION [Adventure Works].[Ship Date].[Calendar Week],DIMENSION [Adventure Works].[Ship Date].[Calendar Year],DIMENSION [Adventure Works].[Ship Date].[Date],DIMENSION [Adventure Works].[Ship Date].[Day Name],DIMENSION [Adventure Works].[Ship Date].[Day of Month],DIMENSION [Adventure Works].[Ship Date].[Day of Week],DIMENSION [Adventure Works].[Ship Date].[Day of Year],DIMENSION [Adventure Works].[Ship Date].[Fiscal],DIMENSION [Adventure Works].[Ship Date].[Fiscal Quarter of Year],DIMENSION [Adventure Works].[Ship Date].[Fiscal Semester of Year],DIMENSION [Adventure Works].[Ship Date].[Fiscal Year],DIMENSION [Adventure Works].[Ship Date].[Month of Year],DIMENSION [Adventure Works].[Ship Date].[Week of Year],DIMENSION [Adventure Works].[Ship Date].[Year By Months],DIMENSION [Adventure Works].[Ship Date].[Year By Quarters],DIMENSION [Adventure Works].[Ship Date].[Year By Weeks],DIMENSION [Adventure Works].[Source Currency].[Source Currency],DIMENSION [Adventure Works].[Source Currency].[Source Currency Code],DIMENSION [Adventure Works].[Date].[Calendar Quarter] HIDDEN,DIMENSION [Adventure Works].[Date].[Calendar Semester] HIDDEN,DIMENSION [Adventure Works].[Date].[Fiscal Quarter] HIDDEN,DIMENSION [Adventure Works].[Date].[Fiscal Semester] HIDDEN,DIMENSION [Adventure Works].[Date].[Month Name] HIDDEN,DIMENSION [Adventure Works].[Delivery Date].[Calendar Quarter] HIDDEN,DIMENSION [Adventure Works].[Delivery Date].[Calendar Semester] HIDDEN,DIMENSION [Adventure Works].[Delivery Date].[Fiscal Quarter] HIDDEN,DIMENSION [Adventure Works].[Delivery Date].[Fiscal Semester] HIDDEN,DIMENSION [Adventure Works].[Delivery Date].[Month Name] HIDDEN,DIMENSION [Adventure Works].[Department].[Department] HIDDEN,DIMENSION [Adventure Works].[Employee].[Sales Territory Key] HIDDEN,DIMENSION [Adventure Works].[Geography].[Geography Key] HIDDEN,DIMENSION [Adventure Works].[Internet Sales Order Details].[Internet Sales Order] HIDDEN,DIMENSION [Adventure Works].[Organization].[Organization] HIDDEN,DIMENSION [Adventure Works].[Reseller].[Geography Key] HIDDEN,DIMENSION [Adventure Works].[Reseller Sales Order Details].[Reseller Sales Order] HIDDEN,DIMENSION [Adventure Works].[Sales Summary Order Details].[Sales Order] HIDDEN,DIMENSION [Adventure Works].[Ship Date].[Calendar Quarter] HIDDEN,DIMENSION [Adventure Works].[Ship Date].[Calendar Semester] HIDDEN,DIMENSION [Adventure Works].[Ship Date].[Fiscal Quarter] HIDDEN,DIMENSION [Adventure Works].[Ship Date].[Fiscal Semester] HIDDEN,DIMENSION [Adventure Works].[Ship Date].[Month Name] HIDDEN,DIMENSION [Product].[Product Categories] NOT_RELATED_TO_FACTS FROM _XL_GROUPING113 ( LEVEL [(All)],LEVEL [Category],LEVEL [Category1] GROUPING,LEVEL [Subcategory],LEVEL [Product],GROUP [Category1].[SubcategoryXl_Grp_1] ( MEMBER [Product].[Product Categories].[Subcategory].&[26],MEMBER [Product].[Product Categories].[Subcategory].&[27] ) ) )

After that, you can issue queries directly against the session cube, i.e.

SELECT NON EMPTY Hierarchize({DrilldownLevel(DrilldownMember({{DrilldownLevel({[Product].[Product Categories].[All Products]})}}, {[Product].[Product Categories].[Category].&[4]}),[Product].[Product Categories].[Category1])}) DIMENSION PROPERTIES PARENT_UNIQUE_NAME,[Product].[Product Categories].[Subcategory].[Category],[Product].[Product Categories].[Subcategory].[Category1] ON COLUMNS FROM [Adventure Works_XL_GROUPING0] WHERE ([Measures].[Internet Sales Amount]) CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS

HTH,

Mosha (http://www.mosha.com/msolap)