How to combine report-as-filter and metric qualifier

[dlutzker]

I am working on a report that shows students names, the total number of classes they passed, and the date of the last class each passed. These are students who have not graduated, and the way I accomplished that is by including a report as filter, the sub-report being a report of graduates and the program they graduated. The [highlight]Count of Classes Passed[/highlight] so far is accurate. I cannot get the latest date of a passed class on this report without messing up the metric [highlight]Count of Classes Passed[/highlight].

I am able to get the date of the last exam on a separate report, because we have added a sequence number in the fact table: an integer in order of the date of the class, for each student in each program. I created a metric for the sequence number, and a filter qualifying on that metric, making the output level the set of Program, Student and Exam Date, and ranking by the top 1 value.

Can anybody come up with a way to display these together on one report? I can't change the metric settings of the [highlight]Count of Classes Passed[/highlight] to ignore the report filters, because it needs the Report as Filter. Without the filter that gives me the latest exam date, I get a row for every class passed, with counts = 1. With the filter I get no results, if it finishes before timing out.

Any ideas?

 

[dlutzker]

I have continued to work on this, and tried many combinations involving the advanced filtering and dimensionality of both metrics, and set outputs and break bys in the metric qualification filter.

The problem that continues to haunt me is that the sub-report is not interacting with the metrics the way I need it to. Here is some of the code:

create table ZZMQ00 nologging as
select a11.PROGRAM_DIM_ID  PROGRAM_DIM_ID,
	a11.CONTACT_LU_ID  CONTACT_LU_ID,
	count(distinct a11.COMPLETER_FACT_ID)  WJXBFS1
from SDW_COMPLETER_FACT a11
where a11.PROGRAM_DIM_ID in (210)
group by a11.PROGRAM_DIM_ID,
	a11.CONTACT_LU_ID 

create table ZZMD01 nologging as
select a11.CONTACT_LU_ID  CONTACT_LU_ID,
	a11.PROGRAM_DIM_ID  PROGRAM_DIM_ID,
	a13.REGION_LU_ID  STUDENT_REGION_LU_ID,
	a11.EXAM_TIME_DIM_ID  EXAM_TIME_DIM_ID,
	max(a11.TAKEN_SEQUENCE)  WJXBFS1
from SDW_ORDERED_EXAM_FACT_SS a11, 
	SDW_EXAM_CENTER_DIM a12, 
	SDW_EXAM_STATE_LU_VW a13, 
	SDW_CONTACT_LU a14
where a11.EXAM_CENTER_DIM_ID = a12.EXAM_CENTER_DIM_ID and 
	a12.STATE_LU_ID = a13.STATE_LU_ID and 
	a11.CONTACT_LU_ID = a14.CONTACT_LU_ID
 and	[highlight](([b]not[/b] ((a11.PROGRAM_DIM_ID)[/highlight]
 in	(select c22.PROGRAM_DIM_ID
	from SDW_CONTACT_LU c21, 
		SDW_PROGRAM_DIM c22
	where ((c21.CONTACT_LU_ID,
		c22.PROGRAM_DIM_ID)
	 in	(select pa1.CONTACT_LU_ID,
			pa1.PROGRAM_DIM_ID
		from ZZMQ00 pa1)))))
 and a11.PROGRAM_DIM_ID in (210)
 and a11.CONTACT_LU_ID <> 0
 and a14.ACTIVE_FLAG = 1
 and a13.REGION_LU_ID in (61))
group by a11.CONTACT_LU_ID,
	a11.PROGRAM_DIM_ID,
	a13.REGION_LU_ID,
	a11.EXAM_TIME_DIM_ID

The WHERE clause includes the sub-query with a NOT operator, but it is operating on only one attribute, Program_Dim_ID. It should be operating on both [highlight]Program_Dim_ID AND Contact_LU_ID[/highlight]

I have put the subreport in the metric qualification - same results.
I have removed Program from the sub-report (leaving a prompt for Program) - the temporary table it builds is ignored by the rest of the report.
Any ideas??