Is it possible to design a graphical interface for a user-defined query?

[Former Member]

Dear community,

I have created the following query:

SELECT SUM(Debit), Project FROM BTF1 WHERE
(Account LIKE '68%' OR Account LIKE '58%') AND
RefDate >= '2017.01.01' AND RefDate <= '2017.06.30' GROUP BY Project

Now I would like to design a graphical interface to collect input (namely the RefDate and the Account).

I tried making the input parametric (i.e. T0.[RefDate]>=[%0]): it does open a window to input data, but I would prefer an input window that executes the query after entering input and clicking a button, without opening the query.

Is it possible to do it in SAP B1?

Thanks a lot for the help.

[Johan_H · Accepted solution]

Hi Claudio,

You may use the SDK to create your own form for input of parameters with a button to execute the query and show you the results, and put a link somewhere in the main menu or top menu, to open this form.

However, that would be a LOT of work, that would involve programming, and as a result you would save the user maybe two clicks, and that only the first time that they run the query.

I recommend that you simply use this query:

SELECT SUM(Debit), Project
FROM BTF1 
WHERE (Account LIKE '[%0]%%' OR Account LIKE '[%1]%%')
  AND RefDate BETWEEN [%2] AND [%3]
GROUP BY Project

Once the user has clicked through the menu and has found the query, they click it, the parameter input form is shown with a button to actually run the query. That is exactly what you described that you want.

For the next run of the query, the user does not have to click through the menu again to find the query. All they have to do is this:

1. Do not close the window with the query results
2. Instead click the 'Execute' button at the bottom of the form with the query result. This will open the input parameter form directly.
3. Input the next set of parameters and press enter or click the 'OK' button.
4. Repeat steps 1 through 3 as many times as needed.

Regards,

Johan

[gianluca_calloni]

Hi Claudio. With query manager you could only do a query. If you use some parameter, the user must input parameter before run the query.

The query code is always exposed in the query manager. The parameter will be replaced by the value entered by the user.

You could use the between statement for the ref date instead 2 condition ( >= and <=)

Eg: T0.RefDate between '2017-01-01' and '2017-06.30'

and with parameter

T0.RefDate between [%0] and [%1]

Hope this help..

LUCA

[Johan_H]

Hi Luca,

"...The query code is always exposed in the query manager..."

This is incorrect. The query code can be hidden and made inaccessible with an authorization.

Regards,

Johan

[Former Member]

Grazie! 🙂