Matrix report with Commant type Text - cannot set OR condition

I have a matrix report that uses a Dataset of Command type - Text. I am
trying to set an OR condition but I am not able to - it defaults to AND. The
field in the filters is disabled in both the DataSet > Edit SElected Dataset
and Layout > Table > Properties.
Is there a way to specify the OR condition?
ThanksOn Feb 5, 3:55 pm, "shikarishambu" <shikarishamb...@.hotmail.com>
wrote:
> I have a matrix report that uses a Dataset of Command type - Text. I am
> trying to set an OR condition but I am not able to - it defaults to AND. The
> field in the filters is disabled in both the DataSet > Edit SElected Dataset
> and Layout > Table > Properties.
> Is there a way to specify the OR condition?
> Thanks
I'm not sure if I quite follow you. In terms of the Layout >> Table >>
Properties, the only option available for Fields is 'And.' For Dataset
>> Edit Selected Dataset, for the Filter tab, the same is also true.
In this case, the Command Type of text or Stored Procedure doesn't
really matter. It seems like you will want to create a separate
dataset for each filtering option in order to get the 'Or'
functionality. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||Sorry, if i was not being clear. I have a dataset which has two date
fields - intial date and execution date. I have a parameter - for year
I want to show all rows that match the following condition
initial date_year = parm_year OR execution date_year = parm_year.
Is this not possible using the filters tab?
Thanks
"EMartinez" <emartinez.pr1@.gmail.com> wrote in message
news:def7007a-2bd5-4718-b21d-5540dbe4e6b0@.s8g2000prg.googlegroups.com...
> On Feb 5, 3:55 pm, "shikarishambu" <shikarishamb...@.hotmail.com>
> wrote:
>> I have a matrix report that uses a Dataset of Command type - Text. I am
>> trying to set an OR condition but I am not able to - it defaults to AND.
>> The
>> field in the filters is disabled in both the DataSet > Edit SElected
>> Dataset
>> and Layout > Table > Properties.
>> Is there a way to specify the OR condition?
>> Thanks
>
> I'm not sure if I quite follow you. In terms of the Layout >> Table >>
> Properties, the only option available for Fields is 'And.' For Dataset
>> Edit Selected Dataset, for the Filter tab, the same is also true.
> In this case, the Command Type of text or Stored Procedure doesn't
> really matter. It seems like you will want to create a separate
> dataset for each filtering option in order to get the 'Or'
> functionality. Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant|||On Feb 6, 11:32 am, "shikarishambu" <shikarishamb...@.hotmail.com>
wrote:
> Sorry, if i was not being clear. I have a dataset which has two date
> fields - intial date and execution date. I have a parameter - for year
> I want to show all rows that match the following condition
> initial date_year = parm_year OR execution date_year = parm_year.
> Is this not possible using the filters tab?
> Thanks
> "EMartinez" <emartinez...@.gmail.com> wrote in message
> news:def7007a-2bd5-4718-b21d-5540dbe4e6b0@.s8g2000prg.googlegroups.com...
> > On Feb 5, 3:55 pm, "shikarishambu" <shikarishamb...@.hotmail.com>
> > wrote:
> >> I have a matrix report that uses a Dataset of Command type - Text. I am
> >> trying to set an OR condition but I am not able to - it defaults to AND.
> >> The
> >> field in the filters is disabled in both the DataSet > Edit SElected
> >> Dataset
> >> and Layout > Table > Properties.
> >> Is there a way to specify the OR condition?
> >> Thanks
> > I'm not sure if I quite follow you. In terms of the Layout >> Table >>
> > Properties, the only option available for Fields is 'And.' For Dataset
> >> Edit Selected Dataset, for the Filter tab, the same is also true.
> > In this case, the Command Type of text or Stored Procedure doesn't
> > really matter. It seems like you will want to create a separate
> > dataset for each filtering option in order to get the 'Or'
> > functionality. Hope this helps.
> > Regards,
> > Enrique Martinez
> > Sr. Software Consultant
Thanks for the clarification. The most effective way to do this is via
a stored procedure (Command type: StoredProcedure)/query (Command
type: text) that is sourcing the report. That way you can set the
dataset query to something like:
select xxxxx, ... from tableX where initial_date_year = @.parm_year OR
execution_date_year = @.parm_year
And then select the 'Edit Selected Dataset' [...] button on the 'Data'
tab >> 'Parameters' tab >> and for the 'Name' you should have
'@.parm_year' and then for the 'Value' select '=Parameters!
parm_year.Value' (assuming that your report parameter is named
'parm_year'). Hope this helps further.
Regards,
Enrique Martinez
Sr. Software Consultant|||Thank you very much for the detailed explanation. Unfortunately, I am not in
a position to create sp as I not accessing the DB directly. My SSRS instance
interfaces with Microsoft Dynamics AX - which uses perspectives (models) to
expose the DB entities to SSRS.
Thanks
"EMartinez" <emartinez.pr1@.gmail.com> wrote in message
news:9deb4ce6-b98e-452e-b0dd-4ca5103b567f@.e10g2000prf.googlegroups.com...
> On Feb 6, 11:32 am, "shikarishambu" <shikarishamb...@.hotmail.com>
> wrote:
>> Sorry, if i was not being clear. I have a dataset which has two date
>> fields - intial date and execution date. I have a parameter - for year
>> I want to show all rows that match the following condition
>> initial date_year = parm_year OR execution date_year = parm_year.
>> Is this not possible using the filters tab?
>> Thanks
>> "EMartinez" <emartinez...@.gmail.com> wrote in message
>> news:def7007a-2bd5-4718-b21d-5540dbe4e6b0@.s8g2000prg.googlegroups.com...
>> > On Feb 5, 3:55 pm, "shikarishambu" <shikarishamb...@.hotmail.com>
>> > wrote:
>> >> I have a matrix report that uses a Dataset of Command type - Text. I
>> >> am
>> >> trying to set an OR condition but I am not able to - it defaults to
>> >> AND.
>> >> The
>> >> field in the filters is disabled in both the DataSet > Edit SElected
>> >> Dataset
>> >> and Layout > Table > Properties.
>> >> Is there a way to specify the OR condition?
>> >> Thanks
>> > I'm not sure if I quite follow you. In terms of the Layout >> Table >>
>> > Properties, the only option available for Fields is 'And.' For Dataset
>> >> Edit Selected Dataset, for the Filter tab, the same is also true.
>> > In this case, the Command Type of text or Stored Procedure doesn't
>> > really matter. It seems like you will want to create a separate
>> > dataset for each filtering option in order to get the 'Or'
>> > functionality. Hope this helps.
>> > Regards,
>> > Enrique Martinez
>> > Sr. Software Consultant
>
> Thanks for the clarification. The most effective way to do this is via
> a stored procedure (Command type: StoredProcedure)/query (Command
> type: text) that is sourcing the report. That way you can set the
> dataset query to something like:
> select xxxxx, ... from tableX where initial_date_year = @.parm_year OR
> execution_date_year = @.parm_year
> And then select the 'Edit Selected Dataset' [...] button on the 'Data'
> tab >> 'Parameters' tab >> and for the 'Name' you should have
> '@.parm_year' and then for the 'Value' select '=Parameters!
> parm_year.Value' (assuming that your report parameter is named
> 'parm_year'). Hope this helps further.
> Regards,
> Enrique Martinez
> Sr. Software Consultant|||On Feb 6, 12:32=A0pm, "shikarishambu" <shikarishamb...@.hotmail.com>
wrote:
> Sorry, if i was not being clear. I have a dataset which has two date
> fields - intial date and execution date. I have a parameter - for year
> I want to show all rows that match the following condition
> initial date_year =3D parm_year OR execution date_year =3D parm_year.
> Is this not possible using the filters tab?
> Thanks
> "EMartinez" <emartinez...@.gmail.com> wrote in message
> news:def7007a-2bd5-4718-b21d-5540dbe4e6b0@.s8g2000prg.googlegroups.com...
>
> > On Feb 5, 3:55 pm, "shikarishambu" <shikarishamb...@.hotmail.com>
> > wrote:
> >> I have a matrix report that uses a Dataset of Command type - Text. I am=
> >> trying to set an OR condition but I am not able to - it defaults to AND=.
> >> The
> >> field in the filters is disabled in both the DataSet > Edit SElected
> >> Dataset
> >> and Layout > Table > Properties.
> >> Is there a way to specify the OR condition?
> >> Thanks
> > I'm not sure if I quite follow you. In terms of the Layout >> Table >>
> > Properties, the only option available for Fields is 'And.' For Dataset
> >> Edit Selected Dataset, for the Filter tab, the same is also true.
> > In this case, the Command Type of text or Stored Procedure doesn't
> > really matter. It seems like you will want to create a separate
> > dataset for each filtering option in order to get the 'Or'
> > functionality. Hope this helps.
> > Regards,
> > Enrique Martinez
> > Sr. Software Consultant- Hide quoted text -
> - Show quoted text -
In the "Filters" menu, you have Expression , Operator, and Value
I would suggest that you have an Expression:
=3D CBool( (Fields!InitialDateYear =3D Parameters!Year.Value )
Or ( Fields!ExecutionDateYear.Value =3D Parameters!Year.Value ) )
Operator: =3D
Value:
=3D True
This way all of the conditional OR is handled in the Expression that
returns True of False, then you filter on only the rows that are True
-- Scott|||Will try that out. Smart work around :)
"Orne" <polysillycon@.yahoo.com> wrote in message
news:27fce259-12e2-4bc0-a129-ade4a24c7148@.h11g2000prf.googlegroups.com...
On Feb 6, 12:32 pm, "shikarishambu" <shikarishamb...@.hotmail.com>
wrote:
> Sorry, if i was not being clear. I have a dataset which has two date
> fields - intial date and execution date. I have a parameter - for year
> I want to show all rows that match the following condition
> initial date_year = parm_year OR execution date_year = parm_year.
> Is this not possible using the filters tab?
> Thanks
> "EMartinez" <emartinez...@.gmail.com> wrote in message
> news:def7007a-2bd5-4718-b21d-5540dbe4e6b0@.s8g2000prg.googlegroups.com...
>
> > On Feb 5, 3:55 pm, "shikarishambu" <shikarishamb...@.hotmail.com>
> > wrote:
> >> I have a matrix report that uses a Dataset of Command type - Text. I am
> >> trying to set an OR condition but I am not able to - it defaults to
> >> AND.
> >> The
> >> field in the filters is disabled in both the DataSet > Edit SElected
> >> Dataset
> >> and Layout > Table > Properties.
> >> Is there a way to specify the OR condition?
> >> Thanks
> > I'm not sure if I quite follow you. In terms of the Layout >> Table >>
> > Properties, the only option available for Fields is 'And.' For Dataset
> >> Edit Selected Dataset, for the Filter tab, the same is also true.
> > In this case, the Command Type of text or Stored Procedure doesn't
> > really matter. It seems like you will want to create a separate
> > dataset for each filtering option in order to get the 'Or'
> > functionality. Hope this helps.
> > Regards,
> > Enrique Martinez
> > Sr. Software Consultant- Hide quoted text -
> - Show quoted text -
In the "Filters" menu, you have Expression , Operator, and Value
I would suggest that you have an Expression:
= CBool( (Fields!InitialDateYear = Parameters!Year.Value )
Or ( Fields!ExecutionDateYear.Value = Parameters!Year.Value ) )
Operator: =
Value:
= True
This way all of the conditional OR is handled in the Expression that
returns True of False, then you filter on only the rows that are True
-- Scottsql