We recently upgraded our environment to 7.7.03 from 7.6.10. We noticed today that all the library reports are miscalculating subtotals and providing column totals instead of subtotals. has anyone seen this issue before?

This message has been edited. Last edited by: Kerry, November 16, 2011 02:38 PM

I would create a simple report with subtotals using the CAR file to see if the issue is subtotals or something else. The opposite of the process of elimination: start with the minimum, then add more options until it breaks.

I really don't see anything "fishy" in your code except for the fact that you're using a SUBTOTAL instead of a RECOMPUTE so I am not sure that the total values you'll get for M, T, W, TH and F are correct.

My only concern is that if the very same code gives you different results in 7.6.10 vs. 7.7.03 then something is not adding up (so to speak). Hopefully there are no hidden SET'tings doing magic behind the scenes.

I am sorry i should have mentioned that I have tried both RECOMPUTE and subtotals but when i caster and burst this reprot, i am getting grand total of the einter column (company total) not sub totals.

so here is brief summary of the problem:

When i run this report live, everything works fine. I get subtotals by district.

when i run this report using report caster, I get company totals in subtoal section after every district. i have tried to reproduce this problem using the car file but i am not able to reproduce this problem using the car file because everything seems to be working when i use car file (subtotals and recomputes)

I noticed a &BURST variable in your code. Are you using the Burst functionality in Report? Which value are you passing to &BURST when you run the schedule?

If you are indeed using burst reports, you may be getting just one "piece" of it and that SUBTOTAL you are seeing may look like a report total just because that's all that shows up for the particular BURST value in place. Just something to keep in mind; it may not be the case at all.

That's why I suggested that you enabled -SET &ECHO=ON and showed us the code with all Dialogue Manager stuff out of the way. It may be a bit easier to debug that way (the style sheet declaration adds no value either).

Yes I am bursting this report by DistrictID and Car file code works great when i burst call file with following code. Both recompute and subtotal work in car file

DEFINE FILE CAR
DISTRICTID/A5 = IF COUNTRY EQ 'ENGLAND' THEN '8165' ELSE
IF COUNTRY EQ 'FRANCE' THEN '8170' ELSE
IF COUNTRY EQ 'ITALY' THEN '8176' ELSE
IF COUNTRY EQ 'JAPAN' THEN '3021' ELSE '8000';
END
TABLE FILE CAR
SUM
CAR.BODY.DEALER_COST
CAR.BODY.RETAIL_COST
BY &BURST NOPRINT
BY CAR.ORIGIN.DISTRICTID
BY CAR.ORIGIN.COUNTRY
BY CAR.COMP.CAR
ON CAR.ORIGIN.DISTRICTID RECOMPUTE AS '*TOTAL'
ON TABLE SET BYDISPLAY ON
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT PDF
END

I still dont see anything in the trace that can cause this issue. It seems like some problem in the report caster.

