Is this possible ? Select expert i guess...

[AndersV]

Hello!

I have a problem with a report. Made in crystal reports v 8.5 using an access database.

I don´t know if this is possible to do in crystal.

Here is what i want to do... the data is presented like this in the database:

Artnr Value Date Status
100 10 1
100 11 0
110 8 2007-10-10 0
120 9 0


The problem is...

I want to fetch all rows with status = 1, so where there are two rows that has the same value for ArtNr but with a difference in status i only want to show the one with status = 1

If there is a row looking like ArtNr=120 i want to show it, that is if it dosen´t have values on status like the row for 110

I don´t want to show rows with status = 0 and where date has a value

The rows are presented in the details section.

Can i control this with the select expert? If i can, how do i write?

 

[CR85user]

I want to fetch all rows with status = 1, so where there are two rows that has the same value for ArtNr but with a difference in status i only want to show the one with status = 1

So selection criteria would include {table.status} = 1

If there is a row looking like ArtNr=120 i want to show it, that is if it dosen´t have values on status like the row for 110

I need more explanation of possible scenarios for this.

I don´t want to show rows with status = 0 and where date has a value

For this basic selection criteria would read something like:

isnull({table.date}) and not({table.status} = 0)

Are you sure you only want to retrieve these records? It is possible of course to retrieve more rows and only 'show' those with stats 1 or no date obviously through suppression.

'J

 

[AndersV]

Thank you for your answer!

If there is a row looking like ArtNr=120 i want to show it, that is if it dosen´t have values on status like the row for 110

I need more explanation of possible scenarios for this.

---

That problem with this is that, if the rows for example like the one with artnr = 120 dosent have any data with the status = 1 i want to show the row with status = 0 and that dosent have a date. Get the criteria?

Should this be done with suppresion or using the select expert?

 

[Madawc]

Group by Artnr. Suppress the detail lines and show details in the group header or footer. Sort by Status, ascending or descending to get the value you want.

One other point. If you put data in a code box, it comes out in fixed-width font. Thus

Artnr        Value          Date         Status
100          10                          1
100          11                          0
110          8              2007-10-10   0
120          9                           0

You get this using ]code[ and ]/code[, but with the square brackets the other way round.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[Madawc]

PS, you can only select on the basis of the properties of indivdual 'rows', in your case individual records. You can't compair one record to another before deciding if you want to select it. You can suppress its detail line on this basis, and also groups can be suppressed. Next and Previous are useful functions, but not in Selection.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[lbass]

Insert a group on {table.artnr} and then go to report->selection formula ->GROUP and enter:

(
sum({table.status},{table.artnr}) > 0 and
{table.status} = 1
) or
(
isnull({table.date}) and
sum({table.status},{table.artnr}) = 0
)

-LB

 

[AndersV]

Nice answers but i still have this problem

Artnr        Value          Date         Status
100          10                          1
100          11                          0
110          8              2007-10-10   0
120          9                           0

I want to fetch the row 120 and it would be something like lbass wrote to get it. But then i will also get the row 100 with status = 0 and i dont want that row because there is already a value for artnr= 100 that has status = 1.

Can i make a check, if table.artnr = table.artnr and if one of the rows has status = 1 i want to get it.

Get the problem?

 

[Madawc]

If you suppress detail lines and just show a group header or footer, you won't have duplicates showing.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[lbass]

I don't think my solution would return the 0 for 100. Did you try it?

-LB

 

[AndersV]

Getting the error "The summary / running total field could not be created"

(sum({ArtPris.status},{ArtPris.Artikelnr}) > 0 and
{Artpris.Status} = 1) or
(isnull({Artpris.FromDag}) and
sum({Artpris.status},{Artpris.Artikelnr}) = 0)

Looks ok to me...

 

[lbass]

You have to have a group inserted on {Artpris.Artikelnr}, and you have to be placing this in report->selection formula->GROUP.

-LB