Percentages of subtotals

I have a matrix report with subtotals on the end of each row. I want to put
in brackets the percentage of the subtotal that each value is in the row. I
am not sure how to do this. Does anyone have any ideas?On Sep 27, 4:00 pm, "Fresno Bob" <nos...@.nospam.com> wrote:
> I have a matrix report with subtotals on the end of each row. I want to put
> in brackets the percentage of the subtotal that each value is in the row. I
> am not sure how to do this. Does anyone have any ideas?
You should be able to use an expression similar to the following:
=(Fields!SomeField.Value/Sum(Fields!SomeField.Value) ) * 100
-or- if this doesn't work due to automatic summing in the matrix
control, you might need to do something like this (I think):
=(Sum(Fields!SomeField.Value)/Total) * 100
For the brackets, you will need to select F4 (Properties Window) once
you have selected the field. In the Format section enter an expression
similar to the following:
="[#,.00%]"
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant

percentage in matrix table

Hello Chris,
I have a data set with the following fileds:
plan
month
leads
orders
I have made the following matrix table: month for column, plan for row, and
leads and orders for the details, and subtotals.
--plan--Jan--Feb--Total
--leads orders-leads orders-leads orders
GOLD --9--8--17--12--26--20
GOLDP --9--7--38--21--47--28
PLATINUM --6--3--8--6--14--9
PLATINUMP --550--343--954--572--1504--915
GrandTotal --574--361--1017--611--1591--972
i want to add another column that calculates the percentage of
total orders / total leads, so it should look like this
--plan--Jan--Feb--Total--% ordered
-- leads orders leads orders leads orders
GOLD --9--8--17--12--26--20--76.92 %
GOLDP --9--7--38--21--47--28--59.57 %
PLATINUM --6--3--8--6--14--9--64.29 %
PLATINUMP --550--343--954--572--1504--915--60.84 %
GrandTotal --574--361--1017--611--1591--972
I have spent a many hours working on it. i ended up adding another table
just for the % ordered column, but it's impossible to align it with the
matrix table.
Is there anyway i can integrate it into the matrix table?
thanks
aaronHi, Roni
It is very strange how you ended with such % calculations they are not
correlate to the numbers (spatially in gold, platinum and so on it is
probably means something),
In any case, you can use calculated field with formula expression in the
same matrix.
Regards, ProJester,
MSN Programmer.
"aaron" wrote:
> Hello Chris,
> I have a data set with the following fileds:
> plan
> month
> leads
> orders
> I have made the following matrix table: month for column, plan for row, and
> leads and orders for the details, and subtotals.
> --plan--Jan--Feb--Total
> --leads orders-leads orders-leads orders
> GOLD --9--8--17--12--26--20
> GOLDP --9--7--38--21--47--28
> PLATINUM --6--3--8--6--14--9
> PLATINUMP --550--343--954--572--1504--915
> GrandTotal --574--361--1017--611--1591--972
> i want to add another column that calculates the percentage of
> total orders / total leads, so it should look like this
> --plan--Jan--Feb--Total--% ordered
> -- leads orders leads orders leads orders
> GOLD --9--8--17--12--26--20--76.92 %
> GOLDP --9--7--38--21--47--28--59.57 %
> PLATINUM --6--3--8--6--14--9--64.29 %
> PLATINUMP --550--343--954--572--1504--915--60.84 %
> GrandTotal --574--361--1017--611--1591--972
> I have spent a many hours working on it. i ended up adding another table
> just for the % ordered column, but it's impossible to align it with the
> matrix table.
> Is there anyway i can integrate it into the matrix table?
> thanks
> aaron|||% calculations are done by dividing total orders/total leads. why is it
strange?
and how do i do "use calculated field with formula expression in the
same matrix"? i can't figure out how to add it.
"ProJester" wrote:
> Hi, Roni
> It is very strange how you ended with such % calculations they are not
> correlate to the numbers (spatially in gold, platinum and so on it is
> probably means something),
> In any case, you can use calculated field with formula expression in the
> same matrix.
> Regards, ProJester,
> MSN Programmer.
> "aaron" wrote:
> > Hello Chris,
> >
> > I have a data set with the following fileds:
> > plan
> > month
> > leads
> > orders
> >
> > I have made the following matrix table: month for column, plan for row, and
> > leads and orders for the details, and subtotals.
> > --plan--Jan--Feb--Total
> > --leads orders-leads orders-leads orders
> > GOLD --9--8--17--12--26--20
> > GOLDP --9--7--38--21--47--28
> > PLATINUM --6--3--8--6--14--9
> > PLATINUMP --550--343--954--572--1504--915
> > GrandTotal --574--361--1017--611--1591--972
> >
> > i want to add another column that calculates the percentage of
> > total orders / total leads, so it should look like this
> >
> > --plan--Jan--Feb--Total--% ordered
> > -- leads orders leads orders leads orders
> > GOLD --9--8--17--12--26--20--76.92 %
> > GOLDP --9--7--38--21--47--28--59.57 %
> > PLATINUM --6--3--8--6--14--9--64.29 %
> > PLATINUMP --550--343--954--572--1504--915--60.84 %
> > GrandTotal --574--361--1017--611--1591--972
> >
> > I have spent a many hours working on it. i ended up adding another table
> > just for the % ordered column, but it's impossible to align it with the
> > matrix table.
> >
> > Is there anyway i can integrate it into the matrix table?
> >
> > thanks
> >
> > aaron|||the % is calculated by total orders / total leads. why is it strange?
and how exactly do you do "you can use calculated field with formula
expression in the same matrix"? i can't figure it out.
also, is there anyway to anchor the column heading when they scroll down
like it in ms excel?
thanks
"ProJester" wrote:
> Hi, Roni
> It is very strange how you ended with such % calculations they are not
> correlate to the numbers (spatially in gold, platinum and so on it is
> probably means something),
> In any case, you can use calculated field with formula expression in the
> same matrix.
> Regards, ProJester,
> MSN Programmer.
> "aaron" wrote:
> > Hello Chris,
> >
> > I have a data set with the following fileds:
> > plan
> > month
> > leads
> > orders
> >
> > I have made the following matrix table: month for column, plan for row, and
> > leads and orders for the details, and subtotals.
> > --plan--Jan--Feb--Total
> > --leads orders-leads orders-leads orders
> > GOLD --9--8--17--12--26--20
> > GOLDP --9--7--38--21--47--28
> > PLATINUM --6--3--8--6--14--9
> > PLATINUMP --550--343--954--572--1504--915
> > GrandTotal --574--361--1017--611--1591--972
> >
> > i want to add another column that calculates the percentage of
> > total orders / total leads, so it should look like this
> >
> > --plan--Jan--Feb--Total--% ordered
> > -- leads orders leads orders leads orders
> > GOLD --9--8--17--12--26--20--76.92 %
> > GOLDP --9--7--38--21--47--28--59.57 %
> > PLATINUM --6--3--8--6--14--9--64.29 %
> > PLATINUMP --550--343--954--572--1504--915--60.84 %
> > GrandTotal --574--361--1017--611--1591--972
> >
> > I have spent a many hours working on it. i ended up adding another table
> > just for the % ordered column, but it's impossible to align it with the
> > matrix table.
> >
> > Is there anyway i can integrate it into the matrix table?
> >
> > thanks
> >
> > aaron|||Hi, Aaron,
do you figure out how to add that last percent column? If Yes, please let
me know how. Thanks!
Henry
"aaron" wrote:
> Hello Chris,
> I have a data set with the following fileds:
> plan
> month
> leads
> orders
> I have made the following matrix table: month for column, plan for row, and
> leads and orders for the details, and subtotals.
> --plan--Jan--Feb--Total
> --leads orders-leads orders-leads orders
> GOLD --9--8--17--12--26--20
> GOLDP --9--7--38--21--47--28
> PLATINUM --6--3--8--6--14--9
> PLATINUMP --550--343--954--572--1504--915
> GrandTotal --574--361--1017--611--1591--972
> i want to add another column that calculates the percentage of
> total orders / total leads, so it should look like this
> --plan--Jan--Feb--Total--% ordered
> -- leads orders leads orders leads orders
> GOLD --9--8--17--12--26--20--76.92 %
> GOLDP --9--7--38--21--47--28--59.57 %
> PLATINUM --6--3--8--6--14--9--64.29 %
> PLATINUMP --550--343--954--572--1504--915--60.84 %
> GrandTotal --574--361--1017--611--1591--972
> I have spent a many hours working on it. i ended up adding another table
> just for the % ordered column, but it's impossible to align it with the
> matrix table.
> Is there anyway i can integrate it into the matrix table?
> thanks
> aaron