Schedule Executed Due To NEXTRUNTIME at C15088IBIPROD:8200
Starting task: Task 1
Task type: MR Standard Report
Task domain: develope/develope.htm
Retrieving MR report: app/car
Connecting to server EDASERVE with execution id ibiadmin
Executing focexec.
JOIN
STAGE_CHILDDETAIL_POS.STAGE_CHILDDETAIL_POS.SCHOOLID IN STAGE_CHILDDETAIL_POS
TO UNIQUE DIM_SCHOOL.DIM_SCHOOL.SCHOOLID IN DIM_SCHOOL TAG J0 AS J0
END
JOIN
STAGE_CHILDDETAIL_POS.STAGE_CHILDDETAIL_POS.WEEKENDINGDATE IN
STAGE_CHILDDETAIL_POS TO UNIQUE DIM_TIME.DIM_TIME.DAYBEGINTIME IN DIM_TIME
TAG J1 AS J1
END
TABLE FILE STAGE_CHILDDETAIL_POS
SUM
'STAGE_CHILDDETAIL_POS.STAGE_CHILDDETAIL_POS.ENROLLEDACTIVE' AS 'Active'
'STAGE_CHILDDETAIL_POS.STAGE_CHILDDETAIL_POS.ENROLLEDUNSCHEDULED' AS 'Unschded'
'STAGE_CHILDDETAIL_POS.STAGE_CHILDDETAIL_POS.NEWENROLL' AS 'New'
'STAGE_CHILDDETAIL_POS.STAGE_CHILDDETAIL_POS.WITHDRAW' AS 'Termed'
'STAGE_CHILDDETAIL_POS.STAGE_CHILDDETAIL_POS.TOTALFTEWEEK/P8.1' AS 'FTE'
BY DISTRICTID NOPRINT
BY HIGHEST 1 WEEKENDINGDATE NOPRINT
BY 'J0.DIM_SCHOOL.DIVISIONNAME' AS 'Division'
BY 'J0.DIM_SCHOOL.DISTRICTNAME' AS 'District'
BY 'STAGE_CHILDDETAIL_POS.STAGE_CHILDDETAIL_POS.SCHOOLID' AS 'School'
ON DISTRICTID SUBTOTAL
HEADING
FOOTING
WHERE J0.DIM_SCHOOL.SCHOOL_STATUS IN ('I','O');
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT PDF
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
UNITS=IN,
SQUEEZE=ON,
ORIENTATION=LANDSCAPE,
TYPE=REPORT,
GRID=OFF,
FONT='ARIAL',
SIZE=9,
ENDSTYLE
END
SET DISTRIBUTE=OFF
WARNING. NO TEXT SUPPLIED BELOW SUBHEAD OR SUBFOOT
WARNING. NO TEXT SUPPLIED BELOW SUBHEAD OR SUBFOOT
0 HOLDING PDF FILE ON PC DISK ...
Task finished.
Value: 8151 distributed to ReportLibrary (R16dmfp3lt54_31) with the group id R16dmfp3lt54 at the version 31
Value: 8152 distributed to ReportLibrary (R16dmfp3lt55_31) with the group id R16dmfp3lt55 at the version 31
Value: 8153 distributed to ReportLibrary (R16dmfp3r856_31) with the group id R16dmfp3r856 at the version 31
Value: 8154 distributed to ReportLibrary (R16dmfp3t757_31) with the group id R16dmfp3t757 at the version 31
Value: 8155 distributed to ReportLibrary (R16dmfp3t758_31) with the group id R16dmfp3t758 at the version 31
Value: 8156 distributed to ReportLibrary (R16dmfp3tn59_31) with the group id R16dmfp3tn59 at the version 31
Value: 8157 distributed to ReportLibrary (R16dmfp3tn60_31) with the group id R16dmfp3tn60 at the version 31
Value: 8159 distributed to ReportLibrary (R16dmfp3tn61_31) with the group id R16dmfp3tn61 at the version 31
Value: 8161 distributed to ReportLibrary (R16dmfp3v662_31) with the group id R16dmfp3v662 at the version 31
Value: 8162 distributed to ReportLibrary (R16dmfp3v663_31) with the group id R16dmfp3v663 at the version 31
Value: 8163 distributed to ReportLibrary (R16dmfp3vl64_31) with the group id R16dmfp3vl64 at the version 31
Value: 8164 distributed to ReportLibrary (R16dmfp3vl65_31) with the group id R16dmfp3vl65 at the version 31
Value: 8165 distributed to ReportLibrary (R16dmfp3vl66_31) with the group id R16dmfp3vl66 at the version 31
Value: 8168 distributed to ReportLibrary (R16dmfp40567_31) with the group id R16dmfp40567 at the version 31
Value: 8170 distributed to ReportLibrary (R16dmfp40568_31) with the group id R16dmfp40568 at the version 31
Value: 8173 distributed to ReportLibrary (R16dmfp40569_31) with the group id R16dmfp40569 at the version

Arif, the code you are displaying in the report caster log is not consistent with the one you originally posted. Well, that's beyond the point. Could you put the result set you are getting with one of the reports (hiding any meaningul data of course) in comparison to what you expect to see as a result given the same data?

Arif, if you cannot replicate this behaviour with the CAR table (or any other IBI-supplied file) I don't think we will be able to do it either and see the wrong results you describe. I would advise that you contact Tech Support on this one and let us know what the outcome is.

Problem solved.It was by HIGHEST 1 WEEKENDING DATE;I had to take the qweekending value in variable and use where statement for caster jobs. I had to do it for several reports that were used to burst at different levels.