Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 336
  • Last Modified:

Crystal Report Formula Help

I am working on a Crystal Report that takes 2 parameters: OrderId, LineNo

OrderId is a string
LineNo is a number

These paramters are also passed to an embedded sub-report.

My old Record Selection Formula for the main report and sub-report was:

{Command.ORDER_ID} = {?OrderID}
and
{Command.Line_no} = {?LineNo}

That worked fine.

What I am aiming to do, is to change the formula so that LineNo becomes an optional parameter without breaking the report.

The report is accessed via web browser, so at times LineNo will not be entered, but OrderId will always be provided.

My formula looks now:

{Command.ORDER_ID} = {?OrderID}
and
if {?LineNo} <= 0
then CStr({Command.Line_no}) like '*'
else {Command.Line_no} = {?LineNo}

But is not working. Even though I do not get any formula errors, I am still prompted to enter both parameters. On other words, the report will not run if I only enter OrderId.

Any help in solving this issue would be very much appreciated.

Thanks!
0
metropia
Asked:
metropia
  • 13
  • 7
4 Solutions
 
metropiaAuthor Commented:
I changed the formula to:

{Command.ORDER_ID} = {?OrderID}
and
if {?LineNo} = ''
then CStr({Command.Line_no}) like '*'
else
{Command.Line_no} = ToNumber ({?LineNo})

Still not working.
Capture.PNG
0
 
metropiaAuthor Commented:
i changed the field type from numeric to string (in the report) the database field is still number
0
 
SarekOfVulcanCommented:
If HasValue({?LineNo}) Then
    {Command.ORDER_ID} = {?OrderID}
        And {Command.Line_no} = {?LineNo}
Else
    {Command.ORDER_ID} = {?OrderID}
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
mlmccCommented:
What version of Crystal?

CR2008 and CR2011 have added the OPTIONAL parameter capability and it is used as shown above.  If you have any other version of Crystal you have to supply values for the parameters.

You may be able to supply a default value so the user doesn't have to enter it but they will see it in the prompt.

You could use your version as

(
    {?LineNo} <= 0
     OR
    {Command.Line_no} = {?LineNo}
)
AND
{Command.ORDER_ID} = {?OrderID}


Using your IF structure
try it as

{Command.ORDER_ID} = {?OrderID}
and
if {?LineNo} <= 0 then
    TRUE
else  
    {Command.Line_no} = {?LineNo}

mlmcc


0
 
metropiaAuthor Commented:
crystal reports version 11.5.10.1263
0
 
metropiaAuthor Commented:
to SarekOfVulcan: when i use your formula i get the error shown on the image attached.

i changed the parameter LineNo to be a number again in the report and subreport. error message
0
 
mlmccCommented:
You have version 11.5 or XI R2.

All parameters are required.  The code he proposed is for CR2008 (V12) or CR2011 (V14)

You will need to upgrade to the newer version or use code like I provided.  The user will be prompted for all parameters and must provide a value.  As I stated you could provide a default value that would indicate ALL

mlmcc
0
 
metropiaAuthor Commented:
I understand, and thank you for the explanation.
I could assign a default value, but how do you specify a value of ALL for a numeric parameter when is not provided by the user?

Thank you.

0
 
metropiaAuthor Commented:
for line number there will always be at least one line (1)

i would like to have the capability of allowing the user to retrieve information per line, or if they prefer to see all the lines then just by providing the orderid.
0
 
metropiaAuthor Commented:
question, the lineno details are displayed in the sub-report that is embedded on the main report, when creating the record selection formula, do i have to created the formula on the main and sub-report?
0
 
metropiaAuthor Commented:
mlmcc, i tried both of the formulas you showed me, they did not break the report, but i am still not making it work.

the report still works if i enter order id and line no giving me on the sub-report details for the line number specified.

then i run the project again, and enter order id and a zero as line number, and the report returns details for line number 2 - I must mention that the order i enter has 3 lines total.

ideally in the latest scenario, the sub-report should show the 3 lines.

i am attaching a copy of my report, hoping that you or any other expert can have a better vision of what i am doing on my side.

thank you very much. PackList.rpt.txt
0
 
mlmccCommented:
You could use an invalid value like -5 then test for it

mlmcc
0
 
mlmccCommented:
If the problem is in the subreport did you change the selection formula there?

mlmcc
0
 
mlmccCommented:
By the way, you can upload RPT files directly.

mlmcc
0
 
metropiaAuthor Commented:
Yes I changed the formula in the sub-report too. Both main and sub-report have the same record selection formula.

Let me attach the report again if that what I need to do. PackList.rpt
0
 
metropiaAuthor Commented:
lmmc, i ran the subreport by itself, and it works. i think the problem is with the main report, not sure what.
0
 
mlmccCommented:
You are linking on the line number rather than the line number parameter.

By linking on the line number you will always have a valid value and get only that line number in the subreport

mlmcc
PackListRev1.rpt
0
 
metropiaAuthor Commented:
thanks a lot for fixing that, everything seems to be working now.
0
 
metropiaAuthor Commented:
Thank you so much for your help.

By the way to mlmcc, I may have another question regarding this sema report. I think I will need to add a date range kind of in the same way it was done for LineNo.

I will give it a try on my own, since I have your steps already to follow in the report, but if I get stuck, I will definitely post a new question, hoping you can help me one more time.

Once againg..thanks a bunch!
0
 
metropiaAuthor Commented:
to mlmc,

i have a question abuot database expert, when i modify the command query, and change the order by from createdDT DESC, to order_id

I save the changes, then run the report but I only get a prompt for Line No, the prompt for OrderId is gone. What could be causing this?

Thanks.
0
 
mlmccCommented:
I don't know unless you also got rid of the use of the parameter.

mlmcc
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 13
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now