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,
Re: Condition in SHAPE for Data Grid Report
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
Re: Condition in SHAPE for Data Grid Report
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
Re: Condition in SHAPE for Data Grid Report
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)
Re: Condition in SHAPE for Data Grid Report
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)
1 Attachment(s)
Re: Condition in SHAPE for Data Grid Report
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,
Re: Condition in SHAPE for Data Grid Report
Try creating a new report once the query uses the right field names