Improving the crystal Report performance

RaoVP
RaoVP used Ask the Experts™
on
Hi,
I do have the problem running the report for the larger dataset like for eg: Six Months worth of data
1)The report is been pulled from the cursor
2) There are no record selection on the report side, everything is done on the Database Query
3) I am using Array for printing the results
4) I do have 8 levels of grouping, in which first 2 grouping's and last 2 grouping's (four grouping) are harded coded eg: they run everytime when the report run , But the remaining grouping are 3,4,5,6 are picked from the UI.if there is no grouping picked then report only runs for remaining 4 groups

I am calling the crystal report through a URL and URL is directly hitting the crystal server.

what i have been observing is that its reading all the records and then grouping on the report side and then printing the first page.

grouping on the server option is graded out .

How can i improve the performance of the report?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

Commented:
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  
Kurt ReinhardtSr. Business Intelligence Consultant/Architect

Commented:
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
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
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
Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

Author

Commented:
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.
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
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.

Author

Commented:
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'
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

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

mlmcc

Author

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

Author

Commented:
I mean any way to improve the performance of the report?
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
>>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

Author

Commented:
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

Author

Commented:
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,
 
'Facility',
FACILITY_TABLE.FACILITY_NAME,
 'Shift',
SHIFT_TABLE.SHIFT_NAME,
'A') as GROUPER,
....

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.
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
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.
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
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.

Author

Commented:
@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?

Author

Commented:
@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.

Author

Commented:
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"

Author

Commented:
@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.

Author

Commented:
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}"


Author

Commented:
@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
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};

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.
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
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.  ;-)

Author

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

Author

Commented:
@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

Author

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

Author

Commented:
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.
You can use anything you want in GROUPER as long as you convert any non-text to text and then format it so that the values when sorted alphabetically come out in the right order. For a number you do that on the Crystal side, for a date you can do it on the Oracle side.

Author

Commented:
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!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial