Improving the crystal Report performance

Report Definition
-----------------

 Number of Database Fields:   16    
 Number of Summary Fields:   2    
 Number of Constant Formulas:   1    None in use
 Number of Print Time Formulas:   119    
 Number of On-Demand Formulas:   82    Only 22 in use
 Number of Global Variables:   11    
 Number of Shared Variables:   1    
 UFLs in use:   None    
 Page N of M Used:   No    
 File Format Schema:   12.0.0    
 

Saved Data
----------

 Number of Data Sources:   1    
 Unicode Saved Data:   Yes    
 Total Number of Records:   2605    
 Selected Number of Records:   2605    
 Recurring Database Record Length:   2 KB  
 Recurring Formula Record Length:   467 bytes  
 Constant Formula Record Length:   71 bytes  
 Size of Saved Records:   6 MB  
 Size of Saved Memo Fields:   0 bytes  
 

Processing
----------

 Grouping Done on Database Server:   No    
 Sorting Done on Database Server:   No    
 Record Selection Done on Database Server:   Yes    
 Require Two Passes:   Yes    
 Require the Total Page Count:   No    
 Number of Nodes in Group Tree:   1016    
 Number of Summary Values:   539    
 Size of Saved Group Tree:   18 KB  
 Built the Group Tree in Progress:   No    
 

Latest Report Changes
---------------------

 Recurring Database Fields:   Changed    
 Summary Fields:   Changed    
 Report Groups:   Changed    
 Constant Formulas:   Changed    
 Recurring Formulas:   Changed    
 Print Time Formulas:   Changed    
 Database Table Linking:   Changed    
 Record Sort Order:   Changed    
 

Performance Timing
------------------

 Open Document:   0 ms  
 Run the Database Query:   636 ms  
 Read Database Records:   288850 ms  
 Format First Page:   289563 ms  
 Number of pages formatted:   2    
 Average time to format a page:   144786 ms  
 Number of page starts generated:   285    
 Average time to generate a page start:   14 ms  

How long does it take to run the query on the server by itself? If you have a database query already written and only need summary data, why not build a group by into the query?

~Kurt

What are the groups that use user input?
If they are formulas the formulas probably can't be passed to the server for grouping.

I agree  with Kurt the best way to improve performance is to do as much on the server as possible.

Why are you using arrays to do the data printing?
That will also be rather slow.

mlmcc

yes the user input grouping is using the formulas like
case patient: Patient Name
case Physician:Physician Name
case Nurse:Nurse Name

since the user can group however he want to group the data and see the records.

I am using the arrays since it prints the data for last six months on the report for those particular tests.

How are you populating the arrays?

What is the actual formula for one of the groups 3-6

mlmcc

What connectivity are using to what DB Type? Remember that cursors in SQL Server procedures separate from the output cursor slow down performance tremendously, while having no noticeable impact in Oracle.

Below is the formula i am using for the grouping

select {?Grp2}
    case    'Facility'      :   column1
    case    'Physician'     :   column2
    case    'Shift'         :  column3
    case    'Modality'      :  column4
    case    'Dietician'     :  column5
    case    'Nurse'         :  column6
default                       : 'No Selected Group'

That formula cannot be passed to the database so you will have to do grouping on the client side.

mlmcc

is there any work around to pass the grouping to the DB?

I mean any way to improve the performance of the report?

>>is there any work around to pass the grouping to the DB?
I am by no means a SQL  expert.  You might be able to pass a parameter to a stored procedure that evaluates the grouping requirement and does the grouping in the SP.  That might also then require you to get summary values rather than the individual records.

Why do you need the intricate grouping?

How are you calling the reports?
Are you using the default Crystal parameter screen?

mlmcc

Why do you need the intricate grouping?
 - We need it since we gave him the option to create his own choice of grouping and saving it has his custom report
 
How are you calling the reports?
- We are calling from the cursors

Are you using the default Crystal parameter screen?
- They are picking the parameters in the UI and passing it as the URL to the crystal server

What kind of db are you using?  What is the datasource for the report (tables, stored procedure, CR Command, etc.)?

 James

We are using oracle11g as DB and we are connecting using the TNS Names

Essentially you need to return a field that is a decode of that parameter that you give a handy name like
"grouper", and that contains the values for your group rule

Say for example you had only three possibilities, "Facility", "Shift", or no group, your decode would be

SELECT
DECODE(parameter,
 
Then on the report just group on GROUPER. The 'A' is for the No Group rule, it will put everything in the
first and only group - which is the same as having no group at all.

Be careful because they will still have to be the same datatype for the report to use them correctly.

mlmcc

True, though you can always convert in the decode. Remember you still bring in all the actual fields as well, this is just for grouping purposes.

In your case all fields will have to be strings or Crystal will have trouble since it will expect a specific data type and if it changes the report will abort with an invalid data error.  the Field GROUPER cannot change types between runs of the report.

mlmcc

it does not need to. Convert everything in grouper to varchar2. Again, you are ONLY using it for grouping. You are running it in addition to bringing in the individual values for each item on their own. Trust me and try it, it works.

@David11Norman
Thanks for your reply,But ia m not sure where to put the formula which you have given to me, I am calling the cursor into my report,
if i need to put this in the cursor statement what should i use in the place of parameter.
Can you please be little more clear, i am lost.

It needs to go wherever your select statement is. I assume your cursor is in a stored procedure that you are calling either by a direct connection or a command line, correct?

@David Norman. I a getting the error ORA-01722 when i am trying to enter the shift , The Data in the Shift_ID is usually 1M,2M,3M,1T,2T........

It sounds to me like some of your fields are numeric and some are strings. You need to convert them all to strings. This error indicates that an earlier field is a numeric and thus it is assuming a numeric should always be returned. It is giving you the error because '1M' is not numeric. That is what I meant earlier when I said "Convert everything in grouper to varchar2. " If you send the code snippet and the data type for each field in the DECODE I could help some more.

SELECT DISTINCT
       ORGANIZATION_DIMS.ORG_DIM_SEQ_NO,
       ORGANIZATION_DIMS.ORG_NAME,
       PATIENT_DIMS.PAT_DIM_SEQ_NO "PATIENT SEQ NO",
       PATIENT_DIMS.FIRST_NAME ||', '||PATIENT_DIMS.LAST_NAME "PATIENT FULL NAME",
       PATIENT_DIMS.SHIFT_ID "SHIFT",
       DECODE(PARAMETER,'FACILITY', ORGANIZATION_DIMS.ORG_DIM_SEQ_NO,'SHIFT', PATIENT_DIMS.SHIFT_ID,'No Group') as GROUPER
       
       
FROM
       PATIENT_DIMS
JOIN
       PARTY_RELATIONSHIPS
ON     PATIENT_DIMS.PAT_DIM_SEQ_NO=PARTY_RELATIONSHIPS.CHILD_PR_SEQ_NO
JOIN
       ORGANIZATION_DIMS
ON     PARTY_RELATIONSHIPS.PARENT_PR_SEQ_NO=ORGANIZATION_DIMS.ORG_DIM_SEQ_NO

So I assume ORG_DIM_SEQ_NO is a number. Convert it to text in the Decode. Then in the report set your grouping as "If {?parameter} = 'Shift' then ToNumber(Grouper) else Grouper"

@David...
You are right the org_dim_seq_no is the Number, i have converted to to_char(org_dim)seq_no) in the decode function and using the "if...else" formula ...but still i am getting various errors in the formula like "A Number is required here" after the else statement.

I was afraid of that The If statement is demanding a consistent data type. I know this is turning into a mess, but the last thing left to try is to prefix zeroes to the front of the string for when the choice is facility to make all the numbers the same length strings. That way they can remain strings and when you sort alphabetically they will come out right (ie, alphabetically 02 comes before 11, but 2 does not.) If you need some help writing that part send me the max number of digits for the seq_no and I'll send you an example.

SELECT DISTINCT
       ORGANIZATION_DIMS.ORG_DIM_SEQ_NO,
       ORGANIZATION_DIMS.ORG_NAME,
       PATIENT_DIMS.PAT_DIM_SEQ_NO "PATIENT SEQ NO",
       PATIENT_DIMS.FIRST_NAME ||', '||PATIENT_DIMS.LAST_NAME "PATIENT FULL NAME",
       PATIENT_DIMS.SHIFT_ID "SHIFT" ,
       DECODE('{?GROUP1}','FACILITY',TO_CHAR(ORGANIZATION_DIMS.ORG_DIM_SEQ_NO),'SHIFT', PATIENT_DIMS.SHIFT_ID,'No Group') "GROUPER"
       
       
FROM
       PATIENT_DIMS
JOIN
       PARTY_RELATIONSHIPS
