How to create a record selection criteria for information design tool. Business filter?

[Former Member]

This is my first time posting to SAP forums.  I hope I'm in the correct area.

I have written crystal reports for several years now, but now I'm moving to create my first universe using Information Design Tool 4.1.

I have added 2 tables and linked them.  I Have removed the fields in each table I don't want in the universe.

There are a fiew fields that I want to restrict the data that a user can view.  My goal is to filter those records I don't want them to have access to within the field.

In a crystal report setting wen designing a report I would use the record selection criteria to filter out what I don't want the user to see using a NOT statement

I have been searching around looking for how to do this in the Information Design tool.

I am not a SQL person so I don't know how to write the appropriate statement in SQL.

I found that on the business layer, I can create  filter as a "business" type which seems very similar to crystal reports.  I selected my field, and selected "not equals" to then typed in my constant of "OIS*" so that anything that starts with OIS wont show up when a person uses the universe.

I think this may be the correct way to do this, if not please help.

So now that I have my filter created, what do I do to make it work?  When I view the field, I still see data that starts with OIS, so I assume there must be a way to activate this filter but I cant figure it out.

Any help would be appreciated.

Thanks

[amitrathi239 · Accepted solution]

Hi,

If you are looking to restrict all universe objects values excluding the OIS values then create filter like.

substring(@Select(Class name\Object name),1,3)<>'OIS' and under the Properties tab select the option "use filter as mandatory in query" & "Apply on universe".

After that all values related to OIS will not come in the reporting.

If you are looking to restrict the business type LOV's only so that users can not select the OIS values in the report then you need to create custom lov's and assign the custom lov's to the business type object.

Click on the "parameters & List of values" icon ->under the "List of values" section select "List of values based on the business layer objects"->Edit query->drag the business type object in the select and query filter part.condition would be business type ->different from pattern->'OIS*'

After this double click on the business type object->advance tab->List of values->select the newly create list of values.

After this users can not see the values start from OIS in the report prompts.

Amit

[Former Member · Accepted solution]

Thank you for your assistance.  I did not see the option under properties to use filter as mandatory in query.  Below is a screen shot of the filter I created which is based on a single data field that I am trying to filter it out.  I didn't quite understand the substring you listed and how/where to apply that.  Will the method I'm using below work?   I set the filter to apply to the universe as you suggested, but if I go to this filed in the business layer and have it show me the values, it still shows up. Should it filter it out in the business layer (I figured this is how I should test it)?

thanks again for your help.

[amitrathi239 · Accepted solution]

Hi,

I don't think so your condition will work.to exclude or include values based on the Pattern functions are not available in the filter conditions.

My filters conditions syntax is based on the  Native Filter type not by Business filter type check-box.

if you are not comfortable with Native filters syntax then create one more object in business layer based on the Case Type Desc.

Object Case Type.

Select part=substring(@Select(class name\Case Type Desc;1;3)

With this you will get the first three letters.

After that create filter and use newly created Case Type object not equal to  OIS. (Don't put star after OIS).Next to filter definition tab->click Properties tab select the option "use filter as mandatory in query" & "Apply on universe".

Amit

[Former Member · Accepted solution]

This is definitely different that Crystal Reports designing.  I am trying to understand but having some trouble.  If your still willing to help me here is what I have done.  I created a new native filter and cut/paste your line of code in and assumed I'm suppose to put the name of the field into the line.  I have tried to enter it a few different ways, but I keep getting errors.  I have attached a screen shot of one of my attempts.  Since I don't understand the code, its difficult for me to figure it out.

[amitrathi239 · Accepted solution]

Hi,

this is wrong syntax.

First double click on the Case Type Desc object.you will get the  values like

@Select(Your object Class name\Object name)

After that either add the substring syntax manually or replace the bold part in the below  one with Right syntax is below.

substring(@Select(Class name\Object name),1,3)<>'OIS'

Amit

[Former Member · Accepted solution]

thanks for hanging in there to help me.  I am sure we are getting close.  So I double clicked on the Case Type Desc field and I can see the select statement that is below:

I then cut/pasted your substring line of code and put the class/object name where the bold part is which looks like this

but as you can see I'm getting an error.  Obviously I'm either missing something or as the message says my version doesn't support it?

[Former Member · Accepted solution]

I found another method after doing a lot of reading and testing.  I added a where statement and now it is filtering out anything that starts with OIS.  Thank you for your patience.

[amitrathi239 · Accepted solution]

Hi,

No your steps are wrong.Follow these steps.

1) Right click on the class and click on the filter.

2) click on the SQL assistant

