Print Page | Close Window

Parameters

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=22705
Printed Date: 27 Apr 2024 at 5:08am


Topic: Parameters
Posted By: kris_a
Subject: Parameters
Date Posted: 29 Nov 2018 at 10:30am
Hello - I am very new to crystal and am trying to work through a lot on my own, but I'm getting hung up on some things and hoping to get some help.

I am working on some parameters for a report, there are 3 criteria that I would like the user to be able to make selections on. I tackled the one I thought was the easiest first, but it is not working and I'm not sure what I'm doing wrong.

We have 2 districts in the company (A & B). I set up a new parameter - type: string, LOV: static, value field: DISTRICT_CODE, and clicked to Append all database values and A & B show up under the values. Entered it into the report, the prompt to enter values pops up, I select District Code A and it gives me all of the records, not just ones for District A. Same thing when I select District Code B.

The second parameter will need to be an option show all customers including one particular customer or all customers except this one particular customer.

The third is an option to either see any "open" orders (based on order status field) OR to see any orders that have been invoiced MTD (true 1st of the month) AND any open orders.

Any guidance on these would be much appreciated!
Thanks, Kris


Replies:
Posted By: kevlray
Date Posted: 29 Nov 2018 at 11:47am
Did you put the prompt into the Select Expert?

Not sure best how to handle the second prompt.  Maybe needs to be a formula.

Again the third one may need a formula of some sort.  Will need more information about the data.

Posted By: DBlank
Date Posted: 30 Nov 2018 at 2:59am
As Kevlray indicates, after you create the parameter you have to use that parameter in the select expert to make it act as a filter.
For your specific customer item I would do something like a static parameter that has the value of 1 and 2 with a descriptions of 'include customer XYZ' and 'Exclude customer XYZ'.
In the select expert you would have something like
{?DistrictParam}={table.district} and
({?IncludeCustomer}=1 OR
(({?IncludeCustomer}=2 AND {table.cusotmerID} <> 'ValueHere'))

Posted By: kris_a
Date Posted: 30 Nov 2018 at 3:04am
I thought I had done the Select Expert, but I must not have done it correctly - that worked for the sorting by district prompt - thank you!

I will try that for the second customer specific prompt.

For the third prompt - I'm trying to replicate a MTD order report from our old home-grown system in our new ERP system. The old report gave them the option to see any orders that are open in the system through the end of time - no date range. They also had the option to include any orders that were closed/invoiced that had shipped from the beginning of the month in which the report was run. I would base this off a status data field from the order table and use something like {CUSTOMER_ORDER.STATE} <> "Invoiced/Closed" for the option to just show open orders and not include the shipped ones. Let me know what other info about the data would help.

Posted By: DBlank
Date Posted: 30 Nov 2018 at 3:12am
If for your last item you want to filter to all or only an option that = a value in your data set add another static number parameter with a value of 1 and 2 with whatever descriptions like 'all open invoices' and 'closed only' (or whatever) and add another part to the select statement using that logic
AND
({Param3}=1 or
({Param3}=2 and {CUSTOMER_ORDER.STATE}="Closed"))

Posted By: kris_a
Date Posted: 30 Nov 2018 at 3:36am
Ok for the 2nd prompt to include/exclude the specific customer - when I select the option "Yes" to include them the data pulls correctly, however selecting "No" overrides the company restriction, parts starting with "N" restriction and district code distinction and shows all data. Here's my select expert:

{CUSTOMER_ORDER.COMPANY} = "110" and
not ({CUSTOMER_ORDER_JOIN.CATALOG_NO} startswith "N") and
{CUSTOMER_ORDER.DISTRICT_CODE} = {?District} and
{?IncludeCust}="Yes" or {?IncludeCust}="No" and {CUSTOMER_ORDER_JOIN.CUSTOMER_NO}<>"12345"

Posted By: DBlank
Date Posted: 30 Nov 2018 at 3:40am
you need to parenth the conditions so that each one executes

{CUSTOMER_ORDER.COMPANY} = "110" and
not ({CUSTOMER_ORDER_JOIN.CATALOG_NO} startswith "N") and
{CUSTOMER_ORDER.DISTRICT_CODE} = {?District} and

(
{?IncludeCust}="Yes" or
   (
    {?IncludeCust}="No" and    
    {CUSTOMER_ORDER_JOIN.CUSTOMER_NO}<>"12345"
   )
)

Posted By: kris_a
Date Posted: 30 Nov 2018 at 5:55am
I REALLY appreciate the help, of course I forgot the parenth, I swear I'm more new than stupid! :) All 3 prompts seem to be working - thanks!!


Posted By: kris_a
Date Posted: 30 Nov 2018 at 6:29am
Spoke to soon...prompt 3 works fine if I select value 1 (open orders only), but value 2 (open orders & invoiced MTD orders) gives me a blank report. Here's what I have:

({?Order Status}="1" and {CUSTOMER_ORDER_JOIN.STATE}<>"Invoiced/Closed" or ({?Order Status}="2"
and {CUSTOMER_ORDER_JOIN.REAL_SHIP_DATE}in monthtodate))

I realize because I'm working with old data in a TEST environment that there probably not many (if any orders) invoiced "this month" but it should still be giving me the open orders, correct?

Update: I just ran several orders through quickly in the test envir and invoiced them. They do show up with value 2 selected, but just the invoiced orders, not all of the open orders.

Posted By: DBlank
Date Posted: 30 Nov 2018 at 7:25am
Do 'open' orders have a matching row in the CUSTOMER_ORDER_JOIN table or do you need to do a left join and account for that?

Posted By: kris_a
Date Posted: 30 Nov 2018 at 8:27am
If I'm understanding you correctly, yes the CUSTOMER_ORDER_JOIN.STATE field houses all of the status' we would consider 'open' and the invoiced/closed one. Do I need do I need to account for the state equaling basically anything including 'Invoiced/Closed' in the or half of my formula?

I may be way off on that.

Posted By: DBlank
Date Posted: 30 Nov 2018 at 10:48am
I was thinking you were bringing in another table and that the join might have been excluding the rows you were missing. You should not need to indicate anything
to select 'All' of the status categories.

If you have NULLS in your data set you might need to to set the formula to use default values for nulls. I can't really 'picture' your full data set to give anymore advise...
I suggest you drop all the values into your detail section to visually understand your full data set to see how you need to filter.

Posted By: kris_a
Date Posted: 04 Dec 2018 at 8:20am
Thank you! When I looked at again I realized that it was excluding the open orders because they did not have a real ship date so I was able to rework it and pull those in. Thanks again for the help!


Print Page | Close Window