Skip to Content
1
Sep 03, 2011 at 02:23 PM

Can we change parameter descriptions in Selection Criteria. YES we can!

2121 Views

Dear All,

I have been struggling some time on some way to change the parameter descriptions in the Query - Selection Criteria screen.

While doing numerous tests on trial-and-error, I accidentally hit upon a solution which appears to work!

To change a parameter description, we simply have to reverse the order in which the WHERE clause is written.

Please refer to the Query below:

I used Customization Tools to create a UDT called @SCANNING

and a UDF called U_ourref

Title : ourref

Description : Our Reference

-


If I use WHERE clause the conventional way, ie

WHERE

T1.[ImportLog] = @ourref

I get the following Selection Criteria screen:

----------------------------------------------------
Query - Selection Criteria Screen
----------------------------------------------------
Import Log         .........................

[OK]   [Cancel]

-


If I write WHERE clause by interchanging the 2 sides of the equality, i.e

WHERE

@ourref = T1.[ImportLog]

I get this screen:

----------------------------------------------------
Query - Selection Criteria Screen
----------------------------------------------------
Our Reference         .........................

[OK]   [Cancel]

Well! Is this not a way of changing the parameter description?

And it works!

-


My Problem

The solution is nice. However, I wanted to use LIKE instead of EQUAL TO

in order to allow Query to search for similar items.

With the conventional clause, I can use '%[%1]%' in declaration and LIKE in the WHERE clause.

With the reverse clause, I cannot figure out how to do it.

Could anybody help?

Thanks

Leon Lai

.

.

.

-


declare @ourref nvarchar (30)
set @ourref =
/*select T2.U_ourref FROM [dbo].[@SCANNING] T2 where T2.U_ourref*/ '[%1]'



SELECT
T0.[TaxDate] AS 'Doc Dt',


T0.[DocNum] AS 'SAP Ref.',


T0.[CardName]  AS 'Supplier Name',
T0.[DocTotal]  AS 'Amount',
T1.[ImportLog] AS 'Our Ref',
T1.[BlockNum]  AS 'Reqn No.'


FROM [dbo].[OPCH] T0
INNER JOIN [dbo].[PCH1] T1 ON T0.[DocEntry] = T1.[DocEntry]
INNER JOIN [dbo].[OJDT] T2 ON T0.[TransID] = T2.[TransID]

WHERE
@ourref = T1.[ImportLog]