3) click on the case Type desc object.

4) add the substring part and click validate.

5) click on the properties tab and select the options.After that this condition is going to be applicable for all universe data.

Amit

[amitrathi239 · Accepted solution]

Hi,

Thats good..I have posted different one.If required to restrict the all the values in the universe where value is OIS.

Amit

[Former Member · Accepted solution]

your images were excellent.  I followed it exactly but I get an error substring invalid identifier

[amitrathi239 · Accepted solution]

Hi,

or try with this instead of substring formula.

@Select(V Cases\Case Type Desc) NOT LIKE 'OIS%'

Amit

[Former Member · Accepted solution]

using the last post and removing substring, I don't get any errors.  I went into properties selected use filter as mandatory in query and apply on universe.  Then I double clicked on Case Type Description field and selected show values and all the data with OIS still shows up.  So it appears the query is not taking effect?

[amitrathi239 · Accepted solution]

Hi,

instead of clicking show values ,try to create some queries and see if this is restricting the values or not.

Drag some object in the select part and click on the "View Script" and this restriction will come in the SQL where part.

Amit

[Former Member · Accepted solution]

Since I'm more familiar with Crystal, I just created a quick CR and the OIS values were filtered out.  Thank you.  I was able to create a 2nd filter to also filter out 2 more data items.  I couldn't figure out a way to do it with the OIS filter you helped me create, so I just created a 2nd one to filter out the other data and it works fine now.  I hope that is an appropriate way t o add an 2nd data element that I wanted filtered by creating a 2nd filter for the same field?

I appreciate you hanging in there to help me out.  I'll mark the last item as the correct answer.  If you don't recommend I create 2 filters on the same field, let me know if there is a better way to accomplish filtering out OIS and In Custody.

thanks again for your help!!

[amitrathi239 · Accepted solution]

Hi,

It's fine.end result is to exclude the values which you have already done.

Amit

[Former Member · Accepted solution]

excellent, thank you!!

[Former Member · Accepted solution]

Apparently I cant see correctly today.  the 2nd filter I applied to remove "In Custody" from the case types is not being filtered out when I create a report using the universe.  My filter is currently written as indicated in the image below

@Select(V Cases\Case Type Desc) NOT LIKE 'OIS%' 'In Custody%'

but this statement give me an error indicating right parenthesis missing.  Any help on what I'm missing would be appreciated.

[amitrathi239 · Accepted solution]

HI

try this.

@Select(V Cases\Case Type Desc) NOT LIKE ('OIS%', 'In Custody%')

Amit

[Former Member · Accepted solution]

Thanks for the quick response.  I get an error that says invalid relational operator

[amitrathi239 · Accepted solution]

HI

try something like.if this will not work then will check tomorrow.

@Select(V Cases\Case Type Desc) NOT LIKE ('OIS%') or  ('In Custody%')

Amit

[amitrathi239 · Accepted solution]

or try like this.

@Select(V Cases\Case Type Desc) NOT LIKE 'OIS%'

or

@Select(V Cases\Case Type Desc) NOT LIKE 'In Custody%'

[Former Member · Accepted solution]

I get the invalid expression error

[Former Member · Accepted solution]

On this one I get the same error

[amitrathi239 · Accepted solution]

Hi,

try with this.It's working for me.

@Select(Case\Case Type desc) NOT LIKE ('OIS%')

AND

@Select(Case\Case Type desc) NOT LIKE ('In Custody%')

If this will not work for you then you can create two separate filter for each value.

Amit

[Former Member · Accepted solution]

Good Morning,

I tried using AND which gives me an invalid expression.  Don't know why its working for you.

I tried the 2 filter method you recommended yesterday and the 2nd filter did not work.

However I tried it again today and the only difference is I left the parenthesis around the 'In Custody' and today its working.

Thanks again, I don't understand it, but at least its fixed.  I'll mark the last response as correct.