asked on

Crystal Reports: Needing advice on a variable used in several calculations

Currently, I've been tasked with cleaning up a large volume of reports that have "static" values to make them more dynamic.

My only problematic variable so far has been with a "Length of Stay" (LOS) variable. In the reports, a claim may be either "Less than or equal to 10 days" OR "Any number of days" (the latter could be 0 through whatever number whereas the former only could be 0 through 10).

As the reports are set up, the @LOS variable I discovered hasn't been calculating correctly; that is, it was setup as a filter on the column as a numeric. It looks like one could enter "10" and that was translating in the Crystal coding to LOS <=10...which is ok...but then if you removed the filter, the LOS wasn't necessarily picking up ALL LOS.

So my question is: if I keep this variable as is, where the user may enter 10, which maps to the <=10 days, how would one advise to have the parameter for "ANY LOS"?

Also, being more familiar with SQL, I feel like it would be easier to do a calculation within the SQL command along the lines of a DATEDIFF(dd, End_date, Start_date) as LOS...and if needed for groupings, a CASE statement then that would state if <=10 then 1 else 0...

the basic code is below. Thank you for any assistance.

SELECT SUM( MASTER_CLAIM.TOTAL_MAXIMIUM_AMOUNT) AS MaxTot,
SUM( MASTER_CLAIM.TOTAL_DISCOUNT) AS DiscTot
from MASTER_CLAIM
where MASTER_CLAIM.DOCUMENT in
(
SELECT DISTINCT MASTER_CLAIM.[DOCUMENT]
FROM MASTER_CLAIM INNER JOIN
MASTER_CLAIM#DETAIL ON MASTER_CLAIM.[DOCUMENT] = MASTER_CLAIM#DETAIL.[DOCUMENT]
WHERE (MASTER_CLAIM#DETAIL.LINE_CODE = '123' OR
...)
AND (MASTER_CLAIM#DETAIL.SERV_DT BETWEEN {?First DOS} AND {?Last DOS})
AND (MASTER_CLAIM#DETAIL.PAY_DT <= {?Paid Date})
AND (MASTER_CLAIM#DETAIL.ADJUSTMENT_CODE IS NULL OR MASTER_CLAIM#DETAIL.ADJUSTMENT_CODE NOT LIKE 'D%')
AND (MASTER_CLAIM#DETAIL.ADJUSTMENT_CODE_2 IS NULL OR MASTER_CLAIM#DETAIL.ADJUSTMENT_CODE_2 NOT LIKE 'D%')
AND (MASTER_CLAIM.VALID_CLAIM IS NULL)
AND (MASTER_CLAIM.REGION LIKE '1S%')
AND (MASTER_CLAIM#DETAIL.QTY<={?LOS})
AND (MASTER_CLAIM#DETAIL.PAR_NONPAR = --'{?Participating}')
)

peter57r

I can't quite fit your description to the code you have posted.

The only reference to LOS in the code is as a parameter value here:

...AND (MASTER_CLAIM#DETAIL.QTY<={?LOS})....

This looks like a straight value comparison on a database field so I don't see what Datediff() has to do with it?

In terms of an 'all values' option for a parameter field, the solution depends on which version of Crystal Reports you are using.
CR2008 has optional parameters and can set default values for static parameters. Earlier versions are more limited in their features.

britpopfan74

ASKER

Sorry, I realize I wasn't very clear...

Currently, the filter setup under @LOS variable in "Select Expert" for the "Less than or equal to 10" is:

{@LOS} <= 10.00 and
{@LOS} <= {?LOS}

The other coding is in the "Command" box to set up additional SQL.

I'm getting appropriate values for the @LOS "Less than or equal to 10" but was asking for feedback on what someone might recommend for the other condition where @LOS is "ANY number of days"

The LOS variable is setup as NUMERIC with a default of 10.00 (<=10); however, I've tried different variations to see what I can use in the filter as "ANY" -- the filter setting to"any number" doesn't seem to be giving reliable results, hence my mentioning of maybe adding to the original SQL.

Maybe just turning off the filter is the work-around to get all LOS.

Kurt Reinhardt

1) Use something like the following:

SELECT
	SUM(MASTER_CLAIM.TOTAL_MAXIMIUM_AMOUNT) AS MaxTot,
 	SUM(MASTER_CLAIM.TOTAL_DISCOUNT) AS DiscTot
FROM
	MASTER_CLAIM 
WHERE
	MASTER_CLAIM.DOCUMENT in 
	(
	SELECT DISTINCT
		MASTER_CLAIM.[DOCUMENT]
	FROM
		MASTER_CLAIM INNER JOIN
               	MASTER_CLAIM#DETAIL ON MASTER_CLAIM.[DOCUMENT] = MASTER_CLAIM#DETAIL.[DOCUMENT]
	WHERE
		(
		MASTER_CLAIM#DETAIL.LINE_CODE = '123' OR
		...
		) 
		AND (MASTER_CLAIM#DETAIL.SERV_DT BETWEEN {?First DOS} AND {?Last DOS})
		AND MASTER_CLAIM#DETAIL.PAY_DT <= {?Paid Date}
		AND (MASTER_CLAIM#DETAIL.ADJUSTMENT_CODE IS NULL OR MASTER_CLAIM#DETAIL.ADJUSTMENT_CODE NOT LIKE 'D%') 
		AND (MASTER_CLAIM#DETAIL.ADJUSTMENT_CODE_2 IS NULL OR MASTER_CLAIM#DETAIL.ADJUSTMENT_CODE_2 NOT LIKE 'D%') 
		AND MASTER_CLAIM.VALID_CLAIM IS NULL
		AND MASTER_CLAIM.REGION LIKE '1S%'
		AND ({?LOS} = 999999 OR MASTER_CLAIM#DETAIL.QTY <= {?LOS})
		AND (MASTER_CLAIM#DETAIL.PAR_NONPAR = --'{?Participating}')         
	)

Open in new window

2) After you exit the Database Expert, edit the {?LOS} parameter in the Field Explorer to include a value of 999999 (at the top of the list of values) with a description of 'Any LOS' and set the property to display the parameter description only. Also, allow custom values. This way the end user will see an option to select 'Any LOS', but can also enter in any numeric value they want. If you really want to limit the selectable LOS to a range of 1 - 10 then simply add list values of 1 - 10 under the initial value of 999999 and make sure you create descriptions for each value (value - 1 and description = 1, for example). If you do this, turn off the ability to use custom values.

britpopfan74

ASKER

Rhinok,

This makes perfect sense, thank you.

When I try implementing it though, I keep getting an error under the "Formula Workshop" window stating "A number is required here" where there is the line:

{@LOS} <= {?LOS}

I have gone into the LOS variable and created one line 999999, second row 1 (per your advice) and edited the SQL code to include lines:

      AND ({?LOS} = 999999 OR MASTER_CLAIM#DETAIL.QTY <= {?LOS})
            AND (MASTER_CLAIM#DETAIL.PAR_NONPAR = '{?Participating}')
      )

On refresh it seems I can either get the formula piece working and not the SQL (get server error) or can get the SQL working but get a formula error...do you know what I may be doing incorrectly?

ASKER CERTIFIED SOLUTION

Kurt Reinhardt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

britpopfan74

ASKER

My stupid error! Thank you so much!!!