How do I add a Percentage Column after the Total on a Matrix?

Hi. I am looking to create a Matrix-based Report in SQL Server 2005 Reporting Services. I have my query, and a dynamic number of both rows and columns. I have Totals on the "bottom" and "right".

What I'd like to do is add a Percentage column to the right of the right-based Totals column. So the columns would read Col1, Col2, .., ColN, Total, Percentage.

Is this possible?

Thanks.

You could do something like - matrixcoltotal / matrixtotal and set your Format Code to P0 on the properties window.

Your expression will look something like this. =Sum(Fields!ReportField.Value,"MatrixGroup")/Sum(Fields!ReportField.Value,"dataset"). Dont forget to change the FormatCode.

|||How would I place this column to the RIGHT of the Subtotal column?

|||

Add an invisible column group which will be grouped by the percentage expression and add the subtotal to this colum group have the which will give you the sum of all individual percentages.

Shyam

|||Here is the error message I am receiving:

"A group expression for the matrix 'matrix1' includes an aggregate function. Aggregate functions cannot be used in group expressions."

I created a new Column group, outside of my month-by-month column group, and made it invisible. I do see a new "Total" to the right....however when I put in my sum(value, "InnerRowGroup")/sum(value, "OuterRowGroup") as the group expression, I get the above error.

Thank you both greatly for your help. I feel about 75% towards getting this to work.

Jason

|||

No, you dont have to group by the aggregate which is an obvious logical error. Just group by the expression without the sum function which would be:

Fields!InnerGroupField/Fields!OuterGroupField

Then using the subtoal would automatically mean a sum which is what you want I guess.

Shyam

|||What if the percentage I want to calculate is the ratio between two columns? Is this possible, or will I have to pivot the data in the database before I send it to SSRS? This would suck since I have to completely revamp the stored proc.

How do I add a Percentage Column after the Total on a Matrix?

Hi. I am looking to create a Matrix-based Report in SQL Server 2005 Reporting Services. I have my query, and a dynamic number of both rows and columns. I have Totals on the "bottom" and "right".

What I'd like to do is add a Percentage column to the right of the right-based Totals column. So the columns would read Col1, Col2, .., ColN, Total, Percentage.

Is this possible?

Thanks.

You could do something like - matrixcoltotal / matrixtotal and set your Format Code to P0 on the properties window.

Your expression will look something like this. =Sum(Fields!ReportField.Value,"MatrixGroup")/Sum(Fields!ReportField.Value,"dataset"). Dont forget to change the FormatCode.

|||How would I place this column to the RIGHT of the Subtotal column?

|||

Add an invisible column group which will be grouped by the percentage expression and add the subtotal to this colum group have the which will give you the sum of all individual percentages.

Shyam

|||Here is the error message I am receiving:

"A group expression for the matrix 'matrix1' includes an aggregate function. Aggregate functions cannot be used in group expressions."

I created a new Column group, outside of my month-by-month column group, and made it invisible. I do see a new "Total" to the right....however when I put in my sum(value, "InnerRowGroup")/sum(value, "OuterRowGroup") as the group expression, I get the above error.

Thank you both greatly for your help. I feel about 75% towards getting this to work.

Jason

|||

No, you dont have to group by the aggregate which is an obvious logical error. Just group by the expression without the sum function which would be:

Fields!InnerGroupField/Fields!OuterGroupField

Then using the subtoal would automatically mean a sum which is what you want I guess.

Shyam

|||What if the percentage I want to calculate is the ratio between two columns? Is this possible, or will I have to pivot the data in the database before I send it to SSRS? This would suck since I have to completely revamp the stored proc.

How do I add a Percentage Column after the Total on a Matrix?

Hi. I am looking to create a Matrix-based Report in SQL Server 2005 Reporting Services. I have my query, and a dynamic number of both rows and columns. I have Totals on the "bottom" and "right".

What I'd like to do is add a Percentage column to the right of the right-based Totals column. So the columns would read Col1, Col2, .., ColN, Total, Percentage.

Is this possible?

Thanks.

You could do something like - matrixcoltotal / matrixtotal and set your Format Code to P0 on the properties window.

Your expression will look something like this. =Sum(Fields!ReportField.Value,"MatrixGroup")/Sum(Fields!ReportField.Value,"dataset"). Dont forget to change the FormatCode.

|||How would I place this column to the RIGHT of the Subtotal column?|||

Add an invisible column group which will be grouped by the percentage expression and add the subtotal to this colum group have the which will give you the sum of all individual percentages.

Shyam

|||Here is the error message I am receiving:

"A group expression for the matrix 'matrix1' includes an aggregate function. Aggregate functions cannot be used in group expressions."

I created a new Column group, outside of my month-by-month column group, and made it invisible. I do see a new "Total" to the right....however when I put in my sum(value, "InnerRowGroup")/sum(value, "OuterRowGroup") as the group expression, I get the above error.

Thank you both greatly for your help. I feel about 75% towards getting this to work.

Jason|||

No, you dont have to group by the aggregate which is an obvious logical error. Just group by the expression without the sum function which would be:

Fields!InnerGroupField/Fields!OuterGroupField

Then using the subtoal would automatically mean a sum which is what you want I guess.

Shyam

|||What if the percentage I want to calculate is the ratio between two columns? Is this possible, or will I have to pivot the data in the database before I send it to SSRS? This would suck since I have to completely revamp the stored proc.

