Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Grand Total come out different than the sum

الإجابة Grand Total come out different than the sum

  • Sunday, March 03, 2013 4:33 AM
     
     
    Sign In to Vote
    0

    Hi All,

    I browsed a cube and the grand total is different from the sum of the all the rows displayed.  Because I created this project from my mock-up database, I know the raw data well.  The figures are all positive, but the total is negative.  Why is that and how do I fix it?  

    Also, is there a way to modify the Grand Total row such as changing the header or remove the total?

    BI Analyst

     

All Replies

  • Sunday, March 03, 2013 5:21 AM
     
     
    Sign In to Vote
    1

    Hi,

    Grand Total will be always equal to Sum of all the rows total ..Please recheck your star schema cube design and especially the keys. Check your each dimension and fact total comparison one by one .. you will surely find the solution on why ur grand total is appearing to be different than the sum ..

    Hiding the Grand Total

    Use the SCOPE statement to set the measure value to NULL for the [All] member. If you want it universally hidden, use the Root function

    SCOPE(Root());
    This=NULL;
    END SCOPE;

    Display -ve Values as +ve

    you can achieve this by using format string property ..The first section defines the format for positive values and zeros, and the second section defines the format for negative values.

    More information here

    http://msdn.microsoft.com/en-us/library/ms146084.aspx

    http://msdn.microsoft.com/en-us/library/cc879322.aspx

    Setting Cell Properties = http://msdn.microsoft.com/en-us/library/ms145573.aspx

    Best Regards,
    Silna
    Please feel free to ask if you have any doubts and remember to mark the correct replies as answers.

     
  • Sunday, March 03, 2013 1:47 PM
     
     
    Sign In to Vote
    0

    Hi Silna,

    I am not quite sure if it is the cube design that causes the problem. I have two measure attributes from the fact table - revenue and expense.  The expense result came out fine, but the revenue is showing a negative grand total even though all the values in the column are positive.  I placed the "sales reps" attribute to the row area of the cube and compare the result against the query I did in the server, the result came out fine.  Each value per sales rep matched between the query and the cube.  It is just the revenue grand total that is off.  (Again, the total for the expense is fine.)  Any thought?

    BI Analyst

     
  • Monday, March 04, 2013 9:35 AM
     
     Answered
    Sign In to Vote
    1
    let me guess that the data type is Integer, and the grand total is over 2bn.
    • Marked As Answer by BIAnalyst Tuesday, March 05, 2013 12:35 AM
    •  
     
  • Monday, March 04, 2013 3:24 PM
     
     
    Sign In to Vote
    0

    Hi Krisztian,

    You are correct; the revenue total is 2.3 billions.  With your hint, I changed the data type to bigint, refreshed the data source view, deployed the project, and browsed again.  It is still showing some negative value.  I tried to filter it down to make the total below 2 billions and the total showed up correctly.  

    I checked the property in SSAS, the datatype showed up as System.Int64 instead of 32.  So I the data type did get updated.  Any more glue?

    BI Analyst

     
  • Monday, March 04, 2013 3:49 PM
     
     Answered
    Sign In to Vote
    1

    Hi BI Analyst,

    I had a different a problem, but in my case, changes that have been made to the DSV but (in my case - the attributes) haven't changed . So I had to change it by hand .

    Could it be the Measure itself hasn't been changed .

    I know DSV still has issues in SQL 2008 R2 .

    Please let me know if the problems are related :)

    Regards, David .

    • Marked As Answer by BIAnalyst Tuesday, March 05, 2013 12:35 AM
    •  
     
  • Monday, March 04, 2013 7:23 PM
     
     
    Sign In to Vote
    0

    Hi David,

    I checked the attribute property by clicking the attribute in the Tables pane.  The DataType shows System.Int64.  Where else can I check?  

    Update (after 5 hours):  I created a new project (hence, a new dsv) and the revenue shows up fine this time, but the old project is still having the same problem.  When you ask "Could it be the Measure itself hasn't been changed.", what do you mean?  How can I change it?


    BI Analyst


    • Edited by BIAnalyst Tuesday, March 05, 2013 12:28 AM
    •  
     
  • Tuesday, March 05, 2013 12:34 AM
     
     
    Sign In to Vote
    0

    I got it!  I removed the revenue attribute from the Measure Group pane and added it back.  Now the revenue came up without the problem!  Thank you all for your help!

    BI Analyst

     
  • Wednesday, March 06, 2013 9:29 AM
     
     
    Sign In to Vote
    1

    Hi BIAnalyst ,

    Happy to hear you have found the answer :)

    One option like you wrote, is to remove and add it back or you can find in the BIDS all places the measure appears or being data casted  (structure,partitions ..) .

    Regards, David .

     
  • Wednesday, March 06, 2013 12:15 PM
     
     
    Sign In to Vote
    0

    Thanks again, David.  Learning something new everyday.  In conclusion, refreshing is not enough, we need to remove and add it back in order for the change to take place.  It does not need to be this way; because it does not make sense.  I am wondering if 2012 does that too.

    BI Analyst