CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 7 of 7

Hybrid View

  1. #1
    Join Date
    Aug 2010
    Posts
    18

    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,

  2. #2
    Join Date
    Jan 2006
    Location
    Fox Lake, IL
    Posts
    15,007

    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
    David

    CodeGuru Article: Bound Controls are Evil-VB6
    2013 Samples: MS CODE Samples

    CodeGuru Reviewer
    2006 Dell CSP
    2006, 2007 & 2008 MVP Visual Basic
    If your question has been answered satisfactorily, and it has been helpful, then, please, Rate this Post!

  3. #3
    Join Date
    Aug 2010
    Posts
    18

    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 View Post
    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

  4. #4
    Join Date
    Jan 2006
    Location
    Fox Lake, IL
    Posts
    15,007

    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)
    David

    CodeGuru Article: Bound Controls are Evil-VB6
    2013 Samples: MS CODE Samples

    CodeGuru Reviewer
    2006 Dell CSP
    2006, 2007 & 2008 MVP Visual Basic
    If your question has been answered satisfactorily, and it has been helpful, then, please, Rate this Post!

  5. #5
    Join Date
    Aug 2010
    Posts
    18

    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 View Post
    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)

  6. #6
    Join Date
    Aug 2010
    Posts
    18

    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.

    Name:  Report Design.jpg
Views: 815
Size:  93.4 KB

    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 View Post
    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,

  7. #7
    Join Date
    Jan 2006
    Location
    Fox Lake, IL
    Posts
    15,007

    Re: Condition in SHAPE for Data Grid Report

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

    CodeGuru Article: Bound Controls are Evil-VB6
    2013 Samples: MS CODE Samples

    CodeGuru Reviewer
    2006 Dell CSP
    2006, 2007 & 2008 MVP Visual Basic
    If your question has been answered satisfactorily, and it has been helpful, then, please, Rate this Post!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  





Click Here to Expand Forum to Full Width

Featured