Summary of list or crosstab should show total data only for selected parent leve

[xyz]

Hi All,

Datasource is cube, using DMR functions, client has a requirement, where list or crosstab should show summary data only for selected location. Someone can help me on this. Current output and expected output are shown below.

First row in the below output is the parent row, whereas the last row has to show the same total's as first row(i.e. Parent row).

Current Output:

Location  Department  Saturday  Sunday  Monday  Tuesday  Wednesday  Thursday  Friday 
XX                    ABC                  2            1            3              1                                             2             
  YY                   DEF                  1            1            1              1                                             1       
  zz                   GHI                  1                          2                                                             1
Total                                         4            2            6              2                                             4       


Expected Output:

Location  Department  Saturday  Sunday  Monday  Tuesday  Wednesday  Thursday  Friday 
XX                    ABC                  2            1            3              1                                             2           
  YY                   DEF                  1            1            1              1                                             1     
  zz                   GHI                  1                          2                                                             1
Total                                         2            1            3              1                                             2

Can, someone please help me, how can I achieve the above expected output.
Your help will be really appreciated :)

Thanks & Regards,
XYZ

[xyz]

Hi All,

Can someone, help me how to achieve the below requirement?

Your help will be much appreciated :)


Thanks & Regards,
XYZ

[xyz]

Hi All,

Can someone, please provide suggestions on the same.



Thanks & Regards,
XYZ

[xyz]

Hi All,

Can some one, please give some suggestion or share there thoughts on the same?




Thanks & Regards,
XYZ

[adik]

is this a relational or dimensioanl source?
also is that a crosstab? or why are yy and zz slightely to the right than xx?

[xyz]

Hi Adik,

This is dimensional soure, where as data source is cube.
The sample, I provide, we can achieve both in list and crosstab. Consider it as crosstab.
YY and ZZ are the childrens, I have added indent in the crosstab to show difference between a parent member and child members.




Thanks & Regards,
XYZ

[adik]

let's say xx is a level called Product Line and yy and zz are child members
if you do total(currentMeasure within set [Product Line]) doesn't that work?

if it is a list than you have to put the parent query item in a separate column than the child and do a group and then add a total

[xyz]

Hi Adik,

Thanks for reply, I tried it already but the result is not as expected?

Do you have any other suggestions or idea's on it?



Thanks & Regards,
XYZ

[xyz]

Hi All,

Can someone, please help me on this?

Thanks & Regards,
XYZ

[Lynn]

You keep bumping up your post without providing any other information or detail. You responded to Adik's suggestion saying the result is not as expected, but you don't explain anything more about this mysterious unexpected result.

You've provided no detail on the structure of your hierarchies, you've posted nothing about any of the expressions you are using, and you've done nothing to explain anything you've already tried to produce the totals. Your subject indicates "selected parent", but you haven't mentioned a single thing about a prompt or means of selecting anything. Why don't you try mocking up a similar example using the Cognos samples?

If you are going to keep bumping the post then at least add something more for people to work with. Otherwise we are all just guessing blindly. I can't speak for everyone, but to me this often feels like a waste of time when there are other posts out there with a greater likelihood of providing real help.

[xyz]

Hi Lynn,

Thanks for reply, I am extremely sorry for not providing enough information. I though I had provided enough information with examples. It's my bad.

I have requirement to create a cross tab report with 'Location', 'Department' and  'Fiscal Day of Week' dimensions and Task Count as a measure. It needs to have summary. To get the summary, I selected the cross tab as whole and did the Automatic Summary. 'Location', 'Department'  and 'Fiscal Day of Week' are also having prompts, where 'Location' is tree prompt with 'All' selected by default, 'Fiscal Day of Week' has a default date, and 'Department' is a multiselect prompt. Where 'Department' dimension I have written a prompt macro, which I am using in the cross tab, when nothing is selected in 'Department' value prompt, it will display 'All' by default.

The issue, I am facing here is  'Total' row should show the totals of 'All' Location row, which is Parent, whereas 'Total' row is summing up the totals for 'All' parent row as well as other children Locations (YY & ZZ). Please find the attached image of current output and expected output.

To achieve the totals for 'All', I created separate data item with expressions like total([Task Count] within set ([PM].[EM Org Hierarchy].[Hierarchy]->?p_location?)) and pulled in the Total row, but the result is summing the totals for 'All' parent as well as children YY & ZZ. Whereas the total should do sum only for 'All' parent only.

I tried all other options of writing the expression to get the totals, but the result is not as expected. I hope, I am clear with my explanation this time. Please let me know, if I am still not clear with what I have explained.

Thanks & Regards,
XYZ
The issue, I am facing here

[Lynn]

How about dragging in the All level member of your location dimension and nesting beneath? I'm not seeing why you need a calculation if you just want that value anyway.

[Karti]

I agree with Lynn. Why would you need a total value at the bottom if you have that value at the top? Its like having 2 totals showing the exact same thing.

It would probably be better off if you put the 'All' at the bottom instead of at the top, and the report should be what you want.

[xyz]

Thanks Lynn & Karti,

Thank you for suggestions Lynn & Karti, as this report is one of the report in tabs of a dashboard, as other reports have totals, our onsite counterpart wanted to have total in this report as well to make it consistent with other reports in other tabs of a dashboard. So, I suggested my onsite counterpart to not have 'All' row, so that totals will give the result as expected. And he is okay with it.

Your help is really appreciated :)



Thanks & Regards,
XYZ