repeating execution 3

[Cagliostro]

Hi,
I have a small question,
when I insert different expressions in a Crystal Report, it is executed many times, as many times as expression is repeated in the design. I want the expression to be executed at once, how can I do this?
(I'm dummy in Crystal Reports)


Ion Filipski

 

[kskid]

A little more technical information would be helpful. See FAQ149-3762

When you say expression, are you talking about SQL expression? It would be helpful to post your "expression", too.

Normally repeating execution in a report means that you have it in the detail section and will execute with each detail record selected. If you want it to execute one, are you saying once for the entire report or once for each group.

As I said earlier, you are too vague with your post for us to provide you with a workable solution.

Cheers,
-LW

 

[Cagliostro]

there is ODBC connection to MSSQL 2000, Crystal Report version 10
select something from sometable
where account_id={?xxxxx?yyyyy}

Ion Filipski

 

[kskid]

Need more technical information. As I said earlier, see FAQ149-3762 so we can see exactly what the problem is. Different versions of Crystal have different solutions.

What is the formula or expression you are using?

Where is the formula being used? In the record selection, detail section, group header/footer, report header/footer?

What does the sample data look like and what results are you expecting.

There is just too many questions surrounding your problem to determine with any certainty what needs to be done.

Understand where we are coming from?

-LW

 

[Cagliostro]

there is such feeling you have not read my reply.
copy/paste
------------------
1. there is ODBC connection to MSSQL 2000, Crystal Report version 10
2. select something from sometable
3. where account_id={?xxxxx?yyyyy}
------------------------------------

parsing what I've replied
1. there is ODBC connection to MSSQL 2000, Crystal Report version 10
1. *******data source ^^^^^^^^^^^^^******* **** version ^^

formula (just an sql query):
2. select something from sometable
3. where account_id={?xxxxx?yyyyy}


the expression is used in header

Ion Filipski

 

[kskid]

It depends on what you are selecting ( see Database->show SQL Query)

You did not specify which header section.

If the expression is in the Report header, then it will execute only once. If it is in the page header, then it will execute with ever new page. If it is in a group header section then it will execute twith every change of group.

Again I reiterate, You did not provide any sample data and what you are expecting as output. Surely, you can provide a general description of the type of data you are trying to select and what you want to do with it. What type of report are you trying to create? A summary report of sales? A detail doctor/patient report? A report on personnel work hours?

There are many people in this forum with different business disciplines that can provide the necessary assistance you need but you have to be more specific in what you are trying to accomplish other than I want to "execute this one time".

Yes I can "select something from some table where accountid = {?xxxx?yyyy}" but I need more specifics than what you provided. What tables are involved? How are they linked? What are the link fields? What are the relationship between the tables? One to one or one to many? How do you want the data grouped? What kind of summary or running totals do you need?

That is what I need in order for me (or anyone else in this forum) to provide a woking solution for you.

Honestly, we are more than willing to help but we just need more technical information.

-LW

 

[Cagliostro]

Ok, I need to insert the same expression 100 times in some report, no matter what I want to do. I want this expression to be executed at once, not 100 times. It does not matter where data is takem from, what kind of accessing database and such kind of stuff. Maybe this could be a matter of versions, but I don't believe.

Ion Filipski

 

[kskid]

Your question may be more technical than anyone here can address.

If I understand you correctly, you are asking How does The Crystal Report Software itself handles an expression that has been placed on a report 100 times.

Does the software executes it 100 times or one for each time it is found on the report canvas. Or does Crystal Report executes it one time, stores the results and places the value on the report canvas whenever the expression is encountered.

If that is what you are asking, then you will have to address this with Business Objects itself. Whether they will provide you information about how their software works sounds propriety to me.

That kind of technical information is NOT what this forum is about.

Good luck

 

[kskid]

I have a couple more questions that I probably should have asked at the beginning of this thread.
[ol][li]Are you using Crystal Reports directly (i.e., Start->Programs->Crystal Reports) or are you writing an application (i.e., VB, C#) that access Crystal?[/li]
[li]What are you trying to do that requires you to know whether it executed once or 100 times.[/li][/ol]

-LW

 

[Cagliostro]

I don't access report from anywhere. Just designing it directly.

What am I doing is looking in SQLServer profiler. There is shown each operation each time is executed. I've overpassed this by using shared variables.

Ion Filipski

 

[kskid]

Normally SQL expressions are executed once based on Database->Show SQL Query. If linked properly with the proper record selection formula in place, then the number of records that are return will be minimal. However, if a formula cannot be properly evaluated (it's in the record selection formula but not in Database->show SQL query, then a full table scan is made and ALL the records are returned. This is espcially evident when trying to writing formulas that make date calculations.

Hope this helps

-LW

 

[IdoMillet]

Consider placing a subreport in the report header. It will execute only once. You can then grab the result(s) using shared variables using formulas inside the main report.

Cheers,
- Ido

CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[kskid]

One thing I forgot to mention.

Within a given Crystal Report, the SQL Query (Database->Show SQL Query) will execute once. However, if that same report contains any subreports, then the SQL Query of the subreport(s) will execute as many times as it is triggered depending on what section the subreport(s) is/are placed.

That is why Reports with subreports causes poor performance because of the repeated hits to the database.

Subreports are normally used as a last resort to solve a reporting problem.

Cheers,
-LW

 

[kskid]

One thing I forgot to mention.

Within a given Crystal Report, the SQL Query (Database->Show SQL Query) will execute once. However, if that same report contains any subreports, then the SQL Query of the subreport(s) will execute as many times as it is triggered depending on what section the subreport(s) is/are placed.

That is why Reports with subreports causes poor performance because of the repeated hits to the database.

Subreports are normally used as a last resort to solve a reporting problem.

Cheers,
-LW

 

[IdoMillet]

Actually, that is one case where a subreport actually improves performance, because by placing it in a section that executes only once, you can avoid the repeated evaluation of an SQL expression.

Cheers,
- Ido

CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[Cagliostro]

IdoMillet, thanks, if you gave the responce easterday morning it would save a lot of time.

http://www.tek-tips.com/viewthread.cfm?qid=997590&page=1

I've solved my problem already by using subreports, the same as you told there. Anyway, your post is helpful and thanks a lot


Ion Filipski