 | |
Re: About sum of descendants
microsoft.public.sqlserver.olap microsoft.public.sqlserver.olap
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
Re: About sum of descendants -
02-16-2005
, 09:05 AM
|
VisualTotals function will be helpful. There is example in BOL. Also, it can be enabled through dimension security setting. Open Cube role manager and edit your role. Click Dimensions tab and select the dimension. Select "Custom" under rule list and click (...) button, then Custom Dimension Security dialog box appears. There if click Commnon tab, you can enable VisualTotals. Ohjoo Kwon www.olapforum.com "Polly" <Polly (AT) discussions (DOT) microsoft.com> wrote in message news:E4098FB2-E39D-43F7-9A62-1296E2542CFC (AT) microsoft (DOT) com... Using "sum(Descendants ([Item Category].Currentmember,, leaves),[Measures].[Qty])", we can sum up all descendants' "qty" for current member. However, I only want to sum up the "qty" of descendants that are selected by users. For example, Category A Item 1 100 Category A Item 2 200 Category A Item 3 300 Total for Category A 600 Category B Item 4 400 Total for Category B 400 Total for All Categories 1000 If user only choose Item 1, 2 and 4, then the result should be displayed like this: Category A Item 1 100 Category A Item 2 200 Total for Category A 300 Category B Item 4 400 Total for Category B 400 Total for All Categories 700 and not this Category A Item 1 100 Category A Item 2 200 Total for Category A 600 Category B Item 4 400 Total for Category B 400 Total for All Categories 1000 Does anybody know how to write the MDX? Thanks a lot! Polly |
#2
| |||
| |||
|
|
VisualTotals function will be helpful. There is example in BOL. Also, it can be enabled through dimension security setting. Open Cube role manager and edit your role. Click Dimensions tab and select the dimension. Select "Custom" under rule list and click (...) button, then Custom Dimension Security dialog box appears. There if click Commnon tab, you can enable VisualTotals. Ohjoo Kwon www.olapforum.com "Polly" <Polly (AT) discussions (DOT) microsoft.com> wrote in message news:E4098FB2-E39D-43F7-9A62-1296E2542CFC (AT) microsoft (DOT) com... Using "sum(Descendants ([Item Category].Currentmember,, leaves),[Measures].[Qty])", we can sum up all descendants' "qty" for current member. However, I only want to sum up the "qty" of descendants that are selected by users. For example, Category A Item 1 100 Category A Item 2 200 Category A Item 3 300 Total for Category A 600 Category B Item 4 400 Total for Category B 400 Total for All Categories 1000 If user only choose Item 1, 2 and 4, then the result should be displayed like this: Category A Item 1 100 Category A Item 2 200 Total for Category A 300 Category B Item 4 400 Total for Category B 400 Total for All Categories 700 and not this Category A Item 1 100 Category A Item 2 200 Total for Category A 600 Category B Item 4 400 Total for Category B 400 Total for All Categories 1000 Does anybody know how to write the MDX? Thanks a lot! Polly |
#3
| |||
| |||
|
|
Dear Ohjoo, It seems that your method is used for retricted access to certain type of members. However, my point is that user may want to view 3 items(say item 1, item 2 and item 4) at this time and may want to view only two items (say item 3 and item 4) next time. Actually, we don't know which item will they choose each time. So, your suggested method may not be applicable to my case. Do you have other suggestion that fit to my case? Anyway, thanks for your suggestion. Polly "Ohjoo Kwon" wrote: VisualTotals function will be helpful. There is example in BOL. Also, it can be enabled through dimension security setting. Open Cube role manager and edit your role. Click Dimensions tab and select the dimension. Select "Custom" under rule list and click (...) button, then Custom Dimension Security dialog box appears. There if click Commnon tab, you can enable VisualTotals. Ohjoo Kwon www.olapforum.com "Polly" <Polly (AT) discussions (DOT) microsoft.com> wrote in message news:E4098FB2-E39D-43F7-9A62-1296E2542CFC (AT) microsoft (DOT) com... Using "sum(Descendants ([Item Category].Currentmember,, leaves),[Measures].[Qty])", we can sum up all descendants' "qty" for current member. However, I only want to sum up the "qty" of descendants that are selected by users. For example, Category A Item 1 100 Category A Item 2 200 Category A Item 3 300 Total for Category A 600 Category B Item 4 400 Total for Category B 400 Total for All Categories 1000 If user only choose Item 1, 2 and 4, then the result should be displayed like this: Category A Item 1 100 Category A Item 2 200 Total for Category A 300 Category B Item 4 400 Total for Category B 400 Total for All Categories 700 and not this Category A Item 1 100 Category A Item 2 200 Total for Category A 600 Category B Item 4 400 Total for Category B 400 Total for All Categories 1000 Does anybody know how to write the MDX? Thanks a lot! Polly |
#4
| |||
| |||
|
|
Hi Ohjoo, You write in all your messages the link on the www.olapforum.com No doubt, there is a lot of usfull information on your web site. But mostly reader of this group is not master of Korean language ;-( It would be wonderful, if you could deploy your content translated into English. Thanks, Vladimir Chtepa "Ohjoo Kwon" <ojkwon (AT) olap (DOT) co.kr> schrieb im Newsbeitrag news:OXTwjU$EFHA.3888 (AT) TK2MSFTNGP12 (DOT) phx.gbl... VisualTotals function will be helpful. There is example in BOL. Also, it can be enabled through dimension security setting. Open Cube role manager and edit your role. Click Dimensions tab and select the dimension. Select "Custom" under rule list and click (...) button, then Custom Dimension Security dialog box appears. There if click Commnon tab, you can enable VisualTotals. Ohjoo Kwon www.olapforum.com "Polly" <Polly (AT) discussions (DOT) microsoft.com> wrote in message news:E4098FB2-E39D-43F7-9A62-1296E2542CFC (AT) microsoft (DOT) com... Using "sum(Descendants ([Item Category].Currentmember,, leaves),[Measures].[Qty])", we can sum up all descendants' "qty" for current member. However, I only want to sum up the "qty" of descendants that are selected by users. For example, Category A Item 1 100 Category A Item 2 200 Category A Item 3 300 Total for Category A 600 Category B Item 4 400 Total for Category B 400 Total for All Categories 1000 If user only choose Item 1, 2 and 4, then the result should be displayed like this: Category A Item 1 100 Category A Item 2 200 Total for Category A 300 Category B Item 4 400 Total for Category B 400 Total for All Categories 700 and not this Category A Item 1 100 Category A Item 2 200 Total for Category A 600 Category B Item 4 400 Total for Category B 400 Total for All Categories 1000 Does anybody know how to write the MDX? Thanks a lot! Polly |
#5
| |||
| |||
|
|
I'm sorry for my previous answer before I understood your issue fully. Yes, looks like that server-side setting cannot solve your issue, if I know right. You can still use VisualTotals() in your MDX query to solve your issue, but it will needs bothersome hard coding. I, now, wonder it is the portion of the client tool. Many client tools has support the features of visual totals. For example, you can toggle between real totals and visual totals using "Include Hidden Items in Total" button in Excel PivotTables. If you use OWC, you can also do the same operation by checking "Visible items only" or "All items(including hidden items)" under "Calculate totals based on" of Report Tab of Commands and Options dialog box. Ohjoo Kwon www.olapforum.com "Polly" <Polly (AT) discussions (DOT) microsoft.com> wrote in message news:EC1F8217-7236-40CF-A2F0-7A6D9F25FFE9 (AT) microsoft (DOT) com... Dear Ohjoo, It seems that your method is used for retricted access to certain type of members. However, my point is that user may want to view 3 items(say item 1, item 2 and item 4) at this time and may want to view only two items (say item 3 and item 4) next time. Actually, we don't know which item will they choose each time. So, your suggested method may not be applicable to my case. Do you have other suggestion that fit to my case? Anyway, thanks for your suggestion. Polly "Ohjoo Kwon" wrote: VisualTotals function will be helpful. There is example in BOL. Also, it can be enabled through dimension security setting. Open Cube role manager and edit your role. Click Dimensions tab and select the dimension. Select "Custom" under rule list and click (...) button, then Custom Dimension Security dialog box appears. There if click Commnon tab, you can enable VisualTotals. Ohjoo Kwon www.olapforum.com "Polly" <Polly (AT) discussions (DOT) microsoft.com> wrote in message news:E4098FB2-E39D-43F7-9A62-1296E2542CFC (AT) microsoft (DOT) com... Using "sum(Descendants ([Item Category].Currentmember,, leaves),[Measures].[Qty])", we can sum up all descendants' "qty" for current member. However, I only want to sum up the "qty" of descendants that are selected by users. For example, Category A Item 1 100 Category A Item 2 200 Category A Item 3 300 Total for Category A 600 Category B Item 4 400 Total for Category B 400 Total for All Categories 1000 If user only choose Item 1, 2 and 4, then the result should be displayed like this: Category A Item 1 100 Category A Item 2 200 Total for Category A 300 Category B Item 4 400 Total for Category B 400 Total for All Categories 700 and not this Category A Item 1 100 Category A Item 2 200 Total for Category A 600 Category B Item 4 400 Total for Category B 400 Total for All Categories 1000 Does anybody know how to write the MDX? Thanks a lot! Polly |
#6
| |||
| |||
|
|
Dear Ohjoo, I have tried to press the button in Excel PivotTables before. However, it doesn't work. Thanks for your advice again! Polly "Ohjoo Kwon" wrote: I'm sorry for my previous answer before I understood your issue fully. Yes, looks like that server-side setting cannot solve your issue, if I know right. You can still use VisualTotals() in your MDX query to solve your issue, but it will needs bothersome hard coding. I, now, wonder it is the portion of the client tool. Many client tools has support the features of visual totals. For example, you can toggle between real totals and visual totals using "Include Hidden Items in Total" button in Excel PivotTables. If you use OWC, you can also do the same operation by checking "Visible items only" or "All items(including hidden items)" under "Calculate totals based on" of Report Tab of Commands and Options dialog box. Ohjoo Kwon www.olapforum.com "Polly" <Polly (AT) discussions (DOT) microsoft.com> wrote in message news:EC1F8217-7236-40CF-A2F0-7A6D9F25FFE9 (AT) microsoft (DOT) com... Dear Ohjoo, It seems that your method is used for retricted access to certain type of members. However, my point is that user may want to view 3 items(say item 1, item 2 and item 4) at this time and may want to view only two items (say item 3 and item 4) next time. Actually, we don't know which item will they choose each time. So, your suggested method may not be applicable to my case. Do you have other suggestion that fit to my case? Anyway, thanks for your suggestion. Polly "Ohjoo Kwon" wrote: VisualTotals function will be helpful. There is example in BOL. Also, it can be enabled through dimension security setting. Open Cube role manager and edit your role. Click Dimensions tab and select the dimension. Select "Custom" under rule list and click (...) button, then Custom Dimension Security dialog box appears. There if click Commnon tab, you can enable VisualTotals. Ohjoo Kwon www.olapforum.com "Polly" <Polly (AT) discussions (DOT) microsoft.com> wrote in message news:E4098FB2-E39D-43F7-9A62-1296E2542CFC (AT) microsoft (DOT) com... Using "sum(Descendants ([Item Category].Currentmember,, leaves),[Measures].[Qty])", we can sum up all descendants' "qty" for current member. However, I only want to sum up the "qty" of descendants that are selected by users. For example, Category A Item 1 100 Category A Item 2 200 Category A Item 3 300 Total for Category A 600 Category B Item 4 400 Total for Category B 400 Total for All Categories 1000 If user only choose Item 1, 2 and 4, then the result should be displayed like this: Category A Item 1 100 Category A Item 2 200 Total for Category A 300 Category B Item 4 400 Total for Category B 400 Total for All Categories 700 and not this Category A Item 1 100 Category A Item 2 200 Total for Category A 600 Category B Item 4 400 Total for Category B 400 Total for All Categories 1000 Does anybody know how to write the MDX? Thanks a lot! Polly |
#7
| |||
| |||
|
|
I did the same test just now. First, I select part of members of time dimsion on row. For example, [1997], [Q1], [1], [2], [Q3],... It works well. Do I still misunderstand your issue? Ohjoo "Polly" <Polly (AT) discussions (DOT) microsoft.com> wrote in message news:1DB9036E-EBCD-4B0D-B483-D0BE094E893B (AT) microsoft (DOT) com... Dear Ohjoo, I have tried to press the button in Excel PivotTables before. However, it doesn't work. Thanks for your advice again! Polly "Ohjoo Kwon" wrote: I'm sorry for my previous answer before I understood your issue fully. Yes, looks like that server-side setting cannot solve your issue, if I know right. You can still use VisualTotals() in your MDX query to solve your issue, but it will needs bothersome hard coding. I, now, wonder it is the portion of the client tool. Many client tools has support the features of visual totals. For example, you can toggle between real totals and visual totals using "Include Hidden Items in Total" button in Excel PivotTables. If you use OWC, you can also do the same operation by checking "Visible items only" or "All items(including hidden items)" under "Calculate totals based on" of Report Tab of Commands and Options dialog box. Ohjoo Kwon www.olapforum.com "Polly" <Polly (AT) discussions (DOT) microsoft.com> wrote in message news:EC1F8217-7236-40CF-A2F0-7A6D9F25FFE9 (AT) microsoft (DOT) com... Dear Ohjoo, It seems that your method is used for retricted access to certain type of members. However, my point is that user may want to view 3 items(say item 1, item 2 and item 4) at this time and may want to view only two items (say item 3 and item 4) next time. Actually, we don't know which item will they choose each time. So, your suggested method may not be applicable to my case. Do you have other suggestion that fit to my case? Anyway, thanks for your suggestion. Polly "Ohjoo Kwon" wrote: VisualTotals function will be helpful. There is example in BOL. Also, it can be enabled through dimension security setting. Open Cube role manager and edit your role. Click Dimensions tab and select the dimension. Select "Custom" under rule list and click (...) button, then Custom Dimension Security dialog box appears. There if click Commnon tab, you can enable VisualTotals. Ohjoo Kwon www.olapforum.com "Polly" <Polly (AT) discussions (DOT) microsoft.com> wrote in message news:E4098FB2-E39D-43F7-9A62-1296E2542CFC (AT) microsoft (DOT) com... Using "sum(Descendants ([Item Category].Currentmember,, leaves),[Measures].[Qty])", we can sum up all descendants' "qty" for current member. However, I only want to sum up the "qty" of descendants that are selected by users. For example, Category A Item 1 100 Category A Item 2 200 Category A Item 3 300 Total for Category A 600 Category B Item 4 400 Total for Category B 400 Total for All Categories 1000 If user only choose Item 1, 2 and 4, then the result should be displayed like this: Category A Item 1 100 Category A Item 2 200 Total for Category A 300 Category B Item 4 400 Total for Category B 400 Total for All Categories 700 and not this Category A Item 1 100 Category A Item 2 200 Total for Category A 600 Category B Item 4 400 Total for Category B 400 Total for All Categories 1000 Does anybody know how to write the MDX? Thanks a lot! Polly |
#8
| |||
| |||
|
|
Dear Ohjoo, In my pivot table, only measures and calculated members that do not involve descendants work. For those calculated members that involve descendants, they do not work. Is there any problem of using "sum of descendants" in MDX? Polly "Ohjoo Kwon" wrote: I did the same test just now. First, I select part of members of time dimsion on row. For example, [1997], [Q1], [1], [2], [Q3],... It works well. Do I still misunderstand your issue? Ohjoo "Polly" <Polly (AT) discussions (DOT) microsoft.com> wrote in message news:1DB9036E-EBCD-4B0D-B483-D0BE094E893B (AT) microsoft (DOT) com... Dear Ohjoo, I have tried to press the button in Excel PivotTables before. However, it doesn't work. Thanks for your advice again! Polly "Ohjoo Kwon" wrote: I'm sorry for my previous answer before I understood your issue fully. Yes, looks like that server-side setting cannot solve your issue, if I know right. You can still use VisualTotals() in your MDX query to solve your issue, but it will needs bothersome hard coding. I, now, wonder it is the portion of the client tool. Many client tools has support the features of visual totals. For example, you can toggle between real totals and visual totals using "Include Hidden Items in Total" button in Excel PivotTables. If you use OWC, you can also do the same operation by checking "Visible items only" or "All items(including hidden items)" under "Calculate totals based on" of Report Tab of Commands and Options dialog box. Ohjoo Kwon www.olapforum.com "Polly" <Polly (AT) discussions (DOT) microsoft.com> wrote in message news:EC1F8217-7236-40CF-A2F0-7A6D9F25FFE9 (AT) microsoft (DOT) com... Dear Ohjoo, It seems that your method is used for retricted access to certain type of members. However, my point is that user may want to view 3 items(say item 1, item 2 and item 4) at this time and may want to view only two items (say item 3 and item 4) next time. Actually, we don't know which item will they choose each time. So, your suggested method may not be applicable to my case. Do you have other suggestion that fit to my case? Anyway, thanks for your suggestion. Polly "Ohjoo Kwon" wrote: VisualTotals function will be helpful. There is example in BOL. Also, it can be enabled through dimension security setting. Open Cube role manager and edit your role. Click Dimensions tab and select the dimension. Select "Custom" under rule list and click (...) button, then Custom Dimension Security dialog box appears. There if click Commnon tab, you can enable VisualTotals. Ohjoo Kwon www.olapforum.com "Polly" <Polly (AT) discussions (DOT) microsoft.com> wrote in message news:E4098FB2-E39D-43F7-9A62-1296E2542CFC (AT) microsoft (DOT) com... Using "sum(Descendants ([Item Category].Currentmember,, leaves),[Measures].[Qty])", we can sum up all descendants' "qty" for current member. However, I only want to sum up the "qty" of descendants that are selected by users. For example, Category A Item 1 100 Category A Item 2 200 Category A Item 3 300 Total for Category A 600 Category B Item 4 400 Total for Category B 400 Total for All Categories 1000 If user only choose Item 1, 2 and 4, then the result should be displayed like this: Category A Item 1 100 Category A Item 2 200 Total for Category A 300 Category B Item 4 400 Total for Category B 400 Total for All Categories 700 and not this Category A Item 1 100 Category A Item 2 200 Total for Category A 600 Category B Item 4 400 Total for Category B 400 Total for All Categories 1000 Does anybody know how to write the MDX? Thanks a lot! Polly |
#9
| |||
| |||
|
|
I thought my example alreay gives the results that you want. But I guess you have other calculated members and some of them use descendants. I reproduced what you say using Excel PivotTable. I'm not sure using descendants causes this problem. I think if VisualTotal is calculated from locally cached result set, there should be no problems. Now I ask Microsoft to confirm that it is just a client bug or other cause exists. Ohjoo "Polly" <Polly (AT) discussions (DOT) microsoft.com> wrote in message news:371EB436-17BC-4EE4-AA26-90EB2DB0804C (AT) microsoft (DOT) com... Dear Ohjoo, In my pivot table, only measures and calculated members that do not involve descendants work. For those calculated members that involve descendants, they do not work. Is there any problem of using "sum of descendants" in MDX? Polly "Ohjoo Kwon" wrote: I did the same test just now. First, I select part of members of time dimsion on row. For example, [1997], [Q1], [1], [2], [Q3],... It works well. Do I still misunderstand your issue? Ohjoo "Polly" <Polly (AT) discussions (DOT) microsoft.com> wrote in message news:1DB9036E-EBCD-4B0D-B483-D0BE094E893B (AT) microsoft (DOT) com... Dear Ohjoo, I have tried to press the button in Excel PivotTables before. However, it doesn't work. Thanks for your advice again! Polly "Ohjoo Kwon" wrote: I'm sorry for my previous answer before I understood your issue fully. Yes, looks like that server-side setting cannot solve your issue, if I know right. You can still use VisualTotals() in your MDX query to solve your issue, but it will needs bothersome hard coding. I, now, wonder it is the portion of the client tool. Many client tools has support the features of visual totals. For example, you can toggle between real totals and visual totals using "Include Hidden Items in Total" button in Excel PivotTables. If you use OWC, you can also do the same operation by checking "Visible items only" or "All items(including hidden items)" under "Calculate totals based on" of Report Tab of Commands and Options dialog box. Ohjoo Kwon www.olapforum.com "Polly" <Polly (AT) discussions (DOT) microsoft.com> wrote in message news:EC1F8217-7236-40CF-A2F0-7A6D9F25FFE9 (AT) microsoft (DOT) com... Dear Ohjoo, It seems that your method is used for retricted access to certain type of members. However, my point is that user may want to view 3 items(say item 1, item 2 and item 4) at this time and may want to view only two items (say item 3 and item 4) next time. Actually, we don't know which item will they choose each time. So, your suggested method may not be applicable to my case. Do you have other suggestion that fit to my case? Anyway, thanks for your suggestion. Polly "Ohjoo Kwon" wrote: VisualTotals function will be helpful. There is example in BOL. Also, it can be enabled through dimension security setting. Open Cube role manager and edit your role. Click Dimensions tab and select the dimension. Select "Custom" under rule list and click (...) button, then Custom Dimension Security dialog box appears. There if click Commnon tab, you can enable VisualTotals. Ohjoo Kwon www.olapforum.com "Polly" <Polly (AT) discussions (DOT) microsoft.com> wrote in message news:E4098FB2-E39D-43F7-9A62-1296E2542CFC (AT) microsoft (DOT) com... Using "sum(Descendants ([Item Category].Currentmember,, leaves),[Measures].[Qty])", we can sum up all descendants' "qty" for current member. However, I only want to sum up the "qty" of descendants that are selected by users. For example, Category A Item 1 100 Category A Item 2 200 Category A Item 3 300 Total for Category A 600 Category B Item 4 400 Total for Category B 400 Total for All Categories 1000 If user only choose Item 1, 2 and 4, then the result should be displayed like this: Category A Item 1 100 Category A Item 2 200 Total for Category A 300 Category B Item 4 400 Total for Category B 400 Total for All Categories 700 and not this Category A Item 1 100 Category A Item 2 200 Total for Category A 600 Category B Item 4 400 Total for Category B 400 Total for All Categories 1000 Does anybody know how to write the MDX? Thanks a lot! Polly |
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.