Really long record selection formula on CE9

[gortuk]

Hey - has anyone had problems with reports using lengthy record selection formulas on CE9? This is what I'm seeing:

1) Report created with very long record selection formula (100+ lines). Report runs OK in CR 9.
2) Report saved to CE 9 server. (Our CE 9 is fully updated to Service Pack 2, running on Win2K.)
3) When you log in to the CMC and view the report's "Filters" tab, the "Record Selection Formula" box is blank.
4) When the report is viewed on demand, or a scheduled .rpt instance is viewed, various pageserver errors appear. If the report is scheduled to run as a .pdf, it runs fine and views fine. You can also run the report via CR 9 on the server.

The selection formula isn't really complex, it's just lengthy - lots of if statements. I believe the length of the record selection is the issue because if I cut it down by removing some sections, the report runs OK and views OK in CE 9, and the record selection formula appears in the "Filters" tab.

Has anyone run into similar issues with long record selection formulas? Note that this is not my report so rewriting is not an option immediately available to me.

Thanks!

 

[synapsevampire]

It would be interesting to see this record selection formula, why didn't you post it?

I haven't experienced it yet, but my longest record selection formula is about 50 lines thus far.

It's also interesting to see that it runs fine while scheduled, indeed it's a curious one...

-k

 

[gortuk]

The reason I didn't post it, synapsevampire, is that the bulk of the text in the select formula is names of organizations that my company does business with. It's just this, repeated a LOT (over 100 times):

if companyname="Name"
then (companycode in [list of numbers] or companyname in [list of companies])
else

 

[Turkbear]

Hi,
Your statement

When the report is viewed on demand, or a scheduled .rpt instance is viewed, various pageserver errors appear.

is a little vague..What errors?

I suspect the selection formula may not be the actual cause of the problem since it is obviously working OK for the PDF output ( at least I assume you get the expected output).

 

[gortuk]

The errors returned include:
1) "Invalid job number"
2) "Internal error. Please contact your system administrator"
3) "The request could not be submitted for background processing"
and I seem to recall a blank one popping up once in a while.
These errors are returned seemingly randomly, different errors for the same report instance. If I view these reports repeatedly to test, eventually the pageserver starts giving errors for ALL viewed reports.

As I said in my original post, my suspicion is that the size of the record formula is at fault because for exactly the same report, when I remove a large section of the record selection formula, the report works as expected. I have removed different sections to confirm that it isn't just an error with one specific line. (Eg if it has 100 lines, and I remove 80-100 and it works, and I remove 60-79 and it works, I know there isn't one line messing up the whole job).

My question is whether anyone else has used large record selection formulas and noticed any errors resulting.

 

[KingfisherINC]

I wonder if your Cache/Page servers are running out of temp space? (location of cache files).

Also can you try viewing the report using Advanced DHTML viewer (to force RAS rather than cache/page).

It is strange that obviously the Job server can handle the selection formula ok.

Kingfisher [CECP]

 

[synapsevampire]

The Record selection formula might be simplified a bit to shrink it by using a CASE statement, here's the example from the Help file.

Select {Customer.Fax}[1 To 3]
Case "604","250" :"BC"
Case "206","509","360":"WA"
Default:"";

This might be able to prove or disprove the size limit theory.

The fact that it doesn't show in CE is what makes me suspect that you might be on to something.

-k

 

[rhinok]

You definitely need to shrink the record selection statement. Based on your sample code, I would say that every single record for your report is being brought back to the Enterprise Server from the db and is then filtered out on the client. This supports Kingfisher's question as to whether the Cache/Page servers are running out of space. They might be because they're doing much more work than they should.

I think you can shrink the record selection and pass it to the db for processing by modifying your code to look something like this:

{table.companycode} In Switch
(
{?CompanyName} = "Name1",[list of numbers],
{?CompanyName} = "Name2",[list of numbers],
etc..(remove the comma from the end on the last line)
)

Or

{table.companyname} In Switch
(
{?CompanyName} = "Name1",[list of companies],
{?CompanyName} = "Name2",[list of companies],
etc..(remove the comma from the end on the last line)
)

~Kurt