How could I make sure order total is at the bottom of the page?

I have to show mutiplie orders in a report. Order total of one order has to show at the bottom of the page. Now if the order has only one or two items, the order total won't go to the bottom of the page. How can I make sure this order total is at the bottom of the page just as Crystal Report do( set Group Footer at the bottom of the page).

one way you could do it, is to use a textbox, and place it at the bottom of the page then set the value of the textbox to the value of the total in the table. Once you have done that you could hide the specific textbox in the table.
The other thing you could try is setting the height of the table to be exactly the height of the page - margins.|||No. It doesn't work|||Heres how I do,..
Drag a new table onto your design surface, Delete the Header and Detail rows,...
Your left with a footer, place it at the bototm of your report, and It will persist at the bottom, hopefully. And it wont go labeling off multiple rows, it will act as your "Grand Total" area if ya like

How change "Total" caption on hierarchy

Hello,

I want to change the "Total" Caption in hierarchy by "Total by Client" for example. I would know if is it possible or not? And how do this if it's possible.

Thanks,

Betty

Betty,

you want to rename the "All Level"? There is a property for that (in the dimension's properties "AttributeAllMemberName")...

sql

How can same query be way slower on an identical database?

Hi,
I have SQL 2000 server on a P4 box. There are 8 - 10 databases total
and they are all backup/reporting purposes. So there are no other
users.
I have 2 identical databases with identical tables and identical
stored procedures. Only the data is different. Let me name them DB1
and DB2.
I execute the SP1 on DB1, which has more records than DB2, and it
gives me the results in 2 - 5 minutes.
I execute the SP2 (which is the exact copy of SP1) on DB2, which has
less records than the DB1, and it never gives me the result. After
hours it throws an error that is not specific.
I put indexes, tried to tune it etc. But no use. I still add some
records on both and run the query on Query Analyzer, and get results
for DB1 but not for DB2.
Why would it do this? Is this a database issue or the server issue you
think?
Thanks in advance.
- Denwell... they're not really identical if the rows are different... how have
you verified that schema is the same? Are you just 'sure' or have you used a
tool like DBCompare from redgate?
Also... have you looked at the estimate plans? Do they come back the same?
If not... you might have a statistics difference between the DB's... even if
schema is the same it's certainly possible that auto created stats could be
differernt between the db's...
hope this helps a bit,
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"DenoxiS" <google@.deniznet.com> wrote in message
news:d2478899.0402061716.7ffce2d2@.posting.google.com...
> Hi,
> I have SQL 2000 server on a P4 box. There are 8 - 10 databases total
> and they are all backup/reporting purposes. So there are no other
> users.
> I have 2 identical databases with identical tables and identical
> stored procedures. Only the data is different. Let me name them DB1
> and DB2.
> I execute the SP1 on DB1, which has more records than DB2, and it
> gives me the results in 2 - 5 minutes.
> I execute the SP2 (which is the exact copy of SP1) on DB2, which has
> less records than the DB1, and it never gives me the result. After
> hours it throws an error that is not specific.
> I put indexes, tried to tune it etc. But no use. I still add some
> records on both and run the query on Query Analyzer, and get results
> for DB1 but not for DB2.
> Why would it do this? Is this a database issue or the server issue you
> think?
> Thanks in advance.
> - Den|||If you are performing any kind of iterative calculation in the stored
procedure it could be that there is data on DB2 that is causing an infinite
loop. If the data on DB1 is "good" it would not have this problem. I have
run into this most often when handling date calculations.
"Brian Moran" <brian@.solidqualitylearning.com> wrote in message
news:u51B%23TS7DHA.3420@.TK2MSFTNGP11.phx.gbl...
> well... they're not really identical if the rows are different... how have
> you verified that schema is the same? Are you just 'sure' or have you used
a
> tool like DBCompare from redgate?
> Also... have you looked at the estimate plans? Do they come back the same?
> If not... you might have a statistics difference between the DB's... even
if
> schema is the same it's certainly possible that auto created stats could
be
> differernt between the db's...
> hope this helps a bit,
> --
> Brian Moran
> Principal Mentor
> Solid Quality Learning
> SQL Server MVP
> http://www.solidqualitylearning.com
>
> "DenoxiS" <google@.deniznet.com> wrote in message
> news:d2478899.0402061716.7ffce2d2@.posting.google.com...
> > Hi,
> >
> > I have SQL 2000 server on a P4 box. There are 8 - 10 databases total
> > and they are all backup/reporting purposes. So there are no other
> > users.
> >
> > I have 2 identical databases with identical tables and identical
> > stored procedures. Only the data is different. Let me name them DB1
> > and DB2.
> >
> > I execute the SP1 on DB1, which has more records than DB2, and it
> > gives me the results in 2 - 5 minutes.
> >
> > I execute the SP2 (which is the exact copy of SP1) on DB2, which has
> > less records than the DB1, and it never gives me the result. After
> > hours it throws an error that is not specific.
> >
> > I put indexes, tried to tune it etc. But no use. I still add some
> > records on both and run the query on Query Analyzer, and get results
> > for DB1 but not for DB2.
> >
> > Why would it do this? Is this a database issue or the server issue you
> > think?
> >
> > Thanks in advance.
> >
> > - Den
>|||Take a look at the selectivity of each set of tables. Is the data very similar ie is the distribution of data similar between the tables? Taking a better look at the generated query plans will provide you with the answers you are seeking.