Creating Data items for months based on inpur given in Prompt page

[mark123]

Hi,

I have a relational model in which the report has two prompts selected from the date column in database. One prompt is month name and and one is year.
I need to create a report based on the prompt selected . For example if user selects January 2016, the data in the report should be displayed for Dec 2015, Nov 2015, Oct 215 and Sept 2015. Each of the four months should be in a separate column like one column is DEC , one for NOV like that. So, Can any one help me how to write the query for data items for previous months.

Thanks,
Mark

[Lynn]

Hi,

I have a relational model in which the report has two prompts selected from the date column in database. One prompt is month name and and one is year.
I need to create a report based on the prompt selected . For example if user selects January 2016, the data in the report should be displayed for Dec 2015, Nov 2015, Oct 215 and Sept 2015. Each of the four months should be in a separate column like one column is DEC , one for NOV like that. So, Can any one help me how to write the query for data items for previous months.

Thanks,
Mark

For your month prompt set the month name as display value but put month number as use value. This will let you construct a date to use for filtering purposes. The make timestamp function below would result in a date of 2016-01-01 using your example. The filter would get everything from 4 months prior up to but not including the selected month.

Code Select Expand


[Your Date] >=  _add_months ( _make_timestamp( ?YearPrompt?, ?MonthPrompt?, 1 ) , -4)
and
[Your Date] <  _add_months ( _make_timestamp( ?YearPrompt?, ?MonthPrompt?, 1 ) , -1)


To display your data in columns by month you can use a crosstab layout. Ideally you'd have a date dimension containing the month name for your dates. If not you'll need a query item to produce this.

[mark123]

Hi Thanks for the reply

I am creating the following data item base on your suggestion

([Presentation Layer].[Logical_Col_Txn].[STAT_DT]>=  add_months ( make_timestamp(?Year? , ?Month?, 1 ) , -3))
and
([Presentation Layer].[Logical_Col_Txn].[STAT_DT]<  add_months ( make_timestamp( ?Year?, ?Month?, 1 ) , -1))

and i am getting error as Boolean value expression as query item is not supported.

Thanks

[Michael75]

Looks as if Lynn is offline, so I'll jump in here. The code Lynn gave is to be used in a filter, not a data item. The filter should be added to the report's main query - the one that feeds your crosstab. If you have a column that gives you dates in the format 'DEC 2015' etc., use this column for your crosstab columns.

[mark123]

Hi Thanks for your reply

I am getting the following error while running the report

  Data source adapter error: java.sql.SQLSyntaxErrorException: ORA-00904: "MAKE_TIMESTAMP": invalid identifier

This id the query  i have written in filter

([Presentation Layer].[Logical_Col_Txn].[STAT_DT]>=  add_months ( make_timestamp(?Year? , ?Month?, 1 ) , -3))
and
([Presentation Layer].[Logical_Col_Txn].[STAT_DT]<  add_months ( make_timestamp( ?Year?, ?Month?, 1 ) , -1))

Thanks

[Lynn]

Look at my original post for the syntax. There is an underscore in front of the function name. It is _make_timestamp and _add_months.


Sent from my iPhone using Tapatalk

[mark123]

Thanks Lynn for your help

I am still getting the following error

Need to resolve prompts.
XQE-PLN-0097 Invalid format for numeric prompt 'Month'.
XQE-PLN-0097 Invalid format for numeric prompt 'Year'

I selected value prompt for month and year prompt. Is it correct or should i select date prompt there(but i was unable to filter date prompt to display only month or year instead of the whole thing).

Thanks
Mark