PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197
[RESOLVED] Join Datatables for local Reporting-VBForums
Results 1 to 8 of 8

Thread: [RESOLVED] Join Datatables for local Reporting

  1. #1

    Thread Starter
    Hyperactive Member Zeljko's Avatar
    Join Date
    Oct 2006
    Location
    Internet
    Posts
    441

    Resolved [RESOLVED] Join Datatables for local Reporting

    I don't know how to join multiple datatables created in Designer in 1 single Datatable.
    I need 1 Datatable with all data in it so I can give that single Datatable to one single Tablix in ReportViewer (rdlc).
    Problem: ReportViewer Tablix can accept only one datatable at time and I need them all at once in one tablix.

    How did I get stuck here:
    * I have added Dataset from Solution Explorer to my Project (ds1)
    * Double click on that item gave me Dataset Designer (ds1.xsd)
    * Here I have created 4 Datatables with all needed columns, mutually connected with parent-child relationships (one to many, nested).
    dt1 is parent to dt2, dt2 is parent to dt3, dt3 is parent to dt4 (dt1 > dt2 > dt3 > dt4).
    * On my forms I have binded all my controls to this 4 Datatables and their respective columns so the data is populated hierarhicaly to dataset (as planed).
    * I have added ReportViewer Control to one form for reporting purposes and Design a new report (rdlc)...

    As I have read in past few days (read: week or two) I need to Join all my datatables to one single (flated) datatable somehow and then feed that DataTable to my Report.
    So basicaly I have 2 questions:
    1. How to join my 4 datatables? Can I do it from Designer or I must do it in code?
    2. If I cant do joining from Designer how will I see this new datatable with all columns from my Report? If I wanna add a Dataset/DataTable to my Report I go to Tab Report Data - New - Dataset - comboToSelectExistingDatasets and if Datatable/Dataset is not created in Designer I can not see it here to add it to work with it.

    I will be really really really thankful for any help I can get

    Zeljko
    vs2010, .net 3.5
    1. If this post helped you, please Rate it = That's You, saying Thanks, to Me ...Left side of this post: [Rate this post]
    2. Mark this Thread Resolved if your question has been answered That's You, saying Thanks, to Group ...Menu on top of your original Post: [Thread Tools]>[Mark Thread Resolved]
    3.
    Check my site: www.er-ef.net Check my snippets: Get installed .NET versions Regex extracting Join hierarchically nested Datatables in one flattened Datatable


  2. #2
    eXtreme Programmer .paul.'s Avatar
    Join Date
    May 2007
    Location
    Chelmsford UK
    Posts
    23,121

    Re: Join Datatables for local Reporting

    you can query your datatables with LINQ (in code) + create a new joined table
    exactly how depends on the exact structure + contents of your datatables

  3. #3

    Thread Starter
    Hyperactive Member Zeljko's Avatar
    Join Date
    Oct 2006
    Location
    Internet
    Posts
    441

    Re: Join Datatables for local Reporting

    My Datatables are looking like this:
    Code:
    ds1
    ---dt1
    ---aAutoID (PK)
    ---aUser
    ------dt2
    ------bAutoID (PK)
    ------baID (relation to dt1)
    ------bAdresses
    ---------dt3
    ---------cAutoID (PK)
    ---------cbID (relation to dt2)
    ---------cSubUsers
    ------------dt4
    ------------dAutoID (PK)
    ------------dcID (relation to dt3)
    ------------dSubSubUsers
    
    dt1 > dt2 : one to many : aAutoID (PK) > baID
    dt2 > dt3 : one to many : bAutoID (PK) > cbID
    dt3 > dt4 : one to many : cAutoID (PK) > dcID
    If I eventually find a way to join above tables in one flat datatable - programmatically,
    how will I add that new Datatable to my Report if Report can not see it (it can only see what is in Designer-Solution explorer)?
    Last edited by Zeljko; Aug 16th, 2011 at 04:42 AM.
    1. If this post helped you, please Rate it = That's You, saying Thanks, to Me ...Left side of this post: [Rate this post]
    2. Mark this Thread Resolved if your question has been answered That's You, saying Thanks, to Group ...Menu on top of your original Post: [Thread Tools]>[Mark Thread Resolved]
    3.
    Check my site: www.er-ef.net Check my snippets: Get installed .NET versions Regex extracting Join hierarchically nested Datatables in one flattened Datatable


  4. #4
    eXtreme Programmer .paul.'s Avatar
    Join Date
    May 2007
    Location
    Chelmsford UK
    Posts
    23,121

    Re: Join Datatables for local Reporting

    you need to create an empty datatable with the same fields as your dynamic datatable + set that as your datasource, then swap datasources at runtime

  5. #5

    Thread Starter
    Hyperactive Member Zeljko's Avatar
    Join Date
    Oct 2006
    Location
    Internet
    Posts
    441

    Re: Join Datatables for local Reporting

    I was afraid of doing so much duplicate job but the more I think about it it seems like an easy solution to my problem.
    So, I will create another Dataset with single Datatable and add (through designer) all columns from above 4 datatables to this datatable. Fortunately each column name in all 4datatables is unique to whole dataset so I can have exactly the same column names in the new single datatable

    So, now I need idea on how to populate this new datatable with data from original 4 datatables?
    How to loop through all datatables and not mess up the records?
    1. If this post helped you, please Rate it = That's You, saying Thanks, to Me ...Left side of this post: [Rate this post]
    2. Mark this Thread Resolved if your question has been answered That's You, saying Thanks, to Group ...Menu on top of your original Post: [Thread Tools]>[Mark Thread Resolved]
    3.
    Check my site: www.er-ef.net Check my snippets: Get installed .NET versions Regex extracting Join hierarchically nested Datatables in one flattened Datatable


  6. #6

    Thread Starter
    Hyperactive Member Zeljko's Avatar
    Join Date
    Oct 2006
    Location
    Internet
    Posts
    441

    Resolved Re: Join Datatables for local Reporting

    So I have made a first Mock-Up of my effort to
    IT WORKS PERFECTLY! Join 4 hierarchically nested datatables in 1 single flattened datatable..

    Code is posted here 'as is' so that others my find it useful or at least as a starting point...
    If you have any ideas to refine this code (or part of it) I'm all ears
    VB.Net Code:
    1. 'I have 1 dataset and in that dataset 4 datatables with all needed columns created through designer
    2. 'every datatable name in original dataset has unique name (unique to all dataset and not just datatable)
    3. 'in every datatable columns are:
    4. '   first column is Int32 - autogeneratedID - unique to table
    5. '   second column is Int32 - ID - the same key as parent key (this field is binding conection to parent table: parent:child > one:many > autogeneratedID:ID
    6. '   other columns as you like
    7. 'Expected result is 1 new dataset with 1 new flattened datatable created totally through code which conteins all above 4 datatables. Result is showned in new form in a datagridview manner...
    8.  
    9. Private Sub FlattenAndShowDatatable_Click(sender As System.Object, e As System.EventArgs) Handles ToolStripBtnPrint.Click
    10. 'original datatables        
    11.         Dim dsOrg As New DataSet
    12.         Dim dt1 As DataTable = ds1.dt1o.Copy
    13.         Dim dt2 As DataTable = ds1.dt2o.Copy
    14.         Dim dt3 As DataTable = ds1.dt3o.Copy
    15.         Dim dt4 As DataTable = ds1.dt4o.Copy
    16.         dsOrg.Tables.AddRange(New DataTable() {dt1, dt2, dt3, dt4})
    17. 'preparing for new dataset/datatable
    18.         Dim dsJoined As New DataSet
    19.         Dim dtJ As New DataTable
    20.         dsJoined.Tables.Add(dtJ)
    21.  
    22. 'last table in original dataset (when I reach the end row in this table it moves to one higher parent table (curentIndexTable-1)
    23.         Dim startTableIndx As Integer = dsOrg.Tables.Count - 1
    24.  
    25.         Do
    26.             Dim dr As DataRow = dtJ.NewRow
    27.             Dim keyparent As String = Nothing
    28.  
    29.             For iTbl As Integer = startTableIndx To 0 Step -1
    30.                 For iRow As Integer = 0 To dsOrg.Tables(iTbl).Rows.Count - 1
    31.  
    32.                     Dim alreadyExist As Boolean = False
    33.                     For i As Integer = 0 To dtJ.Rows.Count - 1
    34.                         If iTbl = startTableIndx AndAlso dtJ.Rows(i).Item(dsOrg.Tables(iTbl).Columns(0).ToString).ToString = dsOrg.Tables(iTbl).Rows(iRow).Item(0).ToString Then
    35.                             alreadyExist = True
    36.                             Exit For
    37.                         End If
    38.                     Next
    39.  
    40.                     If alreadyExist = False Then
    41.                         If dsOrg.Tables(iTbl).Rows(iRow).Item(0).ToString = keyparent Or keyparent = Nothing Then 'if its not correct parent, skip it
    42.                             For iCol As Integer = 0 To dsOrg.Tables(iTbl).Columns.Count - 1
    43. 'add column if column name not existing in table
    44.                                 If Not dsJoined.Tables(0).Columns.Contains(dsOrg.Tables(iTbl).Columns(iCol).ToString) Then
    45.                                     dsJoined.Tables(0).Columns.Add(dsOrg.Tables(iTbl).Columns(iCol).ToString)
    46.                                 End If
    47. 'HERE WE ACTUALLY POPULATING THE CELL IN NEW DATATABLE
    48.                                 dr(dsOrg.Tables(iTbl).Columns(iCol).ToString) = dsOrg.Tables(iTbl).Rows(iRow).Item(iCol)   'dr("ColumnName") = "aaa" ...add data to this column under that row
    49.                                 If iCol = 1 Then   'second Column contains ID of parent table
    50.                                     keyparent = dsOrg.Tables(iTbl).Rows(iRow).Item(iCol).ToString
    51.                                 End If
    52.                             Next iCol
    53.                             Exit For
    54.  
    55.                         End If
    56.                     End If
    57.  
    58.                     If iRow = dsOrg.Tables(startTableIndx).Rows.Count - 1 Then
    59.                         startTableIndx -= 1
    60.                     End If
    61.  
    62.                 Next iRow
    63.             Next iTbl
    64.  
    65. 'HERE WE ARE ACTUALLY ADDING A ROW POPULATED WITH CELLS TO NEW DATATABLE
    66.             dtJ.Rows.Add(dr)
    67.         Loop Until startTableIndx = -1
    68.  
    69. 'showing the results in new form with datagridview
    70.         Dim myForm As New Form
    71.         Dim dgv As New DataGridView
    72.         With dgv
    73.             .Name = "dgv"
    74.             .Location = New Point(15, 15)
    75.             .Dock = DockStyle.Fill
    76.             .AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill
    77.         End With
    78.         myForm.Controls.Add(dgv)
    79.         dgv.DataSource = dtJ
    80.         'dgv.DataMember = "dtJ"
    81.         myForm.WindowState = FormWindowState.Maximized
    82.         myForm.Show()
    83.     End Sub
    Last edited by Zeljko; Aug 21st, 2011 at 01:46 AM.
    1. If this post helped you, please Rate it = That's You, saying Thanks, to Me ...Left side of this post: [Rate this post]
    2. Mark this Thread Resolved if your question has been answered That's You, saying Thanks, to Group ...Menu on top of your original Post: [Thread Tools]>[Mark Thread Resolved]
    3.
    Check my site: www.er-ef.net Check my snippets: Get installed .NET versions Regex extracting Join hierarchically nested Datatables in one flattened Datatable


  7. #7
    eXtreme Programmer .paul.'s Avatar
    Join Date
    May 2007
    Location
    Chelmsford UK
    Posts
    23,121

    Re: [RESOLVED] Join Datatables for local Reporting

    did that work ok with your report?

  8. #8

    Thread Starter
    Hyperactive Member Zeljko's Avatar
    Join Date
    Oct 2006
    Location
    Internet
    Posts
    441

    Re: [RESOLVED] Join Datatables for local Reporting

    Not there yet.
    I'm still finishing some other stuff, and joining tables in to one flatten table was big break point form me.

    Joining works. And yes I can 'import' it in report. Importing part (lets call it filtering and sorting) is not yet done but I will post results as I go there. I think I will now how to deal with lot of duplicates, but still, better that then nothing

    .paul., thanks for idea
    1. If this post helped you, please Rate it = That's You, saying Thanks, to Me ...Left side of this post: [Rate this post]
    2. Mark this Thread Resolved if your question has been answered That's You, saying Thanks, to Group ...Menu on top of your original Post: [Thread Tools]>[Mark Thread Resolved]
    3.
    Check my site: www.er-ef.net Check my snippets: Get installed .NET versions Regex extracting Join hierarchically nested Datatables in one flattened Datatable


Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width