Question Re: Parameter for all Invoice CR layout in Technology Q&A

Parameter for all Invoice CR layout

former_member351438 — Mon, 12 Feb 2018 14:09:11 GMT

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.

Re: Parameter for all Invoice CR layout

kothandaraman_nagarajan — Mon, 12 Feb 2018 14:34:52 GMT

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

Re: Parameter for all Invoice CR layout

former_member351438 — Mon, 12 Feb 2018 14:36:50 GMT

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.

Re: Parameter for all Invoice CR layout

DellSC — Mon, 12 Feb 2018 14:45:30 GMT

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

Re: Parameter for all Invoice CR layout

former_member351438 — Tue, 13 Feb 2018 05:38:37 GMT

I've got error..

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

Re: Parameter for all Invoice CR layout

former_member351438 — Tue, 13 Feb 2018 05:43:44 GMT

Got an error.

It says Field is not known

Re: Parameter for all Invoice CR layout

DellSC — Tue, 13 Feb 2018 19:11:19 GMT

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')}

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

-Dell