How to create a optional filter condition in Universe

[Former Member]

Dear All

I am using BI 4.1 ( .Unv ) , we need to add a optional condition in Universe. I tried different ways to do it, but unable to do it correctly.

In one of my approach, I created a report and put a optional prompt there then edited the SQL and copied that prompt condition and pasted that into a Universe condition. Then I used this filter into a report when i ran the report without selecting any value into the prompt it was throwing an error.

Please suggest if there is any way by which we can do this.

BO should have this functionality for better end user experience.

Thanks,

Mayur

[Former Member · Accepted solution]

Hello Mayur,

What do you mean by an optional condition - you can chose to use a condition or not.  That is optional.

If you mean an optional prompt then BO does have this functionality and has had for more than 10 years.  It is well documented in the manuals and also on forums (@prompt syntax).

One thing I'm not clear about is why you are using a .unv as they are deprecated in 4.1.  Do you mean .unx?

Thanks

[Former Member · Accepted solution]

Hi Bill,

Yes we are still using .unv as the universe we have is created in 2008 and since then we been migrating this through different versions.

What I meant is a Filter which is created in the Universe and which has ability to act like a report level optional filter ..means if you select any value then it will fetch data for that selection else it will give data for all LOVs

Yes BO has optional prompt functionality only for report level filter .. I mean when you pull a dimension into filter panel and select its value equal to prompt and in prompt properties you check mark the Optional Prompt option.

The requirement is to create a Universe level filter which when used into a report then it should should have this optional prompt functionality. either users can run report with setting some value or without selecting anything.

Thanks

[Former Member · Accepted solution]

One more thing this requirement is there because we have more than 100 standard public reports and there are many users who have only read access.

In many report we have used universe level conditions( for which we dont have facility to set it as a optional ) , when user want to run a specific report for all countries in that case they have to manually select all countries, hence what we are trying to do if he wants data for all counties then should simply not select any value

Universe Filter Condition Name :  Select Country

@Select(Asset\Country) IN @Prompt('Enter Asset Country(s)','A','Asset\Dataset ID',MULTI,FREE)

[Former Member · Accepted solution]

HI Mayur,

SAP stopped developing the .unv format many years ago - it is deprecated and .unx is recommended.  This feature does exist in a .unx Universe.

A workaround I remember (and it's documented on the web in many places) is something like:

@Select(Asset\Country) IN @Prompt('Enter Asset Country(s)','A','Asset\Dataset ID',MULTI,FREE) OR @Prompt('Enter Asset Country(s)','A','Asset\Dataset ID',MULTI,FREE)=''

[Former Member]

Hi Mayur,

    Is it solved?

Regards.

[Former Member]

Hi Mayur,     I have the same doubt,what is the Optional condition? a filter in the report? an optional prompt? You can manage it creating a Filter in the Universe, or a filter in the header of the report, it will give you "All data" and the option of choose which one do you want to see. Regards

[Former Member]

Hi Mayur,

     What I mean is that: You can create in the universe.UNV the optional filter

And then use it as an optional prompt in webi.

[Former Member]

Thank you can bell .

I have never used XML notation in where clause of filter , when i tried to write one by taking reference of your screenshot then my filter is not parsing the SQL ( I have oracle in the back end )

Please suggest .

Thanks

Mayur

[Former Member]

Use this option: