Grand totals page

We have a report that includes a list that expands down to 17 inches to accomodate two pages for a report and this list is grouped on a particular location. Each report is grouped by State. So in each state there can be many locations that report various items that comprimse two pages.

Now, what we need is a "state grand total" page and I am wondering about the best way to go about doing this. We do not want to create a new report with a separate stored proc that returns grand totals. What we would like is to do a running total or something like that. I could then include this on a subreport, but I need it to be the last page. For some reason, I thought there was a Report Footer section, but I only see Page Footer.

I hope I made myself clear enough and not being too confusing and I many thanks for any helpful information.

guyinkalamazoo3 wrote:

We do not want to create a new report with a separate stored proc that returns grand totals.

This is how I handle it. The auto total feature in RS is horrific IMO.

Grand Totals in Report Footer?

I have a table report with five groups and want to add grand totals to
my report footer. But if I add a field to the footer:
=Fields!Period_MonthToDate_Measures_B.Value
The footer shows the total of only the first item in the highest level
group. If I use a sum function:
=Sum(Fields!Period_MonthToDate_Measures_B.Value)
The total is five times the actual total, I guess due to the fact that
I have five levels.
How do I show the correct report total?
Thanks,
BurtBurt - I have been looking for an answer to a similar question - It would
strongly appear that MS didn't include this type of feature in this
release...and that is a kind way of putting it.
-KB
"Burt" <burt_5920@.yahoo.com> wrote in message
news:19e5f39f.0407271554.6cc831f0@.posting.google.com...
> I have a table report with five groups and want to add grand totals to
> my report footer. But if I add a field to the footer:
> =Fields!Period_MonthToDate_Measures_B.Value
> The footer shows the total of only the first item in the highest level
> group. If I use a sum function:
> =Sum(Fields!Period_MonthToDate_Measures_B.Value)
> The total is five times the actual total, I guess due to the fact that
> I have five levels.
> How do I show the correct report total?
> Thanks,
> Burt|||i had the same problem and i solved it with the runningvalue
did u try using RunningValue'

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)

Grand Total of an iif expression

