why all records are read at the time of running?

[rajrev]

HI,
Time consuming problem with the developed report.
A report where the data coulumns(fields,formulas) in the group footer, because there is one field called language (which is from the table LANG.)
LANG table contains Empid and Language.
ex.
111 English
111 Spanish
222 French
and we are using main table called Empinfo.

and Empid as a parameter. if we select 111 then

the output of the report :

Name :....
Empid:....
DOB :....
Lang: English, Spanish.

for getting the "Lang" result we are placed all the columns in group footers and written some formulas in the groupheader and detail and groupfooter section..

Its working good.. only problem is at the time of running the report it takes more then a minit to display the one page result. It searchs all the data (more the 2millian records)in the DB.
On my point of view its because of the Index problem in database!! before talking to the DBA, pl. give me some adv. or suggestion if there's any wrong with CR8.5 / my formulas/...
The explained problem is not clear then, pl. let me know I'll try to explain again clearly.
Thanks
MK

 

[Ngolem]

There is nothing wrong with CR 8.5 per se

Show us your Record selection formula...as well as any other formulas involved that might be important.

for getting the "Lang" result we are placed all the columns in group footers and written some formulas in the groupheader and detail and groupfooter section..


This tells us nothing...


Jim Broadbent

The quality of the answer is directly proportional to the quality of the problem statement!

 

[rajrev]

Thanks for your response.
I'll explain the formulas, which was given by one of our friend in tek-tips.

1. @resetLang. which was placed in the groupheader.
to reslet the global stringvar x := " ".
2. @AddLang. which was placed in the detail
to add the languages in one line.
ie global stringvar x:={xxx.xx}+", ";
3. @DispLang which was placed in the groupfooter.
for dispaying the result :
Lang :sss, aaa, ...

need more detial pl. let me know.
Thanks
MK

 

[MJRBIM]

Are you SELECTING the data based on on "Empid "? If not what are you SELECTING on?

 

[rajrev]

yes I'm selecting based on Empid

 

[rajrev]

Sorry for confusing you people.

Better I start from the begining.. that'll give the clear outline of the problem.

We are creating a one page resume for an employee.
The output they need is :
Name :xxx
EmpID:111
Mobility:AL,IL,NY

Education
school degree
eeee MS
ssss BS

Languages
Spanish,English,Franch
....

All are in the Groupfooter

Eduation is a Sub report
Language is another Sub report

Mobility is from different table called mobilitytbl
empid state
111 NY
111 IL
111 AL
333 IL
333 NY
444 OI
...

Parameter => Empid ....


for listing the mobility in one line we are using the 3 formulas which i explined in the previous threads.
1. @resetmo => global stringvar mo:=" " in group header
2. @addmo => mo:=mo+{xx.field}+", " in Detail
3. @dispmo => global stringvar mo :=left(mo,(length(mo)-2)) in Group footer.

ofcourse on this level i find the running numbers in the bottom 111111 of 111111 records..

after that
I'm using the sub report for education its working fine with the same time delay.

and I tried to do the same thing like mobility for language in a sub report, I got error.
error :"String length is 0 or not an integer"

so where i'm doing mistakes, any best solution for this problem... or need more info pl. let me know.

Thanks
MK

 

[synapsevampire]

I'm a little confused about where the problem is occuring.

Jim had requested the record selection formula, which you omitted.

If the problem is simply speed, post the record selection formula (Report->Edit Selection Formula->Record) and the resulting SQL (Database->Show SQL Query).

What is in the record selection formula should be reflected in the SQL, this goes for subreports as well.

I won't bother getting into why you need subreports, but minute isn't a horrible time for 2 million rows considering your requerying the database with each subreport.

Your best performance will be using a Stored Procedure, and a single View will outrun subreports every time. Check with your dba about rewriting th SQL into a View with correlated subqueries.

Posting what does work, yet omitting things like the aforementioned and the type of database and connectivity used will result in confusion and more questions.

Make sure that you use the CR supplied ODBC driver (when using ODBC) if you're using SQL Server or Oracle, not the ones supplied with the database.

-k

 

[Ngolem]

I second SV comments

Your speed of execution is not usually based on the formulas embedded in the report...unles there is a wild For_Next loop of somekind.

Rather it is the record selection formula....you have ignored my request for this formula and presented me with the barest of info that I really cannot comment on since this is not the root of your problem.

Your problem is two-fold

1. You are taking 1 minute to accumulate data (my criteria in general is that a report be completed within one minute). Two million records is a fair bit of data but not impossible to deal with if processed on the server....but if you aren't pushing your record selection down to the server...CRYSTAL WILL CRAWL...since it must process a good portion of these records once again (and if there is significant time delay in a subreport it can get REALY ugly.

2. You are using a subreport that will be repeated many times during the execution of this report (once/EmployeeID). This kind of thing should be avoided if possible ... or if not then this subreport should be designed to tak a minimum of time (extracting a couple of Language items from a table doesn't seem like a timewaster butbut we cannot assess that without hard information to go on[\b]

What information???

1. The names of all tables involved c/w linking fields (all of them)...including the subreport table to see if a subreport can be avoided.

2. A copy of the record selection formula for both the Main Report AND the subreport...so we can see what is going on there...and hopefully improve it.

Give us this info and we shall see what can be done.



Jim Broadbent

The quality of the answer is directly proportional to the quality of the problem statement!

 

[rajrev]

I'm sorry i forgot to produce the record selection formula. Thanks for your replay. And I apologize for my previous explanations. I did a big blender mistake.
Instard of writing the {?Empid}={E.Empid} in record selection,
i worte the {?EmpId}=GroupName ({E.EMPLID}) in group selection.
Speed problem was over.. thanks for your response.
.....................................................

Now I need to display the Language in a series.
At present I'm getting the result like.
Language:
Spanish
English
(These are from the table called "Lang" which contains Empid, Lang.)
how to convert it like
Language : Spanish, English,...
But I already created this kind of format for Mobility.
(Which i explined in the previous posts.)
I tried to do the same thing for language, but it opens the formula @dispmo => global stringvar mo :=left(mo,(length(mo)-2))(Group footer) and diplay an error :"String length is 0 or not an integer"
could u please help on this problem.
(I'll expline more about the subreports asap.)
Need more info or if i confused again please let me know, I'll try my best next time.
Thanks for your reponse.
MK

 

[lbass]

You might have some nulls for mobility, so you could try changing your details level formula to:

whileprintingrecords;
stringvar mo;
if isnull({xx.field}) then mo := mo else
mo:=mo+{xx.field}+", "

Also change your display formula to:

whileprintingrecords;
stringvar mo;
if length(mo) > 2 then
left(mo,length(mo)-2)

You probably should do the same kind of thing for language formulas.

-LB

 

[rajrev]

Thank you very much. I'll try this and you u know.
Thanks
MK

 

[rajrev]

Hi,
It is working good. Thanks a lot for your replies.
By the way, I have a question.
For example I'm using the language as a subreport and placed in group footer1.
I suppress all the section in the subreport with a condtion isnull(...).
But I'm hanging with, how to suppress the group footer1 (because if language is null then, I have a blank space in the main report).
pl. let me know, need more info.
Thanks
MK

 

[rajrev]

FYI...
Ignore the previous post.I found the solution for this.
Thanks,
MK

 

[rajrev]

Sorry I'm worng.. could any one help on this.. for the previous post..
Thanks
MK

 

[Madawc]

Right-click, [Format Section] and set the formula for suppress,
isnull({language})

Madawc Williams
East Anglia, Great Britain