BusinessObjects Board

Report filter with variable or universe object

building reports webintelligence
#1

Hi

Is the possibility of adding variable as data range for report filter.

I am able to create report filter with between instead of selecting the value from the object can variable or Universe object can be used. If possible can you please provide me details.

Thanks.

ThanksForHelp (BOB member since 2006-09-25)

#2

At report level you can use runtime prompts in the query filters section

At universe level you may create a condition object with date prompts for the same and use it in your report.

CSM07 (BOB member since 2016-11-15)

#3

Hi CMS07,

Thanks, I am planning to schedule the report so prompt is not an option. that is why I am looking for variable so it can calculated and used for date range.

ThanksForHelp (BOB member since 2006-09-25)

#4

If you are looking at scheduling, what are the time rangers that you are looking at?

Scheduling is commonly achieved with relative date objects.
See this topic for details: DB-specific Relative Dates SQL Server, Oracle, Netezza, DB2

#5

Hi,

The report query will have data for all period on shipment date but CustomerReqDate between last 12 months for example 2018 04 - 2019 03 for current month is need in the report. Only shipment date is mapped to our fiscal Calendar and all the report calculation is based on shipment date. But there is one user requirement for the report based on CustomerReqDate on rolling 12months. So changes on Universe will not be approved.

For example

ShipmentDate--------CustomerReqdate
12/05/2019----------1/2/2019
25/06/2019----------2/24/2019
10/10/2020----------3/3/2019
02/20/2020----------12/31/2018
05/10/2018----------11/12/2018

Any thing that can be done from report level will be great, like variable or dynamic report filter.

Thanks for you time

ThanksForHelp (BOB member since 2006-09-25)

#6

Here’s a SQL Server example:

SELECT DATEDIFF(MONTH,Table.ShippingDate,CAST(GETDATE() AS DATE))

If ShippingDate was in September, that will give 6.

If you create the above without the SELECT as an object called, say, Months Since Shipped, you can have that in your query condition where Months Since Shipped between 0 and 12.
If you schedule this, you’ll always get data for the current month so far and the previous 12 months, i.e. March 1st 2018 to now

#7

Hi Mark,

Thanks for your suggestion but my understanding this is Universe level, is there any chance it can be done in report level.

Really appreciated your help. Thanks

ThanksForHelp (BOB member since 2006-09-25)

#8

You can but the problem that you’ve got is that you’ll be bringing back all data forever and then filtering for just the last 12 months. If that doesn’t both you, it’s achievable.

P.S. it would bother me. :mrgreen:

#9

Mark thanks for your quick reply.

I am trying report variable and adding filter. User needs all the data for other report. There are multiple report in the document.

=MonthsBetween(CurrentDate();[Backlog Customer Request Date])

But the is not correct.

CustReqDate----VariableDiff------CurrentDate
03/01/2019--------0--------------3/13/19
03/22/2019--------0--------------3/13/19
03/31/2019--------0--------------3/13/19
02/14/2019--------0--------------3/13/19
02/22/2019--------0--------------3/13/19
02/28/2019--------0--------------3/13/19

My understanding Feb should be 1, but it is showing zero. Please help if I am missing any thing.

ThanksForHelp (BOB member since 2006-09-25)

#10

OK, that’s fine if there’s other uses for the other data within the report.

Based on being on 14/3/19, what dates do you want to show for CustReqDate? e.g. 1/3/18 - current, etc.?