I have a report grouped on the user. The report calculates various
utilization totals based on the username. Some users have a Utilization = 100% for only 12 or 17 hours and other have a Utilization = 100% for 40
hours. At the group level, I can calculate the utilization with the
following formula...
=iif ( Fields!zUserID.Value ="USER1 ",
(Sum( Fields!zBillingWorkQty.Value , "GroupUserName") + Sum(
Fields!zQtyBilled.Value , "GroupUserName")) / 12 ,
iif ( Fields!zUserID.Value ="USER2 ", (Sum(
Fields!zBillingWorkQty.Value , "GroupUserName") + Sum(
Fields!zQtyBilled.Value , "GroupUserName")) / 17 , (Sum(
Fields!zBillingWorkQty.Value , "GroupUserName") + Sum(
Fields!zQtyBilled.Value , "GroupUserName")) / 40 ))
However, my issue is how do I get a grand total of this utilization on the
report footer. I cannot sum () this formula in the report footer. I receive
an error becuase of the "GroupUserName" parameter.
Is there a way to use the sum () function in the Report Footer and just
reference the name of the textbox in the group footer and it's corresponding
value that has already been calculated for each group?I probably do not understand your question very well, but you might try to
simply do the sum without regard to the groupings ( simply take out the
groups) and do the sum for the entire data set...
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"Jack Bender" wrote:
> I have a report grouped on the user. The report calculates various
> utilization totals based on the username. Some users have a Utilization => 100% for only 12 or 17 hours and other have a Utilization = 100% for 40
> hours. At the group level, I can calculate the utilization with the
> following formula...
> =iif ( Fields!zUserID.Value ="USER1 ",
> (Sum( Fields!zBillingWorkQty.Value , "GroupUserName") + Sum(
> Fields!zQtyBilled.Value , "GroupUserName")) / 12 ,
> iif ( Fields!zUserID.Value ="USER2 ", (Sum(
> Fields!zBillingWorkQty.Value , "GroupUserName") + Sum(
> Fields!zQtyBilled.Value , "GroupUserName")) / 17 , (Sum(
> Fields!zBillingWorkQty.Value , "GroupUserName") + Sum(
> Fields!zQtyBilled.Value , "GroupUserName")) / 40 ))
> However, my issue is how do I get a grand total of this utilization on the
> report footer. I cannot sum () this formula in the report footer. I receive
> an error becuase of the "GroupUserName" parameter.
> Is there a way to use the sum () function in the Report Footer and just
> reference the name of the textbox in the group footer and it's corresponding
> value that has already been calculated for each group?
>|||The issue here is that I need a conditional total and percentage calculation
based on the users that billed time in the report. If User 1 and/or User 2
are in the report, then they have different criterias for utilization
calcualtons than everyone else. All other users are based on 40 hours a
week. I determine this already at the group footer, so it would be much
easier just to grand total the cell value of the group footer then to
re-engineer the formula for the report footer.
"Wayne Snyder" wrote:
> I probably do not understand your question very well, but you might try to
> simply do the sum without regard to the groupings ( simply take out the
> groups) and do the sum for the entire data set...
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> I support the Professional Association for SQL Server ( PASS) and it''s
> community of SQL Professionals.
>
> "Jack Bender" wrote:
> > I have a report grouped on the user. The report calculates various
> > utilization totals based on the username. Some users have a Utilization => > 100% for only 12 or 17 hours and other have a Utilization = 100% for 40
> > hours. At the group level, I can calculate the utilization with the
> > following formula...
> >
> > =iif ( Fields!zUserID.Value ="USER1 ",
> > (Sum( Fields!zBillingWorkQty.Value , "GroupUserName") + Sum(
> > Fields!zQtyBilled.Value , "GroupUserName")) / 12 ,
> >
> > iif ( Fields!zUserID.Value ="USER2 ", (Sum(
> > Fields!zBillingWorkQty.Value , "GroupUserName") + Sum(
> > Fields!zQtyBilled.Value , "GroupUserName")) / 17 , (Sum(
> > Fields!zBillingWorkQty.Value , "GroupUserName") + Sum(
> > Fields!zQtyBilled.Value , "GroupUserName")) / 40 ))
> >
> > However, my issue is how do I get a grand total of this utilization on the
> > report footer. I cannot sum () this formula in the report footer. I receive
> > an error becuase of the "GroupUserName" parameter.
> >
> > Is there a way to use the sum () function in the Report Footer and just
> > reference the name of the textbox in the group footer and it's corresponding
> > value that has already been calculated for each group?
> >
> >

Grand Total not correct..

Hey guys,

got a strange problem happening and wondering if anyone can help me..

I have added a calculated member which calculates an amount from a percentage of sales. Only certain customers get this rebate..

([Measures].[Sales Amount] / 100) * [Measures].[DMF Percentage]

When i run the report., the calculation works, but the grand total does not add up at all.. See Below: (sorry for the long post)

The total it is showing is $2,888,196.60, where if i add up all the individial ones, the total is $60,393.99

Can anyone help me with this problem?

Thanks

Scotty

2007 Q1 P1

Sales Amount

DMF Value

$10.03

$6,453.16

$3,097.86

$406.85

$721.00

$343,758.92

$8,593.97

$9,898.78

$757.04

$3,243.78

$2,996.92

$301.00

$5,104.94

$102.10

$612.46

$948.40

$21.36

$78.15

$0.00

$23,609.67

$708.29

$28,562.25

$1,664.70

$18,877.30

$53,328.95

$1,599.87

$11,048.06

$73,165.96

$2,194.98

$48,843.92

$1,465.32

$75.06

$54,544.81

$1,636.34

$22,595.68

$677.87

$1,038.74

$319.90

$780.57

$20,278.27

$202.78

$91.94

$299.45

$84.29

$5,193.40

$44,062.76

$5,516.30

$269.04

$2,206.00

$2,115.15

$4,807.06

$5,807.60

$3,885.50

$145.07

$1,412.26

$7,612.20

$753.50

$18,131.96

$126,928.37

$44,628.20

$52.95

$2,076.93

$150.80

$73,777.80

$106,490.34

$2,129.81

$38,891.58

$31,473.10

$944.19

$116,537.06

$18,251.76

$547.55

$1,270.00

$2,737.50

$443.23

$131.07

$333.50

$98,267.33

$2,948.02

$58,050.00

$1,741.50

$59,816.85

$1,794.51

$1,843.65

$19,529.77

$585.89

$127,937.43

$3,838.12

-$2,208.12

$7,409.64

