How to pass parameter selection to SQL

[timky]

Hi experts,

I have a real problem here. I have checked with our local senior crystal consultant and they were not able to help me.

I have defined a friendly date parameter called (?Process Date) and allow user to select
P - (current biz date)
C - (current system date) or
user input date (dd/MM/yyyy format)

The following formula is to parser the (?Process Date)

(@Parser Date)

LOCAL NUMBERVAR ndd:=0;
LOCAL NUMBERVAR nmm:=0;
LOCAL NUMBERVAR nyyyy:=0;
LOCAL STRINGVAR DbParser;

IF {?Process Date} = 'P' THEN
DATE(GetBizProcessDate)
ELSE IF {?Process Date} = 'C' THEN currentdate
ELSE
IF ISDATE({?Process Date}) THEN
(
nmm := ToNumber(TOTEXT(CDATE({?Process Date}), "MM&quot);
ndd := ToNumber(TOTEXT(CDATE({?Process Date}), "dd&quot);
nyyyy := ToNumber(TOTEXT(CDATE({?Process Date}), "yyyy&quot);

IF ((nmm < 13) AND (ndd < 13)) THEN
(
DbParser := TOTEXT(CDATE(nyyyy, ndd, nmm), &quot;MM/dd/yyyy&quot;
CDATE(DbParser);
) ELSE CDATE({?Process Date})
)
ELSE DATE(0,0,0)

If user select 'P' options, the report will use the UFL function (GetBizProcessDate) to retrieve current biz process date from database.

Ok, the real problem is when I add this formula into the Selection Formula, it will not pass to SQL to process until it return all the records.

How can I improve this type of selection so that it will pass to SQL?

Your help is very much appriecated.

 

[synapsevampire]

First, never use a variable when constructing a record selection formula, or in any formulas which are used by the record seleciton formula.

Let's change how you're doing this to make more sense:

In your @parser formula just use:
IF {?Process Date} = 'C' THEN
currentdate
// not sure how well Crystal passes SQL from a UFL
ELSE
IF {?Process Date} = 'P' THEN
CDATE(GetBizProcessDate)
else
if isdate({?Process Date}) then
cdate({?Process Date})
// you may need to straighten add in another clause for
// an error trap

In the record selection formula use:

(
{table.date} = {@parser}
)

There are basic factors involved here that you didn't share:

Crystal version
Database used
Connectivity used

And as you build the @parser formula used by the record selection formula, do it one step at a time (or remark out sections) so you know where you're failing. Then check the Database->Show SQL Query to test.

BTW, I have a FAQ on creating record selection formulas here:

faq767-3825

-k

 

[timky]

Hi kai,

thanks for the response. In fact, I have viewed through all the relavent posting before I posted this question.

sorry.. the info as below :-

Crystal Version - 8.5
Database - Oracle 8i
Connectivity - ODBC

I have tried the method that u mentioned but it still wouldn't pass down the selection to SQL due to the UFL function. If I remark it, then it will be alright.

If I don't use the UFL function, is there any other methods to go about it? How usually other experience programmer handle this case? I believe that this problem is quite common as most of the system will store the biz process date in the table so that when the date rollover to next date, it wouldn't effect the system processing. However, we need to give user the flexibility to select the date.

ok, just a small query about your FAQ767-3825, section below :-

(
If {Table.CustomerChoices} <> “All” then
{Table.CustomerChoices} = {?MyParmChoices}
else if
{Table.CustomerChoices} = “All” then
True
)
and
(
{table.CustomerLocation} in [“CA”, “OR”, “WA”]
)

--

this selection will retrieved all customer Location in CA, OR and WA. Only then, it will perform filtering whether to select CA, OR, WA or ALL.

do u think we need to build sepecial function to retrieve the biz process date??

thank you.

 

[synapsevampire]

Hi Timky (how'd you know my name?).

I'm not sure what your question is regarding the customerchoices, but that is a different parameter and field from the states, I left the state in the wuery because it was a continuation from the previous record selection.

The point of it is to get SQL to pass if they make choices, otherwise pass nothing, it never passes &quot;All&quot;.

Perhaps if you describe with example tables and data what this UFL does we can find an alternative way of handling it.

At least you have 2/3's of it passing at this stage.

-k

 

[AnnetteH]

Using a UFL or a regular Crystal formula will never get passed with the SQL query since it can't be translated into SQL.

You have three possible options given the complexity of your formula. One is to convert your UFL function into an Oracle function, then create a SQL Command object containing your query which uses the Oracle function in the WHERE clause, passing the parameters directly into the function. Two is to write a stored procedure which takes the parameter, does the work of the function, AND returns the result set. Three (not tested) is to also convert the function to Oracle, then use it in a SQL Expression, then use the SQL Expression in a select formula.

 

[synapsevampire]

Anette: Crystal formulas can be passed, not sure what you mean by &quot;regular&quot;. Just don't use variables in it.

All three of your options makes sense (essentially they're the same), but you omitted that they could use a Crystal formula to do the same thing if possible, which may not be the case here.

Check out my FAQ here, it uses Crystal formulas.

-k

 

[timky]

AnnetteH,

Your third options sound good to me. But the problem is how am I going to convert the oracle function into SQL Expression?

I don't know crystal well and so far I don't see you can add the oracle function into crystal reports or SQL Expression.

Pls help.

Kai - Actually, out database design is very simple as below.

Trasaction Table (consists of daily transaction records)
Parameter Table (consists of all the relevant parameters info)

GetBizProcessDate() will retrieve the biz date from parameter table and return the date.

As such, user have three options to generate the transaction report.

I hope AnnetteH's solution will work. We try not to use the stored procedures as we have about four hundred reports. Imagine, how many we need to convert it.

thank you.

-tim

Tim

 

[AnnetteH]

Synapsevampire: You're right that some formulas will get passed. I overgeneralized. By 'regular' I meant a true Crystal formula and not a SQL Expression.

However, almost none of the functions available for use in Crystal formulas will get converted to SQL and passed to the database. And no control structures such as the If-Then in your example will get passed. In your example, the formula is evaluated BEFORE the query is constructed since it uses only input parameters and no database fields, so the result of your formula is passed as a constant in the database query. Which, of course is fine, and works for many situations.

My understanding of this question was that the function GetBizProcessDate accesses the database to return a value. (I'm not even sure how that could be programmed into a UFL unless they used OS Authentication or embedded the logon criteria.) So, the most straightforward option seemed to be to convert that to an Oracle function and use it in a SQL Expression.

Timky:
1. Create an Oracle function called GetBizProcessDate and give it whatever access privileges are required for your users.
2. Create a Crystal formula that converts your Process Date parameter into an actual date for ease of processing later. @ProcessDate =
if isdate({?Process Date}) then cdate({?Process Date})
3. Create a SQL Expression field called Parser where you pick the right date to use. %Parser =
Decode({?Process Date},'C',sysdate,
'P',GetBizProcessDate,
{@ProcessDate})
4. Use the SQL Expression in your selection formula
{transaction.date}=%Parser

Hope this works. I don't have time to check it. Gotta go to the dentist.

 

[timky]

AnnetteH,

Finally, I learnt how to call Oracle function from SQL Expression. Thanks…

However, I noticed that you cannot access the Crystal Formula or Parameter from SQL Expression Editor. Any idea how to overcome it??

I have done a simple test and created an Oracle function to return the ‘BizDate’. I created a SQL Expression, {%GetBizProcessDate} as below (GetBizDate() is an oracle function) :-

To_char(GetBizDate(‘P’), ‘dd/MM/yyyy’)

The problem is it wouldn’t get pass down to SQL to process. It’s I have done something wrong!! Please advise.

Many thanks.


Tim

 

[AnnetteH]

OK, there is a problem passing report parameters in a SQL Expression. Use a SQL Command instead. (Assuming you're using CR 9 or 10.) Here's an example that I did using the XTREME sample data.

I created an Oracle function as shown.

CREATE OR REPLACE FUNCTION
GET_REPORT_DATE (REPORT_PARAM VARCHAR2)
RETURN DATE
AS
BIZDATE DATE;
BEGIN
IF REPORT_PARAM = 'C' THEN RETURN SYSDATE;
IF REPORT_PARAM = 'P' THEN
SELECT MAX(order_date) into BIZDATE from orders;
RETURN BIZDATE;
END IF;
BEGIN
BIZDATE := TO_DATE(REPORT_PARAM,'DD/MM/YYYY');
RETURN BIZDATE;
END;
EXCEPTION
WHEN OTHERS THEN RETURN SYSDATE;
END;

Then I created a report based on the following SQL using a SQL Command object.
SELECT &quot;ORDERS&quot;.&quot;ORDER_ID&quot;,
&quot;ORDERS&quot;.&quot;ORDER_AMOUNT&quot;,
&quot;ORDERS&quot;.&quot;CUSTOMER_ID&quot;,
&quot;ORDERS&quot;.&quot;EMPLOYEE_ID&quot;,
&quot;ORDERS&quot;.&quot;ORDER_DATE&quot;
FROM &quot;XTREME&quot;.&quot;ORDERS&quot; &quot;ORDERS&quot;
WHERE &quot;ORDERS&quot;.&quot;ORDER_DATE&quot;
= Get_Report_Date('{?ReportDate}')

Note the single quotes around the parameter. When you use parameters in SQL Commands, Crystal just concatenates them in, so you need to add the quotes since the SQL statement would not be correct without them around the string literal.

Note that if the ReportDate parameter contains anything other than 'P', 'C', or a valid date in the 'dd/mm/yyyy' format, the current date will be returned.

I've verified this example. It works as expected for me.

 

[synapsevampire]

Annette: You're mistaken about passing SQL, check the dozens of posts here where people have thanked me for demonstrating this technique. I've done extensive research and believe that I have the definitive FAQ for CR 8.5 and below, like many people your were probably using variables or you constructed the record selection improperly - instant SQL killer, read my FAQ and test. Though some formulas may not pass due to functions used, the above example does, and the IF's are VERY important, as are the parenthetical structure.

Fortunately for you, you're using CR 9 which allows for a good deal more flexibility.

BTW, Tim's using CR 8.5, it's in the thread.

Tim: If a SQL Expression is accepted by Crystal, it's getting passed to the database (check the select line).

Not sure where your problem is now, let's read of what you need at this point.

-k

 

[timky]

Annette,

Unfortunately, I am using CR 8.5 and it didn't come with the SQL Command Object features. I wish I can do something in CR 8.5 without upgrading to CR 9.0.

Kai,

My problem is still the same. It still cannot pass down the selection to SQL to process. I noticed that CR 8.5 wouldn't pass donw any defined functions (UFL or orcale functions) to SQL.

pls help.



Tim

 

[synapsevampire]

Instead of using the Oracle function, write the functionality into the SQL Expression.

I don't have Oracle installed, so I can't test right now, but a SQL Expressions should handle it.

SQL Expressions are passed to the database, if there isn't an error then it must be passing because Crystal couldn't perform the function. I'm not sure if you one can use functions safely in them as I haven't done so, but make sure that you're using the Crystal supplied ODBC driver.

Since you're able to write Oracle functions, Annette made a good suggestion of just using a Stored Procedure.

-k

 

[timky]

kai,

As I mentioned earlier, I have tried it but it wouldn't get pass down to SQL. do u think I need to try out CR 9.0?

We have converted some of our critical reports to Stored Procedures and I hope that we don't have to do the rest as we have written 400 hundred over reports so far and most of them are very complicated. I really hope that CR 8.5 can handle this part.

tq

Tim

 

[AnnetteH]

Synapsevampire: I think we've just got a misunderstanding of terms. When I say &quot;passed to the database,&quot; I mean that the formula is converted to a database equivalent and shows up in it's converted state in the SQL query. This is different from your definition which seems to be that the selection formula works correctly. Obviously working correctly is the most important feature, but in your examples there are really two different things going on. Some parts of the formulas are converted and passed in the SQL query. Other parts are evaluated BEFORE the query is constructed and hence are passed to the database only as literals.

In the case of your FAQ in particular, you have an example that contains
If {Table.CustomerChoices} <> &quot;All&quot; then
{Table.CustomerChoices} = {?MyParmChoices}
else if
{Table.CustomerChoices} = &quot;All&quot; then
True
Assuming that you really mean
If {?MyParmChoices} <> &quot;All&quot; then
{Table.CustomerChoices} = {?MyParmChoices}
else if
{?MyParmChoices} = &quot;All&quot; then
True
Then what happens is that Crystal evaluates the condition and if the parameter is equal to &quot;All&quot;, it appends nothing to the query for that condition. If the parameter is not equal to &quot;All&quot; it appends the appropriate statement to the WHERE clause of the query to return the list of choices. Check the generated SQL and you will see what I mean.

Even your totext example is not converted to a database equivalent like TO_CHAR and passed to the database. Instead Crystal Reports does the datatype conversion and passes the result in the SQL query.

Again, what is most important is the final result, and your examples are useful instruction. However, the If-then works because the condition is evaluated using parameter values and not database fields and the ToText works because it is using parameter values and not database fields. If either used database fields then they would not work.

 

[AnnetteH]

Tim, here is a solution that should work with CR 8.5. I don't have 8.5 running so I can't verify. (Actually, I'm using CR 10. Yes, its now available!)

Create the Oracle function similar to this. (No parameters requires.)
CREATE OR REPLACE FUNCTION
GET_REPORT_DATE
RETURN DATE
AS
BIZDATE DATE;
BEGIN
SELECT MAX(order_date) into BIZDATE from orders;
RETURN BIZDATE;
END;

Create the report without selection criteria.

Create a SQL expression call Get_Date with just a call to the function.
Get_Report_Date

Construct the selection formula like this.
If {?ReportDate} = 'C' then
{ORDERS.ORDER_DATE} = CurrentDate
Else If {?ReportDate} = 'P' then
{ORDERS.ORDER_DATE} = {%Get_Date}
Else
{ORDERS.ORDER_DATE} =
Date (ToNumber (Right({?ReportDate},4)),
ToNumber (Mid({?ReportDate},4,2)),
ToNumber (Left({?ReportDate},2)))

That should do it.

 

[timky]

AnnetteH,

Thanks so much for your effort. However, what I want is the selection must be able to pass down to database to process before it return the result.

For the mentioned solution, it works but it only get filter out when all the result return from database. Imagine, we have billion records and it tooks few hours to pull all the records and only then do the filtering.

We never notice this problem when we start to develop our reports. We reliaze this problem when our database grow bigger. Any more brilliant ideas!

once again thanks your time and effort.

Tim

 

[AnnetteH]

Tim,

This solution should be doing that for you. If the parameter is 'C' then the WHERE clause should be saying WHERE your_date_field = To_Date(...for today...). If the parameter is 'P' then the WHERE clause should be saying WHERE your_date_field = Get_Report_Date. If the parameter is a date string then the WHERE clause should be using that.

You won't get one distinct query this way. You'll get three different ones depending on the value of the parameter, but in each case the filter should be in the query. Is that not what's happening? It does happen that way in my example. Look at the Database|Show SQL with each different paramter type. If it isn't passing a filter on the date as expected, post it here.

 

[timky]

AnnetteH,

Perhaps, I show you what I have done as your helpful hints.

1. Create a oracle function called GET_REPORT_DATE (As you mentioned)
2. Create a report without selection criteria.
3. Create a SQL expression call Get_Date with just a call to the oracle funciton.
4. Contruct a formula like this (as you mentioned)
If {?ReportDate} = 'C' then
{ORDERS.ORDER_DATE} = CurrentDate
Else If {?ReportDate} = 'P' then
{ORDERS.ORDER_DATE} = {%Get_Date}
Else
{ORDERS.ORDER_DATE} =
Date (ToNumber (Right({?ReportDate},4)),
ToNumber (Mid({?ReportDate},4,2)),
ToNumber (Left({?ReportDate},2)))

The problem is for point no. 2, how to create a report without selection criteria?? If not selection criteria provided, the SQL query wouldn't know the parameter value.

However, I placed the formula in the 'Select Expert'.

Please advise the steps that I mentioned.

tq

Tim

 

[synapsevampire]

Annette: We don't have a diffferent meaning, and thanks for pointing out the flaw in my FAQ, I meant this:

If {?MyParmChoices} <> &quot;All&quot; then
{Table.CustomerChoices} = {?MyParmChoices}
else if
{?MyParmChoices} = &quot;All&quot; then
True

Your point is puzzling, why would you want to pass anything if the selection is ALL? You want to state where table.field = table.field??? Pointless, or perhaps you misunderstand it's intent or I misunderstand yours.

And this:

&quot;Even your totext example is not converted to a database equivalent like TO_CHAR and passed to the database. Instead Crystal Reports does the datatype conversion and passes the result in the SQL query.&quot;

My FAQ didn't have any totext in the record selection formula, it was there to show people how to display parameters in the report, and it would be silly to create a whole column on the database side so that the server could return the same to_char of some parameter (it wouldn't be known to it anyway through CR).

I'm a major proponent of using Views (never tables), and against the Views, create Stored Procedures or create normalized Views for the data set required.

Using CR 9 I'd use The Add Command to pass SQL as the next option, next use the Crystal linking (leverage SQL Expressions wherever possible), last use the Crystal SQL Designer. In CR 8.5 you can use ADO and pass real SQL also.

Everything should be processed on the database server.


Thanks again for finding the flaw in my FAQ< I can't believe that so many people read it without catching that...

-k