SQL Parameter in Layout Definition

Roberto Marra

Hi there,
I got this problem with the SQL Parameter in a Layout Definition. I would like that the user (in our case agents) can launch a report just after selecting some parameter as "from date" "to date" and "customer". Until now everything was working because in the customer combo-box the user got a list of all the customer, now we want that the user can select in the range of his own customer, whithout seeing all the other.

I thought that I could handle this with a query, in the SQL Parameter, like:

[SELECT T0.CardCode, T0.CardName FROM OCRD T0 INNER JOIN OSLP T1 ON T0.SlpCode = T1.SlpCode
 INNER JOIN OUSR T3 ON T1.SlpName = T3.u_name WHERE T3.u_name = [%UserId] ORDER BY T0.CardName]

(In our case the OUSR.U_NAME is equal to OSLP.SlpName)

But dosn't work.

Does somebody already face this problem?

Thx for any help

Cheers
Roberto

Philipp Knecht

Hi Roberto

I just tested your query within Qeury Analyzer.
UserId is alphanumeric. So please use '[%UserId]' and the query will work.

hth

Roberto Marra

Hi Philipp and thx for your reply. Infact yesterday I tried as well as you suggesting me, but I got this error that you can see in the attachment

Roberto

Attachment
[url=http://www.coresystems.ch/wp-content/../wp-content/forum-image-uploads/gokyo66/ErrorLayoutDefinition.JPG]ErrorLayoutDefinition.JPG[/url]

Massimiliano Luppis

Ciao Roberto,
(come stai ?).

It looks like UserId parameter is not defined.
This is pretty strange since UserId is a system parameter, like LangCode, that usually you have no need to define.

Try see if UserId is inserted in the Param Table:
Tool -> User Defined Object -> Default Forms -> SWA_LD Parameters.

Hth
Max

Roberto Marra

Ciao Max, tutto bene grazie.

I already checked and is there infact in another query it works properly, is just inserted as SQL Parameter in the Layout Definition that give me problem.

Ciao and thx for your support

Roberto

Massimiliano Luppis

Wow, when a strange problem get out you are there !
I try to guess: maybe u_name has to be written U_Name or U_name ?

Roberto Marra

I don't think is case sensitive, but Im going to try and let you know.

Philipp Knecht

Hi there

Let me guess. You start the layout from the Layoutform directly by pressing "OpenDesigner" or "Preview"?

Then this is expected behaviour. Let me explain why:

There are Systemparameters like UserId, LangCode, ...
They get filled when starting the Layout from Menu or Form.

But when you start the Layout like that this Params have no values.
So if you would like to Test from the layoutform you need this Parameters also in the Parameter list.

So just add them in the List and you can Test the Layout.

hth

Roberto Marra

Hi Philipp,
I don't get your point. I got the problem even if I start the report from the menu Sales Report, where I puted it.

Btw let me understand, what kind of information is holded by "%UserID"? Let say that in my OUSR table I got the following:

User Code: AG_07
User Name:Angelo Guitti

which one is the correct one to refer in the WHERE clause?

ROberto

PS:Max I tried but is no matter of upper or lower case

Massimiliano Luppis

Boh, in these situations I usually try to walk around the problem.
For example try this:
remove the query from the first row, insert as first query something more simple that uses also [%UserID], like

SELECT * FROM OUSR T0 WHERE T0.u_name = '[%UserId]'

just to see if the first query succeed in catching the parameter.

Massimiliano Luppis

Sorry, in my previous post I forgot:
reinsert the original query as second row, otherwise your layout cannot find data !

Philipp Knecht

Hi all

It's a bug.
When starting fromk the menu UserId doesn't get filled somehow.
I'll fix it in recent beta which will be released next week.

Please let me know if you are interested in an development version.

Workaround:
Start the layout from a form.

hth

Massimiliano Luppis

I was sure that if there is a bug, Roberto will find it :)

Roberto Marra

:) Thnx guys anyway.

Pls Philipp let me know when is ready, Im not so in hurry to get a development version but ready for the next release.

Thx 4 the support Philipp and Max

Roberto