$12,571.79

$1,168.00

$3,029.21

$914.49

$4,390.07

$934.88

$230.77

$11,167.58

$2,403.23

$11,656.50

$20,722.90

$4,287.29

$148,204.76

$5,187.17

$1,068.91

$37.37

$268.86

$418.76

$4,549.74

$45.50

$2,541.82

$25.42

$11,324.19

$113.24

$430.06

$4.30

$1,871.75

$1,708.40

$8,314.29

$83.14

$28,158.40

$281.58

$310.80

$9,790.22

$293.71

$24,653.49

$246.53

$231.57

$901.84

$271.56

$233.03

$1,339.37

$164.38

$141,002.09

$2,820.04

$6,602.88

$132.06

$5,248.72

$52.49

$189.22

$1.89

$582.41

$397.70

$3,418.89

$5,243.68

$52.44

$6,407.74

$64.08

$6,307.73

$63.08

$15,741.19

$157.41

$3,876.07

$38.76

$23,880.87

$716.43

$0.00

$1,228.00

$1,522.00

$9,931.88

$47,516.31

$950.33

$33,437.79

$668.76

$35,417.15

$708.34

$495,086.89

$14,852.61

$4,212.05

$126.36

$369.00

-$35.71

-$1.07

$880.00

$6,475.46

$2,364.96

$73.64

$49,913.90

$998.28

$6,612.57

$4.05

$3,008,538.12

$2,888,196.60

A calculated measure won't automatically aggregate like a regular measure. The MDX expression could be re-written to roll up from individual customers. Could you describe [Measures].[DMF Percentage] - if it is a regular cube measure on a different measure group than [Sales Amount], you might be able to create a new cube measure, using a Measure Expression?|||

Hey Deepak,

Thanks for the explantion.

the dmf percantage is just a regular cube measure from a different measure group (DMF)

I would be thankful if you could explain how to create a new cube measure using a measure expression..

thanks a lot,,

Scotty

|||

Create a named calculation on the [Sales Amount] fact table like [DMF Value]: [Sales Amount] / 100. Then add a new "sum" measure: [DMF Value] on this named calculation field, defining its Measure Expression as: [Measures].[DMF Value] * [Measures].[DMF Percentage].

|||

Deepak,

thanks for the hand.. You are all over SSAS obviously an expert with it.. I am having one more problem that i posted last week and no one has been able to help. Would you mind looking at the post below and see if you have any idea?

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1175605&SiteID=1

Much appriciated.

Thanks

Scotty

Grand Total Needed

I have two subreports (Revenue and Expense) that each have subtotals.

This works just fine, but I need a Grand Total that would sum the two subtotals.

How would I go about doing this?

So it looks something like this:

Revenue Report (matrix)

Subtotal

Expense Report (matrix)

Subtotal

<~~~~ Need Grand Total here.

Greg,

It would be difficult across two data regions, myself, I create Calculated GrandTotal field in my dataset and then use that field for display.

Ham

|||

Ok, so say DataSet1 has Total1 and DataSet2 has Total2.

How would I sum the two and display the result at the end of the second report?

|||

Greg,

This should work for you.

Sum(Fields!Counter.Value, "DataSet1")+ Sum(Fields!Counter2.Value, "DataSet2")

Ham

|||

I'm not quite sure how to implement that since my revenue and expense report have a column for each month in the current year.

Maybe I should draw it out a little better:

Revenue Report(matrix)

Jan Feb Mar .........

Account 1 $100 $200

Account 2 $50 $300

Subtotal $150 $500

Expense Report (matrix)

Jan Feb Mar .........

Account 1 $100 $100

Account 2 $0 $300

Subtotal $100 $400

Grand Total $50 $100

I need the Grand Total for each month (subtract Expense subtotal from Revenue subtotal)

|||

Greg,

You will need to use subtraction instead of addition. I was able to get resume for my calculated fields with 2 matrix reports.

Sum(Fields!Income1.Value, "DataSet1") - Sum(Fields!Income1.Value, "DataSet1")

Calculated fields value :=Fields!Income.Value and Income

Calculated fields value :=Fields!Income.Value and Income2

Ham

|||Where would I need to place that code so that it will give the grand total for each month?|||

Greg,

I added a textbox to the bottom of my 2 matrix to make my Grand total align correctly.

Ham

|||

