none
Report Builder 2.0 bug? RRS feed

  • Question

  • First of all, I'm using the pre-release version of SQL Server 2008, and up until recently the pre-release version of RB2.0, although I downloaded what I believe is the official release of this via a recent post on this forum.

     

    In any event, I get different totals based, apparently, on the order in which I select attributes and/or whether I drag-and-drop the attributes or double-click to add them to the dataset and matrices.  In short, there's not much rhyme or reason as to how the figures sub-total and total up.  It appears that when RB2.0 goes haywire it is summing up cartesian products.  But again, the same report against the same model can yield the correct totals if the attributes are pulled in a certain order.

     

    Could this be a SQL Server 2008 pre-release issue?  Has anyone else seen it?  Thanks.

     

    Note that I appear to always get correct totaling if I manually add the child groups.

    Thursday, November 13, 2008 11:19 PM

Answers

  • The key to understanding Report Model queries is the entity displayed at the top of your navigation tree.  For your incorrect result scenarios, I'm guessing that one of the dimension tables is the navigation root rather than the fact table.  Are all of your totals coming from the fact entity or do you have any totals/aggregates from other entities?

     

    We didn't make any changes in this area between RC0/1 and release so this is almost certainly not a prerelease issue.

    Wednesday, November 19, 2008 2:00 AM
    Moderator

All replies

  • This appears to be the well established pattern when developing a model against a star schema design:

     

    Select from two dimension tables (parent tables) and then from the fact table (child table) -- wrong results, the totals overcount.

     

    Select from one dimension table, then the fact table, and then any number of dimension tables -- correct results.

    Friday, November 14, 2008 12:20 AM
  • Bob,

     

    Can you clarify what type of data source you are using?

    1. Analysis Services

    2. Report model over relational data

    3. Report model over Analysis Services

     

    Also, when you drag the items on, are you talking about in the query designer, the table/matrix wizard, or on the main layout editor (e.g. in the Grouping pane)?

     

    Thanks in advance,

    Monday, November 17, 2008 5:55 PM
    Moderator
  • #2, report model over relational data.

     

    And I've only confirmed this pattern: 

    1) select attributes from dimension then fact then any number of other dimensions:  correct results

    2) select attributes from dimension, and then another dimension, then the fact table:  incorrect resuults, overcounting

     

    Again, I de-installled the pre-release RB20 and installed the official release.  I'm still using the pre-release SS2008.

     

    Thanks.

    Monday, November 17, 2008 6:42 PM
  • The key to understanding Report Model queries is the entity displayed at the top of your navigation tree.  For your incorrect result scenarios, I'm guessing that one of the dimension tables is the navigation root rather than the fact table.  Are all of your totals coming from the fact entity or do you have any totals/aggregates from other entities?

     

    We didn't make any changes in this area between RC0/1 and release so this is almost certainly not a prerelease issue.

    Wednesday, November 19, 2008 2:00 AM
    Moderator
  • This is maddening, frankly.  If I'm using a Report Model as the basis for any dataset in a Report Builder 2.0 report, and I need to add a field, whether I double-click or drag it always creates a new group, which does really weird things to the resulting data.  I find that the only way I can do this is to completely delete all the fields then add them back again, making sure to do them in the order specified in this thread (add fields from a dimension, add the measure fields, add fields from the other dimensions).  If I forget a field and go to an earlier dimension to get it the whole thing falls apart and I have to start all over. 
    There needs to be some way to control this or change it rather than just starting all over again.  Has anyone found anything useful?  Or is there some sort of fix to this coming at some point in the future?
    Wednesday, September 23, 2009 2:21 PM