Exclude values in Crystal like in BEx queries

[Private_Member_24235]

Dear all,

I have a crystal report which is referring to an BEx query. After refreshing the database connection in Crystal, the variables from the BE query gets leveraged into the crystal as parameter, so everything is fine and working good.

For special analysis it is also neccessary to exclude values. I can not see any button or function in the filter pop-up screen of Crystal to exclude values. Also in the parameter option I could not find any property to determine the behaviour of the parameter.

Ho can filter values be excluded from the user side in crystal?

Any help would be great. Thank you in advance.

Best regards,

Stefanos from Munich/Germany

[Former Member · Accepted solution]

Hi Stefanos

this is an EXCELLENT question

have you tried maintaining your BEx query with TWO variables?  One for Include and one for Exclude?

[carsten_ziemann · Accepted solution]

Mike i thing that could work but the variable could not be mandetory!

[Private_Member_24235 · Accepted solution]

Hi Mike,

thank you for your hint. I did it with a second variable.

Best regards,

Stefanos

[vitran23 · Accepted solution]

Can the exclusion variable be "non" mandatory? It seems like it has to be mandatory when set. I don't always want it to be filled so it would be null when not in use.

[carsten_ziemann]

Hey Stefanos,

which version of Crystal Reports you use

2011/2013 or Crystal Reports Enterprise

best regards

Carsten

[Private_Member_24235]

Hi Carsten,

we are using CR 2008.

Is there any option in this version to exclude values fromuser side?

Thank you in advance.

Best regards,

Stefanos

[carsten_ziemann]

Hey Stefanos,

i don´t think so that there is a exclude function.

But if you create a variable with "selection option" your user could choose all "other" values. With this you could "exclude" several or one value bye choose all other values!

Could that a solution for your problem?

[Private_Member_24235]

Hey Carsten,

yes would be applicable. Is there an option to exclude in the higher CR versions.

Best regards,

Stefanos

[abhilash_kumar]

The next higher version of CR 2008 is CR 2013 and there are no differences in the way both handle BEx queries.

CR 2013 only has two new export options, everthing else is same as CR 2008.

-Abhilash

[abhilash_kumar]

Like I said, the < and > button on the prompt screen will let you add and remove values from the list.

If you however, want the values in the list to the dynamic - e.g: For user A you see values x,y,z, for User B you see p, q, r, then you should probably create an Authorization variable (?).

-Abhilash

[Private_Member_24235]

Hi Abhilash,

where can I find the < and > buttons in the filter popup of Crystal? I can not see any of this buttons in the variable section, please see my screenshot (it is in german, but nevertheless you can see, that there is no such a buton).

Is this button maybe only available in higher CR versions?

Best regards,

Stefanos

[carsten_ziemann]

Hey Stefanos,

in this case it is "Hinzufügen" With this option you could add severel values to "Aktuelle Werte" and only for this values you get your result.

It think thats the same as the buttons < and > as Abbislash mentioned.

best regards

Carsten

[abhilash_kumar]

This screenshot is Not from the CR Designer. It is the prompt page in BI LaunchPad.

Press the "..." button to see/remove/add values to the prompt.

-Abhilash

[Private_Member_24235]

Hey Carsten,

exactly. This is working fine. But what I want to do is, to use "Hinzufügen", select one or two values, e.g. material number and mark them somehow as exclude.

So I want to get everything, except the value which I have selected. Accoring to the scrrenshot: I want to see all data, but no record for this selected material.

Instead of selecting every material number I want to see, which is a hughe amount, I want to see all data, but select some values for exclusion.

Is there any way to enable this functionality somewhere else?

Best regards,

Stefanos

[abhilash_kumar]

You'll need to handle this inside the BEx query.

-Abhilash

[abhilash_kumar]

Hi Stefanos,

What exactly do you mean by 'exclue values in the prompt screen'?

And, what exactly do you mean by 'property to determine the behaviour of the parameter'?

-Abhilash

[Private_Member_24235]

Dear Abhilash,

when I execute a BEx query, I can use variable filter to include specific values, e.g. customer, material or Cost center. It is also possible to exclude one or more values by clicking on the exlude button. See also my screenshot as reference.

I want to use the same functionality in Crystal report, but as far as I can see, all values I typed in or select in the variable field are getting only included. Sometime the user wants to exclude also some values.

How is this working in Crystal reports. Any help would be great.

Best regards,

Stefanos

[abhilash_kumar]

Use the "<" button in the prompt screen to remove values from the value list and ">" to add values to the list.

-Abhilash