More grouping Woes!

I have a result set of consisting of names and addresses. each person at a
particular address (1-n people) gets an identical groupid to group them in a
"Family".
My report lists each column, groupid, name, address, with one row for each
person. I would like for it to group on the groupid, then list each person in
a subgroup sorted by name.
When I set a "group" row on my report and group on the groupid, all i get is
the first row of the family, and the rest of the data is dropped. how do i
get the group to show all the data? I am using RS2005 with VS2005.
thank you in advance for your help!!I ran into a similar problem a little while ago. I had numerical data based
on the # of hours worked and then a description of what was done in those
hours. I grouped on the person and date and then summed the hours they
worked for that span of time so my output looked something like:
John Doe
11/12/2006
Regular OT Description
On-Site 8 0 Worked on
server.
Remote 0 2
However the description for the remote would not print out, even though the
output was =fields!onsitenotes.value & fields!remotenotes.value, because they
were in the same group
What I wound up doing was setting a different group underneath the name/date
for each row of data(on-site, remote) I'm not sure if you will be able to do
exactly that but I basically had to assign some flags based on the work-type
(on-site, remote, etc) in the query and then group on those flags.
So it wound up being something like:
Group1(
John Doe
11/12/2006
Regular OT
Description
Group2 ( On-Site 8 0 Worked
on server.)
Group3 ( Remote 0 2 Helped on
the phone.)
)
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200612/1

More grouping questions - sum of maximum

I have a table with two groups, unit and domicile. Structure appears as follows:

GH1 - Domicile Group: Bentwood
GH2- Unit Group: 200
Detail: LegID Unit ID Miles TripMiles
1 200 25 150
2 200 75 150
3 200 50 150

Subtotal for GH2 Count(Fields!legID.value) Sum(Fields!Miles.value) First(Fields!TripMiles.value)
3 150 150

This works, what breaks is the subtotal for GH1. on the TripMiles Field.
It doesnt like Sum(First(Fields!Tripmiles.value,"Unit_Group")), nor can I do Sum(ReportItems!Trip_group.value) I get the error that an aggregate cannot occur on a report item unless it is in the page header or footer.

Of course if I try Sum(Fields!TripMiles.value,"Domicile_Group") I get 450 which is incorrect.

I've tried making First(fields!tripmiles.value) a calculated field which I just then drop into the header of the Unit group<GH2>. This works perfectly until I then try to Sum that field in the Domicile group. I can create the expression but once I click ok, VS2005 reports that it must shut down and crashes to desktop, when I do a recovery all my changes are lost.

<by the way this crash always occurs when I create a calculated field that uses First/Max or Min, then try to use that field in another calculation. Fields based on IIF work without issue>

To clarify, I want to take the TripMiles Column and for The Unit group show the first value that occurs for that unit as the value.

In the Domicile Group, I want to sum all of those Unit Values.
if unit 200 had 3 occurances of 150, my Unit group would have a value of 150 <not 450>
if Unit 300 had 4 occurances of 100, my Unit group would have a value of 100 <not 400>
My total for the Domicile would be 250 <not 850>

Thank you for any assistance.

I can't think of a way of doing this but surely in your case the subtotal at the Domicile level for TripMiles column should equal that of the Miles column.

I would cheat and just put Sum(Fields!Miles.Value) in the subtotal for the TripMiles column in the Domicile group footer.

|||

Bah I jinxed myself using a perfect scenario in that mielage column didnt I <sigh>

Yes I agree with you in this particular instance with the data I displayed. TripMiles never change, it is a base amount for the order. I should have added two more rows to show that the two columns do not always match.

Let's pretend that the TripColumn was labeled something else, Like fuel Consumed.

