Solved

Crystal Reports 9 selection formula error

Posted on 2011-03-02
11
396 Views
Last Modified: 2012-06-21
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_account_vw_1.ldr_entity_id} ="MHC" and
{MHC_GROUPING.GROUP_ID} = {?group_id} and
{ldr_acct_bal_join_mhc_account_vw_1.PMF_LINE} in {?lineitem} and
{ldr_acct_bal_join_mhc_account_vw_1.TYPE} = "Statistical" and
(({ldr_acct_bal_join_mhc_account_vw_1.processing_yr} = tonumber({?year})-1
 AND {ldr_acct_bal_join_mhc_account_vw_1.amt_class_type}= "ACTUAL")
OR ({ldr_acct_bal_join_mhc_account_vw_1.processing_yr} =tonumber({?year})
 AND {ldr_acct_bal_join_mhc_account_vw_1.amt_class_type} in["ACTUAL", "BUDGET", "FORECAST"])) and

IF {?sub_line1} <> "ALL" THEN
{ldr_acct_bal_join_mhc_account_vw_1.SUB_LINE_1} IN {?sub_line1} and

IF NOT ISNULL({?AcctNumber}) THEN
{ldr_acct_bal_join_mhc_account_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.
0
Comment
Question by:khasm1972
  • 5
  • 3
  • 2
  • +1
11 Comments
 
LVL 19

Expert Comment

by:GJParker
ID: 35019303
Not sure if thsi is your problem but i woudl try changing the last part of your record selection formula to

and (
IF {?sub_line1} <> "ALL" THEN
{ldr_acct_bal_join_mhc_account_vw_1.SUB_LINE_1} IN {?sub_line1}
Else
True)

and (
IF NOT ISNULL({?AcctNumber}) THEN
{ldr_acct_bal_join_mhc_account_vw_1.ACCOUNT_ID} = {?AcctNumber}
Else
True)

HTH
0
 

Author Comment

by:khasm1972
ID: 35019352
I'm still getting the crystal report view error:
Please enter information for the prompt: sub_line1
0
 
LVL 42

Expert Comment

by:zephyr_hex (Megan)
ID: 35019373
i would change the last part to:
AND
( {?sub_line1} = "ALL" OR
{ldr_acct_bal_join_mhc_account_vw_1.SUB_LINE_1} IN {?sub_line1})

AND
( ISNULL({?AcctNumber}) OR
{ldr_acct_bal_join_mhc_account_vw_1.ACCOUNT_ID} = {?AcctNumber})
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 35019395
How are you passing the value?

mlmcc
0
 
LVL 19

Expert Comment

by:GJParker
ID: 35019413
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
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:khasm1972
ID: 35019478
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.....
0
 

Author Comment

by:khasm1972
ID: 35019526
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=%22All%22&promptex4=%22All%22&promptex5=%22All%22&promptex6=%22All%22&promptex7=%22All%22&promptex8=%22All%22&promptex9=%22All%22&promptex10=200632&promptex11=%22None%22&promptex12=S&promptex14=%22Gas Income%22&promptex15=%22%22&promptex16=&init=actx

with promptex14 being sub_line1......
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 35019557
Can Sub_Line1 have multiple values?

Try it without the join.

mlmcc
0
 

Author Comment

by:khasm1972
ID: 35019695
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.
0
 
LVL 100

Accepted Solution

by:
mlmcc earned 500 total points
ID: 35021408
There is no promptex13 in the list.

mlmcc
0
 

Author Closing Comment

by:khasm1972
ID: 35037432
Decided to start over with the asp page as the error was probably generated from there.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I know it’s not a new topic to discuss and it has lots of online contents already available over the net. But Then I thought it would be useful to this site’s visitors and can have online repository on vim most commonly used commands. This post h…
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …

919 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now