Simple SQL report for Customer Name and RDR1.ItemCode

[former_member754655]

Hi

I am looking to create a SQL report which looks at CustomerCode/Name From SAP b1 sales order form... I am pulling the ordr table and the rdr1 table through an inner join.

My first parameter is $[$4.0.0] which successfully pulls Card code from the sales order from ordr

My next parameter is $[$38.0.0] and I want to pull item lines...

My goal is to check the current sales order to display the items on the sales order against the customer, to show what recent transactions have been made on these products, including quantity and price

Everything is working except the second parameter.

Thanks for any help

[chris_keating]

This is not a SQL Anywhere issue. You will want to correctly tag this question so that the correct resources are engaged.

[kothandaraman_nagarajan · Accepted solution]

Hi,

To pull the item, the parameter should be $[$38.1.0].

Here the complete query to pull previous sales order,

select T0.Docnum,T0.Docdate,T0.CardCode,T0.CardName, T1.Itemcode from ORDR T0 inner join RDR1 T1 on T0.Docentry = T1.Docentry Where T0.CardCode = $[ORDR.Cardcode] and T1.Itemcode = $[$38.1.0]

Regards,

Nagarajan

[Johan_H]

Hi Andrew,

From the syntax you mentioned I understand that you are talking about a Formatted Search.

A Formatted Search (a.k.a. FMS) can be put in almost any field, on the header or on the row level. When you use a query in the FMS, parameters like $[$4.0.0] will refer to fields on the header or row level.

When the FMS is put in a field on the header level, you can only use parameters that refer to fields on the header level. When the FMS is put in a field on the row level, you can use parameters that refer to fields on the header level and/or row level.

Regards,

Johan