Grouping in Table

Your help on this could make my day a better one ....

I was trying to create groups on a table report item in SRS 2005 report. The criterion I'm using in one group is exact opposite of the criterion I have in the next group. My dataset has records which fulfill both criteria but my table displays only records for the first group.

Is there a limit on the number of groups we can use in SRS reports and/or on building criteria for filters?

Thanks,

Samtadsh

There is no limit on the number of groups.

Can you please explain you problem in detail?

Priyank

|||

Thanks for you're fast response.

I'll try to explain it better.

I have a filter for group A which says Fields!col1.Value = xxxx. Another group, B, has a filter Fields!col1.Value != xxxx.

Can I display the output of these filters in one table?

Currently, I can see the result of group A but not that of group B.

samtadsh

|||

One of us is confused (and it is probably me <g>).

By "filter" do you mean the "Group on" expression, or are these really filters?

The short answer is "yes it is possible to do what you want" but I'm not entirely sure about what your expectations are of groups, and whether that is the correct way to do what you're trying to do.

I described a similar situation in a recent post, actually (http://spacefold.com/lisa/?date=2007-08-09) which calls it "multiple detail bands" for reasons that are irrelevant here <s>.

The part that is relevant here is that if all you want to do is filter the items first by one set and then by the other set, all you really need to do is create a sort order (forget about filters and groups) to do what you want, forcing some records "up" and some "down"

In my example in the post, the order clause, included among others for the table, was this:

=IIF(Left(UCase(Fields!RDLLayoutName.Value),4) = "IMG_","A","Z")

... yours might be IIF(Fields!col1.Value = xxxxx,"A","Z")

... does this ring any bells for you?

>L<

|||

I was refering the actual filters not the 'group on' ones.

But your suggestion of sorting helped me a lot.

I have another problem though. I need to display multiple details with parent-child associations. One set of record in a table needs to have the parent record in one row(top most) and the child records in the following rows but evenly split vertically - all in one table. Something like this:

Parent1: Parent1.Value

Child11: Child11.Value Child12: Child12.Value

Child13: Child11.Value Child14: Child14.Value

Child15: Child15.Value

Parent2: Parent2.Value

Child21: Child21.Value Child22: Child22.Value

Child23: Child23.Value Child24: Child24.Value

The problem I have is all the data is in one dataset and if I try to show it like above, I can only bind one child record per row. Even if the data comes from separate datasets, I will have the same issue. All child values come from the same column in the dataset and Parent values from another column.

Is there a way to do this kind of layout in using table report item?|||

OK -- should I assume you have already read the blog post on this subject that I referred to in my last message ?

-- assuming so, I think that your layout is actually easier than the one I describe there if your data is multiple children all "flattened out" into one dataset. But I am not positive, so can you give me an example SELECT from the parent and 2 children, with a couple of sample rows of what the data table looks like?

>L<

|||

The format of the data looks like this. I didn't show all the columns but these are good samples.

PARENT CHILD

PID NAME CID ROLE PERSONNEL_NAME
- -- -
8 Company1 5 Accountant Jerry Maguire

8 Company1 2 Accountant Doe John

8 Company1 4 Manager Smith John

8 Company1 9 Manager John Q

3 Company2 12 Manager Stuart Little

3 Company2 7 Accountant Erin Brockovich

3 Company2 6 Sales Person Woody Allen

6 Company3 8 Manager Doe John

Thanks.

Samtadsh

|||

SO is the Parent joining to a position table, and the Role value represents your multiple children, is that what you mean?

I thought when you said it was a multi-child that it was separate child tables and that your data would look "flatter".

Do you have a known number of roles (for example, you show three above, which represent three children)? Or do you think of it as "one child per role" where it is dynamic?

As you see, I am guessing. I would feel much better if you supplied a sample SQL statement <s>, so that I could understand what is "child 1" and what is "child 2" from your POV In the data table above, which you want to separate into your separate columns. I have some ideas about how you might do this, but I don't know how to relate your data example to the layout you supplied earlier. I'm thinking the SQL would help, if you don't think it will, can you tell me what columns you want each row above to appear in ?

Or am I terminally confused? <g>

>L<

|||I gave you what I get as a final result set from my stored procedure(one flat table). This will appear as a dataset in my report. I have to find a way to get that layout working off of this flat data. I just labled it 'Parent' and 'Child' on top to give you an idea which set of columns shoud be considered parent and child.

Roles are various. I can't limit myself with some number of roles. The same goes for Personnel_Names. CID will be unique(combination of ROLE and PERSONNEL_NAME will be unique).

I hope this makes it a bit more clear of what I'm trying to say.

Sorry for confusing you.

Thanks for taking the time to respond to my vague questions.

Samtadsh

|||

Lisa,

I think you're making it more complicated than it needs to be and getting a little confused.

From what I've read and seen of the data, the dataset consists of employees who work for a particular company. Withing this company they play a role. I believe the parent ID = company ID and child ID = employee ID and role is just an attribute of an employee.

The layout above seems to be groupped by company and for each company the requirement is to display the employees in multiple columns i.e. more thatn one employee per row.

The problem, as I see it, is that the default behaviour will display 1 employee per row.

I don't mean to butt into your thread but I hope this helps to explain the problem.

In terms of a solution, one thing that springs to mind is a matrix nested in the table with the employee on the columns groupped by =Ceiling(RowNumber(Nothing)/2)

Sorry but I'm not in a position to try this out right now.

Hope this helps.

|||

Sorry, Adam, I am just tring to do what the individual indicated he wanted done. I see the data the same way you do, FWIW, but the trouble is that doesn't explain his/her original question.

And you're not "butting in", and it's not "my thread" <s>. I am glad that somebody else reads the data, as it is presented, the same way I do.

I don't think your solution will work out for him, although matrix was the first thing that came to mind for me as well. I think Pivot might be more flexible here...

The problem for any such solution is what relationship the side-by-side elements really have, though. It bothers me to show stuff going across that has no real relationship to each other. That's why I asked about possible role hierarchies.

>L<

|||

So there are multiple rows (I'm not asking you to limit them, just trying to understand your layout) but you only want two columns -- are you trying to use the order/sort value to determine what goes into the two separate columns, or is this completely separate from your original question <g>?

Do you really want exactly two columns -- Or do you want one column per role, like in a pivot table or a matrix? The latter makes more sense, because you won't be showing the data in a way that that implies a relationship between employees that is not real...

>L<

|||

I have two cases in which this layout should be utilized. IT will be in the same report but different tables.

Case 1: There will be two columns across which employee name together with thier role gets displayed evenly starting from the left column. There is no role heirarchy and no limitation on type of roles. The distribution of the employees should be like this:

COL1 | COL2

Parent1: XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

(Role11) - Employee11 | (Role11)- Employee12

(Role13)-Employee13 |

_

Parent2: XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

(Role21)-Employee21 | (Role22)- Employee22

(Role23)-Employee23 | (Role24)- Employee24

Case 2: There will be two columns across which employee name together with thier role gets displayed evenly starting from the left column. There are two roles. The distribution of the employees should be like this:

COL1 | COL2

Parent1: XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

(RoleA) - EmployeeA1 | (RoleB)- EmployeeB1

- EmployeeA2 |

_

Parent2: XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

(RoleA)-EmployeeA1 | (RoleB)- EmployeeB1

-EmployeeA2 | - EmployeeB2

Thanks,

Samtadsh

|||

I think I have actually done this. I'm going to show you how I would do the first one in pure SQL -- I don't have your data but I believe I have modelled the same situation. I think the way to do the second layout is very similar and I will describe it briefly here as well.

This is what my data looks like:

1) an order header table that will serve as the parent/group
2) an order detail table with line item #s in it, which will serve as the child.

