Having subtotal after specific number of rows/columns

Hi
I'm working on a Matrix report in which i have a column grouping. I
have added a subtotal for the column group. so this total comes as he
last column i.e. after all the values in that column grouping. I want
to have a subtotal after lets say 10 columns and then again a total at
the end.
Can somebody tell me how to have a subtotal in the middle of a group as
well as in the end of the group?
Ankur MehtaAnkur,
The only way I know how to do this is to add another group which breaks down
your columns into sets of X number, depending on what other factor you use to
group on. Example using the Adventure Works sample database: Group on the
main Products, then do a group on Accessories under the products. Then you
can have a group2 footer which subtotals on the second group and a group1
footer which subtotals on the entire Products line.
Sorry I don't know of another way.
Catadmin
--
MCDBA, MCSA
Random Thoughts: If a person is Microsoft Certified, does that mean that
Microsoft pays the bills for the funny white jackets that tie in the back?
@.=)
"Ankur Mehta" wrote:
> Hi
> I'm working on a Matrix report in which i have a column grouping. I
> have added a subtotal for the column group. so this total comes as he
> last column i.e. after all the values in that column grouping. I want
> to have a subtotal after lets say 10 columns and then again a total at
> the end.
> Can somebody tell me how to have a subtotal in the middle of a group as
> well as in the end of the group?
> Ankur Mehta
>

Hardcode subtotal

Hello,

Can I hardcode matrix subtotal?

For instance, I want to hardcode "100%" in the following Sales report:

2007 Year

Quantity

Phones 21%

Notebooks 30%

Total 100%

Please don't ask me why I need to do that, it's a complex MDX query and the report is much more complex than I depicted.

I just need to statically write 100% in subtotal.

Write the following expression

Code Snippet

=100 & "%"

in the text box where u want to display 100%.

Best Regards,

Rajiv

|||

RajivDotNet wrote:

Write the following expression

Code Snippet

=100 & "%"

in the text box where u want to display 100%.

Best Regards,

Rajiv

I would just like to mention the InScope function. If you have a calculation in that column to determine the percentage, you can just add an IIf and InScope function to the expression to determine whether you are within the bounds of your row group, if you are within bounds then do your normal percentage calculation, if you are out of bounds then just return "100" (and use the code p0 in the Format property so that things are formatted correctly when exported to Excel).

|||

Dear Rajiv and Sluggy,

Thank you very much for your quick response.

Your approach worked!

Handling nulls in matrix sub totals

I have a matrix where I am using the inscope function to return different
calculations depending on which group it is in (ie the detail is just the
value and the sub totals are sum(values)).
In for the subtotal group I have used:
iif(isnothing(sum(Fields!CurrentCount.Value)), 0,
sum(Fields!CurrentCount.Value)
But this is still returning blanks where there are no values in the group to
sum together.
Can anyone suggest how to get around this issue as it ?
TIA
Andrewyou might try iif(sum(Fields!CurrentCount.Value = nothing)
"Andrew Murphy" <AndrewMurphy@.discussions.microsoft.com> wrote in message
news:56B0AB62-6CDE-4F41-BF39-AC72FABAC2C0@.microsoft.com...
>I have a matrix where I am using the inscope function to return different
> calculations depending on which group it is in (ie the detail is just the
> value and the sub totals are sum(values)).
> In for the subtotal group I have used:
> iif(isnothing(sum(Fields!CurrentCount.Value)), 0,
> sum(Fields!CurrentCount.Value)
> But this is still returning blanks where there are no values in the group
> to
> sum together.
> Can anyone suggest how to get around this issue as it ?
> TIA
> Andrew
>|||also if this formula you put on here looks just like the one you have in the
expression then you need to add a perenthesis onto the end. I tried to
recreate your problem and I get a 0. Weird
"Andrew Murphy" <AndrewMurphy@.discussions.microsoft.com> wrote in message
news:56B0AB62-6CDE-4F41-BF39-AC72FABAC2C0@.microsoft.com...
>I have a matrix where I am using the inscope function to return different
> calculations depending on which group it is in (ie the detail is just the
> value and the sub totals are sum(values)).
> In for the subtotal group I have used:
> iif(isnothing(sum(Fields!CurrentCount.Value)), 0,
> sum(Fields!CurrentCount.Value)
> But this is still returning blanks where there are no values in the group
> to
> sum together.
> Can anyone suggest how to get around this issue as it ?
> TIA
> Andrew
>|||and aslo maybe you should try to set the null value to 0 before you sum it.
"Andrew Murphy" <AndrewMurphy@.discussions.microsoft.com> wrote in message
news:56B0AB62-6CDE-4F41-BF39-AC72FABAC2C0@.microsoft.com...
>I have a matrix where I am using the inscope function to return different
> calculations depending on which group it is in (ie the detail is just the
> value and the sub totals are sum(values)).
> In for the subtotal group I have used:
> iif(isnothing(sum(Fields!CurrentCount.Value)), 0,
> sum(Fields!CurrentCount.Value)
> But this is still returning blanks where there are no values in the group
> to
> sum together.
> Can anyone suggest how to get around this issue as it ?
> TIA
> Andrew
>