Parameter for all Invoice CR layout

[former_member351438]

Hello,

Im new in CR layout.

This time i add a parameter. User can select which Invoice series theywanted to view within selected date range. But I also wanted to include one option to view all the series within date range.

This is my fomula.

{OINV.DocDate}={?DateRange} and //{OINV.CANCELED}='N' //and ////(not(hasvalue({?Canceled})) or {OINV.CANCELED}={?Canceled})

{NNM1.SeriesName}={?SeriesName@select Distinct SeriesName from NNM1 where SeriesName in ('CAN18', 'DMM18', 'JED18','SER18')}

Thank you for the advance help.

[former_member351438]

I need to put 'AND' after this?

{NNM1.SeriesName}={?SeriesName@select Distinct SeriesName from NNM1 where SeriesName in ('CAN18', 'DMM18', 'JED18','SER18')}

That formula is for the selection of the series.

[DellSC]

I just re-read your question and I had misunderstood it in my previous response. You're going to change it to something like this:

(
({?SeriesName@Select "*All" union select Distinct SeriesName from NNM1 where SeriesName in ('CAN18','DMM18','JED18','SER18')} = '*All') or

({NNM1.SeriesName}={?SeriesName@Select "All" union select Distinct SeriesName from NNM1 where SeriesName in ('CAN18','DMM18','JED18','SER18')})

)

I use "*All" instead of just the word "All" because the * makes it sort to the top of the list.

-Dell

[former_member351438]

Got an error.

It says Field is not known

[DellSC]

Some of this syntax may be dependent on what type of database you're connecting to. You're going to have to tweak the "Select" statement based on that. Here are a couple of other ideas to try for that:

{?SeriesName@Select "*All" as SeriesName union select Distinct SeriesName from NNM1 where SeriesName in ('CAN18','DMM18','JED18','SER18')}

or

{?SeriesName@Select distinct "*All" as SeriesName from NNM1 union select Distinct SeriesName from NNM1 where SeriesName in ('CAN18','DMM18','JED18','SER18')}

-Dell

[kothandaraman_nagarajan]

Hi,

Try this,

{OINV.DocDate}={?DateRange} and //{OINV.CANCELED}='N' //and ////(not(hasvalue({?Canceled})) or {OINV.CANCELED}={?Canceled})

AND

({NNM1.SeriesName}={?SeriesName@select Distinct SeriesName from NNM1 where SeriesName in ('CAN18', 'DMM18', 'JED18','SER18')}) OR ({NNM1.SeriesName}={?SeriesName@select Distinct SeriesName from NNM1)

Regards,

Nagarajan

[former_member351438]

I've got error..

Its said.. That this field "({NNM1.SeriesName}={?SeriesName@select Distinct SeriesName from NNM1)" is not known.