Selection issue 2

[flistea]

I'm trying to do a report in CR10 from a SQL database (3rd party software called Authority Legal) with ODBC connection.

The problem I'm having is that the field I need to extract from - ItemAnswer.Alpha - contains both the address information and whether or the person was insured last year. The data I'm working with has been converted from an old dataease system so records exist for everthing.

I have created a sum to select only the records for people who were insured last year from the following formulas:

If {ItemAnswer.ItemLine_RSN} = 1380 then 1 else 0

If{ItemAnswer.Alpha} in ["x", "X"] then 1 else 0

I can set the selection criteria to only take records where this sum is equal to 2.

The problem I then have is that the address fields are based in the same table being the range
{ItemAnswer.ItemLine_RSN} in [1382 to 1387] with the answer in ItemAnswer.Alpha.

I need to be able to print the alpha answers from Lines 1382 to 1387 but only when the alpha answer to line 1380 is x or X.

Can anyone offer some advice?

Thanks! Flis

 

[kskid]

If both conditions have to be met, then

If {ItemAnswer.ItemLine_RSN} = 1380 AND {ItemAnswer.Alpha} in ["x", "X"] then 1 else 0

-LW

 

[flistea]

Thanks for the suggestion - the problem is that if this becomes selection criteria then all the information I need from {ItemAnswer.ItemLine_RSN}1382 to 1387 is excluded from the report.

 

[kskid]

You might try inserting ItemAnswer again as an alias ItemAnswer_1.

If {ItemAnswer.ItemLine_RSN} = 1380 AND {ItemAnswer.Alpha} in ["x", "X"] then
  {ItemAnswer_1.ItemLine_RSN} in [1382 to 1387]

 

[lbass]

You could create kskid's formula {@1380xX}:

If {ItemAnswer.ItemLine_RSN} = 1380 AND {ItemAnswer.Alpha} in ["x", "X"] then 1 else 0

Then assuming you have a group on {table.person}, go to report->edit selection formula->GROUP and enter:

sum({@1380xX},{table.person}) > 0

This should return all records for people who have both 1380 and the appropriate "x" or "X" in one of their records.

-LB

 

[flistea]

kskid - Thanks for the suggestion. Unfortunately when I tried it it just returned 0 records. I'm not sure whether this is because CR10 creates links are made between the field in the ItemAnswer and ItemAnswer_1 tables?

lbass - if only it were that simple! All the information I need for the people are also contained in the ItemAnswer.Alpha section.

 

[lbass]

When you tried my suggestion, did you first remove record selection criteria? The group selection approach should pull all records for people who meet your criteria.

-LB

 

[flistea]

lbass- I can't group on people as people are not on a separate table.

I'll expand my explanation. The tables I'm using are Item, ItemAnswer, ItemLine, ItemSection and ItemType.

An individual item is a specifice item type. Each ItemType is made up of sections with questions (lines) and then the answers to these.

The selection criteria for the information I need is -{ItemType.ItemType_RSN} = 19 and
{ItemSection.ItemSection_RSN} in [450, 451]

The information for confirming that they were insured last year is -
{ItemAnswer.ItemLine_RSN} = 1380 AND {ItemAnswer.Alpha} in ["x", "X"]

But the name and address information is -
{ItemAnswer.ItemLine_RSN} in [1382 to 1387] with the answer to these questions in {ItemAnswer.Alpha}

I'm grouping on Item.Item_RSN which keeps the record together.

When I select the records for items that were insured last year I exclude the rest of the personal information.

Does that explain it better?

 

[lbass]

Still not clear. What groups ARE you using? What is the group that you want to evaluate for the presence of {ItemAnswer.ItemLine_RSN} = 1380 AND {ItemAnswer.Alpha} in ["x", "X"]? Please provide some sample data that shows your grouping and detail sections.

-LB

 

[flistea]

The only group I am using is Item.Item_RSN and then ItemAnswer.Alpha is the only detail.

So I've created the {ItemAnswer.ItemLine_RSN} = 1380 AND {ItemAnswer.Alpha} in ["x", "X"] formula and done a group
sum ({@1380xX}, {Item.Item_RSN}) > 0 and it's worked!!!!

I've cross checked it with another report and the figures match.

Thank you!!!!!!!

:-D