locked
Sorting/Grouping and group totals RRS feed

  • Question

  • Lets say I have a report that shows users and their tasks. I can group/sort my report by a user and subtotals will show total user time, I also want  to sort same report by a task, so subtotals will show total time for a task. Is that possible in one report?

    Thanks
    Monday, March 2, 2009 4:36 PM

Answers

  • I would add a parameter for Grouping - "What would you like to group by?", with non-queried choices being By User and By Task.

    Then you can use =iif(Parameters!Grouping.Value = "By User",Fields!User.Value,Fields!Task.Value) in both your grouping and sorting expressions.

    I actually used this with 5-10 different choices, in which case if you use exact field names for the parameter values, you can then use =Fields(Parameters!Grouping.Value).Value as your expression for grouping and sorting.

    Hope this helps,
    Julie
    Monday, March 2, 2009 7:32 PM

All replies

  • I would add a parameter for Grouping - "What would you like to group by?", with non-queried choices being By User and By Task.

    Then you can use =iif(Parameters!Grouping.Value = "By User",Fields!User.Value,Fields!Task.Value) in both your grouping and sorting expressions.

    I actually used this with 5-10 different choices, in which case if you use exact field names for the parameter values, you can then use =Fields(Parameters!Grouping.Value).Value as your expression for grouping and sorting.

    Hope this helps,
    Julie
    Monday, March 2, 2009 7:32 PM
  • But I wanted it working as the following:
    Initially the report is sorted and totalled by a user name. Then by clicking on a task column header a sort is changed to a task and so the totals.
    Tuesday, March 3, 2009 1:36 PM
  • You could accomplish this by pairing yuliak's advice with a Drillthrough.  Add a Drillthrough on the task column header's TextBox that points to the same report but changes the GroupBy parameter value to "By Task".  When the user click's on the column header, the report would reload with the new parameter value.  If you don't want the parameter to be visible to the user, you can mark it as Internal.  However, be sure to provide a default value or the report won't run.

    Hope this helps,
    John


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Tuesday, March 3, 2009 6:18 PM
    Answerer