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
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?
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?
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.
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).
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!
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).
Quote from: bdybldr on 05 Jan 2006 11:03:24 AM
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
Good call, Mike. That would be more efficient.
How do you close the thread?
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.