Greg,

Is there a reason why you need 2 matrices? Could you have used 1 Matrix and then selected Expense type field to distingish what totals were being calculated. It much easlier to calculated within a Data Region and to calculated across data regions.

Just thought I would ask.

Ham

|||I used two matrices because the SQL statement to combine Revenue with Expense would be too complex.|||

Could you use a UNION ALL statement with the 2 dataset you are now using. You then could group by expenses, expense type

That would get you the subtotal expense type

and the Grand Total expenses.

|||I'll try it and let you know. Thank you for all of the help by the way!|||

I see that other people in the forums are using UNION ALL. But when I put UNION ALL between the two SQL queries I have, "ALL" is not recognised as a keyword. I get a SQL error near UNION.

I tried the same in SQL 2005 itself and received the same error.

select BLAH,BLAH,BLAH
FROM BLAH,BLAH

WHERE BLAH AND BLAH AND BLAH

GROUP BY BLAH, BLAH, BLAH
ORDER BY BLAH

UNION ALL

select BLAH,BLAH,BLAH

FROM BLAH,BLAH
WHERE BLAH AND BLAH AND BLAH

GROUP BY BLAH, BLAH, BLAH

I get a SQL error: incorrect syntax near the keyword 'UNION'. Both of these queries work fine individually.

|||

Greg,

Your order and group by use by like to following:

select BLAH,BLAH,BLAH
FROM BLAH,BLAH

WHERE BLAH AND BLAH AND BLAH

UNION ALL
select BLAH,BLAH,BLAH

FROM BLAH,BLAH
WHERE BLAH AND BLAH AND BLAH

GROUP BY BLAH, BLAH, BLAH
ORDER BY BLAH

|||

GregSQL wrote:

Revenue Report

Jan Feb Mar .........

Account 1 $100 $200

Account 2 $50 $300

Subtotal $150 $500

Expense Report

Jan Feb Mar .........

Account 1 $100 $100

Account 2 $0 $300

Subtotal $100 $400

Grand Total $50 $100

Ok I have the Grand Total by combining the datasets for Revenue and Expense into one dataset.

Now I'm not sure how to add the subtotals back. There should be a subtotal for Revenue and one for Expense.

Grand Total in Matrixes

Hi,
I am working on matrix report that uses 3 matrixes like below.
In Grand Total matrix, I want to sum matrix 1 and matrix2 subtotals and
display as Grand Total. I had a difficulty to find out the expression for
this(how to reference the subtotals in matrixes?)?
Can we do this or any limitations?
Matrix 1:
Week1 Week2
Sub Total 10 20
Matrix 2:
Week1 Week2
Sub Total 15 25
Grand Total
Week1 Week2
Grand Total 25 45
Any pointers in this regard is appreciated
Thanks
BhaskarOn Nov 16, 5:08 pm, Bhaskar <Bhas...@.discussions.microsoft.com> wrote:
> Hi,
> I am working on matrix report that uses 3 matrixes like below.
> In Grand Total matrix, I want to sum matrix 1 and matrix2 subtotals and
> display as Grand Total. I had a difficulty to find out the expression for
> this(how to reference the subtotals in matrixes?)?
> Can we do this or any limitations?
> Matrix 1:
> Week1 Week2
> Sub Total 10 20
> Matrix 2:
> Week1 Week2
> Sub Total 15 25
> Grand Total
> Week1 Week2
> Grand Total 25 45
> Any pointers in this regard is appreciated
> Thanks
> Bhaskar
Since you have multiple columns that have subtotals, you most likely
will want to sum the subtotals together as part of another report
dataset (via a separate stored procedure/query). If there was only one
subtotal column per matrix, you might have been able to reference
aggregates via an expression similar to this.
=Sum(Fields!Week1.Value, "dsMatrix1") + Sum(Fields!Week1.Value,
"dsMatrix2")
Where dsMatrix1 and dsMatrix2 are the example dataset names used for
Matrix1 and Matrix2 respectively.
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||Thanks Enrique. yeah.. i tried to avoid another query or sp to get the grand
total.
looks like there is no other way. :)
Bhaskar
"EMartinez" wrote:
> On Nov 16, 5:08 pm, Bhaskar <Bhas...@.discussions.microsoft.com> wrote:
> > Hi,
> >
> > I am working on matrix report that uses 3 matrixes like below.
> >
> > In Grand Total matrix, I want to sum matrix 1 and matrix2 subtotals and
> > display as Grand Total. I had a difficulty to find out the expression for
> > this(how to reference the subtotals in matrixes?)?
> > Can we do this or any limitations?
> >
> > Matrix 1:
> >
> > Week1 Week2
> > Sub Total 10 20
> >
> > Matrix 2:
> > Week1 Week2
> > Sub Total 15 25
> >
> > Grand Total
> >
> > Week1 Week2
> > Grand Total 25 45
> >
> > Any pointers in this regard is appreciated
> >
> > Thanks
> > Bhaskar
>
> Since you have multiple columns that have subtotals, you most likely
> will want to sum the subtotals together as part of another report
> dataset (via a separate stored procedure/query). If there was only one
> subtotal column per matrix, you might have been able to reference
> aggregates via an expression similar to this.
> =Sum(Fields!Week1.Value, "dsMatrix1") + Sum(Fields!Week1.Value,
> "dsMatrix2")
> Where dsMatrix1 and dsMatrix2 are the example dataset names used for
> Matrix1 and Matrix2 respectively.
> Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>

Grand Total for field problem

Hello,

In my SRS report, I have a field called "Alternate" where I need to check to find out if my picklist value = 2. This works out just great.

I have created a variable called "Total_Alternate". I don't seem to be able to get the right Grand Total here by using the code below

(1) Value in expression field for textbox9 - gives right total in this field for each group
=SUM(IIF(Fields!new_rpcstatus.value=2, CInt(Fields!Total_Alternate.value), 0))

(2) Value in Table Footer for the above field - doesn't give me the right number
=(Fields!Total_Alternate.Value)

What am I doing wrong?

If you want

number then

=Count(Fields!Total_Alternate.Value)

or if you want sum of these then

=Sum(Fields!Total_Alternate.Value)

sql

Grand total data drill through in Excel 2007 pivot table

Hi

I'm trying to deliver a flexible customer dashboard for a client using OLAP Analysis services (2000 not 2005) and Excel 2007. Everything works great until i try to drill through to the detail data behind the 'Grand Total' figure for a filtered set of data. I've found a Microsoft article that tells me this is not possible in Excel 2003 - does anyone know if it should work in Excel 2007 (I suspect I'll be disappointed and find that it won't) and has anyone come up with a solution to the issue ? Any help would gratefully appreciated before I go and write a series of macros to get each set of detail records into individual worksheets before consolidating them into one big worksheet.

Any advice gratefully received

dave

Hello! I suspect that the grand total is a calculated member and drill through is not supported on calculated members.

Regards

Thomas Ivarsson

Grand Total Count incorrect after incremental update

I've been experiencing a strange behavior with a count measure where the total doesn't show the correct number after an incremental update.

Basically I have about 70 million rows in my fact table and initially I do a full process of the cube to get it up to speed. Then each subsequent day I do an incremental update to that cube adding only new rows. The strange part is my grand total count seems to do it's own thing after each incremental update.

Here's how I discovered the problem:
I made a backup of the AS database and then ran the same test 3 times restoring the original database in between each run. Each time I added about 41K rows to the cube incrementally. Each time I got a different total for the count measure. The first time it only added about 12K to the total count (instead of 41K). The second time it added about 25K. The third time it *subtracted* about 6K from the total count. How is it possible that I can add 41K rows to the cube and have the total come out smaller than it started?

When I dug into it a little more I found that if I looked at the date that the 41K rows were being imported into I saw the correct number imported into the cube. So, it's like the cube processed the rows correctly, but somehow got confused when calculating the grand total. What would cause this? Is this a bug?

How do you do the incremental update of the cube?

Do you create a new partition and insert new rows there?

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights

|||

We are using SQL Server 2005 Standard Edition which doesn't support multiple partitions, so no, just one partition. Standard edition doesn't support proactive caching either, so we don't use that and I don't *think* caching is a problem. I went ahead and ran the aggregate designer and had it optimize the cube to 95% and then set the incremental update to look to a view that contains only the new rows that are not in the cube currently. Then when I run the update I get the random total like I described in my first post. But, like I also mentioned in the first post, if I browse the cube and drill down to the new information, all the expected fact table rows seem to exist in the cube. At the day level (when using the time dimension) all the numbers add up as they should. It's only the rollup totals that seem to be wrong.