Percentage Column in Matrix

Hi,
I would like to generate following table with reporting service matrix.
But if I use the expression : percentage coulmn=sum(field!qty.value) / sum(field!qty.value, "region_group")
It will become a percentage of a row total, instead of a group total (store group). Please see the second table.
Any ideas?

Thank you!

(Correct!)

store1 store2 level1 level2 level3 sub total level1 level2 subtotal category region qty % qty % qty % qty % qty % qty % qty % C1 APAC

10

10%

20

20%

70

70%

100

100%

25

50%

25

50%

50

100%

EURP C2 CHINA

(Wrong)

store1 store2 level1 level2 level3 sub total level1 level2 subtotal category region qty % qty % qty % qty % qty % qty % qty % C1 APAC

10

6%

20

13%

70

46%

100

25

16%

25

16%

50

EURP C2 CHINA

Can somebody help me or give me a hint? (or my question is not clear enough?)
If this kind of report is doable, I will ask my team member change their JSP report to MS RS.
Thank you!!

Percentage Column AND Row

Hello,

I have a matrix that looks as follows:

Name Jan Feb Total

John 5 6 11

Mary 3 4 7

Total 8 10 18

I want to add a percent column to the RIGHT of the total, and also on the bottom row. I can't find any clear examples of how to do this. If I had a new column, it adds additional headers beneath my top row. Or, my columns appear to the LEFT of the data, not the right. Can some please post some simple instructions that will make my simple matrix look like this:

