Record roulette

[hanchilicious]

Hello,

I'm using XI on SQL 8.

Basically, I just want to know wtf is going on here...

I am running a report that is based on the following:
selection criteria: not isnull({productmastertable.product})
using two tables linked on keys
grouped by country, sales group, product
data displayed at GF3 level - all other sections suppressed

For the UK, for example, for one customer, I get 5 records. I can confirm this by conditionally suppressing the display section based on {mytable.country} <> 'UK' and conditionally colouring the data with {if {mytable.customer} = '110017' and {mytable.country} <> 'UK' then crlime else crnocolor.

I get 5 coloured rows.

Then I take these two conditions and put them in the selection criteria, so it looks like: not isnull({productmastertable.product}) and {mytable.country} = 'UK' and {mytable.customer} = '110017'

I get 126 rows. 126 are displayed. 126 are coloured.

The second result is correct. Where do the 121 rows go in the first example?

 

[hanchilicious]

The SQL for both reports show the right data on the database.

 

[hanchilicious]

After a reboot, sometimes I get three coloured lines - and sometimes four. Is the report corrupt?

 

[lbass]

Look at your first post. You are coloring with:

{if {mytable.customer} = '110017' and {mytable.country} <> 'UK' then crlime else crnocolor

Shouldn't that be:

{if {mytable.customer} = '110017' and {mytable.country} [red]= [/red]'UK' then crlime else crnocolor

-LB

 

[hanchilicious]

Whoops. Good spot.

What you said is what it is. I can't copy and paste it, because it's a different machine. It was a typo in the post.

 

[hanchilicious]

Well, I built a new report to emulate the first one, and I get the same issue...

 

[lbass]

How does the customer field fit into your grouping? In the first case, you are allowing in other customers, and I think you are coloring a group footer, not a detail section. So your formula would only capture the last record in a particular group, and if that customer isn't the one you selected there would be no color. Unsuppress the detail section and then I think you'll be able to see what's going on.

-LB

 

[hanchilicious]

Thanks LB. I did that, and it's about right. Thank you.