Help with Grouping and Filtering?

dkim18
dkim18 used Ask the Experts™
on
hi,

Can you help me with grouping and filtering?

Here is my table:
orderNo, Department,status,substatus,orderdate,Approved

Department has D1,D2,D3,S1,S2,S3
Status has pending, accept,decline
substatus has cancel,return

I was able to group by Depart then Status to print them like this:

Department D1

Pending items
list pending itemno here

Accept items
list accepted itemno here

Decline Items
list decline itemno here

-----------------------------

Now I want to break down the accepted items deeper using condition.
Pending items
list pending itemno here

Accept items with substatus cancel
list accepted itemno here

Accept items with substatus cancel
list accepted itemno here

Accept items with approved checked
list accepted itemno here
Decline Items
list decline itemno here


I am using the grouping in SSRS report.
I can't think of a way to add another group.
Can you advise?
Hopefully I was able to present my questions clearly :)
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:

can you show your sql?

you should be able to add a group on status to get what you need.

dept
    status pending
       Details
   status  Accept
        Details



I think I am missing something.

Author

Commented:
I am doing this in SSRS report.
My sql doesn't do the grouping.
I am using the ssrs report's grouping.

As I said, I can group by status.
What I want to do is selectively group (accept status) again using different conditions.

I was wondering if I can accomplish this in SSRS using groups and filter.
I am looking for something like this.
dept
    status pending
       Details
   status  Accept with condition1
        Details
   status  Accept with condition2
        Details

Commented:
YES

group 1 on dept

group 2 on status

your status group can be made into an expression that will group the groups the way you wish

so you will need to make an expression to have ' status  Accept with condition2' as a group.

If you need more show more detailed output and define how you need to break down the status group

Author

Commented:
So I have two groups.
Group1 is by dept
Group2 is by status.
In Group2's expresssion field, it already has status.
You are saying I should add additional expression below that status expression.

Group2
in expression section, I have:
=Fields.Status.Value
=add expression meeting my condition 1
=add expression meeting my condition 2


I didn't quite get how multiple expresssions work in the Group.
Let me explore that.
Thanks.
Commented:
something like this

=iif(Fields!LightsID.Value=1,"True group",
iif(Fields!LightsID.Value = 3,"False group",
"other group"))

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial