Grand Total Problem in Excel

I found a problem when I use Excel 2003 with MSAS 2005. The problem occurs when I use the Hide Levels feature on a row dimension and select the grand total for columns option. I noticed that when I apply filters to the selection, the Grand Total does not reflect the filtered members. If I Show Details then the Grand Total is properly displayed. However, this problem does not happen on every diminsion.

I was able to duplicate the problem using Adventure Works DW. In Excel, put the Product Categories in the Row, Source Currency Code in the Column, Sales Amount as the Data, and Filter on Weight (2.12) and Date.Calendar Year (CY 2004). Ensure that Grand Total for Columns is checked. Show Details down to the Product level; notice that the Grand Total amount is correct. On the Sub Category column, select Hide Levels. Notice that the Grand total shows the incorrect amount. If I do a Hide Levels on the Category, the Grand Total is also correct. I was wondering if this would be considered an Excel bug, a MSAS bug, or if this is the way it is.

Having traced the MDX queries generated by the Excel pivot table, and rerun them in Management Studio, they return the correct totals, regardless of which levels are hidden in the query. So some further processing seems to occur in Excel - maybe someone else knows more about this phase?|||I was tracing a similar issue in the OWC component today for a client and I can confirm that it appears to be some post processing that Excel is doing. Interestingly I found that if you turn on the "Include Hidden items in totals" button (the one just to the right of the refresh button) that it appears to have the opposite effect with this particular query and causes it to display the correct totals - really strange.|||

Hi Darren,

That strangeness may help identify the culprit - it may be the "Visual Totals" connection mode which Excel employs, when the user elects not to include hidden items in the total. So it may be an AS 2005 issue after all - try the following Adventure Works queries, the 2nd one attempts to emulate the "Visual Totals" connection mode :

-- Query submitted by Excel, which works correctly without Visual Totals mode:

SELECT NON EMPTY HIERARCHIZE( Except(AddCalculatedMembers({DrillDownLevel(
{DrillDownLevel({DrillDownLevel({[Product].[Product Categories].[All Products]})},
[Product].[Product Categories].[Category])}, [Product].[Product Categories].[Subcategory])}) ,
AddCalculatedMembers( DrillDownLevel([Product].[Product Categories].[Category].members))) )
DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS
FROM [Adventure Works] WHERE ([Measures].[Sales Amount], [Product].[Weight].&[2.12])

-- Equivalent Visual Totals Query, which doesn't work correctly:

SELECT NON EMPTY VisualTotals(HIERARCHIZE( Except(AddCalculatedMembers({DrillDownLevel(
{DrillDownLevel({DrillDownLevel({[Product].[Product Categories].[All Products]})},
[Product].[Product Categories].[Category])}, [Product].[Product Categories].[Subcategory])}) ,
AddCalculatedMembers( DrillDownLevel([Product].[Product Categories].[Category].members))) ))
DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS
FROM [Adventure Works] WHERE ([Measures].[Sales Amount], [Product].[Weight].&[2.12])

|||

I forgot to mention that I also ran the MDX statements generated by Excel into the Management Studio.And as Deepak said, the Grand Totals are correct.

In my situation, the problem only occurs when one particular dimension is used with a certain filter.In SQL Server, I have a table called Project_d.Each record in this table also has the following fields: Grand Parent Project, Project Link, and Responsible Department.Every Project will roll up to a Grand Parent Project.So my Project dimension hierarchy is Grand Parent Project <- Project.A Project may or may not have a Project Link and/or a Responsible Department.The Project Link and Responsible Department are not defined as individual hierarchies, they just attributes under the Project level with the AttributeHierarchyVisible = True.

In Excel, I can filter on Project Link with other dimensions okay.I only seem to get the Grand Total problem when the Project is placed in the Row and the Project Link or Responsible Department is used as the filter and if I Hide Levels (hide the Grand Parent Projects).I tried putting the Project in the filter and the Project Link in the Row, but the Project Link only has one level so there is nothing to hide.

I cannot separate the Project Link and the Responsible Department from the Project SQL Server table because my fact table does not have a relationship to these values; the relationship is with the Project.I hope this extra information helps and thanks to everyone for your insight.

|||

This could be due to the fact that your attributes are related. Mosha has written a very detailed blog post on the interaction between the WHERE clause and related attributes in MDX which may explain your issue. see http://sqljunkies.com/WebLog/mosha/archive/2006/11/01/slicer_axis_interaction.aspx

If this is the issue that you are seeing, then it sounds like this might be fixed in SP2 which is due out shortly.

|||

I read Mosha’s blog and it is very informative; thank you!It does seem that I am experiencing the condition described, however, in my case the Excel generated query shows the correct information when run in Management Studio.When the pivot table is viewed in Excel, I get the following totals:

SHOW ALL Levels

CAD 10,306.80

EUR 2,576.70

GBP 5,153.40

USD 57,546.30

HIDE Levels (Only Product level visible)

AUD 3,301,776.20

CAD 3,053,884.94

EUR 1,031,955.40

GBP 2,483,938.44

USD 15,937,407.35

Below are the queries generated by Excel.I see the Hide Levels contain the Except statement so maybe there’s hope that SP 2 will address the problem.Thanks again!

SHOW ALL Levels

SELECT NON EMPTY HIERARCHIZE(AddCalculatedMembers({DrillDownLevel({[Source Currency].[Source Currency Code].[All Source Currencies]})})) DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS , NON EMPTY HIERARCHIZE(AddCalculatedMembers({DrillDownLevel({DrillDownLevel({DrillDownLevel({[Product].[Product Categories].[All]})}, [Product].[Product Categories].[Category])}, [Product].[Product Categories].[Subcategory])})) DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON ROWSFROM [Adventure Works] WHERE ([Measures].[Sales Amount], [Product].[Weight].&[2.12], [Date].[Calendar Year].&[2004])

HIDE Levels

SELECT NON EMPTY HIERARCHIZE(AddCalculatedMembers({DrillDownLevel({[Source Currency].[Source Currency Code].[All Source Currencies]})})) DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS , NON EMPTY HIERARCHIZE( Except(AddCalculatedMembers({DrillDownLevel({DrillDownLevel({DrillDownLevel({[Product].[Product Categories].[All]})}, [Product].[Product Categories].[Category])}, [Product].[Product Categories].[Subcategory])}) , AddCalculatedMembers( DrillDownLevel([Product].[Product Categories].[Category].members))) ) DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON ROWSFROM [Adventure Works] WHERE ([Measures].[Sales Amount], [Product].[Weight].&[2.12], [Date].[Calendar Year].&[2004])|||

Darren - you are absolutely right - the root cause here is the fact that related attributes from Product appear both in WHERE and in the axis - which is exactly the example I used in the blog you cited - http://sqljunkies.com/WebLog/mosha/archive/2006/11/01/slicer_axis_interaction.aspx . I don't beleive Visual Totals play any role here. I confirmed that with SP2, the following query gives correct results:

SELECT NON EMPTY VISUALTOTALS(HIERARCHIZE( Except(AddCalculatedMembers({DrillDownLevel(

{DrillDownLevel({DrillDownLevel({[Product].[Product Categories].[All Products]})},

[Product].[Product Categories].[Category])}, [Product].[Product Categories].[Subcategory])}) ,

AddCalculatedMembers( DrillDownLevel([Product].[Product Categories].[Category].members))) ))

DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS

FROM [Adventure Works] WHERE ([Measures].[Sales Amount], [Product].[Weight].&[2.12])

HTH,

Mosha (http://www.mosha.com/msolap)

Grand Total Problem in Excel

I found a problem when I use Excel 2003 with MSAS 2005. The problem occurs when I use the Hide Levels feature on a row dimension and select the grand total for columns option. I noticed that when I apply filters to the selection, the Grand Total does not reflect the filtered members. If I Show Details then the Grand Total is properly displayed. However, this problem does not happen on every diminsion.

I was able to duplicate the problem using Adventure Works DW. In Excel, put the Product Categories in the Row, Source Currency Code in the Column, Sales Amount as the Data, and Filter on Weight (2.12) and Date.Calendar Year (CY 2004). Ensure that Grand Total for Columns is checked. Show Details down to the Product level; notice that the Grand Total amount is correct. On the Sub Category column, select Hide Levels. Notice that the Grand total shows the incorrect amount. If I do a Hide Levels on the Category, the Grand Total is also correct. I was wondering if this would be considered an Excel bug, a MSAS bug, or if this is the way it is.

Having traced the MDX queries generated by the Excel pivot table, and rerun them in Management Studio, they return the correct totals, regardless of which levels are hidden in the query. So some further processing seems to occur in Excel - maybe someone else knows more about this phase?|||I was tracing a similar issue in the OWC component today for a client and I can confirm that it appears to be some post processing that Excel is doing. Interestingly I found that if you turn on the "Include Hidden items in totals" button (the one just to the right of the refresh button) that it appears to have the opposite effect with this particular query and causes it to display the correct totals - really strange.|||

Hi Darren,

That strangeness may help identify the culprit - it may be the "Visual Totals" connection mode which Excel employs, when the user elects not to include hidden items in the total. So it may be an AS 2005 issue after all - try the following Adventure Works queries, the 2nd one attempts to emulate the "Visual Totals" connection mode :

-- Query submitted by Excel, which works correctly without Visual Totals mode:

SELECT NON EMPTY HIERARCHIZE( Except(AddCalculatedMembers({DrillDownLevel(
{DrillDownLevel({DrillDownLevel({[Product].[Product Categories].[All Products]})},
[Product].[Product Categories].[Category])}, [Product].[Product Categories].[Subcategory])}) ,
AddCalculatedMembers( DrillDownLevel([Product].[Product Categories].[Category].members))) )
DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS
FROM [Adventure Works] WHERE ([Measures].[Sales Amount], [Product].[Weight].&[2.12])

-- Equivalent Visual Totals Query, which doesn't work correctly:

SELECT NON EMPTY VisualTotals(HIERARCHIZE( Except(AddCalculatedMembers({DrillDownLevel(
{DrillDownLevel({DrillDownLevel({[Product].[Product Categories].[All Products]})},
[Product].[Product Categories].[Category])}, [Product].[Product Categories].[Subcategory])}) ,
AddCalculatedMembers( DrillDownLevel([Product].[Product Categories].[Category].members))) ))
DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS
FROM [Adventure Works] WHERE ([Measures].[Sales Amount], [Product].[Weight].&[2.12])

|||

I forgot to mention that I also ran the MDX statements generated by Excel into the Management Studio.And as Deepak said, the Grand Totals are correct.

In my situation, the problem only occurs when one particular dimension is used with a certain filter.In SQL Server, I have a table called Project_d.Each record in this table also has the following fields: Grand Parent Project, Project Link, and Responsible Department.Every Project will roll up to a Grand Parent Project.So my Project dimension hierarchy is Grand Parent Project <- Project.A Project may or may not have a Project Link and/or a Responsible Department.The Project Link and Responsible Department are not defined as individual hierarchies, they just attributes under the Project level with the AttributeHierarchyVisible = True.

In Excel, I can filter on Project Link with other dimensions okay.I only seem to get the Grand Total problem when the Project is placed in the Row and the Project Link or Responsible Department is used as the filter and if I Hide Levels (hide the Grand Parent Projects).I tried putting the Project in the filter and the Project Link in the Row, but the Project Link only has one level so there is nothing to hide.

I cannot separate the Project Link and the Responsible Department from the Project SQL Server table because my fact table does not have a relationship to these values; the relationship is with the Project.I hope this extra information helps and thanks to everyone for your insight.

|||

This could be due to the fact that your attributes are related. Mosha has written a very detailed blog post on the interaction between the WHERE clause and related attributes in MDX which may explain your issue. see http://sqljunkies.com/WebLog/mosha/archive/2006/11/01/slicer_axis_interaction.aspx

If this is the issue that you are seeing, then it sounds like this might be fixed in SP2 which is due out shortly.

|||

I read Mosha’s blog and it is very informative; thank you!It does seem that I am experiencing the condition described, however, in my case the Excel generated query shows the correct information when run in Management Studio.When the pivot table is viewed in Excel, I get the following totals:

SHOW ALL Levels

CAD 10,306.80

EUR 2,576.70

GBP 5,153.40

USD 57,546.30

HIDE Levels (Only Product level visible)

AUD 3,301,776.20

CAD 3,053,884.94

EUR 1,031,955.40

GBP 2,483,938.44

USD 15,937,407.35

Below are the queries generated by Excel.I see the Hide Levels contain the Except statement so maybe there’s hope that SP 2 will address the problem.Thanks again!

SHOW ALL Levels

SELECT NON EMPTY HIERARCHIZE(AddCalculatedMembers({DrillDownLevel({[Source Currency].[Source Currency Code].[All Source Currencies]})})) DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS , NON EMPTY HIERARCHIZE(AddCalculatedMembers({DrillDownLevel({DrillDownLevel({DrillDownLevel({[Product].[Product Categories].[All]})}, [Product].[Product Categories].[Category])}, [Product].[Product Categories].[Subcategory])})) DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON ROWSFROM [Adventure Works] WHERE ([Measures].[Sales Amount], [Product].[Weight].&[2.12], [Date].[Calendar Year].&[2004])

HIDE Levels

SELECT NON EMPTY HIERARCHIZE(AddCalculatedMembers({DrillDownLevel({[Source Currency].[Source Currency Code].[All Source Currencies]})})) DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS , NON EMPTY HIERARCHIZE( Except(AddCalculatedMembers({DrillDownLevel({DrillDownLevel({DrillDownLevel({[Product].[Product Categories].[All]})}, [Product].[Product Categories].[Category])}, [Product].[Product Categories].[Subcategory])}) , AddCalculatedMembers( DrillDownLevel([Product].[Product Categories].[Category].members))) ) DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON ROWSFROM [Adventure Works] WHERE ([Measures].[Sales Amount], [Product].[Weight].&[2.12], [Date].[Calendar Year].&[2004])|||

Darren - you are absolutely right - the root cause here is the fact that related attributes from Product appear both in WHERE and in the axis - which is exactly the example I used in the blog you cited - http://sqljunkies.com/WebLog/mosha/archive/2006/11/01/slicer_axis_interaction.aspx . I don't beleive Visual Totals play any role here. I confirmed that with SP2, the following query gives correct results:

SELECT NON EMPTY VISUALTOTALS(HIERARCHIZE( Except(AddCalculatedMembers({DrillDownLevel(

{DrillDownLevel({DrillDownLevel({[Product].[Product Categories].[All Products]})},

[Product].[Product Categories].[Category])}, [Product].[Product Categories].[Subcategory])}) ,

AddCalculatedMembers( DrillDownLevel([Product].[Product Categories].[Category].members))) ))

DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS

FROM [Adventure Works] WHERE ([Measures].[Sales Amount], [Product].[Weight].&[2.12])

HTH,

Mosha (http://www.mosha.com/msolap)

Gradient background

Does anyone know how to create a gradient for the background of a row. I
know I can create a gradient bitmap and use that, but that seems like an
inefficient way to go and may hurt performance. Any ideas?Elmer,
An image is the only way.You can repeatX or repeatY so that for
horizontal or vertical gradients, the image can be very small.
It doesn't have to be bitmap, you can use jpegs and I think Gif's.
GIF's are more efficient for this type of image.
Chris
Elmer Miller wrote:
> Does anyone know how to create a gradient for the background of a
> row. I know I can create a gradient bitmap and use that, but that
> seems like an inefficient way to go and may hurt performance. Any
> ideas?

Good Query Writing......

SQL Query,
Col1 in Table1 has the following data.
Col1
1
2
2
3
4
IS it possible to run a query where I can the Col2 as sum from previous
column for each row in col1?
Col1
Col2
1
1
2
3
2
5
3
8
4
12
Thanks,
Hilton
depends what you mean by previous column.
1, 2, 2,
Which 2 comes first? To define that you will need some other value to sort on
If neither is before the other then
select i, sum((select t2.i from tbl where t2.i <= t.i)) from tbl
If there's no other column to sort then use a temp table with an identity
select i, id = identity(int,1,1) as id into #a from tbl
select i, sum((select t2.i from #a where t2.i < t.i or (t2.i = t.i and t2.id
<= t.id)))
from #a
"John Hilton" wrote:

> SQL Query,
>
> Col1 in Table1 has the following data.
>
> Col1
> 1
> 2
> 2
> 3
> 4
>
>
> IS it possible to run a query where I can the Col2 as sum from previous
> column for each row in col1?
>
> Col1
> Col2
> 1
> 1
> 2
> 3
> 2
> 5
> 3
> 8
> 4
> 12
>
>
> Thanks,
> Hilton
>
>
|||Nigel Rivett, I really appricate your time in replying this post!!!
Since the formatting is lost in the post,
I am posting this again.
Col1 in Table1 has the following data.
Col1
1
2
2
3
4
Is it possible to run a query where I can get the Col2 as sum from previous
column for each row in col1?
So the query result should be as follows.
Col2
1
3
5
8
12
Thanks,
Hilton
|||Didn't I answer that?
Nigel Rivett
www.nigelrivett.net
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Here's an alternative to Nigel's:
SELECT [ID] = IDENTITY(INT, 1, 1)
,Col1
INTO #Temp
FROM tbl
ORDER BY Col1 ASC
SELECT Col1 = AVG(t1.Col1)
Col2 = SUM(t2.Col1)
FROM #Temp AS t1
INNER JOIN
#Temp AS t2
ON t1.[ID] >= t2.[ID]
GROUP BY t1.[ID]
ORDER BY t1.[ID]
DROP #Temp
Sincerely,
Anthony Thomas

"John Hilton" <John_Hilton2004@.hotmail.com> wrote in message
news:%23Fw2HOx4EHA.1188@.tk2msftngp13.phx.gbl...
Nigel Rivett, I really appricate your time in replying this post!!!
Since the formatting is lost in the post,
I am posting this again.
Col1 in Table1 has the following data.
Col1
1
2
2
3
4
Is it possible to run a query where I can get the Col2 as sum from previous
column for each row in col1?
So the query result should be as follows.
Col2
1
3
5
8
12
Thanks,
Hilton

Go Through Each Row in Trigger

I was wondering if there was a go through each row in a updated/deleted table in a trigger. I have several booleans in a table and I want a final "complete" boolean to be updated depending on the values of the other booleans. Any easier way to do this?

Quote:

Originally Posted by nycninja

I was wondering if there was a go through each row in a updated/deleted table in a trigger. I have several booleans in a table and I want a final "complete" boolean to be updated depending on the values of the other booleans. Any easier way to do this?

Hi you can open a sql cursor from inserted tables or deleted table that contains the data you have inserted deleted or updated.

After you open a cursor you can loop wile cursor goto eof like a ado recordset cursor.

Look cursor transact-sql statement.

Giving Value for Identity column while inserting

I have a table with an Identity column. The latest Identity value is 1298. A row with Identity 324 was deleted by mistake and I need to insert it now. But if I insert it now it will have Identity value of 1299. I tried giving the value 324 for the Identity column in the Insert statement but it throws error. How can I insert this row with the same Identity value as before??

Quote:

Originally Posted by sajithamol

I have a table with an Identity column. The latest Identity value is 1298. A row with Identity 324 was deleted by mistake and I need to insert it now. But if I insert it now it will have Identity value of 1299. I tried giving the value 324 for the Identity column in the Insert statement but it throws error. How can I insert this row with the same Identity value as before??

Are you not able to insert using a plain insert statement on query analyser?|||You can do this by
SET IDENTITY_INSERT [table_name] ON
and then turn it off