putting rows of a second table inside the first table data based on criteria from the rows of first table. nonprofessional here. RRS feed

  • Question

  • Trying reporting for a .net 2 c# win application.
    also a nonprofessional report writer here. help appreciated.
    i will use sample tables as a question.
    we have data of two tables.
    sample of first table:
        1    X    X    X
        2    X    X    X
        3    X    X    X
        4    X    X    X
        5    X    X    X
    sample data of second table
        3.5    X    X
        1.5    X    X
        5.5    X    X
    assume these as a date columns for rows, rows have other columns as well.
    i want to display the report like the following:
        1    X    X    X

        1.5    X    X

        2    X    X    X
        3    X    X    X

        3.5    X    X

        4    X    X    X
        5    X    X    X

        5.5    X    X

    (it's about sold items, and cheques in original tables)
    i have been using views and runtime data generation and have not used features of reporting services. but i can't do it for this problem.

    even thought a complete answer is more liked, if you just know the solution and don't want to type much, give me the name and i will find it out.
    Monday, May 25, 2009 10:11 PM

All replies

  • Hi Paiman,
    If you have two tables with different data, why not create a query with join like below

    Table 1 :
    col1   col2   col3  col4
    1        x       x      x
    2        x       x      x
    3        x       x      x
    4        x       x      x
    5        x       x      x

    Table 2 :
    Col1  col2 col3
    3.5    x      x
    1.5    x      x
    5.5    x      x

    I did something like this and I used union all between table 1 and table 2 :

    Select col1,...,shippeddate as ColDateTime
    from tb1
    union all
    select Col1,...,invoiceddate as ColDateTime
    from tb2
    order by coldatetime

    In this way shippeddate and invoicedate both are stored as one column and can be sorted.
    • Marked as answer by paiman Tuesday, May 26, 2009 5:54 PM
    • Unmarked as answer by paiman Tuesday, May 26, 2009 11:03 PM
    Tuesday, May 26, 2009 1:39 PM
  • yep, thanks.
    Tuesday, May 26, 2009 5:54 PM
  • I still have problems.
    i need the whole row be displayed in the report, not just dates. so even thought i can sort it by the date column, how is it supposed to show the whole row of each table?

    assume table 1:
    1     column_name_A         column_name_B       column_name_C       column_name_D
    2     column_name_A         column_name_B       column_name_C       column_name_D
    3     column_name_A         column_name_B       column_name_C       column_name_D
    4     column_name_A         column_name_B       column_name_C       column_name_D
    5     column_name_A         column_name_B       column_name_C       column_name_D

    table 2:

    3.5       column_name_F        column_name_G       column_name_H
    1.5       column_name_F        column_name_G       column_name_H
    5.5       column_name_F        column_name_G       column_name_H

    In the report, i use a table with fixed columns to show only the values of the first table.
    now to use data of second table in between rows of first table, i need to use complicated IIF statements to select alternate column and mess the report with complex coding;
    put the following pseudo-code [if row is type table_1, use column_name_A, otherwise column_name_F] in all the columns
    or is there any good solution?
    Tuesday, May 26, 2009 11:16 PM
  • Paiman,

    My apologies. I thought the number of columns in table 1 and table 2 are same. If the number of columns in two tables are different, you cannot use union_all. Also, in Reporting services, you can bind a dataset to a table. I don't know how you can bind rows to different sql tables or datasets. If I understood incorrectly, please let me know. If you have the dataset already configured which gives you all the columns you need, you have to declare a group and have sort it by expression (table1.sort column + table2.sortcolumn). Then report engine will sort the data correctly.

    Wednesday, May 27, 2009 1:43 AM
  • Also, in Reporting services, you can bind a dataset to a table. I don't know how you can bind rows to different sql tables or datasets.
    yes that's true.
    well, i can still do it in another way.
    create a view with all the column types (column_type_A to column_type_H)
    then sort tables in runtime and fill only the columns relating to current row.
    creating a following dataset is solved:
    (can be done runtime)
    (first row is titles, the rest is bank if null, X if there is some value)

               column_name_A     column_name_B     column_name_C       column_name_D       column_name_F        column_name_G       column_name_H
    1         X                                     X                                 X                           X                          
    1.5                                                                                                                                          X                              X                             X
    2         X                                     X                                 X                           X
    3         X                                     X                                 X                           X
    3.5                                                                                                                                          X                              X                             X
    4         X                                     X                                 X                           X
    5         X                                     X                                 X                           X
    5.5                                                                                                                                          X                              X                             X

    now i can use IIF statements. but it doesn't seem like a good solution (if it works!).
    I will try to implement this to see if this works.
    thanks for your reply.

    Wednesday, May 27, 2009 10:17 AM