Author |
Message |
Megan10e
Newbie
Joined: 19 Aug 2010
Online Status: Offline
Posts: 24
|
Posted: 01 Sep 2010 at 10:37am |
Now what I want to do is to create a parameter so that the viewer can choose which range to view, and then see the list of customers whose order totals fall into that range.
My idea to do this was to make a parameter based on a formula (which I can't do), so that you could select which range to view, and then use conditional formatting to show the customers when their range is selected. I can't figure out how to make this happen, since I can't use my formula in the parameter. How could I do this another way?
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 01 Sep 2010 at 11:05am |
create a static param as a string
add in the value section each of the different grouped options you want them to select from
post those options and i can help you write your group select statement
|
IP Logged |
|
Megan10e
Newbie
Joined: 19 Aug 2010
Online Status: Offline
Posts: 24
|
Posted: 01 Sep 2010 at 11:29am |
Okay I did that, and the options are the following ranges:
<101
101-1000
1001-2500
2501-5000
5001-7500
7501-10000
10001-15000
15001-20000
20001-30000
30001-40000
40001-50000
50001-60000
60001-70000
70001-80000
80001-90000
90001-100000
>100000
These are what I've used for labels, although when I've made the formulas, I've add ".99" to the upper range, so every amount is included.
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 01 Sep 2010 at 11:52am |
Go into the select expert
click on the show formula
click the toggle button for 'Group Selection'
click on the formula editor
add your formula as
({?param}="<101" and Sum ({HORDHDR.ORDERTOTAL},{HORDHDR.CUSTNO}) <101)
or
({?param}="101-1000" and Sum ({HORDHDR.ORDERTOTAL},{HORDHDR.CUSTNO}) in 101 to 1000.99)
or
({?param}="1001-2500" and Sum ({HORDHDR.ORDERTOTAL},{HORDHDR.CUSTNO}) in 1001 to 2500.99)
or
...
REPEAT THE OR FOR EACH OPTION
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 01 Sep 2010 at 11:54am |
Note: if you are showing the group tree the groups that are filtered out doing the group select expert still show in the tree but not the report canvas.
|
IP Logged |
|
Megan10e
Newbie
Joined: 19 Aug 2010
Online Status: Offline
Posts: 24
|
Posted: 01 Sep 2010 at 12:20pm |
When I open the select expert, it wants me to choose a field. If I just select "new" and then open the formula editor, and then choose group selection, it won't let me enter a formula at all. I don't see a toggle button anywhere on the select expert.
I don't know if this makes a difference, but I already have a formula in the formula editor, for my date range parameter.
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 01 Sep 2010 at 1:38pm |
not at my machine right now so not sure of the step by step process to bypass the record selact process.
Just go in and add a bogus record selection and save it. Follow my process for the group selection then go delete the bogus individual record selection you added.
Edited by DBlank - 01 Sep 2010 at 1:40pm
|
IP Logged |
|
Megan10e
Newbie
Joined: 19 Aug 2010
Online Status: Offline
Posts: 24
|
Posted: 07 Sep 2010 at 11:38am |
I couldn't get the select formula to work out, but I figured out a way to get close to what I need.
I used the "suppress if" formula in the section expert, and changed the formula to the following:
({?Range Select}="< 101" and Sum ({HORDHDR.ORDERTOTAL},{HORDHDR.CUSTNO}) >100.99) or ({?Range Select}="101-1000" and Sum ({HORDHDR.ORDERTOTAL},{HORDHDR.CUSTNO}) <> 101 to 1000.99) or ({?Range Select}="1001-2500" and Sum ({HORDHDR.ORDERTOTAL},{HORDHDR.CUSTNO}) <> 1001 to 2500.99)
etc.
So the formula is saying: suppress this section when the parameter chosen is "< 101," and the totals come to more than 101, so all the totals I want are shown. But when I select more than one range, they cancel one another out and no values are shown. How can I fix this?
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 07 Sep 2010 at 11:41am |
You have the param set to 'Allow multiple values'?
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 07 Sep 2010 at 11:46am |
Well your logic won't quite work with it but I think you can use the original process I gave you on this and just do NOT in front of each section...
NOT({?param}="<101" and Sum ({HORDHDR.ORDERTOTAL},{HORDHDR.CUSTNO}) <101)
or
NOT({?param}="101-1000" and Sum ({HORDHDR.ORDERTOTAL},{HORDHDR.CUSTNO}) in 101 to 1000.99)
or
NOT({?param}="1001-2500" and Sum ({HORDHDR.ORDERTOTAL},{HORDHDR.CUSTNO}) in 1001 to 2500.99)
or ...
|
IP Logged |
|
|