Forcing data to be pulled only from maximum (latest) record via filters

[mbowes80]

I was wondering if someone could help me find the most efficient way to go about this. We have a form that contains academic data for students that I often need to query. When a student's information is changed, a whole new record is created and then modified... thus, at any given time, only the most recent record contains the correct data. Therefore, to get current data, I've had to find a way to always start by indicating that the most recent record has to be used before any other filters are addressed.

Currently I'm doing something like this:  [Academic Period] = maximum ([Academic Period] for [ID])
I do this filter first set to 'Before auto aggregation', then all other filters (if pulling data from this same view) have to be set to 'After auto aggregation,' or it doesn't seem to work.

It gets more complicated when combining data from this and another view, as I must first do a query containing these filters, then do another query where i start by saying:  [ID] in ([Query1].[ID])
This seems to be the only way to make it work... I've tried using a Join, but there doesn't seem to be any way to force it to perform the filters on the individual queries before performing the Join, so I get too much data.

Anyhow, the above approaches are working, but obviously they add significant time to the reports. Does anyone know of a better way to approach this??

Thanks!

[cognostechie]

I don't know what kind of field is [Academic Period]. If it's a Date, this is what you could do in Framework Manager:

Create a Query subject with this kind of SQL:

Select Student_ID,
          max(Academic Period) as 'Most Recent Record'
from Table Name

Join this to your original Student Query subject with Student_ID on 1:1 basis.

Create a Model Query subject and include items from both the Query subject. Create
a filter to force the join (Student Query Subject.Student_ID = New Query Subject.Student_ID)

Now you have a Query subject that would only show the most recent record for all students.
Use this in the report and other items from other query subjects. It would work because you
already muct be having joins between Student Query subject and other query subjects.

[cognostechie]

Missed out one thing -

The join and filter should be on Student_ID and Academic Period both.

[mbowes80]

Thanks for responding... your idea makes sense. Unfortunately I don't have access to Framework Manager, as I'm in more of a 'functional' department, and out IT dept. sets all that up on the back end. So I'm trying to figure out the most efficient way to do it from the Report Studio interface (if there even is a good way...)

[blom0344]

Any solution from within Report Studio (either by applying auto-joins, complex filters or using ranking) will have possible performance drawbacks. The solution should come from the backend.. The student information table should contain 2 typical dates, a start and enddate for a given record.  Generating a 'new' record requires 'enddating' the previous one. This way one can select the most 'recent' record by filtering the empty enddate fields.

You should question whoever set up the model. Perhaps the date is available but has not been added to the model itself?

[mbowes80]

Well... the interesting thing with that is that there's not actually an end term on these records, they just automatically are considered cut off when they next record begins. So they'd have to create some sort of function to find the effective end term, and then limit the records to those with an end term of 999999. Regardless, something on the back end would be an ideal solution... part of the reason I'm not pushing the issue is that soon I'll be able to query data from another system we are implementing, which automatically takes these (and other) factors into account to find the correct information.

On the subject of a Report Studio level hack, though, I did find a way to speed up the process... rather than filtering for [academic period]=maximum ([academic period] for [id]), I just created a data item using an if-then statement to mark the maximum records 'Y' and the others 'N', then filtered for the 'Y' records. Not sure why, but it works much faster, and seems to be just as accurate.

[rockytopmark]

Along the lines of CognosTechie's original suggestion but within the report, I have found a separate query in the report that is solely used to return the "maximum" record for each parent and then a join query to marry the original query with the maximum query, inner joined, to work sufficiently. 

As Blom wrote, the back end is best.  We have been adding indicators for this very same scenario on my current project which can then be used in simple detail filters MUCH more efficiently.

HTH... M

[mbowes80]

Along the lines of CognosTechie's original suggestion but within the report, I have found a separate query in the report that is solely used to return the "maximum" record for each parent and then a join query to marry the original query with the maximum query, inner joined, to work sufficiently. 

As Blom wrote, the back end is best.  We have been adding indicators for this very same scenario on my current project which can then be used in simple detail filters MUCH more efficiently.

HTH... M

Thanks... can you tell me how you set up the Join? I tried to do something similar, but was unsuccessful. It's possible I didn't set up the 'cardinality' options correctly in the Join dialog.

[blom0344]

Join over both Student Id and Academic Periodic and ensure it is a inner join. (1:1 <--> 1:1)

In SQL it would be along following lines:

Code Select Expand



SELECT FACT.STUDENT_ID,FACT.SOMEITEM,............................
FROM
SOMETABLE FACT
INNER JOIN
(SELECT FACT2.STUDENT_ID ,MAX(FACT2.ACADEMIC_PERIOD) AS
MAX_PERIOD FROM SOMETABLE FACT2) TEMP
ON
FACT.STUDENT_ID = TEMP.STUDENT_ID AND
FACT.ACADEMIC_PERIOD = TEMP.MAX_PERIOD


[mbowes80]

Join over both Student Id and Academic Periodic and ensure it is a inner join. (1:1 <--> 1:1)

In SQL it would be along following lines:

Code Select Expand



SELECT FACT.STUDENT_ID,FACT.SOMEITEM,............................
FROM
SOMETABLE FACT
INNER JOIN
(SELECT FACT2.STUDENT_ID ,MAX(FACT2.ACADEMIC_PERIOD) AS
MAX_PERIOD FROM SOMETABLE FACT2) TEMP
ON
FACT.STUDENT_ID = TEMP.STUDENT_ID AND
FACT.ACADEMIC_PERIOD = TEMP.MAX_PERIOD


Thanks! I think before I was just joining on ID, which is probably why it didn't work. This does work, if a bit slow, like the other methods I've tried. Guess I will have to make due until I can get them to add some sort of item that will allow me to filter for these more efficiently... end term, indicator, or something.