Group By on report not functioning correctly.

[mcupito]

I have a query that retrieves records based on a Start Date and an End Date that the user specifies on a form.

The query retrieves results like this:

PlanDesc  AwardDate  AwardUnits  ForfeitedUnits  PaidOutUnits  AwardID  PlanID
LTIR 1     3/1/2012     5.00        0.00             0.00     1627727365    1
LTIR 1     3/1/2012     5.00        5.00             0.00    -1610671448    1
LTIR 1     3/1/2012     8.00        0.00             0.00     1015507630    1
LTIR 1     3/1/2012    18.00       14.40             0.00     2081999682    1
LTIR 2     3/1/2012     2.00        0.00             0.00     1723620712    2
LTIR 2     3/1/2012     3.00        0.00             0.00    -1915096017    2
Field      5/1/2012     2.00        2.00             0.00     1462793196    3
LTIR 1     3/1/2013     6.00        0.00             0.00     -699388702    1
LTIR 1     3/1/2013     7.00        0.00             0.00      321262743    1
LTIR 2     3/1/2013     2.00        0.00             0.00    -1660429495    2
LTIR 2     3/1/2013     2.00        0.00             0.00     1318429519    2

I am trying to get the results to Group By Year, then Group By PlanDesc, and show the sum of AwardUnits and sum of ForfeitedUnits (per plan, per year).

Does anyone have any experience with something like this?

I am trying to get it to look like:

Year
2012
          PlanDesc      Forfeited Units    Awarded Units
           LTIR 1           19.4                36 
           LTIR 2            0                   5
           Field             2                   2

How the report currently looks: (See attachments)

Currently, the report is giving me incorrect data. I suppose I can always send someone a copy if need be. I feel like there is a problem with the Group By or something.

Thanks for any help.
[IMGNOTHUMB]http://bytes.com/attachments/attachment/7582d1397159504/forfeitrpt.jpg[/IMGNOTHUMB]
[IMGNOTHUMB]http://bytes.com/attachments/attachment/7583d1397159581/forfeitdesign.j pg[/IMGNOTHUMB]

[jimatqsi]

mcupito,
Have you sent the PlanDesc header and detail areas to be invisible? If so, why not turn them visible and see what the detail looks like.

It's not at all obvious why LTR1 does not appear in the main report. The answer could be in the record source. Perhaps you should view the query of each report and compare them for results.

Jim

[mcupito]

Hey Jim, thanks for the comment! The data I posted is the result set once the query is ran. There really is nothing special, just a simple select statement. I think I actually found out how to correct it.

Here is the link I am following (it's taking some tweaking) http://office.microsoft.com/en-us/ac...010006894.aspx

[jimatqsi]

You posted the result from only one query. Are you saying both the report and sub-report use the same query? It seems unlikely. You should probable drill down to the query results through the Data property of each report and double-check like that.

But I don't think grouping explains the missing data. What have you changed? Are those two sections invisible?

Jim

[mcupito]

Yes, the other query is literally just a Sum of the fields. Nothing more (it has nothing to do with my problem also).

As far as the missing data, to be honest I have no clue what caused it. The query returned the data fine.

I went back and only grouped by the Year of the Award Date for the report, I summarized the Forfeited and Awarded Units and it now accurately reflects the data.

When I checked the visibility, it actually was set to "No", however when I set it to "Yes" the report gave me some interesting results, so that's when I decided to follow the MSDN documentation.

I hope that answered your question.

Thanks for helping me!

[anvidc]

Hi, Not sure it work like this...
remove the 'PlanDesc' on PlanDesc Footer.
on the PlanDesc Header add a textbox = to the textbox for the sum on the Footer..