BusinessObjects Board

How to display previous month as separate from input (@prompt month (Jan-2020))?

Hi Team

I Have webi report requirement ( Group Dimension column is hard coded values)

Group - Input Month (Jan-2022) - Previous Month (Dec-2021)


Group -A 30 20
Group - B 10 5

Backend Oracle DB filed : "Order_date_key " (yyyymmdd) format data type is number

when user select from @Prompt : Jan-2022 , I need to display Jan-2022 , Dec-2021 in two separate columns with counts? how to achieve this?

Prompt format : Jan-2022 (Mandatory prompt)
I created filter @ptompt… but no luck
Please help on this

Thanks,
Shannkar

Are you trying to convert “Jan-2022” to “yyyymmdd” format? If so, what should “dd” be set to? If not, please explain further.

Noel

I do not understand what you trying to do. Can you provide any sample data and/or screenshots of your query?

Noel

I am going to make an assumption that given a prompt value you want to select data from the first of the previous month through the end of the month selected.

The following Oracle SQL will get those dates for you…

select ‘Jan-2022’ “Month-Year”
, to_char(add_months(trunc(To_date(‘Jan-2022’, ‘Mon-yyyy’)), -1), ‘yyyymm’) || ‘01’ “Begin Date”
, to_char(last_day(trunc(To_date(‘Jan-2022’, ‘Mon-yyyy’))), ‘yyyymmdd’) “End Date”
from dual;

Just put your @Prompt statement in place of ‘Jan-2022’. That will get you the range of data you want. However, you are not going to be able separate the months in your report unless you are returning a Order Day Key dimension. You could return it in the native ‘yyyymmdd’ format and create a variable in WebI based on that to group the Card Counts by month. Alternatively, you could convert Order Day Key to ‘Mon-yyyy’ and group on that in SQL so you don’t have to create a variable in WebI.

All of this work can and should be done in the universe if possible.

I think I have reached the end the help I can provide in this context. I this helps you get on the right path.

Noel

Thank you Noel,
Let me try from my end

Your result set needs to return order date in some shape or form.

Your prompt condition needs an OR statement where the logic is:
prompt Order Month = Month Order Date
or
prompt Order Month = Previous Month Order Date.

This means that your best solution would be to create a predefined condition in the universe with the logic above. Use the same prompt logic for both parts of your OR condition and simply subtract one month for the second one to get the previous month.

As Noel has said, you will need to add order date or order date key to your results or you won’t know which month the order relates to.

Thank you for the hint