My Matrix presently shows results as follows.
column 1 column 2
row 1 23
row 2 34 34
How would I make the default null value to display as zero. Eg. Row 1, Column 1 would have a value of 0You will need an expression:
=iif(First(Fields!<FieldName>.Value) is Nothing, 0,
First(Fields!<FieldName>.Value))
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"SAcanuck" <SAcanuck@.discussions.microsoft.com> wrote in message
news:9D9A293F-1E84-439E-B1D5-EF6E76DFD068@.microsoft.com...
> My Matrix presently shows results as follows.
> column 1 column 2
> row 1 23
> row 2 34 34
> How would I make the default null value to display as zero. Eg. Row 1,
Column 1 would have a value of 0
Showing posts with label follows. Show all posts
Showing posts with label follows. Show all posts
Monday, March 26, 2012
Monday, March 19, 2012
Matrix Report - Subtotals
Good moring,
I am working on my first matrix report and trying to add in a total for a
group and rows
Table is as follows:
Account Manager Job Number Jan Sales Feb Sales
Peter J54455 598 60
Peter J65559 500 70
David J76666 400 80
Jane J74445 700 90
I would like to have:
1) a subtotal row when the Account Manager changes.
2) a total for each row.
Example:
Account Manager Job Number Jan Sales Feb Sales Total
Peter J54455 500 60
560
Peter J65559 500 70
570
Total Peter 1000 130
1130
David J76666 400 80
480
Total David 400 80
480
Jane J74445 700 90
790
Total Jane 700 90
790
I know this is inherent in the matrix report, however I have spent hours
trying to figure out and still cannot determine how to achieve both of these
2 things (simple as they may be).
Can anyone help?
Thanks JamesAs an amendment,
I have discovered how to add row total so ignore that part.
As to the subtotal on the Account Manager I still have an issue with that.
I tried right clicking on the Account Manager name and choose subtotal.
However this puts a grand total for all account Managers, not a subtotal for
each account manager. Any ideas'?
I am working on my first matrix report and trying to add in a total for a
group and rows
Table is as follows:
Account Manager Job Number Jan Sales Feb Sales
Peter J54455 598 60
Peter J65559 500 70
David J76666 400 80
Jane J74445 700 90
I would like to have:
1) a subtotal row when the Account Manager changes.
2) a total for each row.
Example:
Account Manager Job Number Jan Sales Feb Sales Total
Peter J54455 500 60
560
Peter J65559 500 70
570
Total Peter 1000 130
1130
David J76666 400 80
480
Total David 400 80
480
Jane J74445 700 90
790
Total Jane 700 90
790
I know this is inherent in the matrix report, however I have spent hours
trying to figure out and still cannot determine how to achieve both of these
2 things (simple as they may be).
Can anyone help?
Thanks JamesAs an amendment,
I have discovered how to add row total so ignore that part.
As to the subtotal on the Account Manager I still have an issue with that.
I tried right clicking on the Account Manager name and choose subtotal.
However this puts a grand total for all account Managers, not a subtotal for
each account manager. Any ideas'?
Friday, March 9, 2012
Matrix Column Grouping
What would be the best solution to accomplish the following?
The report would look as follows
Year 1 Total Year 2
Month 1 Month 2
Total items 25 12 5
Item type 1 8 9 4
Item type 2 7 2 3
Item type 3 9 5 1
When you click on the Year 1 totals, the detail months should expand
and shift everything to the right.
Just like in a table, you can hide the detail rows of a table, I would
like to hide/unhide the detail columns of a Matrix. However, when I go
to toggle the visibility of the column like I toggle the visibility of
a row in a table, it only hides the column header (obviously because
it's only toggling the visibility of the textbox itself instead of the
entire column). Is there any way to toggle the visibility of a column
in the same manner you do a row in table?
This could also be accomplished with a Table, utilizing static columns,
but that is not ideal.
In Excel, all that needs to be done is to is to group the headers, and
use the new group field to display the PivotTable headers.
Is there an easier/better way to accomplish what I'm trying to do?After hours of searching, shortly after I wrote this, I found the
answer here:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rshowto/htm/hrs_designer_v1_0zvx.asp
So nevermind.
The report would look as follows
Year 1 Total Year 2
Month 1 Month 2
Total items 25 12 5
Item type 1 8 9 4
Item type 2 7 2 3
Item type 3 9 5 1
When you click on the Year 1 totals, the detail months should expand
and shift everything to the right.
Just like in a table, you can hide the detail rows of a table, I would
like to hide/unhide the detail columns of a Matrix. However, when I go
to toggle the visibility of the column like I toggle the visibility of
a row in a table, it only hides the column header (obviously because
it's only toggling the visibility of the textbox itself instead of the
entire column). Is there any way to toggle the visibility of a column
in the same manner you do a row in table?
This could also be accomplished with a Table, utilizing static columns,
but that is not ideal.
In Excel, all that needs to be done is to is to group the headers, and
use the new group field to display the PivotTable headers.
Is there an easier/better way to accomplish what I'm trying to do?After hours of searching, shortly after I wrote this, I found the
answer here:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rshowto/htm/hrs_designer_v1_0zvx.asp
So nevermind.
Matrix calculation
Hi,
I have a matrix which looks as follows:
Actuals Budget
Item 1 20 50
Item 2 45 30
Item 3 20 15
I would like to add a column with the difference between actuals and budget.
Actuals/budget is 1 group in the matrix being the dimensionname.
How can I calculate the difference, because if I just use the subtotal
function it adds both totals together and I need to have the difference
between them?
Please help...Right click on the cells that contain the data ("Details" as they are
called). Press on the "Add Column" button. In the new column, click
on the details part where the data goes and then put and expression in
there that will take the two data points you want and subtract them in
the way that you want.|||Thanks for the update, I have both actuals and budget in the
"matrix1_ColumnGroup4", how can I then subtract both fields from eachother?
How can I indicate to which field the database needs to look at?
Please help as this is important for me...
"SQL Guy" wrote:
> Right click on the cells that contain the data ("Details" as they are
> called). Press on the "Add Column" button. In the new column, click
> on the details part where the data goes and then put and expression in
> there that will take the two data points you want and subtract them in
> the way that you want.
>|||Ok so you have a new column, in the cell put an expression that will
subtract the two like this:
=Fields!Budget.Value-Fields!Actuals.Value
That should automatically take the budget and actual from the current
row and subtract them from each other. You would also put this in your
select statement. For example
Select actual,budget,(actual-budget) as difference
from YOUR_TABLE_NAME
where ......
Then you would have an already calculated field to use in your
matrix. Really they both go the same way. My preference is to do it
on the SQL side rather than put a formula in the matrix. It's up to
you. Hope it helps!|||I had a question, why would you even need a matrix for this? I think
a table would suffice.|||I had a question, why would you even need a matrix for this? I think
a table would suffice.|||On Nov 5, 5:41 am, DHL <D...@.discussions.microsoft.com> wrote:
> Hi,
> I have a matrix which looks as follows:
> Actuals Budget
> Item 1 20 50
> Item 2 45 30
> Item 3 20 15
> I would like to add a column with the difference between actuals and budget.
> Actuals/budget is 1 group in the matrix being the dimensionname.
> How can I calculate the difference, because if I just use the subtotal
> function it adds both totals together and I need to have the difference
> between them?
> Please help...
If this is a Matrix, this implies your data is set up like:
Items, Type, Amount
Item 1, Actuals, 20
Item 1, Budget, 50
Item 2, Actuals, 45
...
which means you can't actually do a single expression of Fields!
Actual.Value - Fields!Budget.Value.
Two ways I would do this:
1. I would add a new field to the dataset, Amount2, with an
expression of
=IIF( Fields!Type = "Budget", Fields!Amount, IIF( Fields!Type ="Actuals", -1 * Fields!Amount, Nothing ) )
This flips the sign of your budget, so that the Sum of types Actuals
and Budget gets you the difference you asked for. Then, in the
Matrix, add a subtotal on the Columns (group matrix1_Type) and use the
following expression in the Details:
=IIF( InScope("matrix1_Type"), Sum(Fields!Amount.Value), Sum(Fields!
Amount2.Value) )
What happens here is that when the Matrix is calculating the Sum() in
each column, it's doing so under the Scope of that column
("matrix1_Type"). The Total Sum at the end of the Column is done at
the Matrix Scope, so InScope() returns false, and returns the Sum of
the custom field instead.
2. Or, change the Data tab's SQL SELECT to include a union with the
calculated amount
SELECT T.ITEMS, T.TYPE, T.AMOUNT FROM TABLE_NAME T
UNION
SELECT T.ITEMS, 'Total' AS TYPE,
SUM( CASE T.TYPE
WHEN 'Actuals' THEN T.AMOUNT
WHEN 'Budget' THEN -T.AMOUNT ELSE NULL END ) AS AMOUNT
FROM TABLE_NAME T
WHERE T.TYPE IN ( 'Actuals', 'Budget' )
GROUP BY T.ITEMS
then just refresh your existing Matrix and a Totals column will appear
with the total you requested.
-- Scott
I have a matrix which looks as follows:
Actuals Budget
Item 1 20 50
Item 2 45 30
Item 3 20 15
I would like to add a column with the difference between actuals and budget.
Actuals/budget is 1 group in the matrix being the dimensionname.
How can I calculate the difference, because if I just use the subtotal
function it adds both totals together and I need to have the difference
between them?
Please help...Right click on the cells that contain the data ("Details" as they are
called). Press on the "Add Column" button. In the new column, click
on the details part where the data goes and then put and expression in
there that will take the two data points you want and subtract them in
the way that you want.|||Thanks for the update, I have both actuals and budget in the
"matrix1_ColumnGroup4", how can I then subtract both fields from eachother?
How can I indicate to which field the database needs to look at?
Please help as this is important for me...
"SQL Guy" wrote:
> Right click on the cells that contain the data ("Details" as they are
> called). Press on the "Add Column" button. In the new column, click
> on the details part where the data goes and then put and expression in
> there that will take the two data points you want and subtract them in
> the way that you want.
>|||Ok so you have a new column, in the cell put an expression that will
subtract the two like this:
=Fields!Budget.Value-Fields!Actuals.Value
That should automatically take the budget and actual from the current
row and subtract them from each other. You would also put this in your
select statement. For example
Select actual,budget,(actual-budget) as difference
from YOUR_TABLE_NAME
where ......
Then you would have an already calculated field to use in your
matrix. Really they both go the same way. My preference is to do it
on the SQL side rather than put a formula in the matrix. It's up to
you. Hope it helps!|||I had a question, why would you even need a matrix for this? I think
a table would suffice.|||I had a question, why would you even need a matrix for this? I think
a table would suffice.|||On Nov 5, 5:41 am, DHL <D...@.discussions.microsoft.com> wrote:
> Hi,
> I have a matrix which looks as follows:
> Actuals Budget
> Item 1 20 50
> Item 2 45 30
> Item 3 20 15
> I would like to add a column with the difference between actuals and budget.
> Actuals/budget is 1 group in the matrix being the dimensionname.
> How can I calculate the difference, because if I just use the subtotal
> function it adds both totals together and I need to have the difference
> between them?
> Please help...
If this is a Matrix, this implies your data is set up like:
Items, Type, Amount
Item 1, Actuals, 20
Item 1, Budget, 50
Item 2, Actuals, 45
...
which means you can't actually do a single expression of Fields!
Actual.Value - Fields!Budget.Value.
Two ways I would do this:
1. I would add a new field to the dataset, Amount2, with an
expression of
=IIF( Fields!Type = "Budget", Fields!Amount, IIF( Fields!Type ="Actuals", -1 * Fields!Amount, Nothing ) )
This flips the sign of your budget, so that the Sum of types Actuals
and Budget gets you the difference you asked for. Then, in the
Matrix, add a subtotal on the Columns (group matrix1_Type) and use the
following expression in the Details:
=IIF( InScope("matrix1_Type"), Sum(Fields!Amount.Value), Sum(Fields!
Amount2.Value) )
What happens here is that when the Matrix is calculating the Sum() in
each column, it's doing so under the Scope of that column
("matrix1_Type"). The Total Sum at the end of the Column is done at
the Matrix Scope, so InScope() returns false, and returns the Sum of
the custom field instead.
2. Or, change the Data tab's SQL SELECT to include a union with the
calculated amount
SELECT T.ITEMS, T.TYPE, T.AMOUNT FROM TABLE_NAME T
UNION
SELECT T.ITEMS, 'Total' AS TYPE,
SUM( CASE T.TYPE
WHEN 'Actuals' THEN T.AMOUNT
WHEN 'Budget' THEN -T.AMOUNT ELSE NULL END ) AS AMOUNT
FROM TABLE_NAME T
WHERE T.TYPE IN ( 'Actuals', 'Budget' )
GROUP BY T.ITEMS
then just refresh your existing Matrix and a Totals column will appear
with the total you requested.
-- Scott
Wednesday, March 7, 2012
matrix - change color based on data trend up or down
I have a dataset with customer, date and count columns. Then I have a matrix
based on that information as follows:
Oct 2007 Sep 2007 Aug 2007
Customer A 123 432 959
Customer B 984 394 345
Customer C 2459 2335 234
I would like to have the color of the count red if the number is lower for
that customer for the next date (i.e. 123) and green if the number is higher
for the next date (i.e. 984).
Any idea how I do this?
StephanieOn Oct 1, 9:49 am, Stephanie <Stepha...@.discussions.microsoft.com>
wrote:
> I have a dataset with customer, date and count columns. Then I have a matrix
> based on that information as follows:
> Oct 2007 Sep 2007 Aug 2007
> Customer A 123 432 959
> Customer B 984 394 345
> Customer C 2459 2335 234
> I would like to have the color of the count red if the number is lower for
> that customer for the next date (i.e. 123) and green if the number is higher
> for the next date (i.e. 984).
> Any idea how I do this?
> Stephanie
You might be able to use 'iif' in conjunction with the 'Previous'
function to set the background color. I'm not sure of the exact
syntax, but this might lead you in the right direction.
=iif(Previous(Fields!Count.Value) < Fields!Count.Value, "Green",
"Red")
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant
based on that information as follows:
Oct 2007 Sep 2007 Aug 2007
Customer A 123 432 959
Customer B 984 394 345
Customer C 2459 2335 234
I would like to have the color of the count red if the number is lower for
that customer for the next date (i.e. 123) and green if the number is higher
for the next date (i.e. 984).
Any idea how I do this?
StephanieOn Oct 1, 9:49 am, Stephanie <Stepha...@.discussions.microsoft.com>
wrote:
> I have a dataset with customer, date and count columns. Then I have a matrix
> based on that information as follows:
> Oct 2007 Sep 2007 Aug 2007
> Customer A 123 432 959
> Customer B 984 394 345
> Customer C 2459 2335 234
> I would like to have the color of the count red if the number is lower for
> that customer for the next date (i.e. 123) and green if the number is higher
> for the next date (i.e. 984).
> Any idea how I do this?
> Stephanie
You might be able to use 'iif' in conjunction with the 'Previous'
function to set the background color. I'm not sure of the exact
syntax, but this might lead you in the right direction.
=iif(Previous(Fields!Count.Value) < Fields!Count.Value, "Green",
"Red")
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant
Subscribe to:
Posts (Atom)