The simple SELECT statement below (order -> line item) is the way I am modeling your relationship company -> employee

Code Snippet

SELECT sales_no, line_no FROM orderheader H
JOIN orderdetail D ON H.sales_no = D.sales_no

OK?

To get your first layout, my query creates a result with three columns, like this -- I will explain the critical details below:

Code Snippet

select h.sales_no, Col1.line_no As Col1, Col2.line_no AS Col2

from orderheader h
join
(select sales_no, line_no, row_number() over
( partition by sales_no order by line_no) as orderrank from orderdetail ) Col1

on h.sales_no = col1.sales_no and col1.orderrank % 2 = 1

left join
(select sales_no, line_no, row_number() over
( partition by sales_no order by line_no) as orderrank from orderdetail ) Col2

on h.sales_no = col2.sales_no

and (col2.orderrank = col1.orderrank + 1 )

order by h.sales_no, col1.line_no

Here are the critical things to notice in this query:

The first (inner) join gives you all the odd-number line numbers, from the first line number on up (Employee #s 1,3,5 and so on in your case).

The second join is an *outer* join. It will give you Employee #s 2,4,6 and so on but will be null on the last row where the total number of employees is odd.

I have highlighted the three ordering/sorting components because I'm pretty sure that they have to match up to get this to work properly.

|||

Thanks Lisa.

I think the idea would work for my problem too. Unfortunately, I have SQL2000 back-end.(I should have mentioned this from the on set though ... sorry about that.)

It looks like row_number() and partition by are SQL2005 features. I'm looking into ways to acheive similar results using SQL2000 constructs. If you know have this implementation in SQL2000, I would appreciate it greatly.

Thanks.

S.T