Duplicate Formula evaluation

[emallon]

I'm opening a CR8 report from VB6 using the automated server. In the report is a formula that calls a User defined VB6 DLL. The DLL formats a datetime stamp and executes a stored procedure to load a table with user specific data.

The problem is as follows:

When the report is executed manually the stored procedure returns a set of data with one time stamp but when the report is 'called' from the VB6 frontend the report returns two sets of data with two time stamps. This indicates that the formula in the report is being executed twice.
To further back this result, the time stamp difference is always 1 minute and 12 seconds. Also, the report uses the second set of data with the later time stamp.

 

[kenhamady]

I would like to see the Crystal formula that calls the VB DLL.

I am surprised that the report accepts that formula. The manual report may be ignoring the formula altogether since a formula is used to add a column to the results, rather then to set the source data of the report. In other words, once the formula tries to create a table; what did you expect it to do with that table?

When VB 6 runs the report and forumla, where do you see the two data sets? In the report?

Ken Hamady-

http://www.kenhamady.com

http://www.kenhamady.com/

 

[emallon]

BeforeReadingRecords;
salesAuditWeeklySalesAudit({@UserID},{@Password},{@OraID})

Ken,
CRUFLsales is the name of the DLL.
Audit is the name of the class module in VB.
WeeklySalesAudit is name of VB procedure inside of
Audit class module inside of DLL.

Thus its name.

The three parameters are self explanatory. I know you know the function being used in conjunction with the call procedure.

This formula is suppressed and placed in the report header section.

The table data is the source for report.

I see the first result set in the table and the second result set is eliminated by another procedure call to eliminate the each individual's records so the table is constantly cleaned out. I've tested the report with the 'delete' call commented out and both result sets appear in the table.

 

[kenhamady]

Placing this formula in the report header does not cause it to feed any data to the report. (Unless you know some magic that I do not know. I DONT know the function that you are referring to with the call procedure).

The report data is going to come from the tables used in the Visual linking expert, based on the links used, and the criteria set in the select expert/selection formula.

I have no idea how the report will interpret this "formula".

If you delet this formula from the design canvas, and then run the report stand alone do you get different results?

Ken Hamady

http://209.41.46.214/

 

[emallon]

Ken,
The formula works whether its on the design canvas or not. The results don't change. The function in the formula is one I created. Unless you create a duplicate you can't test it. Its located in the DLL I created(CRUFLsales). A function in the DLL executes a stored procedure using the parameters it is passed from Crystal.

The problem I'm trying to resolve is why is the formula evaluated twice when the report is executed from a VB6 front end and only evaluated once when manually executed.

I appreciate your time and effort on this.

Thanks,
Ed.

 

[kenhamady]

I don't think I am being clear. The point I was trying to make is that this formula isn't what is providing the data for the report.

My guess is that if you remove the formula completely from the report formula list, the report will still run exactly the same. This is becuase the report is taking the data directly from the joined tables, not from the formula call.

As far as I know, reports can't get their recordsets from a formula, regardless of what the formula does. That would explain why VB gets the data twice, because VB gets the report data and also processes the call. Meanwhile the report is just retrieving the data naturally and skipping the call.

Ken Hamady

http://www.kenhamady.com/

 

[emallon]

Formula cannot be deleted from report because it returns the session_nbr(table key) for the individual running the report. This key is part of the 'where' clause.

There are no join tables. The report uses a single table loaded by the stored procedure run thru the execution of the formula.

Your theory, if I understand, is that VB is executing the DLL prior to it being called by the report.
I'll examine that.

Just for future info, you can get recordsets back from formulas, provided the formulas are executing store procedures (functions). Here's the steps:
You create a DLL using VB.
Inside the DLL you code functions. My function runs a stored procedure that loads a table.
Then 'Make' the DLL from VB. This registers it on your PC.
At that point, the functions coded in the DLL appear in the additional functions list for your use.

 

[kenhamady]

I know that you can create a DLL and add it to the function list. I don't doubt that the DLL does whatever you tell it to do,including generating a recordset.

But I still don't see how THAT functions recordset can become the data that the report is based on. At best it might affect the recordset of the report.

You said that the formula brings back the key that goes into the where clause. That means there must be a second call to the data. (One to get the key, and one using the WHERE with the key.) That is my point. The first call (formula) is not providing the records for the report, the second call is.

Maybe VB is showing these two calls in a different way than the standalone report.

Does this make any sense? Ken Hamady

http://www.kenhamady.com/