Unlinked Subreport Executing Once for Each Row!?

[PlasteredDragon]

Hi!


I've inherited the reporting subsystem of a large multi-site tracking application. (We're using CR10). Each report/subreport gets its data from a different stored procedure on a SQL Server database.


All of the reports, by requirement, have to display the name of the customer's company in the report footer. Since this software is used by multiple customers, this text cannot be fixed. So all of our reports have an unlinked subreport in the page footer that calls a small stored procedure that fetches the company name from its hiding place in the database.


While running performance traces on the database I was amazed to discover that Crystal is calling this stored proc once for each row in the main report resultset. So if there are 100,000 rows of detail data, Crystal will request the Company Name 100,000 times even though it isn't part of or used in the detail section. This is swamping the database with traffic and it is wholly unecessary to run the company name query more than once.


Which brings me to my question. Is there any way to run an unlinked subreport ONCE only? If not, do I have any other options so that Crystal will fetch this value once, instead of 100,000 times? Please help! I know databases but I'm pretty inexperienced with Crystal.

Thanks!

-- Chuck S.

 

[Turkbear]

Try placing the sub in the REPORT footer, not the Page Footer.
Should then only execute once per report.







To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[PlasteredDragon]

Thanks, but the requirement is that the company name appear at the bottom of every page, not at the end of the last page of the report.

I'm looking for a solution that meets the requirement.

Is there no way to do this?

 

[Turkbear]

Hi,
Not with a subreport, as far as I know..But, can you create a Sql expression that returns the Company Name and use that in a formula in the Page Footer?







To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[PlasteredDragon]

Ah! An alternate solution! That sounds promising.

I know how to write SQL, are you saying there is a way to put SQL directly into a formula field?

And if I did this it would only execute once? Or would it be once with each page?

Or are you talking about adding another data table to my report? Since that data table would not be linked, would the report hit it 100,000 times?

-- Chuck S.

 

[Turkbear]

Hi,
Upon further reflection, you can create a formula
called Company_Name This assumes that the report has a selection criteria that limits the data to one Company and that the Company Name is in the report's tables somewhere)

StringVar Company_Name := {tablename}.{CompanyName}

Place

@Company_Name in the Page Footer..






To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[vidru]

If the name is the only thing you're fetching from the database, perhaps you could put the unlinked subreport in the Report Header, then use a shared variable to put the name in the Page Footer.

For example, move the subreport to the Report Header. Put the subreport in design mode, and enter a formula like this:
[tt]
//The second line is just so the formula doesn't actually
// output any text.
Shared StringVar CompanyName := {ProcedureName.Field};
"";
[/tt]
Presumably, the procedure only returns a single row of data, so I'd place the formula in the subreport's Report Header section, and suppress all other sections.

In the main report, create another formula similar to this:
[tt]
Shared StringVar CompanyName;
[/tt]
Then, just drag the formula into the main report's Page Footer.

As Turk also metioned, you may be able to do this with a SQL Expression field, depending on your type of connectivity, as well as whether the main report is using tables/views or a stored procedure (if it's using a stored procedure, then you won't be able to use a SQL Expression field).

-dave

 

[PlasteredDragon]

I appreciate your advice, Turk and Dave. Sorry I'm so inexperienced with CR! I'm sure my questions sound pretty thick!

Turk, the company name isn't anywhere in the main report's tables, that's why we're grabbing it via an unlinked subreport. It occurred to me already to add it to the recordset returned by the stored proc called in the main report. But at 100,000 rows, adding a 30 character field means that 3 million additional bytes need to be trucked over the network, and if those 3 million bytes amount to 100,000 copies of the same string, it's an incredibly inefficient solution to what should be a simple problem.

Dave, so if I understand you correctly:
1. put the subreport in the report header of the main report
2. put a formula field defining a shared variable in the header of the subreport
3. set this equal to the company name fetched by the subreport
4. suppress all the sections of the subreport except its header
5. define another formula field in the main report that defines the same shared variable
6. place this formula field in the page footer of the main report
7. (and presumably) size the subreport in the report header to be very very tiny, so that it doesn't display.

Did I get that right? So this is really the best way to use an unlinked subreport to display information on a page footer without having it be re-executed N times where N=number of rows in main report?

Seems complicated, how would you recommend documenting something like this for the next report developer who comes along?

 

[vidru]

Yep, you got it right. As far as documenting, in cases like these, I'll create a text object (using red/bold font) that describes anything that a future developer might need to know, then use a suppression formula on the object so it displays at design time, but not at runtime.

-dave

 

[PlasteredDragon]

Thanks for the advice Dave and Turk! Sounds like I have a solution!

-- Chuck S.

 

[Turkbear]

Hi,
Nice one Dave....I learn somethng new every time I visit this place..( Never thought of using the Header/Shared Variable in Footer method).







To Paraphrase:"The Help you get is proportional to the Help you give.."