Post reply

How to add summery to pivot table ?

  • September 24, 2010 at 2:34 pm

    #229744

    Hi,

    I need to have summary by coulmns and by rows for pivot table. Is it possible? And how ?

    Thanks

  • September 24, 2010 at 2:53 pm

    #1227254

    Well, with no code, this is generic, but basically, put in a column that will add headers.

    For example, if you pivoted across MON/TUE/WED/THU/FRI, and now want TOTAL, you'd put

    [MON]+[TUE]+[WED]+[THU]+[FRI] AS TOTAL

    in the select area.

    Another option is unioning an aggregate query to the datasource before you start to pivot.

    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
    Twitter: @AnyWayDBA

  • September 24, 2010 at 3:20 pm

    #1227266

    Thanks, Craig.

    This is summary by rows.

    Unioning to the datasource will bring me summary by columns ?

  • September 24, 2010 at 3:29 pm

    #1227270

    Heh, sorry, for summary by column do all your row summary/setup the entire structure, then union to the tail (adding a hardvalued sortby column, per query, if necessary, to keep it at the tail) an aggregation based on the result set of the pivot/row summary.

    Column summary you'd usually want to do at the reporting services layer, however, as it's cleaner. But that'll get you there.

    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
    Twitter: @AnyWayDBA

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply