second page is not getting loaded

[gagani]

I have created some formulas based on the values returned from subreports and group counts and grand total counts of main report.
When I used those formulas in page header and run the report, the first page is loaded fine. And when
I try to view the second or last page, it is trying to load again and is taking quite long time, finally not getting loaded even.

I am not sure whether we can put such formulas in page header. Could anyone please help me

 

[SmpTraining]

You can put formulas in any section you like - they may not work as expected but they should not cause the report to fail.

As your formula is based on values in a subreport they will be 'WhilePrintingRecords' formulas. These formulas must be placed at the appropriate section if they are to display the correct values.

A problem that can occur with items in the page headers is if crystal does not have enought space on a page to display the page header, page footer, group header and one details line. This may not be an issue for you but to test, if you remove these formulas, will the report run okay (albeit without these values)?

If so, perhaps you can explain the layout of the report a little more? E.g. RHa: Subreport, RHb, Formula to display value from subreport etc. If helpful, paste some of the code too.

Steve Phillips, Crystal Reports Trainer & Consultant

http://www.smptraining.co.uk

 

[gagani]

the values obtained from subreports and the formulas involved are giving the right values. no issues with that.
the only issue is in loading. when I go for the second page or last page, it is taking very long time to get loaded. And after second or last page is loaded, when I use the back button to see the first page, it's again taking very long time.

 

[SmpTraining]

It hard for us to tell where the inefficiencies are without knowing more about the report.

Subreports run every time they appear in a report so if you have one in the page header, the query and the report is run again on every page. Depending on the linking fields, the subreport may well be running a different query on each page and perhaps the subreport on page 1 is quicker than the subreport on page 2 for this reason.

To idfentify what is the real cause of the performance problem, start removing items from the report until you know what's causing the slow performance. If it is the subreport (quite likely) then give us some more details about it.

You started by saying the problem was with the formulas but this is unlikely (but not impossible). Perhaps you can paste the formulas here too?

Steve Phillips, Crystal Reports Trainer & Consultant

http://www.smptraining.co.uk

 

[lbass]

Where are the subreports placed? In what section?

Are the summaries supposed to have the same values on all pages? If not, what are you expecting?

-LB

 

[gagani]

I am giving you the structure:
Formulas in Main report:
Record Selection formula:{ORDERS.SOLDDATE} >={?FromDate} and {ORDERS.SOLDDATE}<={?ToDate}
Group Selecton formula:{SALESBRANCHES.BRANCHNAME} = {?BranchName}

Two formulas which does sum at the group level and grand total level.
formula1: if{ORDERS.SOLDDATE} in {?FromDate} to {?ToDate} then
1
formula2: if({STATUS.STATUS})='YES' and
{ORDERS.SOLDDATE} in {?FromDate} to {?ToDate}
then 1 else 0

Report structure:
Report Header has got 2 subreports
subreport 1 returns distinct counts of
1. if{ORDERS.SOLDDATE} in date(year({?FromDate}),1,1) to {?ToDate} then {PERSONS.ID} else tonumber({@null})
2. if{ORDERS.SOLDDATE} in {?FromDate} to {?ToDate}
then {PERSONS.ID} else tonumber({@null})
subreport 2 returns count of
1. if({STATUS.STATUS})= 'YES' and
{ORDERS.SOLDDATE} in date(year({?FromDate}),1,1) to {?ToDate}
then {USER.orderid}else tonumber({@null})

Page Header has the formulas
1. formula2/formula1 of group level
2. formula2/formula1 of grand total level
3. formula2/one of the value of subreport1
4. one of the value of subreport1/value of subreport2

In short,I kept subreports at report header and placed the formulas based on the values returned from group level, grand total level and sub reports in page header.

 

[lbass]

I don't see any shared variables here, so no values would actually pass to the main report--unless you just didn't show us that these were set to shared variables.

What are the actual contents of your page header formulas? Please show the actual formula for "formula2/formula1".

Do you have a page break before each new group?

-LB

 

[gagani]

Yes, those were set to shared variables. I didn't mention it explicitly.
As mentioned, there are two formulas:
formula2:if({STATUS.STATUS})='YES' and {ORDERS.SOLDDATE} in {?FromDate} to {?ToDate} then 1 else 0
formula1: if{ORDERS.SOLDDATE} in {?FromDate} to {?ToDate} then1

these two are at group level and grand total level.

Now, actual contents of page header has 4 formulasare

1. (Sum ({@formula2}, {SALESBRANCHES.BRANCHNAME})/Sum ({@formula1}, {SALESBRANCHES.BRANCHNAME}))*100

2. (Sum ({@formula2})/Sum ({@formula1}))*100

3. (Sum ({@formula2})/({@firstvalueofsubreport1}))*100

4. (({@valuefromsubreport2})/{@secondvalueofsubreport1})*100

Input parameters for the report are fromdate, todate and branchname.

It will just give the result of one selected branch itself. I need the counts of the selected branch and also for all branches.
So I used group for it.

The values I am getting through these formulas has no problem. I gave an overview of all these formulas just to give an idea of how my report looks.
My questions:
To know why is it getting so delayed when I want to see the next page or last page. I am worrying whether placement of subreports in report header and formulas in page header are affecting in anyway.
2. To my knowledge, in crystal reports when user clicks the next page , then entire report will get loaded again and goes to second page. Is there any way the entire report gets loaded at one go and when the user clicks the next page option, it wont take any time to see the next page.

 

[lbass]

You could add Page N of M to the report, which would force the whole report to be processed before the first page prints.

As long as the subs are in the report header, not the page header, I don't see why there would be a delay per page unless you have a lot of running totals or something like that in other sections of the page which require longer calculation time.

You could also check under report->performance information to see whether you have unused formulas or other items that are causing a page printing delay.

-LB

 

[gagani]

Page N of M report has got very slight improvement compared to earlier. But not so major change.

I did not see performance information option under report. Could you please let me know where is it.

One more thing, In that report, I have created a view in sql and joined with other tables.

The query is like this:

Select orderid from orders
Where orderid in
(select distinct(orderid) from(select *from orders union all select *from person_orders )a
where orderstatus =7)

Is there any way I can write equivalent record selection formula for that query?

 

[lbass]

Report->Performance Information (bottom of dropdown list in XI).
If you look at database->Show SQL query, do you see all selection criteria passing to the query as it is? Is the query appearing as a single block?

You might also find the following thread helpful: thread149-1276307.

-LB

 

[gagani]

I want to filter my report based on the following query:

select orderid from orders where orderid in
(select distinct(orderid) from (select *from orders union all select *from person_orders)a where orderstatus =7)

Is there any way I can write in record selection formula or is it possible to write in sql expression? If so, could you please tell me how do I do it?

 

[gagani]

Could anyone please write the record selection formula for the query.

 

[lbass]

I thought you said you had already built this into your report. I asked to see your SQL query to see whether this view was linking locally versus on the server, and you have not yet responded.

Your query will not work as a SQL expression since it will return a set of values, but it could be used as a command. However, if you go the command route, you should build this query into a command as a subselect, and all other fields so that the command can be used as your sole datasource.

If instead you mean you want to use the query to populate a pick list, then write it as a command (Add command, above table list in the database expert).

Please do not be so impatient--it is the weekend and many people are not active on the site at this time.

-LB