DRILLTHROUGH WHERE CONDITION FAILING

August 16, 2010, 10:22 AM

I created a 2 lvel drill through report. It runs fine when I dont use WHERE condition; but When I use WHERE in all three levels to filter to data for 2011 only. I am getting zero records back. Any Idea, what I am doing wrong?

 
SET SQUEEZE=ON
SET PAGE-NUM = OFF
TABLE FILE AGGRIGATE_ENROLLMENT_VIEW_BV01
HEADING CENTER
"Report 1"
"Enrollment By Division"
" "
SUM
     'AGGRIGATE_ENROLLMENT_VIEW_BV01.AGG_ENR.NEWENROLL'
     'AGGRIGATE_ENROLLMENT_VIEW_BV01.AGG_ENR.ENROLLEDACTIVE'
     'AGGRIGATE_ENROLLMENT_VIEW_BV01.AGG_ENR.WITHDRAW'
BY 'AGGRIGATE_ENROLLMENT_VIEW_BV01.SCHOOL.DIVISIONID'
WHERE FY EQ '2011'

ON TABLE PCHOLD FORMAT PDF OPEN

ON TABLE SET STYLE *
TYPE=REPORT, FONT=ARIAL, $
TYPE=DATA, TOPGAP=0.1, $
TYPE=DATA, COLUMN=DIVISIONID, DRILLTHROUGH=DOWN(DIVISIONID), $
END


TABLE FILE AGGRIGATE_ENROLLMENT_VIEW_BV01
HEADING CENTER
"Report 2"
"Enrollment By DISTRICT"
" "
SUM
     'AGGRIGATE_ENROLLMENT_VIEW_BV01.AGG_ENR.NEWENROLL'
     'AGGRIGATE_ENROLLMENT_VIEW_BV01.AGG_ENR.ENROLLEDACTIVE'
     'AGGRIGATE_ENROLLMENT_VIEW_BV01.AGG_ENR.WITHDRAW'
BY 'AGGRIGATE_ENROLLMENT_VIEW_BV01.SCHOOL.DIVISIONID' NOPRINT
BY DISTRICTID
WHERE FY EQ '2011'

ON TABLE PCHOLD FORMAT PDF OPEN
ON DIVISIONID PAGE-BREAK
ON TABLE SET STYLE *
TYPE=REPORT, FONT=ARIAL, $
TYPE=DATA, TOPGAP=0.1, $
TYPE=DATA, COLUMN=DISTRICTID, DRILLTHROUGH=DOWN(DISTRICTID), $
END

TABLE FILE AGGRIGATE_ENROLLMENT_VIEW_BV01
SUM
     'AGGRIGATE_ENROLLMENT_VIEW_BV01.AGG_ENR.NEWENROLL'
     'AGGRIGATE_ENROLLMENT_VIEW_BV01.AGG_ENR.ENROLLEDACTIVE'
     'AGGRIGATE_ENROLLMENT_VIEW_BV01.AGG_ENR.WITHDRAW'
BY 'AGGRIGATE_ENROLLMENT_VIEW_BV01.SCHOOL.DIVISIONID'
BY 'AGGRIGATE_ENROLLMENT_VIEW_BV01.SCHOOL.DISTRICTID'
BY 'AGGRIGATE_ENROLLMENT_VIEW_BV01.SCHOOL.SCHOOLID'
BY 'AGGRIGATE_ENROLLMENT_VIEW_BV01.TIMEPERIOD.FYPW'
WHERE FY EQ '2011'

ON DISTRICTID PAGE-BREAK
HEADING CENTER
"Report 3"
"Sales Detail Report"
" "
"Category: Product: " "

ON TABLE PCHOLD FORMAT PDF CLOSE
ON TABLE SET STYLE *
TYPE=DATA, TOPGAP=0.1, $
TYPE=REPORT, FONT=ARIAL, $
TYPE=HEADING, LINE=4, ITEM=2, $
TYPE=DATA, COLUMN=DIVISIONID, DRILLTHROUGH=FIRST(DIVISIONID), COLOR=GREEN, $
END

August 16, 2010, 12:45 PM

Francis Mariani

Do I have to ask if there actually is data for 2011? What is the format of field FY?

Also, in a compound PDF report, you should only have one OPEN and one CLOSE - you have two OPEN statements.

quote:
OPEN - Is specified with the first report, and begins the concatenation process. A report that contains the OPEN attribute must be PDF or PS format.

CLOSE - Is specified with the last report, and ends the concatenation process.

NOBREAK - Is an optional phrase that suppresses page breaks. By default, each report is displayed on a separate page.

You can use NOBREAK selectively in a request to control which reports are displayed on the same page.

August 16, 2010, 04:16 PM

Arif

Thank you for helping Francis, DATA TYPE is A4 for FY. I realized that the OPEN and CLOSED should be used only once in whole procedure. HOwever, i am still not able to create 3 level drill.

August 16, 2010, 04:18 PM

Francis Mariani

Is there data for 2011?

August 17, 2010, 09:02 AM

Arif

quote:
Posted August 16, 2010 04:18 PM Hide Post
Is there data for 2011?

Francis

YEs there is data available for 2011. Our FY started on July 1st. So, there are about 11000 records for 2011.