|||

I would urge you to contact product support for analysis services and report this problem.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

Grand Total

I am having a hard time figuring out how to total a column. If I have
January Febuary March
Type1 10 4 3
Type2 4 8 2
Type3 1 9 1
I want a total for each month, NOT each type. I have tried right
clicking on Type and choosing subtotal but that only gives me 10, if I
try to change the expression to a Sum, I get an error that aggregates
can't live in groups.
I have read that I should add a textbox and enter an expression. I
have tried that too and got closer but it seems the only scope that is
actually allowed (dispite what BOL says) is the dataset, which does
give me the total for the whole matix, but I only want each column
totaled. I have tried to replace DataSet1 in the expression with the
name of the Month group, but that errors too.
Am I just blind, this shouldn't be this difficult, right?I have a similar problem but in my case I can't add the columns. My
idea ould be to adjust the MDX statement to get the total row. Just add
the root element to the columns of your query and you should get the
total line.
root element example: [Organisation].[Business Area
Code].CurrentMember.Parent
pjcwik@.gmail.com schrieb:
> I am having a hard time figuring out how to total a column. If I have
> January Febuary March
> Type1 10 4 3
> Type2 4 8 2
> Type3 1 9 1
> I want a total for each month, NOT each type. I have tried right
> clicking on Type and choosing subtotal but that only gives me 10, if I
> try to change the expression to a Sum, I get an error that aggregates
> can't live in groups.
> I have read that I should add a textbox and enter an expression. I
> have tried that too and got closer but it seems the only scope that is
> actually allowed (dispite what BOL says) is the dataset, which does
> give me the total for the whole matix, but I only want each column
> totaled. I have tried to replace DataSet1 in the expression with the
> name of the Month group, but that errors too.
> Am I just blind, this shouldn't be this difficult, right?|||What kind of control are you using - Table or Matrix?
"pjcwik@.gmail.com" wrote:
> I am having a hard time figuring out how to total a column. If I have
> January Febuary March
> Type1 10 4 3
> Type2 4 8 2
> Type3 1 9 1
> I want a total for each month, NOT each type. I have tried right
> clicking on Type and choosing subtotal but that only gives me 10, if I
> try to change the expression to a Sum, I get an error that aggregates
> can't live in groups.
> I have read that I should add a textbox and enter an expression. I
> have tried that too and got closer but it seems the only scope that is
> actually allowed (dispite what BOL says) is the dataset, which does
> give me the total for the whole matix, but I only want each column
> totaled. I have tried to replace DataSet1 in the expression with the
> name of the Month group, but that errors too.
> Am I just blind, this shouldn't be this difficult, right?
>|||OK I got it figured out.
When looking at the matrix report the field names were in the field
spots, but... What needed to happen was to make the field names a SUM.
So instead of just =(Fields!allitmonthclosedcalls.Value) what was
needed was =Sum(Fields!allitmonthclosedcalls.Value) Then the SubTotal
feature worked. The SubTotal can be seen when you right click on the
row group header and choose SubTotal. (I hate it when specifics aren't
given)
Without the SUM in front of the field I only got 10 in the SubTotal
field, but with the =Sum(Fields!allitmonthclosedcalls.Value) then it
added the whole column.
I am assuming that the way it is being read is that the Sum of 10 is
10, the Sum of 4 is 4 and the Sum of 1 is 1. Then the SubTotal is the
Sum of the Sums. A screwy way to get where I needed to be, but it
works.
Hope this helps.
--Pete
pjcwik@.gmail.com wrote:
> I am having a hard time figuring out how to total a column. If I have
> January Febuary March
> Type1 10 4 3
> Type2 4 8 2
> Type3 1 9 1
> I want a total for each month, NOT each type. I have tried right
> clicking on Type and choosing subtotal but that only gives me 10, if I
> try to change the expression to a Sum, I get an error that aggregates
> can't live in groups.
> I have read that I should add a textbox and enter an expression. I
> have tried that too and got closer but it seems the only scope that is
> actually allowed (dispite what BOL says) is the dataset, which does
> give me the total for the whole matix, but I only want each column
> totaled. I have tried to replace DataSet1 in the expression with the
> name of the Month group, but that errors too.
> Am I just blind, this shouldn't be this difficult, right?