SSRS 2008 and Grouping columns/rows

Matrix... tablix.... list/matrix... list/tablix... I have no idea. none of them do what I want them to do.

So, I have resident data... name, gender, care level, room/location information. And I have physician phone numbers. All of this has to look like a form I have but has to be done in SSRS.

The form looks like:

(The phone type and phone number can be divided but don't have to be, if that makes any sense.)

 _______________________________________________________________________________
| Allergies:  NKA                                                               |
|                                                                               |
|_______________________________________________________________________________|
|Resident   |    ID     |      Gender    |    Room    |  Type          Number   |
|___________|___________|________________|____________|_________________________|
|           |           |                |            |  Home          555-3242 |
|Kim        |   123     |     female     |      420   |  Cell          555-1111 |
|___________|___________|________________|____________|__Other_________234-5554_|

I need to recreate that in SSRS. I was putting it in a footer and using lots of textbox labels, rectangles and the like to format that but SSRS isn't that great about alignment and keeping things the same size. I have tried with that layout option but when I deploy them they sometimes look dramatically different that in the design and preview. (sometimes the right sides of the rectangles don't line up properly or the rectangles appear to be side by side but render differently and nothing overlaps but it still ends up funny) I have to redo it and I am trying to use a table at the end of the report this time so that if the information does happen to take up two lines, everything around it easily grows.

What I was trying to do is use grouping in a matrix to re-create the whole effect of the form with the phone numbers as the "details" and have a column group by the Allergies text and then a row group by the person info. But.... I can't seem to get it done correctly and everything looks great except for some reason it will only list one phone number in the details and I need one or more to appear.

I set up my data to look like this:

(name)  (ID)   (gender)   (roomNumber)   (allergies)   (phoneType)  (phoneNumber) 
Kim     123    female     410            NKA           home         555-3332
Kim     123    female     410            NKA           cell         555-2342  
Kim     123    female     410            NKA           other        555-1111           

Oh... what am I doing wrong? If I use a list (for the allergy text above) with a table inside, grouped by the personInfo/personID/stuff with the phone numbers as the details I can't get the person info to line up so that the numbers aren't just hanging below.

Obviously, I've simplified the data but this is the general idea of it... Please, give me some suggestions. For the life of me I can't figure out grouping. Do I need my data laid out differently?

Thank you so much............ for your time, Kim

Ok... edit.... I guess I explained it wrong because some one said my data needs normalization. That's how I set up my data to try and use grouping on the personID/person info and then use the phone numbers as the details.

So, let's say I have a base table that's called person, that stores all my person info. Then I have a table called phone number and it has my numbers. Person and phone are associated by an ID. Then I have an allergies table, associated by the personID, it has an allergy ID, and allergy text in it. Left outer join them. Ta da! This is a really big db and I have to check tons of things and its not feasible to actually provide the query right now. Sorry... all of this is a result from really simplifying the situation but it gets the idea across and I still can't accomplish the grouping I want even if the data was that simple (which is what I am asking help with).

Thanks again!

I do actually really need help with this, I couldn't even get it this morning... So, let's forget the allergies part above to make it really easy. I still can't get the grouping to work on the Person and then use the phone numbers as the details. Here's some sample data and stuff.... Since this report is actually going to serve as form, I have the user select the person they want with the parameters so that the personID is passed into my query/stored proc and I will only get one person back but just as an example I included three people in my person table.

This not the actual structure or data or query.... its just sometime I made in 10 minutes to demonstrate the problem I am having with grouping in SSRS... so please, no comments on db structure or anything like that, I just figured I would get a better respons开发者_运维知识库e if I provided some data to play with in SSRS. If I/some one can get it to work with this data it will work on my real data.

create table #person
(
    personID int identity(1,1), 
    name varchar(20),
    birthdate datetime, 
    gender char(1), 
    roomnumber int

    primary key (personID) 
)

create table #phoneNumbers
(
    phoneID int identity(1,1), 
    personID int, 
    number varchar(8),
    phoneType varchar(10),

    foreign key (personID) references #person,
    primary key (phoneID) 
)

declare @scope int
declare @KimsID int

insert into #person (name, birthdate, gender, roomnumber) values ('Mike','11-22-1979','M',22)
insert into #person (name, birthdate, gender, roomnumber) values ('Kim','11-12-1985','F',123) 

set @scope = SCOPE_IDENTITY()
set @KimsID = SCOPE_IDENTITY()

insert into #phoneNumbers (personID, number, phoneType) values (@scope, '333-2323', 'Home')
insert into #phoneNumbers (personID, number, phoneType) values (@scope, '333-1111', 'Cell')
insert into #phoneNumbers (personID, number, phoneType) values (@scope, '555-6767', 'Other')


insert into #person (name, birthdate, gender, roomnumber) values ('Lizz','7-26-1984','F',4) 
set @scope = SCOPE_IDENTITY()
insert into #phoneNumbers (personID, number, phoneType) values (@scope, '444-4444', 'Home')



select  #person.personID, 
        name, 
        (datediff(YY, birthdate, getdate()) -
            case
                when((month(birthdate)*100 + day(birthdate)) >
                    (month(getdate())*100 + day(getdate()))) 
                then 1
            else 0
            end) as age,
        birthdate, 
        gender, 
        roomnumber,

        number,
        phoneType 

from    #person 
        left outer join #phoneNumbers 
        on #phoneNumbers.personID = #person.personID 

--where #person.personID = @KimsID 

order by #person.name, phoneType 

drop table #person
drop table #phoneNumbers

The original form has denormalized data, as there are many phone numbers in one cell. Your database has a good normalized structure. This is what is making things tricky. The easiest way to get what you want is to denormalize the data before sending it to reporting services. This means getting all phone numbers for a person and putting them in a string.

I took your script, and changed the temporary tables to permanent tables. I then added the following function to get denormalized phone data:

create function fnGetDenormPhoneFromPersonID(@personID int)
returns varchar(8000)
as
begin
    declare @number varchar(8);
    declare @phoneType varchar(10);

    declare @DenormPhone varchar(8000);
    set @DenormPhone = '';

    declare MyCursor cursor for
    select number, phoneType
    from phoneNumbers
    where personID = @personID;

    open MyCursor

    fetch next from MyCursor into @number, @phoneType;

    while @@FETCH_STATUS = 0
    begin
        set @DenormPhone = @DenormPhone + @phoneType + '   ' + @number + ';'
        fetch next from MyCursor into @number, @phoneType;
    end

    close MyCursor
    deallocate MyCursor

    return @DenormPhone

end

Then use the following select:

    select  person.personID, 
    name, 
    (datediff(YY, birthdate, getdate()) -
        case
            when((month(birthdate)*100 + day(birthdate)) >
                (month(getdate())*100 + day(getdate()))) 
            then 1
        else 0
        end) as age,
    birthdate, 
    gender, 
    roomnumber,
    dbo.fnGetDenormPhoneFromPersonID(personID) as DenormPhone

from    person 

and put it into a standard table built by the report designer, with all fields in the details.

I changed the expression for Denorm Phone to

=Replace(Fields!DenormPhone.Value, ";", vbCRLF)

In SSRS 2005, Matrixes and Lists and Tables were all different things. In 2008 they merged them into a tablix. You still have all three controls but underneath they are all a tablix with different defaults.

It looks like you want to use a matrix with 3 levels.

1. Allergies
2. ID and then include the extra columns for room, gender and resident.
3. Details - Not really a group as it doesn't group on anything just for showing the remaining rows.