Name Jan Feb Total %

John 5 6 11 60%

Mary 3 4 7 40%

Total 8 10 18 100%

% 40% 60% 100% 100%

I am so stuck on this I can pull my hair out.

Thanks!

Michael

p.s. I really hope the next version of SSRS has a simple "Sub-total %" option that you can enable just like the sub-total column.

I know the feeling...I am having the same issues

The only answer I came up with is do ALL calculations in a Stored Procedure or in SQL and some kind of way arrange the data in a way that will display your info right in your report.

For example, I had to do a Variance (orginally it was a percent like yours, but they settled on a difference between the last two columns) and instead of doing it in Reporting Services, I created a cursor in SQL and then just did a select top (1) * SQL as dataset. Cheesy I know what what else to do with so little built in stuff within Reporting Services?

Hope that helps...

|||

My simple example on this page is only a small portion of the types of matrices I need to generate. Most are dynamic, have dynamic columns, and various sub-groupings. The code to generate all the sub-totals and the primary totals would be miserable. I've done this before, and one of the reasons I moved to SSRS was because it made it so easy to create pivot tables out of my data and not have to write SQL to handle every possible situation. Your suggestion is probably reasonable, but I hate the idea of doing it manually, and manually adding the various columns and rows to display the data, rather than letting the matrix do all the work (which was my initial goal). I am still hopeful I can figure this out. If anyone else has a suggestion, please respond. I just can't believe that this is so difficult with SSRS.

Mike

|||

If you find a better way using SSRS please post...I am sure all would like to know!

Thanks...

Percentage Column AND Row

Hello,

I have a matrix that looks as follows:

Name Jan Feb Total

John 5 6 11

Mary 3 4 7

Total 8 10 18

I want to add a percent column to the RIGHT of the total, and also on the bottom row. I can't find any clear examples of how to do this. If I had a new column, it adds additional headers beneath my top row. Or, my columns appear to the LEFT of the data, not the right. Can some please post some simple instructions that will make my simple matrix look like this:

Name Jan Feb Total %

John 5 6 11 60%

Mary 3 4 7 40%

Total 8 10 18 100%

% 40% 60% 100% 100%

I am so stuck on this I can pull my hair out.

Thanks!

Michael

p.s. I really hope the next version of SSRS has a simple "Sub-total %" option that you can enable just like the sub-total column.

I know the feeling...I am having the same issues

The only answer I came up with is do ALL calculations in a Stored Procedure or in SQL and some kind of way arrange the data in a way that will display your info right in your report.

For example, I had to do a Variance (orginally it was a percent like yours, but they settled on a difference between the last two columns) and instead of doing it in Reporting Services, I created a cursor in SQL and then just did a select top (1) * SQL as dataset. Cheesy I know what what else to do with so little built in stuff within Reporting Services?

Hope that helps...

|||

My simple example on this page is only a small portion of the types of matrices I need to generate. Most are dynamic, have dynamic columns, and various sub-groupings. The code to generate all the sub-totals and the primary totals would be miserable. I've done this before, and one of the reasons I moved to SSRS was because it made it so easy to create pivot tables out of my data and not have to write SQL to handle every possible situation. Your suggestion is probably reasonable, but I hate the idea of doing it manually, and manually adding the various columns and rows to display the data, rather than letting the matrix do all the work (which was my initial goal). I am still hopeful I can figure this out. If anyone else has a suggestion, please respond. I just can't believe that this is so difficult with SSRS.

Mike

|||

If you find a better way using SSRS please post...I am sure all would like to know!

Thanks...