How to restrict the output of 0VENDOR by excluding the # value in rows?

[Former Member]

We generated a query whose InfoProvider is 0Vendor. Run the query, find the last row shows # as Vendor number and the description of it is "Not Assigned". Then we go to this query design screen, pick 0Vendor in the rows frame, right click and select Restrict, there are two tabs, one is "Fixed Values", the other one is "Variables". Click "Fixed Values" tab, can see that 1st value is "#", but from this window, we can't see any place to exclude this "#" value.

We are very appreciated anyone's input on how to get rid of the "#" value when run query.

Thanks

[Former Member · Accepted solution]

Hi,

After moving '#' from Left side to right side in the 'Selection for Vendor' screen , right click on # and then you can see optioon "Exclude from Selection".

With rgds,

Anil Kumar Sharma .P

[Former Member · Accepted solution]

hi Anil,

We forgot to let you know that under 0Vendor in the rows frame, there is a variable called Z00_VEND attached to it that when we followed what you instructed and save the query, get a msg box which says "Variable 'Z00_VEND' is complex('selection option' or 'query' type). You cannot use any other entries in the selection" with an OK button. Click OK, but the modified query can't be saved. Any idea?

Thanks

[Former Member · Accepted solution]

Hi,

Excluding variable values

You use this function to select the values that you do not need for your report.

<b>This function is only valid for single value variables and not for variable quantities.</b>

...

1. Select the desired value in the right Selection window.

2. Choose Exclude from Selection from the context menu (secondary mouse click).

If you want to include the value in the selection again, choose Include in Selection from the context menu (secondary mouse click).

shylaja.

[Former Member · Accepted solution]

You cannot use a variable for user entry and exclusion on the same characteristic in a query.

[Former Member]

Hi,

Eliminating # in the result area..

Necessary steps to avail this functionality:

Written a VB macro for the same

Here are the steps to do the same

1) In excel menu Tools->macro

2) Enter the macro name say SAPBEXonRefresh

3) click 'create', will go to visual basic editor

4) To display '#' as '' “ ( blank) paste the following code

Sub SAPBEXonRefresh (queryID As String, resultArea As Range)

Dim c As Range

For Each c In resultArea.Cells

If c.Value = "#" Then c.Value = ""

Next c

End Sub

5) Close the editor and click on refresh again.

After refresh you will get blank in the columns with #.

Hope this will help you.

Thank you,

Regards,

G.Ram

[Former Member]

hi Ram,

Get a few questions about your code:

1. 1st line is:

Sub SAPBEXonRefresh (queryID As String, resultArea As Range)

queryID should be our query technical name, right? what should we input for resultArea?

2. The code will be input into each user's excel, that means it's client or user dependent or we would have to let each user input the code into their own excel software in their own machines, right? That maybe not what we want.

Thanks

Message was edited by: Kevin Smith

[former_member188325]

No you don't need to change anything in code.U just copy that code.

Thanks

[former_member188325]

pl refer this post:

[Former Member]

hi CC,

I believe the queryID must be replaced by our query technical name, right?

Also you didn't answer 2nd question, this code is very specific for each user, or that means each user would copy the code into their machine respectively, right?

Thanks

[former_member188325]

Hi,

No queryID would not be replaced by Query technical name..

and this code is not user specific.

After writing this macro,You have to save it as workbook.

Pl don't forget to assign points if it helps.

Thanks