How do I add a static column to a matrix?

Folks,
I'm getting data back from the database in the following format:
row_id | col_Name | col_Value
0 | ID | 10
0 | Name | First
0 | Jan | 1
0 | Feb | 2
1 | ID | 20
1 | Name | Seconds
1 | Jan | 10
1 | Feb | 20
...
We are grouping based on the row_id db column, thus creating dynamic columns
for ID, Name, Jan, Feb, etc.
However, I need to customize the ID and Name column which is, to my
understanding, not something that can be done if they are dynamic columns in
the matrix.
So, what I was thinking of doing was to make two static columns, ID and
Name, and then filter those rows out of the dynamic columns. However, when I
make a second ColumnGrouping in the RDL file for these, and define the two
static columns, what happens is that they appear set up such that these
columns would be in a separate row, rather than off to the left of the rows
of dynamic columns (hopefully I'm explaining this well enough).
What would be the best way to accomplish this, presuming that we can't
simply have the returned data changed to a better format?
Thanks!
randyAssuming you want to transform your table to:
ID | Name | Jan | Feb
10 | First | 1 | 2
20 | Seconds | 10 | 20
etc.
The easiest is to tweak your SQL, but I am assuming this is the result of a
SP that you can't since you said:
> What would be the best way to accomplish this, presuming that we can't
> simply have the returned data changed to a better format?
I'm not sure if you can, but you might have to do this with a custom code
module to transform your data and then bind the report to the dataset from
code. Maybe someone else knows way to modify the underlying dataset
structure.
"Randy Chapman" <RandyChapman@.discussions.microsoft.com> wrote in message
news:5F87A271-7591-4453-975F-06394CC8E2A8@.microsoft.com...
> Folks,
> I'm getting data back from the database in the following format:
> row_id | col_Name | col_Value
> 0 | ID | 10
> 0 | Name | First
> 0 | Jan | 1
> 0 | Feb | 2
> 1 | ID | 20
> 1 | Name | Seconds
> 1 | Jan | 10
> 1 | Feb | 20
> ...
> We are grouping based on the row_id db column, thus creating dynamic
> columns
> for ID, Name, Jan, Feb, etc.
> However, I need to customize the ID and Name column which is, to my
> understanding, not something that can be done if they are dynamic columns
> in
> the matrix.
> So, what I was thinking of doing was to make two static columns, ID and
> Name, and then filter those rows out of the dynamic columns. However,
> when I
> make a second ColumnGrouping in the RDL file for these, and define the two
> static columns, what happens is that they appear set up such that these
> columns would be in a separate row, rather than off to the left of the
> rows
> of dynamic columns (hopefully I'm explaining this well enough).
> What would be the best way to accomplish this, presuming that we can't
> simply have the returned data changed to a better format?
> Thanks!
> randy
>

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 do I access MSDE?

Hi,
I don't have Visual Studio, so I'm using web matrix and MSDE. My question is, how do I get to MSDE? I don't have the enterprise manager that comes with SQL Server. Thanks
amitDownload the SQL Server Web Data Administrator
Easily manage your SQL Server and MSDE data, wherever you are. Use this tool to create and edit databases, manage users and roles, export and import database schema and data, and more—all from your favorite Web browser. Available from the Microsoft Download Center

http://www.microsoft.com/sql

I have not tried it yet, but it might be what you need.

KM|||Check outthis link which has a free Query Analyzer tool. Also here is aweb admin tool from microsoft. HTH|||That's just what I need, thanks to both!sql