Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Technical Questions
Message Icon Topic: Parameters Post Reply Post New Topic
Page  of 2 Next >>
Author Message
kris_a
Newbie
Newbie


Joined: 14 Nov 2018
Online Status: Offline
Posts: 11
Quote kris_a Replybullet Topic: Parameters
    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
IP IP Logged
kevlray
Admin Group
Admin Group
Avatar

Joined: 29 Oct 2009
Online Status: Offline
Posts: 1587
Quote kevlray Replybullet 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.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet 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'))
IP IP Logged
kris_a
Newbie
Newbie


Joined: 14 Nov 2018
Online Status: Offline
Posts: 11
Quote kris_a Replybullet 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.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet 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"))
IP IP Logged
kris_a
Newbie
Newbie


Joined: 14 Nov 2018
Online Status: Offline
Posts: 11
Quote kris_a Replybullet 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"
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet 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"
   )
)
IP IP Logged
kris_a
Newbie
Newbie


Joined: 14 Nov 2018
Online Status: Offline
Posts: 11
Quote kris_a Replybullet 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!!

IP IP Logged
kris_a
Newbie
Newbie


Joined: 14 Nov 2018
Online Status: Offline
Posts: 11
Quote kris_a Replybullet 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.

Edited by kris_a - 30 Nov 2018 at 7:21am
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet 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?
IP IP Logged
Page  of 2 Next >>
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.016 seconds.