How to display content of query filter in WEBI Report

[Former Member]

Hi All,

I have one Query Filter in the WEBI reports. The prompt appears for the user, but when no date is entered, I have added a query to select a default start & end date.

Now I need to display those dates in the Report.

I tried using UserResponse() but as the User has not entered any dates it doesn't give any output.

Any help is appreciated.

Please see the snapshot attached

Many Thanks,

Shrutika

[amitrathi239 · Accepted solution]

Hi,

Can do by multiple ways.

create these two Start/End date object in the universe and keep same logic in the objects select part which you have mentioned in the predefined condition.

Drag these objects in the webi query.Whenever report is refreshed on based the predefined condition,you will get the same values in these objects in webi.

If this will not work then what is the logic behind Start date & End date in universe.

Amit

[Former Member · Accepted solution]

Hi,

The logic behind having the filter is: If the user doesn't enter start/end dates in the prompt,

following query will get executed, which will choose the 1st day of the current month as Start date and yesterday's date as end date.

For ex. If I run the report today, start date will be 1st Aug and End date will be 16th Aug.

Is there a way to get these dates using this filter only, without creating any more objects.

Some function like "UserResponse" which will return dates produced by the filter query itself.

Regards,

Shrutika

[amitrathi239 · Accepted solution]

Hi,

Create two variables in the webi based on the Current date function.

This will give you the First day of Current month.

Start Date=RelativeDate(RelativeDate(CurrentDate();-DayNumberOfMonth(CurrentDate()));1)

End Date=RelativeDate(CurrentDate();-1)

This is for yesterday's date.

Amit

[Former Member · Accepted solution]

Hi.

Thanks for the detailed solution. But I don't wish to create 2 more objects.

You said there are multiple ways to achieve this. Could you please suggest.

I would like to use only this filter, without creating any more objects.

Some function like "UserResponse" which will return dates produced by the filter query itself.

Regards,

Shrutika

[amitrathi239 · Accepted solution]

Hi,

If you are not wanting to create additional objects in the universe then you can create two variables at webi report level to get the dates.

Start Date=RelativeDate(RelativeDate(CurrentDate();-DayNumberOfMonth(CurrentDate()));1)

End Date=RelativeDate(CurrentDate();-1)

What is the issue to create these in the webi level?

Amit

[Former Member · Accepted solution]

Amit,

This is not the only filter i have in place. Another filter is to get the User response, it also contains Start & End Date.

Then we support 2 types of databases. HANA & Oracle.

I will have to create objects to take care of all this.

And I am short of time here.

That is why I was looking for a Solution like already existing function "UserResponse"

[amitrathi239 · Accepted solution]

Hi,

As per my understanding this is the easiest solution to display User entered date or static date in the webi without creating additional objects in the universe.

I don't think so you can do by webi pre-defined functions.You can get the values by ReportFilterSummary() but again there you need to remove all text with the help of replace function.

If you need to display either user entered date or static dates in the single cell then you can use below steps.

Start Date=RelativeDate(RelativeDate(CurrentDate();-DayNumberOfMonth(CurrentDate()));1)

End Date=RelativeDate(CurrentDate();-1)

V Start date=userresponse("Start date")

V End date=userresponse("End date")

=If(isnull(V Start date)) then [Start Date] + "To" + [End Date]  else [V Start Date] + "To" + [V End Date]

Amit

[Former Member · Accepted solution]

Thanks Amit. I will implement this. It works perfectly.

Regards,

Shrutika

[Former Member · Accepted solution]

Hi Amit,

I am trying to implement this. Facing one issue.

Formula used is :

=If(IsNull([U Start Date])) Then ([Start Date] + "To" + [End Date]) Else ([U Start Date] + "To" + [U End Date])

This is not working when there is not input from User. IsNull([U Start Date]) even if it is true, [Start Date] is not getting displayed.

I tried the "Length([U Start Date]) > 0 " function, it is returning true.

Could you please suggest anything..

Regards.

[amitrathi239 · Accepted solution]

Hi,

What is the datatype of [U Start Date] variable? if  String then you can use Length function.

Try with this.

=If(not(IsNull([U Start Date]))) Then ([U Start Date] + "To" + [U End Date]) Else ([Start Date] + "To" + [End Date])

Amit

[Former Member · Accepted solution]

This works:

=If(IsPromptAnswered("Enter Start Date")) Then ([U Start Date] + "To" + [U End Date]) Else ([Start Date] + "To" + [End Date])

Thanks.

[Former Member]

Hi,

sorry if i am wrong, you want to display the data if user did not entered or entered dates right!!!!!!

The set default prompt values at universe level or make the filer as optional filter at query panel then if user may or may not entered the dates then also report will display the all the data.

If you requirement is not this, ignore it.

Thank you.

[kohesco]

Hi,

Check the universe, and check code of filter defined in that univere.  Create dimensions if they do not exist at the moment and add them to the universe/query/webi report.

Grtz

Koen

[Former Member]

Hi Shrutika,

I think you are expecting this function , try the below functions to get the details of filter values that applied in the reports,

ReportFilterSummary() or

ReportFilter()

Regards,

Ragoth.C

[Former Member]

Hi,

I tried both the functions but they are not giving me expected results.

Do you know any other function ?

[Former Member]

Hi,

We can set default values for Start date and End date for query in prompt proerties.

When user doesn't enter any values for Start date and End date it will pick up default values for the prompt.

[srini_cnu]

Hi,

Iam facing the same issues. any date function is there in reports level. we are passed the function it will or not.

Regards

Sree