Howdy, Stranger!

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


Welcome to the new platform of Programmer's Heaven! We apologize for the inconvenience caused, if you visited us from a broken link of the previous version. The main reason to move to a new platform is to provide more effective and collaborative experience to you all. Please feel free to experience the new platform and use its exciting features. Contact us for any issue that you need to get clarified. We are more than happy to help you.

Condition in SHAPE for Data Grid Report

gunapriyangunapriyan Posts: 5Member
edited October 2013 in Advance Visual Basic


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

[U]TITLE 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.