Subreport formula group only showing discrete values 1

[jim789]

Now can I say "Help!!!"?

I'm working in CR 9. I've taken the Seagate Advanced Report Design course and have some limited knowledge of code from long ago programming courses.

I am grouping a report and very similar subreport on three fields. They have the same selection formulas on an ODBC link to a SQL Server database. Three linked tables with normal joins are queried. The second group is using a formula field. The subreport is linked on this and the third group field, which is a database field.

Here is the formula for the second group's formula field:

[
Local StringVar FirstTwo:= Left({wjsmas.ELCNUM},2 );

If FirstTwo = "S-" then "Collections"

Else if FirstTwo = "W-" then "Water"

Else if FirstTwo in ["A1", "A2", "A3", "A4", "GV"]
then "Other"
]


The report is supposed to work so that the subreport is called and prints out the third group totals line by line, once for each of the three groups indicated above and as many iterations as the first group. What is happening is that it does output the "Collections" and "Water" groups correctly but will not output the "Other" group. A drill down in the report preview shows that it has found and totalled all the "Other" data correctly but leaves it blank on the report. There is a subreport section titled "Other" showing on the report but no data. Any clues as to how to fix this? My supervisor and I would both be very grateful for a solution.

 

[lbass]

What happens if you remove the link on the third group field?

-LB

 

[jim789]

Thanks for looking at this, LB.

I tried your suggestion of removing the link on the third field. The result was that it output each of the first group sections on the second group, so instead of one subreport print based on Group 1 there were several. It printed out all these for "Collections" and "Water" but not the missing "Other". I think maybe the problem has to do with the formula group containing the "in" selection operator rather than just one discretely defined subset, since this is the one that isn't outputting to the report.

 

[lbass]

This is hard to visualize without samples, but when you link on the group #2 formula, are you linking the same formula in both the main and subreport to each other? You should have done that instead of linking the formula to a field (if that's what you did).

-LB

 

[jim789]

Hi LB,

Thanks again. I was trying to attach a .pdf file of the report but couldn't get it to go. Can I e-mail it? I'm not sure what you mean but I've done quite a lot of experimenting with the links. The linking utility only has options for linking various fields as far as I can tell. These can be parameter, database or formula fields I think. It doesn't make any difference if I turn the check box on or off. Cheers.

 

[lbass]

We need to stay within this thread. I am asking whether you linked your formula (here without the variable which isn't necessary) in the main report:

if Left({wjsmas.ELCNUM},2 ) = "S-" then "Collections" else if Left({wjsmas.ELCNUM},2 ) = "W-" then "Water" else
if Left({wjsmas.ELCNUM},2 ) in ["A1", "A2", "A3", "A4", "GV"] then
"Other"

...to your formula in the subreport.

-LB

 

[jim789]

Hi LB,

Yes I did link the formula field to the the same field in the subreport. The formula field is "@Opsgroup". The only option I got on the subreport parameter field list drop down was listed under Parameter Fields as "?Pm-@Opsgroup". This is apparently created by Crystal since I only put in the same formula as the main report and same name "@Opsgroup". The main report doesn't list that same parameter field.

Thanks again

 

[lbass]

I recreated your problem by not linking the formula correctly. When you choose {@Opsgroup} for the main report linking field, {?pm-?@opsgroup} automatically appears in the bottom left, but you still have to choose the subreport formula {@Opsgroup} from the dropdown on the bottom right of the screen, and the "select data based on the field" should be checked.

I also think you should be linking on your group one field as well as your other two fields--unless you made the conscious decision to have all group 1 values present in each execution of the subreport.

Finally, you haven't said where your subreport is located. I think it should be in the Group #3 header or footer.

-LB

 

[jim789]

Hi again LB,

I tried the link both ways, checked and unchecked, and with the correct subreport formula as you mentioned. No difference in the output. The report would work if the formula only specified:

if Left({wjsmas.ELCNUM},2 ) = "S-" then "Collections" else if Left({wjsmas.ELCNUM},2 ) = "W-" then "Water"

and didn't capture the rest of the data.

Sorry but I gave the wrong information in my original description. It is linked on Group One and Group Two. I tested it for linking on Group Three also but that gives the wrong result. I also forgot to mention there are four parameter date fields which are also linked and are also in the selection formulas for both reports. I'm pretty sure these aren't the problem since there are several variants of the report which do work using these.

The subreport is in Group Header 2, and it prints out Group Footer 3.

 

[lbass]

I just recreated your setup again, based on your new information, and the only way I don't get the "Other" category is if I do not select the formula in the subreport selection area, and instead link the formula to the field it references. In other words, as long as I select {@Opsgroup} as the main report field for linking and {@Opsgroup} as the subreport field for linking and check the box, it works.

-LB

 

[jim789]

Thanks LB,

You've answered the main question I had about this report. It's apparently not a fundamental type of error in which CR9 does not handle this type of formula function as a group field. There must be something else somewhere in the report which is causing the glitch. I'll continue to hunt for it.