Field selection based on another field

[RobbOrt]

Good Afternoon,
I good really use some help. I am using CR XI and am pulling data using SQL Server 2005.

I am creating a report from two SQL tables that have no common fields and no way to add one to link them together. That being said:
Table1 has street names and address descriptions for every address in the city.
Table2 has meter addresses that can have a street_name, a cross_st_nm or both. The kicker is that these fields are only the first five letters of the street name.

My report is trying to show all the meter information from table2 where the street names do not match from table1.

I created a formula called @LEFT_5_ST_NM, to give me the first 5 letters of the street names from table1.

What I need to get is:
either:
tbl2.street_name not in @LEFT_5_ST_NM
and/or
tbl2.cross_st_nm not in @LEFT_5_ST_NM

Grouped by Atlas_pg, meter_nbr.

Displayed like so:

ATLAS_PG METER_NBR STREET_NM CROSS_ST
a-10-1 1 Sccot Howar
k-12-5 2356 Wshin Linkn
w-5-4 12456 Phili Ntion

Because the following did not match @LEFT_5_ST_NM or
left(tbl1.street_name,5):
Sccot s/b Scott; Wshin s/b Washi; Ntion s/b Natio.

Please advise as I have been at this way too long, THX.

 

[lbass]

In the main report only use table2. Add the fields you want to display to a detail_c section.

Then insert a subreport that references table1 in the detail_b section, and in the detail_a section, add a reset formula:

//{@reset}:
whileprintingrecords;
shared stringvar x := "";

In the subreport, insert a group on a formula:

//{@left5}:
left({table1.streetname},5)

Link the subreport to the main report on the {table2.streetname and the {table2.crossname}. Then go into the subreport record selection formula and remove the record selection formula.

Add a formula like the following and place it in the group section of the subreport:

whileprintingrecords;
shared stringvar x;
if left({table1.streetname},5) = {?pm-table2.streetname} or
left({table1.streetname},5) = {pm-table2.crossname} then
x := left({table1.streetname},5);

Suppress all sections WITHIN the subreport and in the main report, right click on the sub->format subreport->subreport tab->check "suppress blank subreport.

Then in the main report->section expert->GH1c->suppress->x+2, enter:

whileprintingrecords;
shared stringvar x;
x = {table2.streetname} or
x = {table2.crossname}

Also format GH1a and GH1b to "suppress blank section".

This should just return unmatched rows.

-LB

 

[RobbOrt]

Thank you LB.

I will give this a try and let you know if I have any follow up questions/problems.

BTW thanks for the quick responce, I have not asked for much assistance on this site, but other sites have taken days to get any responces. You guys definitely make this site the great tool that it is for us newbees.

 

[RobbOrt]

LB,

When I put the suggested formula in the Group Selection (using the Formula Workshop - Group Selection Formula Editor) I got the message "Thr result of selection formula must be a boolean" when I checked the formula for errors.

What did I do wrong?

 

[RobbOrt]

Also,
1) I have only inserted one subreport (so far) into the Main Report - Details-a. The only thing that I have in the subreport at this point is Group1 {@left5}. What should I have in the details of this report? Should I be inserting the street_name data? or is this subreport completely blank?
2) When I hit the save on the subreport it ran the Generating Data For Saving Subreport:Reading Records for over 10 minutes before I had to shutdown the program and start over completely.
3) What is the reason for using two subreports? One in Main Report Details_a and the second in Main Report Details_b? Should I be linking on the tbl2.streetname and tbl2.crossname seperately? one in each subreport?

THX

 

[lbass]

Nowhere did I suggest using group selection for anything, and I did not suggest two different subreports. Please try again, and follow my instructions step by step.

The main report has three detail sections:

a-Reset formula
b-subreport
c-main report fields (from table2)

The subreport has a group on left({table1.streetname,5) and has a formula created in the field explorer->formula->new:

whileprintingrecords;
shared stringvar x;
if left({table1.streetname},5) = {?pm-table2.streetname} or
left({table1.streetname},5) = {pm-table2.crossname} then
x := left({table1.streetname},5);

...placed in the group header section. All sections in the sub are suppressed.

-LB

 

[RobbOrt]

Thank you. I will start again and be more careful to follow your instructions.

 

[RobbOrt]

LB,
I am still having the same issue when saving the report, it shows Generating Data for Saving Subreport Reading Records (on the bottom right of blue bar). It just keeps rerunning over and over until I end up stopping it. I have even had to stop the CRxi process form the task list once to stop it.
-----------------------------------------------------------------
The Subreport:
dbo.Table1
linked to Main report on
Table2.streetname as ?Pm-tbl2.streetname to Table1.streetname
and
Table2.crossname as ?Pm-tbl2.crossname to Table1.streetname

Removed the Record selection formula

Created formula @left
//{@left5}:
left({table1.streetname},5)

Grouped by @left

Group Selection has formula:
whileprintingrecords;shared stringvar x;
if left({table1.streetname},5) = {?pm-table2.streetname} or
left({table1.streetname},5) = {?pm-table2.crossname} then
x = left({table1.streetname},5);

No fields displayed on subreport and all sections supressed.
Looks like a bunch of gray lines.
-----------------------------------------------------------------
Main Report:
dbo.Table2
GH1 - Atlas_pg
GH2 - Meter_nbr
Details-a: @reset
Details-b: Subreport
Details-c: table2 fields atlas, meter_nbr, streetname & crossname

You said to place:
whileprintingrecords;
shared stringvar x;
x = {tbl_STG_MSS_COT_MS_HYDLOC.MS_HYD_ON_STREET_NAME} or
x = {tbl_STG_MSS_COT_MS_HYDLOC.MS_HYD_CROSS_STREET_NAME}
into x+2 for GH1c
But I do not have a GH1c. If I get rid og GH2 and sort GH1 by Meter_nbr than I have GH1b, but I was not sure where GH1c comes from?

Please let me know if I am still off on any of your instructions.

 

[lbass]

This is NOT a group selection formula, but a formula you create in the field explorer and add to the group header for {@left} in the subreport. Note that the last line contains ":=", NOT "=":

whileprintingrecords;
shared stringvar x;
if left({table1.streetname},5) = {?pm-table2.streetname} or
left({table1.streetname},5) = {?pm-table2.crossname} then
x [red]:=[/red]left({table1.streetname},5);

I apologize for my error--for the section expert formatting I should have been referring to detail a,b,c, NOT, GH1a,GH1b, GH1c.

BUT, let me ask you this--if you are grouping by atlas_pb and meter_nbr, are the table2 streetnames unique to that meter number? If so, then, instead of using the detail sections, use GH#2 a,b,and c sections, with the reset in GH2a, the subreport in GH2b, and the main report fields in GH2c. Then format the GH2 sections as follows:

Then in the main report->section expert->GH2c->suppress->x+2, enter:

whileprintingrecords;
shared stringvar x;
x = {table2.streetname} or //no colons here though
x = {table2.crossname}

Also format GH2a and GH2b to "suppress blank section".

This will make the report faster.

-LB

 

[RobbOrt]

Thanks, I will try that (GH2a,b,c).

I did notice the :=, but when I error check or save it, it tells me that "The result of the selection formula must be bollean."
I was not sure what I was doing that caused the bollean message.

Can you suggest what I would be doing wrong that would cause this message?

 

[lbass]

You got that boolean message because you were creating the formula in the group selection area instead of in the field explorer->formula->new.

-LB