Author |
Message |
yggdrasil
Senior Member
Joined: 19 Jun 2007
Location: United Kingdom
Online Status: Offline
Posts: 150
|
Topic: parameter with multiple values Posted: 30 Jun 2009 at 1:39am |
I am using a parameter with discrete multiple values. The user can select A, B, C, D or any combination of them, in any order. I want to be able to suppress sections depending on what they choose, in other words, if they choose A, C, and D I don't want B to show, or if they choose only D then A, B, and C should be hidden.
Seems simple enough, but if I place the parameter on the canvas only the first one shows, and selections only check the first one. I think the parameter is defined internally as an array, but I can't work out how to get at the individual values.
|
IP Logged |
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
|
Posted: 30 Jun 2009 at 6:26am |
yes, they are stored in an array. You can use ubound{?parameter}) to find the size of the array and to loop through them, or if they are string values you can JOIN({?parameter},"delimiter") or to check if your value is contained in the array you can use IN {?parameter}.
This comes out of Brian's excellent book. I don't look at it too often, but when I need it, it has the answers :)
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 30 Jun 2009 at 7:11am |
You can conditionally suppress using the parameter and it will check to see if the row meets any item in the array. Write it the same as if it the parameter did not allow multiple values.
{table.field}=?parameter.
You can test it out making that a formula field and palcing it on your details section and it will show you TRUE where it would be suppressed and FALSE where it would be shown.
You also can just flat exclude the data by using that same formula in the select statement.
|
IP Logged |
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
|
Posted: 30 Jun 2009 at 10:20am |
or by filtering the data...which basically amounts the same thing.
|
IP Logged |
|
yggdrasil
Senior Member
Joined: 19 Jun 2007
Location: United Kingdom
Online Status: Offline
Posts: 150
|
Posted: 02 Jul 2009 at 6:01am |
Thanks for that, but I need to go one step further. I have filtered out data for the detail lines, which are per child, and I do suppress where I can.
But in the footer sections, I have to show a summary of various counts, totals etc for the various levels, using calculated formulae.
All this works fine, but when, say, D has not been selected in the parameter, I get a row of zeros; and what I need is to suppress all those fields, (either individually or as a section). I can't say 'show zero as blank' because there can be times when all the fields for a selected parameter value are zero, and then I have to show them.
So how can I say Suppress this if A (or B or C or D) is not in the multiple parameter selection made at this time?
|
IP Logged |
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
|
Posted: 02 Jul 2009 at 6:09am |
if you can conditionally suppress a section/row, you can apply the same formula to a field.
It should work.
|
IP Logged |
|
yggdrasil
Senior Member
Joined: 19 Jun 2007
Location: United Kingdom
Online Status: Offline
Posts: 150
|
Posted: 03 Jul 2009 at 12:09am |
I have managed to make it work! I set up 6 formulae as {@parm1} = {?parameter}[1], {@parm2} = {?parameter}[2], etc
Then I suppressed the relevant footer section for A using
( {@parm1} <> 'All' and {@parm1} <> 'All Special' and if {@countarray} >1 then ({@parm1} <> 'A' and {@parm2} <> 'A' and {@parm3} <> 'A' and {@parm4} <> 'A'and {@parm5} <> 'A' and {@parm6} <> 'A'))
and similarly for B etc.
It just wouldn't let me do it direct. It looks complicated, and would be very difficult if I had many more selections for {?parameter}. But it does work.
|
IP Logged |
|
|