Subreport Performance

[Former Member]

Hello Allu2026u2026..

I have a report with 2 subreports. Both subreports contain data from an outside source which is uploaded into a user defined table. When the main report had just 1 subreport, it ran fairly quickly. But when I added the second subreport, it ran for 6 hours or more. Both subreports are constructed basically the same and when run independently, they run quickly. The main report grouping is the same for the 2 subs. I have the 2 subs linked by by the same data field and also have a parameter set up for start & end date which is submitted by the user and driven from the main report. These parameter fields link back to the subreports which have the same start u2013 end fields. I do not know why the run time is so long with the secind subreport added.

All shared variables are working correctly and I am seeing correct expected results, I'm not sure why it takes all day to refresh.

Anyone experience something similar or have any suggestions or comments as to what could be elongating the refresh time.

Thanks Dave

[Former Member · Accepted solution]

Thanks, I'll give that a shot and see what happens.

[Former Member · Accepted solution]

Carl........Sorry for the delay, got pulled away on other tasks. I created another footer section and placed 1 subreport in there along with a reset formula. I now have 1 subreport in footer 1a, and the other in footer 1b. Each passes a formula field which are than added together and displayed in footer 1c. The report went from 6-9 hours run time to around 50 minutes. An improvement, but I would like to get refreshed within minutes. However, my subtotal formulas now shows the total for the first record and displays that value for every other record. How will I now handle the subtotal formula, will I need to reformulate the shared variables or how do I get the subtotals to display correctly again. Also, when you say reformat report, how drastically do you mean.

Thanks

[Former Member · Accepted solution]

It's good to see that the side-by-side subreports was your primary problem.

Remember that every time a subreport is called, it has to re-execute the query to get the data. If an idex isn't being used, that can cause a serious hit to performance. Perhaps you can change the query (adding subreport parameters or whatever) so that an index can be used to speed the subreports up.

Are the subreports displaying multiple records in each, or just used to return some piece of data from the database? If the later, get rid of the subreports altogether. You can perhaps replace them with one or more SQL Command data sources to retrieve the data you need. Or, maybe you can replace all of the tables and subreports with a single SQL Command (which would run the fastest, generally speaking). This may be a bit of work, but if refresh time is that important...

HTH,

Carl

[JWiseman · Accepted solution]

excellent advice as per usual Carl!

a couple more tips to ensure that your performance matches what you see on the report

A)

1) run the report in the designer with the Status Bar showing

2) watch the number of records coming in...you show see something like 100 of 100, 1200 of 1200...ever 100 of 500 or 1200 of 35666...i.e. the first number is the number of records being written to the report, the second number is the total being brought back from the database

3) as Carl suggested, a Command can be used to optimize this

B)

1) count the points of data that you see in each of your subreports...if you see 10 bars on a chart, an optimized report would bring back only 10 records. if you see 40 cells in a cross-tab, then 40 records, etc.

2) again, Commands are a good way to go if you cannot get Perform Grouping on Server to optimize your record set

cheers,

jamie

[Former Member · Accepted solution]

Carl/Jamie........Thanks for the suggestions. I'm not reallyy converse in what you are saying though, by using an SQL command. I'm not exactly sure how to use that approach. By changing the location of the second subreport, my shared variables when summed, now show just the amount of the first record. The subreports return just 1 field, just summed up by many records with multiple rows. I will need some time to absorb and also to research and learn what you are suggesting. It may be several days till I get back to you. Thanks again.

[Former Member · Accepted solution]

As an example of using a SQL Command to replace the subreport that is only returning one value: Assume that the subreport is showing the total payments for a customer during the report period. An SQL Command to get this might look something like:

select customer_code, sum(payment_amount) as Total_Payments
from payments
where date between {?start date} and {?end date}
group by customer_code

This can be added to your report just like any other table in the database. You can then link the command to the other table(s) based on customer_code. Now, all of your records will have the total payment amount field. Place it where you need it.