If I could do an aggregate on the Max/min/first of that field I would be set :(

Edit: OK, I found another thread similar to my situation here :http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=106424&SiteID=1

Where it is stated that at the time that post was made, One year ago last week, you could not do an aggregate of an aggregate and gave a work expression that is one I've tried but errors out.

They then stated to 'modifey' the query to insert nulls at the duplicate. So in my example I'd have
150
null
null
null

How do I do that?

|||

You can use the SQL 2005 ROW_NUMBER() aggreagate function as follows:

SELECT LegID
, UnitID
, Miles
, TripMiles = CASE ROW_NUMBER() OVER (PARTITION BY LegID ORDER BY LegID) WHEN 1 THEN TripMiles ELSE NULL END
FROM YourTable

More grouping questions - sum of maximum

I have a table with two groups, unit and domicile. Structure appears as follows:

GH1 - Domicile Group: Bentwood
GH2- Unit Group: 200
Detail: LegID Unit ID Miles TripMiles
1 200 25 150
2 200 75 150
3 200 50 150

Subtotal for GH2 Count(Fields!legID.value) Sum(Fields!Miles.value) First(Fields!TripMiles.value)
3 150 150

This works, what breaks is the subtotal for GH1. on the TripMiles Field.
It doesnt like Sum(First(Fields!Tripmiles.value,"Unit_Group")), nor can I do Sum(ReportItems!Trip_group.value) I get the error that an aggregate cannot occur on a report item unless it is in the page header or footer.

Of course if I try Sum(Fields!TripMiles.value,"Domicile_Group") I get 450 which is incorrect.

I've tried making First(fields!tripmiles.value) a calculated field which I just then drop into the header of the Unit group<GH2>. This works perfectly until I then try to Sum that field in the Domicile group. I can create the expression but once I click ok, VS2005 reports that it must shut down and crashes to desktop, when I do a recovery all my changes are lost.

<by the way this crash always occurs when I create a calculated field that uses First/Max or Min, then try to use that field in another calculation. Fields based on IIF work without issue>

To clarify, I want to take the TripMiles Column and for The Unit group show the first value that occurs for that unit as the value.

In the Domicile Group, I want to sum all of those Unit Values.
if unit 200 had 3 occurances of 150, my Unit group would have a value of 150 <not 450>
if Unit 300 had 4 occurances of 100, my Unit group would have a value of 100 <not 400>
My total for the Domicile would be 250 <not 850>

Thank you for any assistance.

I can't think of a way of doing this but surely in your case the subtotal at the Domicile level for TripMiles column should equal that of the Miles column.

I would cheat and just put Sum(Fields!Miles.Value) in the subtotal for the TripMiles column in the Domicile group footer.

|||

Bah I jinxed myself using a perfect scenario in that mielage column didnt I <sigh>

Yes I agree with you in this particular instance with the data I displayed. TripMiles never change, it is a base amount for the order. I should have added two more rows to show that the two columns do not always match.

Let's pretend that the TripColumn was labeled something else, Like fuel Consumed.

If I could do an aggregate on the Max/min/first of that field I would be set :(

Edit: OK, I found another thread similar to my situation here :http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=106424&SiteID=1

Where it is stated that at the time that post was made, One year ago last week, you could not do an aggregate of an aggregate and gave a work expression that is one I've tried but errors out.

They then stated to 'modifey' the query to insert nulls at the duplicate. So in my example I'd have
150
null
null
null

How do I do that?

|||

You can use the SQL 2005 ROW_NUMBER() aggreagate function as follows:

SELECT LegID
, UnitID
, Miles
, TripMiles = CASE ROW_NUMBER() OVER (PARTITION BY LegID ORDER BY LegID) WHEN 1 THEN TripMiles ELSE NULL END
FROM YourTable

|||what other solution, if i'm using SQL 2000? what other method to achived the above reasult?|||

Code Block

SELECT LegID
, UnitID
, Miles
, TripMiles = CASE LegID

WHEN (SELECT MIN(LegID) FROM YourTable)

THEN TripMiles

ELSE NULL

END
FROM YourTable

Month Format

Hello Experts!

I am trying to format the datetime field in my grouping. Right now I am using the expression =Month(Fields!TestDateTime.Value) in the edit group properties and it returns (for example) May 01 for the group. I would like it to show up as May 2007 instead. Would someone be kind enough to post what the correct expression should be?

Thanks,

Clint

Not sure if there is a better way, but this works:

Code Snippet

=MonthName(Month(Fields!DateOrderCreated.Value)) + " " + format(Fields!DateOrderCreated.Value,"yyyy")

|||Thanks, but that also does not work. It returns the same "May 01".|||What datatype is the field you are formatting?|||It is a datetime data typ|||

If it is a datetime data type, then you can use Format() on it using any of the standard formatting codes, like this:

Code Snippet

=Format(myDataField, "Mon yyyy")

|||Hmmmm still not working. What area should the code be put in to? I have tried the group properties, the field property value, and the custom format are of the field to no avail. It will either error out or return the same "May 01". I am working from the report layout area of the reporting services.|||LOL. Found the answer. Simply in the Format area, I just put a "Y" in the custom format field. thanks so much for your help though.