A more detailed report!

[Geel]

Hello, i'm building 2 sorts of reports. 1 report is a total report,
this report goes for all their partners. And then their is a report per
partner. Ofcourse a partner has an _ID and this ID is also in every
query and table where it has to be.

Now when i started building i started with the total report, dont worry
bout entry's in forms etc. they should be fine. But the first report
i've got everything worked out ( the total partner report..) And that 1
is working great.

Now i've started building the second report, per partner, and this
report is causing me headaches. Now in the first report i use the GROUP
BY statement to sort everything and to not get 2 many records. In the
second, per opco, i also use the GROUP BY statement so what i then do
is group it first on the opco_ID and then the rest that was in the
first reports group by comes after it, same goes for the query's i am
using in the rest of the report.

Now the following are my GROUP BY statements:

First (total partner report): GROUP BY Contract_savings.Contract_ID,
Menu_choice.Period_start, Menu_choice.Period_end;

Second (per partner report): GROUP BY Contract_savings.Opco_ID,
Contract_savings.Contract_ID, BenefitsTDperiodYTD.PVincrYTD,
BenefitsTDperiodYTD.PVotherYTD;


This way everything works! BUT i've got a seperated query loaded into
the second and first report, wich provides me with the total sums of 2
numbers standing next to each other.. These numbers are showed properly
in the report but the problem is I can not get the totals to be in the
correct place.. I've finnally fixed it to be in the good place in the
query! but whenever i load them into the report, cos of the GROUP by
stament, i have to give them something for being aggregate function.
And when i put them in as GROUP BY then it will cause the table with
records to explode and become to big.. And when i use the First
statement it will show only 1 total, per category.

I dont think their will be much reply's on this but maybe there will be
someone who can explain me something about ORDER and GROUP
functionallity, thx for any help anyways.. everything is much
appreciated.

 

[Geel]

Geel schreef:

Hello, i'm building 2 sorts of reports. 1 report is a total report,
this report goes for all their partners. And then their is a report per
partner. Ofcourse a partner has an _ID and this ID is also in every
query and table where it has to be.

Now when i started building i started with the total report, dont worry
bout entry's in forms etc. they should be fine. But the first report
i've got everything worked out ( the total partner report..) And that 1
is working great.

Now i've started building the second report, per partner, and this
report is causing me headaches. Now in the first report i use the GROUP
BY statement to sort everything and to not get 2 many records. In the
second, per opco, i also use the GROUP BY statement so what i then do
is group it first on the opco_ID and then the rest that was in the
first reports group by comes after it, same goes for the query's i am
using in the rest of the report.

Now the following are my GROUP BY statements:

First (total partner report): GROUP BY Contract_savings.Contract_ID,
Menu_choice.Period_start, Menu_choice.Period_end;

Second (per partner report): GROUP BY Contract_savings.Opco_ID,
Contract_savings.Contract_ID, BenefitsTDperiodYTD.PVincrYTD,
BenefitsTDperiodYTD.PVotherYTD;


This way everything works! BUT i've got a seperated query loaded into
the second and first report, wich provides me with the total sums of 2
numbers standing next to each other.. These numbers are showed properly
in the report but the problem is I can not get the totals to be in the
correct place.. I've finnally fixed it to be in the good place in the
query! but whenever i load them into the report, cos of the GROUP by
stament, i have to give them something for being aggregate function.
And when i put them in as GROUP BY then it will cause the table with
records to explode and become to big.. And when i use the First
statement it will show only 1 total, per category.

I dont think their will be much reply's on this but maybe there will be
someone who can explain me something about ORDER and GROUP
functionallity, thx for any help anyways.. everything is much
appreciated.

I think i know what i need, first of all i will GROUP the different
things in the order that it will work, Then the final minor detail is
that in the smallest groups the things aint showing up in the right
spot, this is caused because the report sorts on the projectname in an
ASCending way..

So what i need is:

GROUP BY bladibla
SORTY BY projname;

something like that, any1 who can validate, much apreciated