(Again, it would be better to get all of your data in one SQL Command, but if you're modifying an existing report, that may be a whole lotta work...)

HTH,

Carl

[Former Member · Accepted solution]

Carl.........thanks for your assistance. I tried using SQL in the report, took out the subreports, but I did not receive expected results. However, by rearranging the subreports, I was able to refresh the report in minutes at most. This is what I need.

But it comes with a price. Now the shared variables from the 2 subreports are displaying zeroes. How can I get the shared variables to work properly now that I mixed things up. Should these be in certain sections.

Below is the format.

GF1A......reset formula for shared variable from sub 1

GF1B......reset formula for shared variable from sub 2

GF1C......Subreport 1

GF1D......Subreport 2

GF1E......Totals of shared variables (shared var a + shared var b)

RFA........blank

RFB.........Grand Total of shared variables (suppressed)

RFC.........Grand Totals

All data from subreports are suppressed and work perfectly on their own.

Any thoughts, suggestions?

[Former Member · Accepted solution]

I don't see anything glaringly wrong with what you have for the main report. Where is the shared variable formula in the subs? Are you sure it is being executed? (For debugging purposes, add a Report Footer to the subs and display the value of the shared variable.)

HTH,

Carl

[Former Member · Accepted solution]

Both subreports have the shared variable in GF1 with a total in RFA. Total is also a shared formula (running total). Both working properly and returning expected results when viewed on their own and all sections are suppressed. Before subreports were separated, report ran for 6-9 hours but returned correct results.

Thanks

[Former Member · Accepted solution]

I've never had a shared variable NOT get passed back to the main report, so I still think that it might be that the formula(s) aren't getting executed.

At the very bottom of the formulas that set the shared variables in the subreport, add the following (basic syntax):

dim debug as number
debug = debug / debug

This will cause a divide by zero error, and pop up the formula editor. On the left side of the window, all fields referenced by the formula, and there current values, will be displayed. Make sure the shared variable has the correct value. (If you don't get the divide by zero, the formula's never getting executed.

HTH,

Carl

[Former Member · Accepted solution]

Carl.........hope this doesn't sound dumb, but do you mean in the actual formula syntax itself. I put those statements in and received "the remaining text does not appear to be part of the formula" error.

Here's example........

whileprintingrecords;

shared currencyvar expected_total;

expected_total := expected_total + Sum ({TABLE_A.ExpectedAmount});

expected_total

dim debug as number

debug = debug / debug

[Former Member · Accepted solution]

Yes, in the formula. However, your formula is in Crystal Syntax, and my example was in Basic Syntax. I can't say I'm strong on Crystal Syntax, but I believe that would be:

Numbervar debug;
debug := debug / debug;

HTH,

Carl

[Former Member · Accepted solution]

Carl............That worked, I assume this is looking at the results from the first record.

The formula workshop displayed for each subreport saying there is a divide by zero.

Subreport 1 returned expected value. (33,199) That is correct for first record.

Subreport 2 returned 0, which is expected for the first record from that subreport.

Can this test go further, in other words check the 2nd/3rd record.etc.....

So, that tells me that the subreports are returning values as expected. Then in the main report, for some reason, they are showing zeroes. Is this because of the layout, should they be in different sections??

Thanks

[Former Member · Accepted solution]

You can control which record the formula gets the divide by zero on using something like this:

numbervar debug;
shared numbervar cnt;

cnt := cnt + 1;

if cnt = 2 then {
  debug := debug / debug;
}

to break on the second record.

Where is the shared variable getting reset to zero? That would be the first issue that I'd look for.

To figure out the order of execution of the formats and subreports, you can put the debug logic in other formulas in the main report. I don't see any issue with the order that you have things defined (as shown in the prior post)...

HTH,

Carl

[Former Member · Accepted solution]

Carl.........I did what you suggested, put the divide by zero logic in all my formulas and watched on the left panel as they were executed. As each one popped up, it displayed zero. I removed each formula one at a time, so the next formula can execute. Each formula resulted in zero. I then went into the subreport and forced the shared variable to a constant, just a random number. Again, all zero results. From a prior post, you stated this would mean the formulas are not passing correct dtata because they are not executing. How can this be accomplished? Although it appears the subreports are placed OK in the main report, what else can be affecting the correct data from displaying??

Thanks for your help and sorry for the delay, priorities intercede.

[Former Member · Accepted solution]

One clarifying point, when formulas are reviewed in the subreport, either from the database or a random, the subreport displays the correct number on the left panel with the divide by zero logic. It is just not being passed/executed to the main report

[Former Member · Accepted solution]

Are you certain that everywhere the values are used they are declared as shared? It sounds like the numbers aren't going between the sub and main reports, which requires shared variables. (Note that global variables do not get passed between sub and main report.)

And, are you certain that all of the formulas have WhilePrintingRecords?

HTH,

Carl

[Former Member · Accepted solution]

Below is a sample of the formulas.......

whileprintingrecords;

shared currencyvar expected_detail;

expected_detail;

Numbervar debug;

debug := debug / debug;

I was unaware that global variables do not pass. How can I determine if mine are global or not??

And yes, all formulas have "While Printingrecords" as the first line along with "shared"

[Former Member · Accepted solution]

Make sure they all have "shared" in the type declaration line, and that they have the exact same data type. Also, to be complete, make sure you didn't misspell the variable anywhere...

HTH,

Carl

[Former Member]

Yes, they are side by side in group footer 1a. I have them there because they both basically perform the exact same function except from 2 different sources. The shared variable they send to the main report are then summed together.

Should they be further apart or in a different subsection??

[Former Member]

Just to check for performance, try putting them in separate sections. If the report runs quickly (relatively speaking) then you probably want to try reformatting the report so the subreports are not side-by-side.

HTH,

Carl

[Former Member]

Where, exactly, are the subreports in relation to each other and within the various formats? If they are side-by-side, I could see it taking quite a while to figure out how things need to be displayed...

HTH,

Carl