Prompts Question

[Cogknow]

Hi,

We have Year prompt (single select) and Month prompt(multi select) in our reports.

When we select month and year combination, we need to filter on from date and to date coulmns from database table.

So, we concat month and year and then take first, last of that month and use it in the following way.

_first_of_month (to_date(?year?||?month?, 'YYYYMM')) >= [DB_Column_Start_Dt]
and
_last_of_month (to_date(?year?||?month?, 'YYYYMM')) <= [DB_Column_End_Dt]

Is this the right approach?

Also, if the user selects more than one month, how do we handle that in the filters?

Can some one please help on this?

[norkos]

Why don't you just use two calendar prompt, for the parameters (startDate, endDate).

In this case you can use a more simpler filter wich will look like this:
  [DB_Column_Start_Dt] =< ?startDate? and ?endDate? <= [DB_Column_End_Dt]