Matrix Reports Filter

I am trying to have a matrix report filter on records that had sales in a prior month but no sales in the current month. Basically looking for accounts we “lost”

For example:

Name Invoice Date Quantity

Joe 4/1/2007 7

John 5/1/2007 13

John 4/1/2007 7

Tim 6/12/2007 6

The matrix report pulls back

Name Month

April May June

Joe 7

Tim 7 13

John 6

I am trying to return only names that had quantity in May and don't in June.

I would like it only to return Tim’s record because we lost his business in June.

Any ideas?

I think in your example you switched Tim and John between the query results and matrix.

Anyway, the best place to do this is in your query. I suspect you'll be running this report based on a "Month" parameter.

So in pseudo code you would write your query like

Code Snippet

SELECT account

FROM accounts_table

WHERE quantity > 0

AND month = @.param_month - 1 month

AND account NOT IN

(

SELECT account

FROM accounts_table

WHERE quantity > 0

AND month = @.param_month

)

Matrix filter - rendering error - BUG?

I have a matrix-rowgroup with a filter. The filter is a simple
"DataSet.SomeValue = True" and it works just fine most of the time.
However, depending on my query-data there will be situations where filter
will filter out all data, thus leaving zero rows. This 'should' result in an
empty matrix.
But instead i get an error when viewing my report.
RenderingException: "Operation is not valid due to the current state of the
object."
Is this intentional?
Can anyone suggest a work-around?You probably have an expression somewhere that tries to access an item from
the matrix. As the matrix item is not there, you are getting the error. You
should recode your expression with an IIF(Fields!MatrixElement is
nothing,...,...).
I also have a matrix which I am filtering. Sometimes I get no data. When I
have no rows returned, there is just a blank report, no errors like you are
getting. Of course I could use the NoRows property to display an alternative
text when no rows are returned.
HTH
Charles Kangai, MCT, MCDBA
"Kristian Vinther" wrote:
> I have a matrix-rowgroup with a filter. The filter is a simple
> "DataSet.SomeValue = True" and it works just fine most of the time.
> However, depending on my query-data there will be situations where filter
> will filter out all data, thus leaving zero rows. This 'should' result in an
> empty matrix.
> But instead i get an error when viewing my report.
> RenderingException: "Operation is not valid due to the current state of the
> object."
> Is this intentional?
> Can anyone suggest a work-around?

Matrix dataset filter out null

I have a dataset which has a ContractNo column which can take a null value. I
cannot carry out the filter on the stored procedure which generates my
dataset.
This dataset is used in a matrix. I need to filter out all rows with a null
ContractNo. I thought I could do this on the matrix properties filter tab
with an expression like:
Fields!ContractNo.Value<>Nothing
However there doeas not appear to be a <> (or not equal to) operator in the
drop-down list in RS2000.
How do I achieve this filter?
What is the != operator?
TIA,
Mageno_manDont know if this will help, but the way I have used to get around it is:
Fields!ContractNo.Value<>Nothing in the formula and make the operator the =and then the value "=true"
Expression Operator
Value
Fields!ContractNo.Value<>Nothing ==true
I hope this makes sense.
"magendo_man" <magendoman@.discussions.microsoft.com> wrote in message
news:75AD1157-EBF6-4E95-8A46-A568A0B5933C@.microsoft.com...
>I have a dataset which has a ContractNo column which can take a null value.
>I
> cannot carry out the filter on the stored procedure which generates my
> dataset.
> This dataset is used in a matrix. I need to filter out all rows with a
> null
> ContractNo. I thought I could do this on the matrix properties filter tab
> with an expression like:
> Fields!ContractNo.Value<>Nothing
> However there doeas not appear to be a <> (or not equal to) operator in
> the
> drop-down list in RS2000.
> How do I achieve this filter?
> What is the != operator?
> TIA,
> Mageno_man|||!= is 'does not equal to'
does it solve your problem? :)
"magendo_man" wrote:
> I have a dataset which has a ContractNo column which can take a null value. I
> cannot carry out the filter on the stored procedure which generates my
> dataset.
> This dataset is used in a matrix. I need to filter out all rows with a null
> ContractNo. I thought I could do this on the matrix properties filter tab
> with an expression like:
> Fields!ContractNo.Value<>Nothing
> However there doeas not appear to be a <> (or not equal to) operator in the
> drop-down list in RS2000.
> How do I achieve this filter?
> What is the != operator?
> TIA,
> Mageno_man|||Had to put an equals sign in expression, i.e expression is
=Fields!ContractNo.Value<>Nothing
"Ben Watts" wrote:
> Dont know if this will help, but the way I have used to get around it is:
> Fields!ContractNo.Value<>Nothing in the formula and make the operator the => and then the value "=true"
> Expression Operator
> Value
> Fields!ContractNo.Value<>Nothing => =true
> I hope this makes sense.
>
> "magendo_man" <magendoman@.discussions.microsoft.com> wrote in message
> news:75AD1157-EBF6-4E95-8A46-A568A0B5933C@.microsoft.com...
> >I have a dataset which has a ContractNo column which can take a null value.
> >I
> > cannot carry out the filter on the stored procedure which generates my
> > dataset.
> >
> > This dataset is used in a matrix. I need to filter out all rows with a
> > null
> > ContractNo. I thought I could do this on the matrix properties filter tab
> > with an expression like:
> >
> > Fields!ContractNo.Value<>Nothing
> >
> > However there doeas not appear to be a <> (or not equal to) operator in
> > the
> > drop-down list in RS2000.
> >
> > How do I achieve this filter?
> >
> > What is the != operator?
> >
> > TIA,
> > Mageno_man
>
>