Condition in SHAPE for Data Grid Report


I have 3 Tables;

[B][U]1. Exporter Returns[/U][/B]

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[/U][/B]

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

My Report should come like as follow;

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

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[/CODE]

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[/CODE]

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

[B]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?[/B]

Please Help Me.

Thanks in advance.

Thanks & Regards,


Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!