none
What do you do? RRS feed

  • Question

  • I have a table with Year, Month, TranType, TranValue and Region columns. I write a lot of reports that use GROUP BY to get aggregate values. I often find that if I have tables in a report side by side the groupings don't line up. An example of the cause is a Region has no rows for a given TranType in a given Year and Month while another Region does. So if I have a rowgroup in my report that groups by TranType it means the rows in the two tables do not align.

    I am working in a datawarehouse environment not OLTP. I can write some SQL to ensure there is always a row for all combinations and give it a TranValue of zero and this will ensure I always have aligned  rows in my groupings.

    What other ways do you address this type of problem?
    Wednesday, August 12, 2009 3:05 PM

Answers

  • You're correct, this problem has to be solved in the query.  RS does not have joins in the report processing runtime so there is no way to get rows to show up in a dataset that were returned only in a different dataset.
    On the report design side, if you always want the tables to line up, it may be simpler to merge them into a single table/tablix (create a "spacer" column with no background/borders if needed to create some visual separation between the two "tables").  You could do FULL OUTER JOINs of your current queries into a single query so you only get combinations that exist in one or more of the resultsets.

    Wednesday, August 12, 2009 11:36 PM
    Moderator