Display First Record Only

[Cynthia]

I have a report that displays a commodity, and then the related Vendors for that commodity.  It groups everything okay.  Including the vendor.

But I only want to show the most recently used vendor. There are two vendors showing, and I only want to see the first one (most recently used) on the report.  Is there a way to do that?

Maximum doesn't work because it's displaying the maximum of each vendor. And I only want one vendor, not both.

Let me know if I haven't explained this well.
Thanks,
Cynthia

Edit, I'm in Cognos Report Studio 8.4

[tupac_rd]

create a data item running-count([vendor]), and filter the new data item =1, will that work?

[Cynthia]

Doesn't look like it. It wants an amount for the running total, and my fields are attributes (char) and identifiers (date).

[Cynthia]

I've also tried HEAD, but I need to head the max, and i'm getting an error about that too...

invalid coercion from 'value' to 'memberset'

[Cynthia]

I'm looking at using running-count though... thanks for the idea. Maybe running-total would work if I cast the date to an integer, but I'm not sure.

the running-count description says this:
running-count ( numeric_expr  [ at exp {, expr } ]  [ <for-option> ] [ prefilter ] )
running-count ( [ distinct ] numeric_expr  [ <for-option> ] [ prefilter ] )
<for-option> ::= for expr {, expr } | for report | auto
Returns the running count by row (including the current row) for a set of values. The "<for-option>" defines the scope of the function. The "at" option defines the level of aggregation and can only be used in the context of relational datasources. The keyword "distinct" is available for backward compatibility of expressions used in previous versions of the product.

But I really don't understand how to use the for-option.  Can anyone give me an example?  :)  I'll look around the forums... but searching for the word 'for' really doesn't work very well!

[tupac_rd]

running-count( [data item1] for report) or

running-count( [data item1] for [data item2])

[Cynthia]

Thanks.

It's still not suppressing the data rows that I want to have not show. I'm looking into Render Variables... but dangit, it's not easy to understand either. :)

[elsolo21]

Have you tried something like:

max([date] for [vendor]

That should get you the most recent record with the corresponding vendor.

[Cynthia]

Yes, but with the grouping... I have more than one vendor, and it's bringing me up the record for each vendor. I only want the one vendor that has the most recent date.

[kattaviz]

Hi,

You can try using the Singleton object. If you sort your query by date DESC then the Singleton pickup only the first value.

HTH

[Cynthia]

Okay thanks, looking into that.

I need the entire row to not show, though. Not just a single column. 
And it's not letting me sort on the singleton object.

[tupac_rd]

max ( [vendor] for report) will this work

[James_Bonnell]

Hmm.  So each Commodity has a Vendor (or more than one), right?

How about trying a data item with max([Vendor Date] for Commodity)?  That should give you the Vendor, per Commodity, that has the highest date.

You may need to then do a filter setting the (Vendor Date) = max([Vendor Date] for Commodity).  I think that should give you only one Vendor per Commodity, unless of course more than one vendor shares the vendor date...

That's what I'd try...

[Sreeni P]

Have u tried with Singleton?like kattaviz said earlier....