Condition in SHAPE for Data Grid Report

gunapriyan · October 24th, 2013, 02:02 PM

Friends,

I have 3 Tables;

1. Exporter Returns

rt_id numeric(18, 0) Unchecked
exporter_id nchar(10) Checked
icd_name nvarchar(50) Checked
port_name nvarchar(50) Checked
country_name nvarchar(50) Checked
variety_name nvarchar(50) Checked
type_name nvarchar(50) Checked
quantity numeric(18, 2) Checked
fob numeric(18, 2) Checked
unitprice numeric(18, 2) Checked
returns_month datetime Checked
submited_date datetime Checked
uname nvarchar(50) Checked

2. Port Master

port_id numeric(18, 0) Unchecked
port_name nvarchar(50) Unchecked
region nvarchar(50) Unchecked

My Report should come like as follow;

TITLE
Date: Page 1 of 1
Region : North
Port Name Quantity FOB UP
XYZ 100 100 1
KLM 200 200 1
ABC 650 650 1
FGH 722 72000 100
Region Total 1672 72950
Region : South
Port Name Quantity FOB UP
KLM 100 100 1
UVW 200 200 1
RST 650 650 1
IJK 722 72000 100
Region Total 1672 72950
Grand Total 3344 145900

Earlier i had used the following query.

Code:

select ROW_NUMBER()OVER (ORDER BY er.Port_Name) AS Row,er.Port_name,pm.Region,
sum(Quantity) Quantity,sum(Fob) Fob,convert(numeric(18,2),sum(Fob)/sum(Quantity)) Unitprice 
from Exporter_Returns er join Exporter_Master em on er.exporter_id=em.exporter_id join 
port_master pm on pm.port_name=er.port_name where returns_month 
between '2010-01-01' and '2013-08-01' and quantity!=0 group by er.Port_Name,pm.Region

I have plan to use SHAPE...... I have confusion in the following code;

Code:

SQL = "SHAPE {SELECT Region FROM Port_Master} APPEND ({SELECT * FROM Exporter_Returns} AS Level2 RELATE Port_Name to Port_Name)"

rs10.Open SQL, cn, adOpenStatic, adLockOptimistic
Set Rpt_RegPorwise.DataSource = rs10
With Rpt_RegPorwise.Sections("Section6").Controls
    .Item("Text6").DataMember = ""
    .Item("Text6").DataField = "Region"
End With
With Rpt_RegPorwise.Sections("Section1").Controls
    .Item("Text2").DataMember = "Level2"
    .Item("Text2").DataField = "Port_Name"
    .Item("Text3").DataMember = "Level2"
    .Item("Text3").DataField = "Quantity"
    .Item("Text4").DataMember = "Level2"
    .Item("Text4").DataField = "Fob"
    .Item("Text5").DataMember = "Level2"
    .Item("Text5").DataField = "Unitprice"
End With

And I need to insert condition ie "returns_date 01/01/2010 to 01/08/2013".

1. When i execute the above SHAPE, I had received error message Data Field "Quantity" is not found
2. If solve by you, How can i join 3 Tables in the above SHAPE....
3. How can i add the above condition in SHAPE?

Please Help Me.

Thanks in advance.

Thanks & Regards,

Aakash