Conditionally hiding values in a Matrix

Hi all,
I have a matrix which has a runningvalue in it's detail cell, so it looks
like this:
item, date1, date2, date3...datex
A, 1, 1, 1...1
B, 1, 2, 3...x
C, 1, 2, -1...y
D, 1, -2, -3...z
This works fine and all is well. However, I now wish to filter out all rows
that have no negative values (which in the above example will only leave C
and D). Since the runningvalues themselves are not in the dataset, is there
any way of basing a whole row's visibility or inclusion based on any value
in the detail cell?
If not could you suggest how I could do this?
Thanks!
ShakShak,
I've had this issue, I found that a stored procedure which works it out
before hand is the only feasible solution. Create a temporary table
containing rows for item A, B, C etc, and a flag to say whether they
have negatives. Then in the SP still, do the normal query but join the
temporary table on item so you can include the flag in the output.
Then you can suppress a row based on the the first flag at that level
using =First(Field!Flag.Value) in the groups filters, or more
efficiently you could remove it from within the SP.
Chris
Shak wrote:
> Hi all,
> I have a matrix which has a runningvalue in it's detail cell, so it
> looks like this:
> item, date1, date2, date3...datex
> A, 1, 1, 1...1
> B, 1, 2, 3...x
> C, 1, 2, -1...y
> D, 1, -2, -3...z
> This works fine and all is well. However, I now wish to filter out
> all rows that have no negative values (which in the above example
> will only leave C and D). Since the runningvalues themselves are not
> in the dataset, is there any way of basing a whole row's visibility
> or inclusion based on any value in the detail cell?
> If not could you suggest how I could do this?
> Thanks!
> Shak|||Hi Chris,
While waiting for a reply, that was the solution I worked on. It seems
precalculation is the only way to solve it, which is a shame - some kind of
"two pass" reporting generation might be complicated though.
My solution was slightly different in that I flag all rows with and ID that
may at any time have a runningvalue less than zero; it's then just easy to
filter the dataset on that flag when required.
Thanks for the reply though!
Shak
"Chris McGuigan" <chris.mcguigan@.zycko.com> wrote in message
news:ubjeuVocFHA.2960@.TK2MSFTNGP09.phx.gbl...
> Shak,
> I've had this issue, I found that a stored procedure which works it out
> before hand is the only feasible solution. Create a temporary table
> containing rows for item A, B, C etc, and a flag to say whether they
> have negatives. Then in the SP still, do the normal query but join the
> temporary table on item so you can include the flag in the output.
> Then you can suppress a row based on the the first flag at that level
> using =First(Field!Flag.Value) in the groups filters, or more
> efficiently you could remove it from within the SP.
> Chris
>
> Shak wrote:
> > Hi all,
> >
> > I have a matrix which has a runningvalue in it's detail cell, so it
> > looks like this:
> >
> > item, date1, date2, date3...datex
> > A, 1, 1, 1...1
> > B, 1, 2, 3...x
> > C, 1, 2, -1...y
> > D, 1, -2, -3...z
> >
> > This works fine and all is well. However, I now wish to filter out
> > all rows that have no negative values (which in the above example
> > will only leave C and D). Since the runningvalues themselves are not
> > in the dataset, is there any way of basing a whole row's visibility
> > or inclusion based on any value in the detail cell?
> >
> > If not could you suggest how I could do this?
> >
> > Thanks!
> >
> > Shak
>