Khari Buchanan
asked on
Crystal Reports 9 selection formula error
I am currently dealing with a baffling issue with Crystal 9. I have an asp page passing parameters to my report and everything looks fine with that. The url string is exactly how it is supposed to look. I created 2 string parameters to match the additional drop down and text box that I added to the report. Both of the Crystal created parameters are strings. When I add both of the parameters to the crystal reports record selection formula I get a prompt error for the sub_line1 field asking me for a value when it is already submitted. Here is my record selection formula:
{ldr_acct_bal_join_mhc_acc ount_vw_1. ldr_entity _id} ="MHC" and
{MHC_GROUPING.GROUP_ID} = {?group_id} and
{ldr_acct_bal_join_mhc_acc ount_vw_1. PMF_LINE} in {?lineitem} and
{ldr_acct_bal_join_mhc_acc ount_vw_1. TYPE} = "Statistical" and
(({ldr_acct_bal_join_mhc_a ccount_vw_ 1.processi ng_yr} = tonumber({?year})-1
AND {ldr_acct_bal_join_mhc_acc ount_vw_1. amt_class_ type}= "ACTUAL")
OR ({ldr_acct_bal_join_mhc_ac count_vw_1 .processin g_yr} =tonumber({?year})
AND {ldr_acct_bal_join_mhc_acc ount_vw_1. amt_class_ type} in["ACTUAL", "BUDGET", "FORECAST"])) and
IF {?sub_line1} <> "ALL" THEN
{ldr_acct_bal_join_mhc_acc ount_vw_1. SUB_LINE_1 } IN {?sub_line1} and
IF NOT ISNULL({?AcctNumber}) THEN
{ldr_acct_bal_join_mhc_acc ount_vw_1. ACCOUNT_ID } = {?AcctNumber}
Any ideas on what could be causing this error would be greatly appreciated. Let me also add that the data is coming from a view created in sql 2005.
{ldr_acct_bal_join_mhc_acc
{MHC_GROUPING.GROUP_ID} = {?group_id} and
{ldr_acct_bal_join_mhc_acc
{ldr_acct_bal_join_mhc_acc
(({ldr_acct_bal_join_mhc_a
AND {ldr_acct_bal_join_mhc_acc
OR ({ldr_acct_bal_join_mhc_ac
AND {ldr_acct_bal_join_mhc_acc
IF {?sub_line1} <> "ALL" THEN
{ldr_acct_bal_join_mhc_acc
IF NOT ISNULL({?AcctNumber}) THEN
{ldr_acct_bal_join_mhc_acc
Any ideas on what could be causing this error would be greatly appreciated. Let me also add that the data is coming from a view created in sql 2005.
ASKER
I'm still getting the crystal report view error:
Please enter information for the prompt: sub_line1
Please enter information for the prompt: sub_line1
i would change the last part to:
AND
( {?sub_line1} = "ALL" OR
{ldr_acct_bal_join_mhc_acc ount_vw_1. SUB_LINE_1 } IN {?sub_line1})
AND
( ISNULL({?AcctNumber}) OR
{ldr_acct_bal_join_mhc_acc ount_vw_1. ACCOUNT_ID } = {?AcctNumber})
AND
( {?sub_line1} = "ALL" OR
{ldr_acct_bal_join_mhc_acc
AND
( ISNULL({?AcctNumber}) OR
{ldr_acct_bal_join_mhc_acc
How are you passing the value?
mlmcc
mlmcc
Does teh report run ok when you run it using teh CR designer ?
If so then I would suggest it's your application code thats at fault
If so then I would suggest it's your application code thats at fault
ASKER
I'm still getting the same error. Let me also add that even though i see the values for sub_line1 in the url as a string and even though the report parameter is defined as a string, when i try to add sub_line1 to this parameter display formula:
join({?sub_line1},", ") + chr(13) +
join({?MHCRSI},", ") + chr(13) +
join({?region},", ") + chr(13) +
join({?state},", ") + chr(13) +
join({?asset_type},", ") + chr(13) +
join({?core},", ") + chr(13) +
join({?profile},", ") + chr(13) +
join({?market},", ")
it gives me the error that a string array is required here.Confusing.....
join({?sub_line1},", ") + chr(13) +
join({?MHCRSI},", ") + chr(13) +
join({?region},", ") + chr(13) +
join({?state},", ") + chr(13) +
join({?asset_type},", ") + chr(13) +
join({?core},", ") + chr(13) +
join({?profile},", ") + chr(13) +
join({?market},", ")
it gives me the error that a string array is required here.Confusing.....
ASKER
Here is the url that is passing the parameters to the report
http://mhccrystal/reportstest/khari/mhc_line_item_analysis_stat.rpt?user0=sis**&password0=b**&user1=sis**&password1=b**&promptex0=%221%22&promptex1=%222011%22&promptex2=[%22New Sales Volume%22-%22New Sales Volume%22]&promptex3=%22Al l%22&promp tex4=%22Al l%22&promp tex5=%22Al l%22&promp tex6=%22Al l%22&promp tex7=%22Al l%22&promp tex8=%22Al l%22&promp tex9=%22Al l%22&promp tex10=2006 32&prompte x11=%22Non e%22&promp tex12=S&pr omptex14=% 22Gas Income%22&promptex15=%22%2 2&promptex 16=&init=a ctx
with promptex14 being sub_line1......
http://mhccrystal/reportstest/khari/mhc_line_item_analysis_stat.rpt?user0=sis**&password0=b**&user1=sis**&password1=b**&promptex0=%221%22&promptex1=%222011%22&promptex2=[%22New Sales Volume%22-%22New Sales Volume%22]&promptex3=%22Al
with promptex14 being sub_line1......
Can Sub_Line1 have multiple values?
Try it without the join.
mlmcc
Try it without the join.
mlmcc
ASKER
good calls on that one mlmcc and GJ...but my original issue still is plaguing me. The report works when I type the values in the designer. The url looks to me exactly how it should passing the string values, so I'm at a loss in terms of identifying the real issue here.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Decided to start over with the asp page as the error was probably generated from there.
and (
IF {?sub_line1} <> "ALL" THEN
{ldr_acct_bal_join_mhc_acc
Else
True)
and (
IF NOT ISNULL({?AcctNumber}) THEN
{ldr_acct_bal_join_mhc_acc
Else
True)
HTH