Solved - Report Prompts using one column

[dammora]

I am trying to build a list report with the following criteria:

Sales PersonÃ,  Ã, AddressÃ,  QTY

The requirement is that these 4 options show up as a prompt.Ã,  So the user can select 1 or the other the report is filtered accordingly.

1.Ã,  QTY between 25K and 50K
2.Ã,  QTY between 50K and 100K
3.Ã,  QTY greater than 100K
4.Ã,  ALL QTY

Any ideas?

Thanks

[mikegreen]

Make a prompt page with a list of these ranges as the display and 1,2,3,4 (or something you can recognize) as the data element (like QTY btw 25 and 50 = 1 returned)... Then, make 4 filters that say "if itempicked=1 then QTY between 25k and 50k"

make sense?

[dammora]

Thanks for your help.  I created one filter instead of 4 and the report runs for 1,2, and 3 but not 4 (ALL).  I keep getting a database error. 

Here is my logic:

IF (?Parameter1? = 1) THEN ([Quote Value] > 25000 AND [Quote Value] < 50000) ELSE IF (?Parameter1? = 2) THEN ([Quote Value] > 50000 AND [Quote Value] < 100000) ELSE IF (?Parameter1? = 3) THEN ([Quote Value] > 100000) ELSE IF (?Parameter1? = 4) THEN ([Quote Value]) ELSE (0)

Any ideas?

[bdybldr]

Good solution Mike.

dammora, try this...
create a static choice prompt (QtyRangesPrompt, or whatever) on your prompt page with the following values...

Use: 'ALL'Ã,  Ã,  Display: 'ALL'
Use: '1'Ã,  Ã,  Ã,  Ã, Display: 'Between 25K and 50K'
Use: '2'Ã,  Ã,  Ã,  Ã, Display: 'Between 50K and 100K'
Use: '3'Ã,  Ã,  Ã,  Ã, Display: 'Greater than 100K'

Then create the following filter...

?QtyRangesPrompt? = 'ALL' OR

CASE ?QtyRangesPrompt?
Ã,  Ã,  Ã,  Ã, WHEN ('1') THEN ([Qty] between 25000 and 50000)
Ã,  Ã,  Ã,  Ã, WHEN ('2') THEN ([Qty] between 50001 and 100000)
Ã,  Ã,  Ã,  Ã, ELSE ([Qty] > 100000)
END

Hope this helps.Ã,  Keep us updated.

[bdybldr]

Dammora, what error are you getting?

Try using a string for your static choices ('1' instead of 1) because it may be getting incompatible data types (int vs. string).

[dammora]

I ended up applying the logic below and the report runs for prompt selection = 4.  It needs Quote Value to be greater than 0.  Not sure if this is correct but it is working....

IF (?Parameter1? = 1) THEN ([Quote Value] > 25000 AND [Quote Value] < 50000) ELSE IF (?Parameter1? = 2) THEN ([Quote Value] > 50000 AND [Quote Value] < 100000) ELSE IF (?Parameter1? = 3) THEN ([Quote Value] > 100000) ELSE IF (?Parameter1? = 4) THEN  ([Quote Value] > 0) ELSE (0)

Thanks for your help!

[bdybldr]

Glad it works.  Just a small thing I noticed.  What if the Quote Values are 25k, 50K, and 100K exactly?  They won't de displayed in this logic.  You should use <= and >=. 

Reminder: Please close this thread (see board rules).

[mikegreen]

Glad it works.  Just a small thing I noticed.  What if the Quote Values are 25k, 50K, and 100K exactly?  They won't de displayed in this logic.  You should use <= and >=. 

Reminder: Please close this thread (see board rules).

Simpler yet - use BETWEEN instead of < >

Cheers,

Mike

[bdybldr]

Good call, Mike.  That would be more efficient.

[dammora]

How do you close the thread?

[bdybldr]

Go to your original post and click the modify button.  Type"[Solved]" before your subject.   Click the save button.  Next select "accept" for the post that solved your issue.

Please see board rules.