PANTHEON™ Help

 Toc
 PANTHEON Hilfe - Willkommen
[Reduzieren]PANTHEON
 [Reduzieren]Leitfäden für PANTHEON
  [Vergrößern]Leitfaden für PANTHEON
  [Vergrößern]Leitfaden für PANTHEON Einzelhandel
  [Vergrößern]Leitfaden für PANTHEON Vet
  [Vergrößern]Leitfaden für PANTHEON Farming
 [Reduzieren]Benutzerhandbücher für PANTHEON
  [Vergrößern]Benutzerhandbuch für PANTHEON
  [Vergrößern]Benutzerhandbuch für PANTHEON Einzelhandel
  [Vergrößern]Benutzerhandbuch für PANTHEON Vet
  [Vergrößern]Benutzerhandbuch für PANTHEON Farming
[Reduzieren]PANTHEON Web
 [Reduzieren]Leitfäden für PANTHEON Web
  [Vergrößern]Leitfaden für PANTHEON Web Light
  [Vergrößern]Leitfaden für das PANTHEON Web-Terminal
  [Vergrößern]Leitfaden für PANTHEON Web Rechtliches
  [Vergrößern]Alte Produkte Archiv
 [Reduzieren]Benutzerhandbücher für PANTHEON Web
  [Vergrößern]Erste Schritte PANTHEON Web
  [Vergrößern]Benutzerhandbuch für PANTHEON Web Light
  [Vergrößern]Benutzerhandbuch für das PANTHEON Web-Terminal
  [Vergrößern]Benutzerhandbuch für PANTHEON Web Legal
  [Vergrößern]Alte Produkte Archiv
[Reduzieren]PANTHEON Granulate
 [Reduzieren]Leitfäden für PANTHEON Granulate
  [Vergrößern]Personalgranulat
  [Vergrößern]Reiseaufträge Granulat
  [Vergrößern]Dokumente und Aufgaben Granule
  [Vergrößern]Dashboard Granule
  [Vergrößern]B2B-Bestellungen Granulat
  [Vergrößern]Feldservice-Granulat
  [Vergrößern]Bestandsgranule für Anlagevermögen
  [Vergrößern]Warenlager Inventar Granulat
 [Reduzieren]Benutzerhandbücher für PANTHEON Granulate
  [Vergrößern]Erste Schritte
  [Vergrößern]Personalgranulat
  [Vergrößern]Reiseaufträge Granulat
  [Vergrößern]Dokumente und Aufgaben Granule
  [Vergrößern]B2B-Bestellungen Granulat
  [Vergrößern]Dashboard Granule
  [Vergrößern]Feldservice-Granulat
  [Vergrößern]Bestandsgranulat für Anlagevermögen
  [Vergrößern]Lagereinlagerung Granulat
  [Vergrößern]Archiv
[Vergrößern]Benutzerseite

Load Time: 390,6242 ms
"
  1001235 | 211401 | |
Label


Function SQLExp

SQLExp is a function that allows you to access data that is otherwise not available in the original dataset of a report.

This way you can include data from other tables into user-defined reports.

Function header

function SQLExp(sql: String): String;

Example of the Use on the Report 103 - Order report - detailed

000001.gif Because the names of datasets and fields could differ in each report, you must check the name of the dataset and fields for each concrete report.

You are collecting sales order and once a week you deliver these orders to consignees. In the sales order report you can create a report of non-dispatched orders with entered Delivery field (delivery method) Delivery - with own truck.

In the dataset of sales order report are not provided the customer's address and post. All these data are available in the Subjects register, as for the Subjects ID, it is already present in the dataset of the 103 - Order report - detailed report.

All criteria were met to use the SQLExp on the report. Let's look!

Put text object on report. Click on the object to open the text editor. Write expression in the editor. You must enclose expression between square bracets.

 

[SQLExp('select acAddress from tHE_SetSubj where acSubject  = ' + dlQuotedStr(<qReportIzpis."acConsignee">) + '')]

Comment:

acConsignee is the field in dataset qReportIzpis which is used to generate a report.

After replacing dlQuotedStr(<qReportIzpis."acConsignee">) , the SQL command is:

'select acAddress from tHE_SetSubj acName = 'Customer & Co.''

and thus the query that is executed is:

select acAddress from tHE_SetSubj acName = 'Customer & Co.'.

Report of the customer's place from the sales order

SQL sentence is in this case join of two tables

[SQLExp('select P.acName from tHE_SetSubj S left join tHE_SetPostCode P on S.acPost = P.acPost where acSubject  = ' + dlQuotedStr(<qReportIzpis."acConsignee">) + '')]

Example of a collective invoice

Let's look at an example where the collective invoice (receipt or issue) has been created from internal documents (receipts or issues) in which you've received or issued goods tracked by serial numbers.

The report of a collective invoice or an overview of serial numbers will not produce information about the received or issued serial numbers of items from the collective invoice, because these serial numbers were received or issued through internal documents.

But you can report a list of received or issued serial numbers! To do this, use the SQLExp function.  Serial numbers will be directly reported using the link table, where type of link between the internal document and the collective invoice is 4.

Reports of this kind are used to report serial numbers in a collective invoice that has been created directly from internal documents.

Create a new copy record from the report of an already existing report for an invoice. Add a new text object and write into it the SQL sentence:

[SQLExp('select S.acSerialNo + '' ('' + LTrim(Str(Sum(S.anQty), 10, 2)) + '')'' as SN from tHE_MoveItemSerialNo S, tHE_LinkMoveItemMoveItem V where V.acLnkKey = S.acKey and V.anLnkNo = S.anNo and V.acType = ''4'' and V.acKey = ''' + <tbIzpisPrometPoz."acKey"> + ''' and V.anNo = ' + VarToStr(<tbIzpisPrometPoz."anNo">) + ' group by S.acSerialNo')]

An Example of a Collective Document Created with Internal Documents:

Let's look at an example where the collective invoice (receipt or issue) has been created from internal issue documents in which you've issued consignment goods tracked by serial numbers.

The report of a collective invoice or an overview of serial numbers will not produce information about the issued serial numbers of items from the collective invoice, because these serial numbers were issued through an issue document for the customer.

But you can report a list of issued serial numbers! To do this, use the SQLExp function. Serial numbers will be directly reported using the link table, where the link between the internal document and the collective invoice is entered with type V.

1. Create a new copy from the report of an already existing report for an invoice. Add a new text object and write into it the SQL sentence:

[SQLExp('select S.acSerialNo + '' ('' + LTrim(Str(Sum(S.anQty), 10, 2)) + '')'' as SN from tHE_MoveItemSerialNo S, tHE_LinkMoveItemMoveItem V where V.acLnkKey = S.acKey and V.anLnkNo = S.anNo and V.acType = ''V'' and V.acKey = ''' + <tbIzpisPrometPoz."acKey"> + ''' and V.anNo = ' + VarToStr(<tbIzpisPrometPoz."anNo">) + ' group by S.acSerialNo')]

A difference from the previous example is only that link type which is in this case V instead of 4.

 

  

     


Rate this topic
Was this topic usefull?
Comments
Comment will also bo visible in forum!