Condition in SHAPE for Data Grid Report

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


I have plan to use SHAPE...... I have confusion in the following 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,

Please reformat your code to add the tags [] use CODE then /code [/]

Here's a sample that used a MSHFlexgrid:

Code:

---

Private Sub Command1_Click()
    Dim cnLvConnection As ADODB.Connection
    Set cnLvConnection = New ADODB.Connection
    Dim rsLvRecordset As ADODB.Recordset
    Set rsLvRecordset = New ADODB.Recordset
    With cnLvConnection
        .Provider = "MSDataShape.1"
        .ConnectionString = "Data Source=" & App.Path & "\db1.mdb;" _
                          & "Data Provider=Microsoft.Jet.OLEDB.4.0;"
        .Open
        With rsLvRecordset
            .CursorLocation = adUseClient
            .CursorType = adOpenStatic
            .LockType = adLockReadOnly
        End With
        Set rsLvRecordset = .Execute("SHAPE {SELECT c.CustomerName As Customer, c.CustomerID FROM Customers c ORDER BY c.CustomerName} As Customers" _
                                  & " APPEND ((SHAPE {SELECT oh.OrderNumber As [Order No], oh.CustomerID, oh.OrderHeaderID FROM OrderHeaders oh ORDER BY oh.OrderNumber} As OrderHeaders" _
                                  & "          APPEND ({SELECT od.OrderLine As [Line], od.OrderLineDescription As [Description], od.OrderLineQuantity As Quantity, od.OrderHeaderID FROM OrderDetails od ORDER BY od.OrderLine} As OrderDetails" _
                                  & "          RELATE OrderHeaderID TO OrderHeaderID))" _
                                  & " RELATE CustomerID TO CustomerID)")
    End With
    '  Setup Grid
    Set Me.MSHFlexGrid1.Recordset = rsLvRecordset
    Me.MSHFlexGrid1.ColWidth(1, 0) = 0 ' c.CustomerID
    Me.MSHFlexGrid1.ColWidth(1, 1) = 0 ' oh.CustomerID
    Me.MSHFlexGrid1.ColWidth(2, 1) = 0 ' oh.OrderHeaderID
    Me.MSHFlexGrid1.ColWidth(3, 2) = 0 ' od.OrderHeaderID
    '  Tidy up
    If Not rsLvRecordset Is Nothing Then
        If rsLvRecordset.State <> adStateClosed Then
            rsLvRecordset.Close
        End If
        Set rsLvRecordset = Nothing
    End If
    If Not cnLvConnection Is Nothing Then
        If cnLvConnection.State <> adStateClosed Then
            cnLvConnection.Close
        End If
        Set cnLvConnection = Nothing
    End If
End Sub

Private Sub Form_Load()
    Me.MSHFlexGrid1.FixedCols = 0
End Sub

---

Thanks for your reply. Now i am facing another one problem. i.e I want to 3 level grouping. To achieve that, I had write the following code;

Code:

---

SHAPE {select distinct(Country_name) from Country_Master}
APPEND ((SHAPE {select distinct(Type_Name) from Type_master } AS Level1
APPEND ({ select er.type_name,er.variety_name,sum(Quantity) Quantity,sum(Fob) Fob,convert(numeric(18,2),sum(Fob)/sum(Quantity)) Unitprice from Exporter_Returns er join type_master ttm on ttm.type_name=er.type_name join variety_master tvm on tvm.variety_name=er.variety_name where returns_month between '2010-01-01' and '2013-08-01' and quantity!=0 group by er.type_name,er.variety_name})
RELATE Type_Name to Type_Name AS Level2))
RELATE Country_Name to Country_Name AS Level3)

---

1. Table Country Master have Country_Name and Type_Master have Type Name.

Country [1st Level]
|
Type [2nd Level]
|
Variety [3rd Level]

I had received Error Message that Country_Name is not in Rowset.

Please help me.

Thanks & Regards,

Aakash

Quote:

---

Originally Posted by dglienna

Please reformat your code to add the tags [] use CODE then /code [/]

Here's a sample that used a MSHFlexgrid:

Code:

---

