The use of user prompted parameters in Business One queries provides powerful flexibility to the Business One reporting environment. A limitation was discovered with the use of this technique. It appears that some erroneous error messages can occur when the user prompted parameters are used in long queries or when they are used in certain nested SELECT statements. The work-around for this limitation is explained in the SAP note #730960. The note describes a process of building a definition for a user prompted parameter that overcomes the problem.
It was discovered that this technique could also be used to provide a more visually appealing user prompt in the case where the user will be required to enter values to be used to generate the query.
The technique allows any table/field to be used to define the user prompt. This means that the user prompt presented using this technique can be the field name for any data base field. There is no longer a limitation of having the user prompt text be that of the field that is being compared in the WHERE clause. So for example if the desire were to ask the user for a range of dates to qualify the document date with, the user prompt might appear as below.
Using the alternate technique a similar user prompt might appear as:
If the terms “From Date” and “To Date” are more meaningful to the user as prompts, then this presentation may be a better one to accomplish the same purpose of asking for this range of dates.
The query below can serve as an example of how this technique can be applied in order to change the prompts that the user is presented with when the query is executed. In this case the fields ‘FromDate’ and ‘ToDate’ from the table OSRT were used to prompt the user.
/*SELECT FROM [dbo].[OSRT] P0*/
declare @StartDate as datetime
/* WHERE */
set @StartDate = /* P0.FromDate */ ‘[%0]’
/*SELECT FROM [dbo].[OSRT] P1*/
declare @EndDate as datetime
/* WHERE */
set @EndDate = /* P1.ToDate */ ‘[%1]’
/*Main Query Area*/
SELECT T0.DocNum ‘Inv#’,T0.DocDate ‘Post Date’,T0.CardCode ‘Customer#’,T0.CardName ‘Customer Name’
FROM OINV T0
WHERE (T0.DocDate >= @StartDate and T0.DocDate <= @EndDate) ORDER BY T0.DocDate, T0.CardCode FOR BROWSE