Exclude rows with no data

I have a report created using MDX imported from another application. In
order to provide flexibility, this MDX carries in several fields that are not
necessarily used when the report is created. (The report prompts for
parameters which determine whether it should be created using budget or
actual data.) Because of this, I am getting rows of data where there are no
values for the type of report I am requesting. For example, if I have budget
data but no actual data for an item, this item still shows up when I request
the report for actual data. Is there any way to prevent these rows from
appearing?On Mar 2, 11:33 am, ppbedz <ppb...@.discussions.microsoft.com> wrote:
> I have a report created using MDX imported from another application. In
> order to provide flexibility, this MDX carries in several fields that are not
> necessarily used when the report is created. (The report prompts for
> parameters which determine whether it should be created using budget or
> actual data.) Because of this, I am getting rows of data where there are no
> values for the type of report I am requesting. For example, if I have budget
> data but no actual data for an item, this item still shows up when I request
> the report for actual data. Is there any way to prevent these rows from
> appearing?
I'm not familiar w/MDX; however, you should be able to filter the
results by something like Sum(Fields!ActualData.Value) > 0. Hope this
is helpful.
Regards,
Enrique Martinez
Sr. SQL Server Developer|||I tried the following filter "=Fields!GrossSales.Value > 0". I get an error
message that says "Processing of filter can not be performed. Please check
the data type value retuend by the filter expression." Can you tell me what
this means and how to set up the statement properly. Thank you for your
help. I'm learning this tool on a "need to know" basis and I have 60 reports
to create w/o any real training.
"EMartinez" wrote:
> On Mar 2, 11:33 am, ppbedz <ppb...@.discussions.microsoft.com> wrote:
> > I have a report created using MDX imported from another application. In
> > order to provide flexibility, this MDX carries in several fields that are not
> > necessarily used when the report is created. (The report prompts for
> > parameters which determine whether it should be created using budget or
> > actual data.) Because of this, I am getting rows of data where there are no
> > values for the type of report I am requesting. For example, if I have budget
> > data but no actual data for an item, this item still shows up when I request
> > the report for actual data. Is there any way to prevent these rows from
> > appearing?
> I'm not familiar w/MDX; however, you should be able to filter the
> results by something like Sum(Fields!ActualData.Value) > 0. Hope this
> is helpful.
> Regards,
> Enrique Martinez
> Sr. SQL Server Developer
>|||On Mar 4, 8:54 am, ppbedz <ppb...@.discussions.microsoft.com> wrote:
> I tried the following filter "=Fields!GrossSales.Value > 0". I get an error
> message that says "Processing of filter can not be performed. Please check
> the data type value retuend by the filter expression." Can you tell me what
> this means and how to set up the statement properly. Thank you for your
> help. I'm learning this tool on a "need to know" basis and I have 60 reports
> to create w/o any real training.
> "EMartinez" wrote:
> > On Mar 2, 11:33 am, ppbedz <ppb...@.discussions.microsoft.com> wrote:
> > > I have a report created using MDX imported from another application. In
> > > order to provide flexibility, this MDX carries in several fields that are not
> > > necessarily used when the report is created. (The report prompts for
> > > parameters which determine whether it should be created using budget or
> > > actual data.) Because of this, I am getting rows of data where there are no
> > > values for the type of report I am requesting. For example, if I have budget
> > > data but no actual data for an item, this item still shows up when I request
> > > the report for actual data. Is there any way to prevent these rows from
> > > appearing?
> > I'm not familiar w/MDX; however, you should be able to filter the
> > results by something like Sum(Fields!ActualData.Value) > 0. Hope this
> > is helpful.
> > Regards,
> > Enrique Martinez
> > Sr. SQL Server Developer
It seems like you might not have set the 'Value' part of the filter to
an integer value. By default, the filter sets all numbers in the
'Value' column to strings unless you use an = sign before it. You
might want to try something like: in the 'Expression' column enter:
=Fields!GrossSales.Value; in the 'Operator' column select '>'; and in
the 'Value' column enter: =0
If this does not correct the problem, you will most likely need to
convert the results of 'Fields!GrossSales.Value' to an integer: via
something like: CInt(Fields!GrossSales.Value).
Regards,
Enrique Martinez
Sr. SQL Server Developer|||Thank you. I had to use the "cint" in the expression column AND the "=0"
in the value column. I appreciate youe help!
PB
"EMartinez" wrote:
> On Mar 4, 8:54 am, ppbedz <ppb...@.discussions.microsoft.com> wrote:
> > I tried the following filter "=Fields!GrossSales.Value > 0". I get an error
> > message that says "Processing of filter can not be performed. Please check
> > the data type value retuend by the filter expression." Can you tell me what
> > this means and how to set up the statement properly. Thank you for your
> > help. I'm learning this tool on a "need to know" basis and I have 60 reports
> > to create w/o any real training.
> >
> > "EMartinez" wrote:
> > > On Mar 2, 11:33 am, ppbedz <ppb...@.discussions.microsoft.com> wrote:
> > > > I have a report created using MDX imported from another application. In
> > > > order to provide flexibility, this MDX carries in several fields that are not
> > > > necessarily used when the report is created. (The report prompts for
> > > > parameters which determine whether it should be created using budget or
> > > > actual data.) Because of this, I am getting rows of data where there are no
> > > > values for the type of report I am requesting. For example, if I have budget
> > > > data but no actual data for an item, this item still shows up when I request
> > > > the report for actual data. Is there any way to prevent these rows from
> > > > appearing?
> >
> > > I'm not familiar w/MDX; however, you should be able to filter the
> > > results by something like Sum(Fields!ActualData.Value) > 0. Hope this
> > > is helpful.
> >
> > > Regards,
> >
> > > Enrique Martinez
> > > Sr. SQL Server Developer
> It seems like you might not have set the 'Value' part of the filter to
> an integer value. By default, the filter sets all numbers in the
> 'Value' column to strings unless you use an = sign before it. You
> might want to try something like: in the 'Expression' column enter:
> =Fields!GrossSales.Value; in the 'Operator' column select '>'; and in
> the 'Value' column enter: =0
> If this does not correct the problem, you will most likely need to
> convert the results of 'Fields!GrossSales.Value' to an integer: via
> something like: CInt(Fields!GrossSales.Value).
> Regards,
> Enrique Martinez
> Sr. SQL Server Developer
>|||One more question....For some reason the Table filter list has the and/or
column on the selection grayed out. I would like to be able to say "sales OR
pounds" not =0. Do you have any idea why the and/or column is not available
under the table filter properties?
Thank you,
PB
"EMartinez" wrote:
> On Mar 4, 8:54 am, ppbedz <ppb...@.discussions.microsoft.com> wrote:
> > I tried the following filter "=Fields!GrossSales.Value > 0". I get an error
> > message that says "Processing of filter can not be performed. Please check
> > the data type value retuend by the filter expression." Can you tell me what
> > this means and how to set up the statement properly. Thank you for your
> > help. I'm learning this tool on a "need to know" basis and I have 60 reports
> > to create w/o any real training.
> >
> > "EMartinez" wrote:
> > > On Mar 2, 11:33 am, ppbedz <ppb...@.discussions.microsoft.com> wrote:
> > > > I have a report created using MDX imported from another application. In
> > > > order to provide flexibility, this MDX carries in several fields that are not
> > > > necessarily used when the report is created. (The report prompts for
> > > > parameters which determine whether it should be created using budget or
> > > > actual data.) Because of this, I am getting rows of data where there are no
> > > > values for the type of report I am requesting. For example, if I have budget
> > > > data but no actual data for an item, this item still shows up when I request
> > > > the report for actual data. Is there any way to prevent these rows from
> > > > appearing?
> >
> > > I'm not familiar w/MDX; however, you should be able to filter the
> > > results by something like Sum(Fields!ActualData.Value) > 0. Hope this
> > > is helpful.
> >
> > > Regards,
> >
> > > Enrique Martinez
> > > Sr. SQL Server Developer
> It seems like you might not have set the 'Value' part of the filter to
> an integer value. By default, the filter sets all numbers in the
> 'Value' column to strings unless you use an = sign before it. You
> might want to try something like: in the 'Expression' column enter:
> =Fields!GrossSales.Value; in the 'Operator' column select '>'; and in
> the 'Value' column enter: =0
> If this does not correct the problem, you will most likely need to
> convert the results of 'Fields!GrossSales.Value' to an integer: via
> something like: CInt(Fields!GrossSales.Value).
> Regards,
> Enrique Martinez
> Sr. SQL Server Developer
>|||On Mar 5, 7:16 am, ppbedz <ppb...@.discussions.microsoft.com> wrote:
> One more question....For some reason the Table filter list has the and/or
> column on the selection grayed out. I would like to be able to say "sales OR
> pounds" not =0. Do you have any idea why the and/or column is not available
> under the table filter properties?
> Thank you,
> PB
> "EMartinez" wrote:
> > On Mar 4, 8:54 am, ppbedz <ppb...@.discussions.microsoft.com> wrote:
> > > I tried the following filter "=Fields!GrossSales.Value > 0". I get an error
> > > message that says "Processing of filter can not be performed. Please check
> > > the data type value retuend by the filter expression." Can you tell me what
> > > this means and how to set up the statement properly. Thank you for your
> > > help. I'm learning this tool on a "need to know" basis and I have 60 reports
> > > to create w/o any real training.
> > > "EMartinez" wrote:
> > > > On Mar 2, 11:33 am, ppbedz <ppb...@.discussions.microsoft.com> wrote:
> > > > > I have a report created using MDX imported from another application. In
> > > > > order to provide flexibility, this MDX carries in several fields that are not
> > > > > necessarily used when the report is created. (The report prompts for
> > > > > parameters which determine whether it should be created using budget or
> > > > > actual data.) Because of this, I am getting rows of data where there are no
> > > > > values for the type of report I am requesting. For example, if I have budget
> > > > > data but no actual data for an item, this item still shows up when I request
> > > > > the report for actual data. Is there any way to prevent these rows from
> > > > > appearing?
> > > > I'm not familiar w/MDX; however, you should be able to filter the
> > > > results by something like Sum(Fields!ActualData.Value) > 0. Hope this
> > > > is helpful.
> > > > Regards,
> > > > Enrique Martinez
> > > > Sr. SQL Server Developer
> > It seems like you might not have set the 'Value' part of the filter to
> > an integer value. By default, the filter sets all numbers in the
> > 'Value' column to strings unless you use an = sign before it. You
> > might want to try something like: in the 'Expression' column enter:
> > =Fields!GrossSales.Value; in the 'Operator' column select '>'; and in
> > the 'Value' column enter: =0
> > If this does not correct the problem, you will most likely need to
> > convert the results of 'Fields!GrossSales.Value' to an integer: via
> > something like: CInt(Fields!GrossSales.Value).
> > Regards,
> > Enrique Martinez
> > Sr. SQL Server Developer
You're welcome. Glad to be of assistance. I have not been able to get
the filter options to allow 'or' in my experiences so far. You might
need to have the filtering in the query or stored procedure that is
sourcing the report to allow for more flexibility. Just a thought.
Regards,
Enrique Martinez
Sr. SQL Server Developer