Private Sub Command1_Click()
    Dim cnLvConnection As ADODB.Connection
    Set cnLvConnection = New ADODB.Connection
    Dim rsLvRecordset As ADODB.Recordset
    Set rsLvRecordset = New ADODB.Recordset
    With cnLvConnection
        .Provider = "MSDataShape.1"
        .ConnectionString = "Data Source=" & App.Path & "\db1.mdb;" _
                          & "Data Provider=Microsoft.Jet.OLEDB.4.0;"
        .Open
        With rsLvRecordset
            .CursorLocation = adUseClient
            .CursorType = adOpenStatic
            .LockType = adLockReadOnly
        End With
        Set rsLvRecordset = .Execute("SHAPE {SELECT c.CustomerName As Customer, c.CustomerID FROM Customers c ORDER BY c.CustomerName} As Customers" _
                                  & " APPEND ((SHAPE {SELECT oh.OrderNumber As [Order No], oh.CustomerID, oh.OrderHeaderID FROM OrderHeaders oh ORDER BY oh.OrderNumber} As OrderHeaders" _
                                  & "          APPEND ({SELECT od.OrderLine As [Line], od.OrderLineDescription As [Description], od.OrderLineQuantity As Quantity, od.OrderHeaderID FROM OrderDetails od ORDER BY od.OrderLine} As OrderDetails" _
                                  & "          RELATE OrderHeaderID TO OrderHeaderID))" _
                                  & " RELATE CustomerID TO CustomerID)")
    End With
    '  Setup Grid
    Set Me.MSHFlexGrid1.Recordset = rsLvRecordset
    Me.MSHFlexGrid1.ColWidth(1, 0) = 0 ' c.CustomerID
    Me.MSHFlexGrid1.ColWidth(1, 1) = 0 ' oh.CustomerID
    Me.MSHFlexGrid1.ColWidth(2, 1) = 0 ' oh.OrderHeaderID
    Me.MSHFlexGrid1.ColWidth(3, 2) = 0 ' od.OrderHeaderID
    '  Tidy up
    If Not rsLvRecordset Is Nothing Then
        If rsLvRecordset.State <> adStateClosed Then
            rsLvRecordset.Close
        End If
        Set rsLvRecordset = Nothing
    End If
    If Not cnLvConnection Is Nothing Then
        If cnLvConnection.State <> adStateClosed Then
            cnLvConnection.Close
        End If
        Set cnLvConnection = Nothing
    End If
End Sub

Private Sub Form_Load()
    Me.MSHFlexGrid1.FixedCols = 0
End Sub

---

---

Code:

---


  SHAPE  {SELECT * from customers}
  APPEND ((SHAPE  {select * from orders}
            APPEND ({select * from [order details]} AS rsDetails
                  RELATE orderid TO orderid)) AS rsOrders
          RELATE customerid TO customerid)

---

which yields:

Code:

---

  Customers.*
  rsOrders
        |
        +----Orders.*
            rsDetails
                |
                +----[Order Details].*

---

from http://support.microsoft.com/kb/308045/en-us (which you should read and understand)

Dear Sir,

Thanks for your reply. But i had tried many ways...Keep on i am facing error.

I request you to please correct in my code and send it to me please.

Thanks & Regards,

Quote:

---

Originally Posted by dglienna

Code:

---


  SHAPE  {SELECT * from customers}
  APPEND ((SHAPE  {select * from orders}
            APPEND ({select * from [order details]} AS rsDetails
                  RELATE orderid TO orderid)) AS rsOrders
          RELATE customerid TO customerid)

---

which yields:

Code:

---

  Customers.*
  rsOrders
        |
        +----Orders.*
            rsDetails
                |
                +----[Order Details].*

---

from http://support.microsoft.com/kb/308045/en-us (which you should read and understand)

---

Dear Friends,

Finally i had avoid error in Shape Command;

SHAPE {select distinct(Country_name) from Country_Master} APPEND ((SHAPE {select cm.country_name,er.type_name,er.variety_name,sum(Quantity) Quantity,sum(Fob) Fob,convert(numeric(18,2),sum(Fob)/sum(Quantity)) Unitprice from Exporter_Returns er join teatype_master ttm on ttm.type_name=er.type_name join teavariety_master tvm on tvm.variety_name=er.variety_name join country_master cm on cm.country_name=er.country_name where returns_month between '" & stretmon & "' and '" & stretmon1 & "' and quantity!=0 group by cm.country_name,er.type_name,er.variety_name } AS Ret APPEND ({select distinct(Type_Name) from TeaType_master} As Typ RELATE Type_Name to Type_Name)) RELATE Country_Name to Country_Name)

But now i am facing new error. Please find my design in attachment.

Attachment 31961

At the end of scene, It showing "Control "Text1" cannot be place in this place.

I am very tired. Please help me.

Thanks in advance.

Thanks & Regards,

Aakash

Quote:

---

Originally Posted by gunapriyan

Dear Sir,

Thanks for your reply. But i had tried many ways...Keep on i am facing error.

I request you to please correct in my code and send it to me please.

Thanks & Regards,

---

Try creating a new report once the query uses the right field names