locked
Samarize Value by field need to show as it is RRS feed

  • Question

  • Hi All,

    I am using Power Pivot tool to draw report out of SQL SERVER DB Table.

    Table has below columns( Site and ColValue) and rows in it.

    Site  ColValue

    PQR  10

    PQR  20

    PQR  30

    In Excel Pivot Table I need to show item as same as present in underline table.

      Values agreegated column

    In above image, in Values box, Can I use function such that it will show all possible values against that site?

    Some how I want to show all possible values without applying any other function over it.

    Friday, February 21, 2014 5:18 AM

Answers

  • Hi Sandip,

    Following Visakh's suggestion, after placing both columns on rows, you can then do the following to get something close to the visual output you have described:

    1. Click on a cell within the Pivot Table
    2. Click on the 'Design' tab under the 'PivotTable Tools'
    3. Click 'Subtotals' and select 'Do Not Show Subtotals'
    4. Click 'Grand Totals' and select 'Off for Rows and Columns'
    5. Click 'Report Layout' and select 'Show in Tabular Form'.
    6. Click 'Report Layout' and select 'Repeat All Item Labels'
    7. Finally, click the 'Analyze' tab and click the 'Buttons' option to deselect it.

    This will result in the following...

    However, I should point out that there may be issues faced further down the line from doing things this way. Another possible approach would be to add a date column to your sites table and introduce a date table. You could then arrange the columns on rows as Site -> Date column (could be date, week or month) and place the [ColValue Measure] in Values, where the [ColValue Measure] will return an aggregate value for the day. It would end up looking something like this...

    Let us know your thoughts on this :)


    Regards,

    Michael

    Please remember to mark a post that answers your question as an answer...If a post doesn't answer your question but you've found it helpful, please remember to vote it as helpful :)





    • Edited by Michael Amadi Friday, February 21, 2014 2:38 PM Further clarifications
    • Marked as answer by BhaSandy Tuesday, February 25, 2014 10:13 AM
    Friday, February 21, 2014 2:22 PM

All replies

  • Add both the fields to rows and try

    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    • Proposed as answer by Michael Amadi Friday, February 21, 2014 2:27 PM
    Friday, February 21, 2014 6:05 AM
  • It wont help in this case as it will show result as

    PQR

     10          

     20

     30

    Ideally it needs to be displayed like below

    PQR  10          

            20

            30

    Values should be like measures in the report.

    If it is not possible to achive can you suggest any other way to present the same informatoin.

    I have agreegated measures in the database itself datewise, like on 21 jan for Site PQR value is 10 similarly on on 21 jan for site pqr values is 20 etc.

    Now Pivot data model store 10 20 30 rows at least otherwise it might have store Lots of rows .

    To use less DB memory. I have agreegated values in domain. 


    • Edited by BhaSandy Friday, February 21, 2014 8:17 AM
    Friday, February 21, 2014 8:11 AM
  • It wont help in this case as it will show result as

    PQR

     10          

     20

     30

    Ideally it needs to be displayed like below

    PQR  10          

            20

            30

    Values should be like measures in the report.

    If it is not possible to achive can you suggest any other way to present the same informatoin.

    I have agreegated measures in the database itself datewise, like on 21 jan for Site PQR value is 10 similarly on on 21 jan for site pqr values is 20 etc.

    Now Pivot data model store 10 20 30 rows at least otherwise it might have store Lots of rows .

    To use less DB memory. I have agreegated values in domain. 
    Friday, February 21, 2014 9:00 AM
  • Hi Sandip,

    Following Visakh's suggestion, after placing both columns on rows, you can then do the following to get something close to the visual output you have described:

    1. Click on a cell within the Pivot Table
    2. Click on the 'Design' tab under the 'PivotTable Tools'
    3. Click 'Subtotals' and select 'Do Not Show Subtotals'
    4. Click 'Grand Totals' and select 'Off for Rows and Columns'
    5. Click 'Report Layout' and select 'Show in Tabular Form'.
    6. Click 'Report Layout' and select 'Repeat All Item Labels'
    7. Finally, click the 'Analyze' tab and click the 'Buttons' option to deselect it.

    This will result in the following...

    However, I should point out that there may be issues faced further down the line from doing things this way. Another possible approach would be to add a date column to your sites table and introduce a date table. You could then arrange the columns on rows as Site -> Date column (could be date, week or month) and place the [ColValue Measure] in Values, where the [ColValue Measure] will return an aggregate value for the day. It would end up looking something like this...

    Let us know your thoughts on this :)


    Regards,

    Michael

    Please remember to mark a post that answers your question as an answer...If a post doesn't answer your question but you've found it helpful, please remember to vote it as helpful :)





    • Edited by Michael Amadi Friday, February 21, 2014 2:38 PM Further clarifications
    • Marked as answer by BhaSandy Tuesday, February 25, 2014 10:13 AM
    Friday, February 21, 2014 2:22 PM