Comparing two rows in a report

building reports desktopintelligence
1

Sorry if this is a stupid question, but…

I’m trying to build a report that highlights changes between consecutive records in a database, i.e. rows in the report.

The application is a student records database, and the table concerned contains a student number, an academic year and a course code (each student has multiple records in this table, effectively one for each year they attend).

What I’m trying to do is report only students who have changed course between one year (specified by a prompt) and the previous year.

Not at all sure how to go about this…

mjmooney (BOB member since 2007-03-27)

2

Welcome to B:bob:B!

Its not a stupid question! If you are limited to doing this in a report then look at the Previous() function and see how you get on with that (if you had access to the universe then depending on your database you might be able to use functions like lead or lag (oracle) to achieve the same result).

Nick Daniels :uk: (BOB member since 2002-08-15)

3

Thanks for the prompt reply - I’ll have a look at that.

EDIT: OK, I can now get the current course code and the previous course code on the same row. So I end up with a report something like:

123456 Bloggs, Joe
YEAR CURRENT COURSE PREVIOUS COURSE
2008/9…Engineering…Engineering
2009/0…Engineering…Engineering

654321 Smith, Fred
YEAR CURRENT COURSE PREVIOUS COURSE
2008/9…Engineering…Engineering
2009/0…Physics…Engineering

But I have no interest in Joe Bloggs - he hasn’t changed course. All I want to see in the report is Fred Smith, who has changed from Engineering to Physics. And I just want the one row:

YEAR CURRENT COURSE PREVIOUS COURSE
2009/0…Physics…Engineering

So how do I filter the results to effect this?

mjmooney (BOB member since 2007-03-27)

4

Hi mjmooney,

Try this.

Create a vaiable in the report.

Test = IsNull([PC] Where (Match([CC];[PC])))

Then Apply Ranking on CC coulmn in the report as
Bottom 1
Based on [Test]

Also uncheck For Each with calculation mode Count.

Let me know if you have any issues in implementing this.

Rakesh_K :india: (BOB member since 2007-12-11)

5

If you’re writing the report in SQL, it would be straightforward to limit your results in the retrieval rather than creating variables in the report - let me know if so and I’ll put some example code together for you. Out of interest, what student database are you using?

Olly :uk: (BOB member since 2008-01-24)

6

I have a similar need - two separate projects that I am using BusObj on.
They may have simple answers that I am just not seeing -

  1. Three variables with seven possible outcomes for each subject:
    Joe Blue
    Joe Red
    Joe Black
    Pete Blue
    Sam Red
    Sam Black
    Mary Blue
    Mary Black
    I want to identify the people with Red and Black only.
  2. An event with four possible outcomes, 1-4, based on a date/time. I want to eliminate all rows in an event that do not have a 4, even if they have a 1, or a 2, or a 3. I only want events that have at least a 1 and a 4, and the 2’s and/or 3’s if they exist. The second part of this one is I want to exclude rows in the event that occur after the 4, but keep the rest.

scottyochim (BOB member since 2007-09-10)

7

Put a filter for Red and Black. :lol:

Rakesh_K :india: (BOB member since 2007-12-11)