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

math or text

in the below sql why is year(classdate) " + " a " + " MONTH(classdate) a math command giving me 2006 - 12 = 167

and not "2006/12" as text? please help me

cmdGetCat = New SqlDataAdapter("SELECT DISTINCT year(classdate) " + " a " + " MONTH(classdate) AS monthcode FROM dbo.classT INNER JOIN dbo.classgiven ON dbo.classT.classcode = dbo.classgiven.classcode WHERE (dbo.classT.discount = '-1') AND (dbo.classT.coned IS NOT NULL) ", conNorthwind)

Hi,

i think the code that you provided has certain syntax problem, can you verify the codes?

anyway the sql statement should be something like this:

"SELECT DISTINCT year(classdate) + ' a ' + MONTH(classdate) AS monthcode FROM dbo.classT INNER JOIN dbo.classgiven ON dbo.classT.classcode = dbo.classgiven.classcode WHERE (dbo.classT.discount = '-1') AND (dbo.classT.coned IS NOT NULL) "

|||

sorry its this.... i dont get 2006/12 ...i get 126 which is 2006-12=126 hahaha ..i need "yyyy/mm" why god why!!!

"SELECT DISTINCT year(classdate) + ' -/' + MONTH(classdate) AS monthcode FROM dbo.classT INNER JOIN dbo.classgiven ON dbo.classT.classcode = dbo.classgiven.classcode WHERE (dbo.classT.discount = '-1') AND (dbo.classT.coned IS NOT NULL) "

|||

Hi,

You can use this sql statement to giv you yyyy/MM in varchar format:
SELECT CAST(YEAR(classdate) AS VARCHAR(4)) + '/' + CAST(MONTH(classdate) AS VARCHAR(2)) AS 'monthcode'
FROM dbo.classT INNER JOIN dbo.classgiven ON dbo.classT.classcode = dbo.classgiven.classcode WHERE (dbo.classT.discount = '-1') AND (dbo.classT.coned IS NOT NULL)

you can also choose to use the CONVERT function to return a certain format of the date and use the data in your programm

Hope this helps

|||

Here is a Convert Sample for you:

select convert(varchar(7),classdate,111) AS 'monthcode' FROM
...........