Using Select expert with derived field 1

[spiderusa]

Hi

First time posting here. I need help with using the select expert on a derived field.

Basically I have to create two report say 'Customer A with sections' and 'Customer A without sections'. My report has CustomerID, Name and Section (derived field) among others. I have also grouped on the CustomerID.

I need to split the report using the derived field. It has two values - either 'Yes' or <Null>. When I use the 'is equal to' in the select expert on 'Yes' value the report works fine. But if the 'not equal to' is used or if checked using the <Null> value the whole report is visible with only the Section field blank. Hope I made some sense. What am I doing wrong ehre. Any suggestions? Thanks.

 

[Madawc]

The Select Expert or the [Selection Formula] [record] is a way of selecting individual records. This can include Formula Field values based on a single 'row', the record or records that would appear on a single detail line. Is that the problem?

If it is, try grouping using the field. If this fails, do a [Save As] and then re-import as a subreport that could go in the report header or footer.

Madawc Williams (East Anglia, UK)

 

[spiderusa]

I forgot to mention one more thing. All the data is present in the Group footer. There are 10 different sections (10 derived fields). So when I check for each customer I get several duplicate customerIds. So grouping was the best option. Using this 10 derived section fields I created another derived field that has the 'Yes' and <Null> value. I tried using the section expert on the group footer. but that didn't work either. its still giving me all the values with the derived column and all other section columns empty..all the other columns have data.
Its working fine for suppress if {@Section} = " " but not for {@Section} = "Section Member"
Weird.

And thanks for your answer Madawc. I need to export this to a spreadsheet so I'm not sure If I can use a subreport on this..I tried grouping on the field - same result. help me out here please. thanks.

 

[lbass]

It's hard to picture your report layout. Please show a sample of what it should look like. Also try converting the null to a value in your formula, as in:

if isnull({table.field}) then "No" else "Yes"

By "derived" do you mean you are using a formula to create a new "field"? If so, please share the exact formula.

-LB

 

[spiderusa]

lbass. Thanks for replying. If I can have your email, I'll send a sample spreadsheet. and yes I meant using a formula.

I'll try to explain the whole thihg again. Hope I make some sense. the layout is like this.

CustomerId, Name, Address, Club.....Member, Section1,
Section2...Section18.

The section values are present under Section_type in the
Order_detail table. So I used a formula to check the sections for each customer and got multiple values for the same customer.

the formula I used is like:
if {CUSTOMER.MASTER_CUSTOMER_ID} = {ORDER_DETAIL.SHIP_MASTER_CUSTOMER_ID}
and {ORDER_DETAIL.PRODUCT_CODE} = 'TECHNOLOGY' then 'T' and so on for each section.

To overcome the duplicates I created running totals for each section and after grouping by the customerId I had another formula for the sections based on the running totals.

All the data is now present in the GroupFooter with the running totals on the details section.

The member field (two values - 'Yes' and <Null>) is also derived using the previously derived sections.

That formula is:

if {@Computer} <> '' then 'Yes'
else if {@Education} <> '' then 'Yes'
else if {@Health} <> '' then 'Yes'..........
.......
else if {Fees} <> ''then 'Yes'

 

[spiderusa]

It worked. I converted the null value to 'No' and now its working..I'm stupid I guess. Thanks for the replies guys.