ON     PATIENT_DIMS.PAT_DIM_SEQ_NO=PARTY_RELATIONSHIPS.CHILD_PR_SEQ_NO
JOIN
       ORGANIZATION_DIMS
ON     PARTY_RELATIONSHIPS.PARENT_PR_SEQ_NO=ORGANIZATION_DIMS.ORG_DIM_SEQ_NO

I am making the formula "Grouping" and entering this value" IF {?GROUP1}= "SHIFT" THEN TONUMBER({Command.GROUPER}) else {Command.GROUPER}"

@David.....
the Maximum Number of digits for seq_no is 12

Okay - you will need to create a separate formula to format {Command.GROUPER}, after which you will just group on THE_FORMULA

THE_FORMULA is:

IF {?GROUP1} <> "SHIFT" OR LENGTH({Command.GROUPER}) = 12
THEN {Command.GROUPER}
ELSE

Then just group on THE_FORMULA ascending.

I did not put that into a crystal formula to test for syntax, so if you get any syntax errors on the SELECT...CASE just use the contextual help for the proper syntax.  This should almost certainly work.

How about

IF {?GROUP1} <> "SHIFT"  THEN
   {Command.GROUPER}
Else
    Right("000000000000" & {Command.GROUPER},12)

mlmcc

That should also work, and is definitely a lot easier to type.  ;-)

it throws an error saying "Colon is expected"( At the first CASE Word) in the formula editor

@David...
so i am using the below code in the command expert
SELECT DISTINCT
       ORGANIZATION_DIMS.ORG_DIM_SEQ_NO,
       ORGANIZATION_DIMS.ORG_NAME,
       PATIENT_DIMS.PAT_DIM_SEQ_NO "PATIENT SEQ NO",
       PATIENT_DIMS.FIRST_NAME ||', '||PATIENT_DIMS.LAST_NAME "PATIENT FULL NAME",
       PATIENT_DIMS.SHIFT_ID "SHIFT" ,
       DECODE('{?GROUP1}','FACILITY',ORGANIZATION_DIMS.ORG_DIM_SEQ_NO,'SHIFT', PATIENT_DIMS.SHIFT_ID,'No Group') "GROUPER"
       
       
FROM
       PATIENT_DIMS
JOIN
       PARTY_RELATIONSHIPS
ON     PATIENT_DIMS.PAT_DIM_SEQ_NO=PARTY_RELATIONSHIPS.CHILD_PR_SEQ_NO
JOIN
       ORGANIZATION_DIMS
ON     PARTY_RELATIONSHIPS.PARENT_PR_SEQ_NO=ORGANIZATION_DIMS.ORG_DIM_SEQ_NO

....Then i need to create the formula
IF {?GROUP1} <> "SHIFT" OR LENGTH({Command.GROUPER}) = 12
THEN {Command.GROUPER}
ELSE

SELECT LENGTH({Command.GROUPER})

CASE 1 := "00000000000" +  {Command.GROUPER}
CASE 2 := "0000000000" +  {Command.GROUPER}
CASE 3 := "000000000" +  {Command.GROUPER}
CASE 4 := "00000000" +  {Command.GROUPER}
CASE 5 := "0000000" +  {Command.GROUPER}
CASE 6 := "000000" +  {Command.GROUPER}
CASE 7 := "00000" +  {Command.GROUPER}
CASE 8 := "0000" +  {Command.GROUPER}
CASE 9 := "000" +  {Command.GROUPER}
CASE 10 := "00" +  {Command.GROUPER}
CASE 11 := "0" +  {Command.GROUPER};

....and then group on the above formula ...is that right.....I did the above process and got the error 'Colon is expected Here" in the formula editor

@mlmcc...when i use the suggested formula it throws the error "Number is expected" after the else statement.

Can i also use collection_date in the grouper?

First, you should NOT have changed the DECODE in your command - you still need to always return a string. That is why we are doing these added zeros to make it alphabetically correct, so change it back to TO_CHAR(ORGANIZATION_DIMS.ORG_DIM_SEQ_NO) in the Command.

Then either mimcc's formula or mine should work - with the caveat (oops) that mine had a typo and should have had a semicolon at the end of EACH 'CASE' line, not just the last one.

Is collection date a date field? If so you will need to convert it to character also with the format 'yyyymmdd'. That will create a string that will always sort in alphabetical order. In that case you can still use @mimcc's formula without changes to group on.

Thanks David Norman....finally i was able to produce it....

You are welcome. Don't forget to mark me as the solution so I can get the points, and good luck!