Databases
StackOverflow2013
Postsdbo
POSSRS 2008 and Grouping columns/rows
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSSRS 2008 and Grouping columns/rows
    primarykey
    data
    text
    <p>Matrix... tablix.... list/matrix... list/tablix... I have no idea. none of them do what I want them to do. </p> <p>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. </p> <p>The form looks like:<br> (The phone type and phone number can be divided but don't have to be, if that makes any sense.)</p> <pre> _______________________________________________________________________________ | Allergies: NKA | | | |_______________________________________________________________________________| |Resident | ID | Gender | Room | Type Number | |___________|___________|________________|____________|_________________________| | | | | | Home 555-3242 | |Kim | 123 | female | 420 | Cell 555-1111 | |___________|___________|________________|____________|__Other_________234-5554_| </pre> <p>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. </p> <p>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. </p> <p>I set up my data to look like this:</p> <pre> (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 </pre> <p>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. </p> <p>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? </p> <p>Thank you so much............ for your time, Kim </p> <p>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. </p> <p>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). </p> <p>Thanks again!</p> <hr> <p>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. </p> <p>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 response 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. </p> <pre> 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 </pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USgloomy.penguin
    1. USgloomy.penguin
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POSSRS 2008 and Grouping columns/rows
      1. This table or related slice is empty.
      1. VTModeratorReview
    2. VO
      singulars
      1. POSSRS 2008 and Grouping columns/rows
      1. This table or related slice is empty.
      1. VTUpMod
    1. COCan you provide a query? You data looks redundant. Probably you need to take a look at normalization.
      singulars
      1. POSSRS 2008 and Grouping columns/rows
      1. USuser194076
    2. CONo...... you don't get it. Sorry, I explained wrong. It is redundant, except the phone numbers, which is what need as the details and the person information is what I need to group by.
      singulars
      1. POSSRS 2008 and Grouping columns/rows
      1. USgloomy.penguin
    3. COSo.... I can join the data so it doesn't look like that but I don't see how that would help me with the grouping in SSRS, can you explain your idea for me, please, on how to re-create the form? I was trying to group by the personID/info and then use the phone numbers as the details. But if you know without joining the data that way I would like to know... I just need it to look like the form, it doesn't matter how the table is joined.
      singulars
      1. POSSRS 2008 and Grouping columns/rows
      1. USgloomy.penguin
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload