Selecting Specific Records for a Report

[Former Member]

I have a report that requires 16 unique records to be displayed when the report is run. The table holds thousands of records but the Daily Report only wants the records that have 16 unique entries for that specific period. Is it possible to select these records only ? Cannot be by date because the entries could have been made anytime during the past 24 hours.

Ron Smith

[Former Member · Accepted solution]

Ron,

This entirely depends on whether your db is designed to capture unique records.

You stated the 24 hour condition.

You implied that all the data is coming from one table.

You'll need to describe

-how many tables

-what the links/constraints are

-any other info you can give

Have you played around with this in a query builder?

Has this report ever been created before, meaning, do you know it's possible

with the present db structure?

Can you add new tables/views to the db?

The Panda

[Former Member · Accepted solution]

The Panda,

The data is in 1 table

I can get the latest reading using max(readingfield)

The unique records are the names of the 16 Meters for the Report

It is a new Report

I can add Tables and Views

So I need the 16 unique records with the latest readingdate

Ron

[Former Member · Accepted solution]

Hi Ron,

Can you filter it with your max(readingfield)

and, perhaps, a case statement.

Select Tabel.Meter

Case "West" : "A''

Case "East" : "B"

or use Wallie's idea of an "in" statement

Maybe you can use max(readingfield) in a sub select

of the where clause.

where table.date = (select max(readingfield) from table)

or, if each meter has a different max(readingfield) , then pull each one

in separately.

or, create a view that pulls in the TOP 16 meters and link that

to the table. This should work if all else fails.

I hope this helps,

The Panda

[former_member230846]

Hi Ron,

If you know which values are required to be displayed, you could hard-code the values in the record selection formula (Report|Edit Selection Formula|Record...).

{database.field} in [value1, value2, value3, etc...]

However, if those values consistently change it will require you to update those values.

Regards,

Wallie

[former_member230846]

Hi Ron,

Not sure why an http:// address showed up there, but it should be:

{database.field} in "[" value1, value2, etc.... "]"

The above quotes should be removed around the square braces.

Regards,

Wallie

Edited by: Wallie Cheng on Sep 2, 2008 11:54 PM

OK the forum is converting some of the characters and not displaying as I want them, so I'll type it out instead.

{database.field} in (square bracket) value1, value2, value3, etc... (square bracket)

[Former Member]

Hi,

define parameters(Dynamic) for your report.insert those parameters on report,for eg you defined 2 paarameters on date with type dynamic and datatype date time.

link those parametsrs with data base field on wchich you want condition.

insert those parameters into report right click on parameter,select expert record.

define condition.

then by doing this you xan select specific records on report.

hope this will help you.

Thanks,

Neetu

[Former Member]

Hi Ron

If those unique entries are for a specific field in the database, you can try applying a Specified Order group on that field.

Hope this helps!!

Regards

Sourashree