Max date in a row

Hi,
I would like to know how to find the max of 4 dates in one row.
so if we have ssn, date1, date2, date3, date4
456123789 12/3/2005, 12/5/2005,11/8/2005,1/2/2006
my output should give me
456123789, 1/2/2006
ThanksCREATE TABLE #foo
(
ssn CHAR(9),
d1 SMALLDATETIME,
d2 SMALLDATETIME,
d3 SMALLDATETIME,
d4 SMALLDATETIME
);
SET NOCOUNT ON;
INSERT #foo SELECT '111111111', '20050101', '20050505', '20050603',
'20050401';
INSERT #foo SELECT '222222222', '20050601', '20050505', '20050203',
'20050201';
INSERT #foo SELECT '333333333', '20050601', '20050601', '20050203',
'20050201';
INSERT #foo SELECT '333333333', '20050601', '20050602', '20050603',
'20050604';
SELECT ssn, d = MAX(d)
FROM
(
SELECT ssn, d = d1 FROM #foo
UNION ALL SELECT ssn, d = d2 FROM #foo
UNION ALL SELECT ssn, d = d3 FROM #foo
UNION ALL SELECT ssn, d = d4 FROM #foo
) x
GROUP BY ssn;
DROP TABLE #foo;
Can I recommend this structure instead:
CREATE TABLE dbo.People
(
ssn CHAR(9) PRIMARY KEY
);
CREATE TABLE dbo.PeopleDates
(
ssn CHAR(9) NOT NULL FOREIGN KEY REFERENCES dbo.People(ssn),
dateInstance TINYINT NOT NULL, -- check for 1-4?
dateValue SMALLDATETIME
);
INSERT dbo.People
SELECT '111111111'
UNION ALL SELECT '222222222'
UNION ALL SELECT '333333333';
INSERT dbo.PeopleDates
SELECT '111111111', 1, '20050101'
UNION ALL SELECT '111111111', 2, '20050505';
/* ...... */
More work up front, and slightly larger storage cost (though you could
offset that a bit by using an INT for the key), but it is more relational in
nature, and look how easy it makes your queries:
SELECT ssn, MAX(dateValue)
FROM dbo.PeopleDates
GROUP BY ssn;
And as well as making this type of query much simpler, you don't have to go
change things when you add a 5th date. (In your current model, you need to
change the schema *and* change the query.)
In addition, I encourage not thinking about dates in these string formats,
or at least when you are explaining an issue to other people, to avoid
confusion and ambiguity. Are your dates:
(a) Mar 12 2005, May 12 2005, Aug 11 2005, Feb 1 2006
or
(b) Dec 3 2005, Dec 5 2005, Nov 8 2005, Jan 2 2005
?
In this case, it was easy to pick out the latest date you expected in the
result, because it was the only one in 2006. But if you included 2/1/2006
as well, I'd be at a loss without requesting further clarification.
You should strive to use string representations of dates that are 100%
unambiguous to both people and code. For example, 'YYYYMMDD' will always
work, no matter who you're talking to or what your SQL Server's regional
settings, dateformat, language, etc.
A
"Amit" <Amit@.discussions.microsoft.com> wrote in message
news:8E25E6E0-E4E0-4739-908A-B995314FFFF6@.microsoft.com...
> Hi,
> I would like to know how to find the max of 4 dates in one row.
> so if we have ssn, date1, date2, date3, date4
> 456123789 12/3/2005, 12/5/2005,11/8/2005,1/2/2006
> my output should give me
> 456123789, 1/2/2006
> Thanks|||Amit wrote:
> Hi,
> I would like to know how to find the max of 4 dates in one row.
> so if we have ssn, date1, date2, date3, date4
> 456123789 12/3/2005, 12/5/2005,11/8/2005,1/2/2006
> my output should give me
> 456123789, 1/2/2006
> Thanks
Table design aside, you can use a scalar function here:
Select
ssn,
dbo.fnGetMaxDate(date1, date2, date3, date4)
From
dbo.MyTable
Create Function dbo.fnGetMaxDate (
@.date1 datetime, @.date2 datetime, @.date3 datetime, @.date4 datetime )
Returns datetime
as
Begin
declare @.datefinal datetime
set @.datefinal = @.date1
If @.date2 > @.datefinal
set @.datefinal = @.date2
If @.date3 > @.datefinal
set @.datefinal = @.date3
If @.date4 > @.datefinal
set @.datefinal = @.date4
Return @.datefinal
End
David Gugick - SQL Server MVP
Quest Software|||SELECT SSN,
CASE WHEN date1 > date2
AND date1 > date3
AND date1 > date4
THEN date1
WHEN date2 > date3
AND date2 > date4
THEN date2
WHEN date3 > date4
THEN date3
ELSE date4
END as MaxDate
FROM SomeTable
Roy Harvey
Beacon Falls, CT
On Thu, 2 Mar 2006 11:35:02 -0800, "Amit"
<Amit@.discussions.microsoft.com> wrote:

>Hi,
>I would like to know how to find the max of 4 dates in one row.
>so if we have ssn, date1, date2, date3, date4
> 456123789 12/3/2005, 12/5/2005,11/8/2005,1/2/2006
>my output should give me
>456123789, 1/2/2006
>Thanks|||Another approach:
SELECT ssn,
MAX( CASE n WHEN 1 THEN dt1
WHEN 2 THEN dt2
WHEN 3 THEN dt3
WHEN 4 THEN dt4
END )
FROM tbl, ( SELECT 1 UNION SELECT 2 UNION
SELECT 3 UNION SELECT 4 ) N ( n )
GROUP BY ssn ;
Anith

Matrix-last column not subtotal of row

	Destination Country		Valid Mail
	Austria	Belgium
Austria	1.9	1.7	1
	23	23
Belgium	1.7	9.7	2
	23	23
Valid Mail	1	2	3

hi,

i have some question for matrix:

how to display 'Valid Mail' which is not subtotal for each row.

how to display 'Valid Mail' which is not subtotal for each column.

if reporting services do not have this function, what should i do in order to get the same result using reporting services?

Thanks

Best Regards,

Penny

Hi Penny,

Were you able to resolve this issue?

-Matt

|||

Hi

It seems to me that you may have a similar problem to the one i had.

Please see the following it may help..
http://msdn.microsoft.com/newsgroups/default.aspx?&query=matrix+subtotal&lang=en&cr=US&guid=&sloc=en-us&dg=microsoft.public.sqlserver.reportingsvcs&p=1&tid=fff4d8a4-5854-4783-9ad7-d73dd3c3c5cb

Matrix-last column not subtotal of row

	Destination Country		Valid Mail
	Austria	Belgium
Austria	1.9	1.7	1
	23	23
Belgium	1.7	9.7	2
	23	23
Valid Mail	1	2	3

hi,

i have some question for matrix:

how to display 'Valid Mail' which is not subtotal for each row.

how to display 'Valid Mail' which is not subtotal for each column.

if reporting services do not have this function, what should i do in order to get the same result using reporting services?

Thanks

Best Regards,

Penny

Hi Penny,

Were you able to resolve this issue?

-Matt

|||

Hi

It seems to me that you may have a similar problem to the one i had.

Please see the following it may help..
http://msdn.microsoft.com/newsgroups/default.aspx?&query=matrix+subtotal&lang=en&cr=US&guid=&sloc=en-us&dg=microsoft.public.sqlserver.reportingsvcs&p=1&tid=fff4d8a4-5854-4783-9ad7-d73dd3c3c5cb

Matrix: Hide null value row

The matrix that i have contains null values and is creating empty rows. I tried grouping the row and then setting the visible property, but that just hides the entire rows. Is there an expression that i would need to ensure that only the null rows are not visible on the matrix?

Thanks for taking the time to read.

The database tables looks like:

Month Sales Product

- - --

August 2007 700.00 Apples

August 2007 400.00 Oranges
September 2007 380.00 Apples
October 2007 1200.00 Oranges
November 2007 NULL NULL
December 2007 NULL NULL
Jan 2008 400.00 Grapefruit

The matrix looks like:

August 2007 Sept 2007 Oct 2007 Nov 2007 Dec 2007 Jan 2008

Apples 700.00 380.00

Oranges 400.00 1200.00

Grapfruit 400.00

Try filtering these records. You can use the filter within the Matrix properties to filter all records where Product Names are NULL

|||

I actually tried filtering in the edit group searching for nulls but it didn't work.

I did a bit of searching and found that i needing to use this: =IsNothing(Fields!productname.Value) in the filter expression for the group.

Thanks for your help.sql

Matrix Zero Values

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

Matrix with totals at the top and a empty row

Hi,
I would like to have the following,
field 1 field 2 field 3
---
empty row
total 1000 1000 1000
empty row
field 4 2000 2000 2000
field 5 8000 8000 8000
Any advice on how to do that?Set Subtotal position to "Before" and use top and bottom padding to add
space around subtotal
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Reg" <reg@.dsl.za.org> wrote in message
news:eB47v2amEHA.2504@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I would like to have the following,
> field 1 field 2 field 3
> ---
> empty row
> total 1000 1000 1000
> empty row
> field 4 2000 2000 2000
> field 5 8000 8000 8000
> Any advice on how to do that?
>|||Thanks this works.
Now just one more problem. If we use the example below, on the column
grouping(Contains Months) have an iif to check if the date is before a
parameter passed to the report. I then use either one or the other field
from the database depending on whether it is true or false.
When I then add the total it would give me the wrong value, not really sure
which value it is giving me, but it always one value from the column and not
a calculated value.
"Lev Semenets [MSFT]" <levs@.microsoft.com> wrote in message
news:ev416MhmEHA.2504@.TK2MSFTNGP14.phx.gbl...
> Set Subtotal position to "Before" and use top and bottom padding to add
> space around subtotal
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "Reg" <reg@.dsl.za.org> wrote in message
> news:eB47v2amEHA.2504@.TK2MSFTNGP14.phx.gbl...
> > Hi,
> >
> > I would like to have the following,
> >
> > field 1 field 2 field 3
> > ---
> > empty row
> > total 1000 1000 1000
> > empty row
> > field 4 2000 2000 2000
> > field 5 8000 8000 8000
> >
> > Any advice on how to do that?
> >
> >
>|||Would you like to create simple report that exhibits this problem and e-mail
it to me?
Thanks,
Lev
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Reg" <reg@.dsl.za.org> wrote in message
news:%23NDgWQimEHA.3876@.TK2MSFTNGP15.phx.gbl...
> Thanks this works.
> Now just one more problem. If we use the example below, on the column
> grouping(Contains Months) have an iif to check if the date is before a
> parameter passed to the report. I then use either one or the other field
> from the database depending on whether it is true or false.
> When I then add the total it would give me the wrong value, not really
> sure
> which value it is giving me, but it always one value from the column and
> not
> a calculated value.
> "Lev Semenets [MSFT]" <levs@.microsoft.com> wrote in message
> news:ev416MhmEHA.2504@.TK2MSFTNGP14.phx.gbl...
>> Set Subtotal position to "Before" and use top and bottom padding to add
>> space around subtotal
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>>
>> "Reg" <reg@.dsl.za.org> wrote in message
>> news:eB47v2amEHA.2504@.TK2MSFTNGP14.phx.gbl...
>> > Hi,
>> >
>> > I would like to have the following,
>> >
>> > field 1 field 2 field 3
>> > ---
>> > empty row
>> > total 1000 1000 1000
>> > empty row
>> > field 4 2000 2000 2000
>> > field 5 8000 8000 8000
>> >
>> > Any advice on how to do that?
>> >
>> >
>>
>

Matrix with custom row total - is it possible?

Hell Everybody,
Let's suppose on my report I have a matrix with sales data - regions in
columns, years in rows. Now I need to add an additional total column with,
let's say, total profit.
I tried to accomplish that adding a hidden value. Unfortunately it seems
like I have no control over which total is displayed and which is not.
Theoretically I could place table object next to my matrix, with the same
row and header size but when it comes to pagination results are disastrous.
For any reason renderer breaks my matrix and table at different row.
Sometimes the difference is more than one row, sometimes it does not break
the table but breaks matrix - I am aware of KeepTogether property.
Please advise.
TomaszHi Tomasz,
Thank you for your post.
Have you tried SubTotal column? To add a subtotal to a matrix, add a
subtotal to an individual group within the matrix. Groups do not have
subtotals by default. To add a subtotal to a group, right-click the group
column or row header and then click Subtotal. This will open a new header
for the subtotal. Reporting Services will calculate the subtotal based on
the aggregate in the data cell for the group.
Hope this will be helpful.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Wei,
Thanks for the answer. I have tried this. The better problem definition is
this: I have a matrix with two different measures. I want to show one of
measures with no subtotals while for the other one I want subtotals only. So
the result would look like this:
year/regional sales, USA, Canada, Total Profit
2004, $29334.00, $23232.00, ($6552.00)
2005, $534435.00, $387745.00, $223445.00
Notice that data in the last column has nothing to do with data in other
columns - it is a different measure for which region/year details are not
visible - I want subtotals only. In contrast, for the region/year sales
measure I do NOT want to show subtotals (yearly sales in this case).
Thanks,
Tomasz
"Wei Lu" <weilu@.online.microsoft.com> wrote in message
news:uLCdsGKdGHA.5024@.TK2MSFTNGXA01.phx.gbl...
> Hi Tomasz,
> Thank you for your post.
> Have you tried SubTotal column? To add a subtotal to a matrix, add a
> subtotal to an individual group within the matrix. Groups do not have
> subtotals by default. To add a subtotal to a group, right-click the group
> column or row header and then click Subtotal. This will open a new header
> for the subtotal. Reporting Services will calculate the subtotal based on
> the aggregate in the data cell for the group.
> Hope this will be helpful.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Hi Tomasz,
Thanks for the update.
How about hide the column you just want Subtotals?
If possible, would you please provide some test data so I could test on my
side?
Hope this will be helpful.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.

Matrix visibility and toggling with multi row fields

I have a matrix with two row fields. The second one doesnt show unless i press the toggle button on the first row field. How can make the first row field automatically expanded to show the second row field?

Thanks.

Is it 2 row groups? If so, go to your second row group, edit and check the visibility tab. Make sure it isn't controlled by another report item.|||Yes it is 2 row groups, it is not controlled by any other items and it is not set to hidden. This is in the properties of the textbox correct?|||I found the problem, i was looking at the text box properties, when you right click on the cell, you must select edit group, not properties.

Matrix Totaling question

Is it possible to set the background color on the Total (Row and/or Column) of a Matrix. I'm talking the entire Row and/or column not just the cell that reads "Total". I would like to have my subtotal rows and columns to be gray.Yes, It is possible!
In the textbox that has the "Total", you'll see, in the right upermost corner, a "green flag"; click and in the properties window set the background color to gray.
-
Telmo Moreira

matrix total column

How do I get the 'Totals' column in a matrix to appear on the left, just
after the row headers instead of on the far right?In the very left hand column, insert a rectangle containing two text boxes
into each cell. This gives the impression of two columns, even though there
is only the one.
It's a work round but it does the job. It would be nice to see this
functionality appear out of the box.
"rocket salad" wrote:
> How do I get the 'Totals' column in a matrix to appear on the left, just
> after the row headers instead of on the far right?|||Here's a tip: Check what happens if you export this report to Excel. I got
into trouble with a report that looked good in RS and PDF, and threw a nice
little error message in my customized text box when doing something
similar...
Kaisa M. Lindahl Lervik
"rocket salad" <rocketsalad@.discussions.microsoft.com> wrote in message
news:005C4819-D8A9-4F59-B55F-87E41440DC6F@.microsoft.com...
> In the very left hand column, insert a rectangle containing two text boxes
> into each cell. This gives the impression of two columns, even though
> there
> is only the one.
> It's a work round but it does the job. It would be nice to see this
> functionality appear out of the box.
> "rocket salad" wrote:
>> How do I get the 'Totals' column in a matrix to appear on the left, just
>> after the row headers instead of on the far right?|||I'm actually quite surprised to find it works perfectly fine. What you say
makes sense, because the logic of having cells within cells sounds a bit
hokey. When I try it, though, it exports fine. I'm using RS2000 and Excel
2003, maybe you were having a problem with a different version of Excel?
"Kaisa M. Lindahl Lervik" wrote:
> Here's a tip: Check what happens if you export this report to Excel. I got
> into trouble with a report that looked good in RS and PDF, and threw a nice
> little error message in my customized text box when doing something
> similar...
> Kaisa M. Lindahl Lervik
> "rocket salad" <rocketsalad@.discussions.microsoft.com> wrote in message
> news:005C4819-D8A9-4F59-B55F-87E41440DC6F@.microsoft.com...
> > In the very left hand column, insert a rectangle containing two text boxes
> > into each cell. This gives the impression of two columns, even though
> > there
> > is only the one.
> >
> > It's a work round but it does the job. It would be nice to see this
> > functionality appear out of the box.
> >
> > "rocket salad" wrote:
> >
> >> How do I get the 'Totals' column in a matrix to appear on the left, just
> >> after the row headers instead of on the far right?
>
>|||Same version of Excel.
Nothing is better than it works for you. :)
Kaisa
"rocket salad" <rocketsalad@.discussions.microsoft.com> wrote in message
news:35CD264D-3EA7-4062-97A4-D9DE1457FB6B@.microsoft.com...
> I'm actually quite surprised to find it works perfectly fine. What you say
> makes sense, because the logic of having cells within cells sounds a bit
> hokey. When I try it, though, it exports fine. I'm using RS2000 and Excel
> 2003, maybe you were having a problem with a different version of Excel?
> "Kaisa M. Lindahl Lervik" wrote:
>> Here's a tip: Check what happens if you export this report to Excel. I
>> got
>> into trouble with a report that looked good in RS and PDF, and threw a
>> nice
>> little error message in my customized text box when doing something
>> similar...
>> Kaisa M. Lindahl Lervik
>> "rocket salad" <rocketsalad@.discussions.microsoft.com> wrote in message
>> news:005C4819-D8A9-4F59-B55F-87E41440DC6F@.microsoft.com...
>> > In the very left hand column, insert a rectangle containing two text
>> > boxes
>> > into each cell. This gives the impression of two columns, even though
>> > there
>> > is only the one.
>> >
>> > It's a work round but it does the job. It would be nice to see this
>> > functionality appear out of the box.
>> >
>> > "rocket salad" wrote:
>> >
>> >> How do I get the 'Totals' column in a matrix to appear on the left,
>> >> just
>> >> after the row headers instead of on the far right?
>>|||You can set this by a property called "position" as "Before" (default is
"After")
To do so, Click on the Green Triangle & property. There u can see position
as last property.
"rocket salad" wrote:
> How do I get the 'Totals' column in a matrix to appear on the left, just
> after the row headers instead of on the far right?

Matrix subtotals only getting first value

Hi
I have created a matrix with subtotal on both the Row and Column. However, the total only seems to be considering the first value it encounters. Has anybody any ideas why this should be the case?
sample output:
Col1 Col2 Col3 Total
Row1 1.0 1.0 1.0
Row2 1.0 2.0 1.0
Total 1.0 1.0 1.0 1.0
*****************************************
* This message was posted via http://www.sqlmonster.com
*
* Report spam or abuse by clicking the following URL:
* http://www.sqlmonster.com/Uwe/Abuse.aspx?aid=eb765bbec307481280b9ce093a602227
*****************************************Solved it, my grouping was wrong.
Opened up another heap of problems, but that is another story ...
*****************************************
* A copy of the whole thread can be found at:
* http://www.sqlmonster.com/Uwe/Forum.aspx/sql-server-reporting/5193
*
* Report spam or abuse by clicking the following URL:
* http://www.sqlmonster.com/Uwe/Abuse.aspx?aid=6f19700c9cb74267842c83847b306254
*****************************************|||How did you resolve? I'm having a similar issue with a matrix. I've one
group for the row, one group for the column and 4 entries for the data. I've
switched the row and column group, eliminated entries for the data and still
have the result of the 1st instance of return data for the subtotal.
Thanks!
Michelle
"Jan Bodey via SQLMonster.com" wrote:
> Solved it, my grouping was wrong.
> Opened up another heap of problems, but that is another story ...
> *****************************************
> * A copy of the whole thread can be found at:
> * http://www.sqlmonster.com/Uwe/Forum.aspx/sql-server-reporting/5193
> *
> * Report spam or abuse by clicking the following URL:
> * http://www.sqlmonster.com/Uwe/Abuse.aspx?aid=6f19700c9cb74267842c83847b306254
> *****************************************
>

Matrix SubTotals

The data cell contains '=iif( Fields!dt.Value <
#11/1/2004#,"*",Sum(Fields!MyValue.Value))' which is causing the subtotals
(row & column) to show an * whenever the row or column shows an *.
How do I get the subtotal to ignore the *'s and subtotal on just those cells
that contain a value.I discovered that the subtotals cell is using the Fields!dt.Value from the
first matrix column, and since the columns are sorted oldest dt to newest the
dt.Value is < the date indicated.
Can someone think of a way I can fix this other the changing the column order?
"Harolds" wrote:
> The data cell contains '=iif( Fields!dt.Value <
> #11/1/2004#,"*",Sum(Fields!MyValue.Value))' which is causing the subtotals
> (row & column) to show an * whenever the row or column shows an *.
> How do I get the subtotal to ignore the *'s and subtotal on just those cells
> that contain a value.|||I changed my iif to =iif(last(Fields!dt.Value)... and this fixed my problem.
"Harolds" wrote:
> The data cell contains '=iif( Fields!dt.Value <
> #11/1/2004#,"*",Sum(Fields!MyValue.Value))' which is causing the subtotals
> (row & column) to show an * whenever the row or column shows an *.
> How do I get the subtotal to ignore the *'s and subtotal on just those cells
> that contain a value.

Matrix subtotal row question

I have created a matrix that looks like the following example:
APRIL MAY JUNE
PRODUCT X 10 20 30
PRODUCT Y 20 30 40
where the data is profit per unit sold (=PROFIT/# of UNITS)
I need to add a row that is the average profit for each month. I know
how to get the subtotal row to show up, but this justs adds the rows
(which is meaningless for me). Even the simple average (e.g. (10 +
20)/2) won't do - I need a weighted average per month (e.g. ALL PROFIT
for APRIL/ALL UNITS sold for APRIL). A data example is:
for April I sold 10 units of X for a $100 profit and I sold 20 units
of Y for a $400 profit
As in the table above, the profit per unit is (100/10) $10 for X and
(400/20) $20 for Y
But the average profit I want is not ($10 + $20) / 2 ($15). It is $500
profit / 30 units = ~$17
So two questions:
How do I add a average row to the matrix?
How do I make this a weighted average?
Sorry if my explanation is confusing or the answer is basic... new to
this and can't find an answer anywhere!It sounds like your current expression is something like this:
=Sum(Fields!ProfitPerUnit.Value)
What you really need is something more like this:
=Sum(Fields!TotalProfit.Value)/Sum(Fields!Units.Value)
If you are only returning ProfitPerUnit and Units in your query, you could
do this instead:
=Sum(Fields!ProfitPerUnit.Value*Fields!Units.Value)/Sum(Fields!Units.Value)
--
This post is provided 'AS IS' with no warranties, and confers no rights. All
rights reserved. Some assembly required. Batteries not included. Your
mileage may vary. Objects in mirror may be closer than they appear. No user
serviceable parts inside. Opening cover voids warranty. Keep out of reach of
children under 3.
"coldfact" <bryan@.coldfact.com> wrote in message
news:249185cd.0407151323.79162246@.posting.google.com...
> I have created a matrix that looks like the following example:
> APRIL MAY JUNE
> PRODUCT X 10 20 30
> PRODUCT Y 20 30 40
> where the data is profit per unit sold (=PROFIT/# of UNITS)
> I need to add a row that is the average profit for each month. I know
> how to get the subtotal row to show up, but this justs adds the rows
> (which is meaningless for me). Even the simple average (e.g. (10 +
> 20)/2) won't do - I need a weighted average per month (e.g. ALL PROFIT
> for APRIL/ALL UNITS sold for APRIL). A data example is:
> for April I sold 10 units of X for a $100 profit and I sold 20 units
> of Y for a $400 profit
> As in the table above, the profit per unit is (100/10) $10 for X and
> (400/20) $20 for Y
> But the average profit I want is not ($10 + $20) / 2 ($15). It is $500
> profit / 30 units = ~$17
> So two questions:
> How do I add a average row to the matrix?
> How do I make this a weighted average?
> Sorry if my explanation is confusing or the answer is basic... new to
> this and can't find an answer anywhere!|||Very nice - works now - thanks for your help!
"Chris Hays [MSFT]" <chays@.online.microsoft.com> wrote in message news:<uONOoHsaEHA.1656@.TK2MSFTNGP09.phx.gbl>...
> It sounds like your current expression is something like this:
> =Sum(Fields!ProfitPerUnit.Value)
> What you really need is something more like this:
> =Sum(Fields!TotalProfit.Value)/Sum(Fields!Units.Value)
> If you are only returning ProfitPerUnit and Units in your query, you could
> do this instead:
> =Sum(Fields!ProfitPerUnit.Value*Fields!Units.Value)/Sum(Fields!Units.Value)
> --
> This post is provided 'AS IS' with no warranties, and confers no rights. All
> rights reserved. Some assembly required. Batteries not included. Your
> mileage may vary. Objects in mirror may be closer than they appear. No user
> serviceable parts inside. Opening cover voids warranty. Keep out of reach of
> children under 3.
> "coldfact" <bryan@.coldfact.com> wrote in message
> news:249185cd.0407151323.79162246@.posting.google.com...
> > I have created a matrix that looks like the following example:
> >
> > APRIL MAY JUNE
> > PRODUCT X 10 20 30
> > PRODUCT Y 20 30 40
> >
> > where the data is profit per unit sold (=PROFIT/# of UNITS)
> > I need to add a row that is the average profit for each month. I know
> > how to get the subtotal row to show up, but this justs adds the rows
> > (which is meaningless for me). Even the simple average (e.g. (10 +
> > 20)/2) won't do - I need a weighted average per month (e.g. ALL PROFIT
> > for APRIL/ALL UNITS sold for APRIL). A data example is:
> > for April I sold 10 units of X for a $100 profit and I sold 20 units
> > of Y for a $400 profit
> > As in the table above, the profit per unit is (100/10) $10 for X and
> > (400/20) $20 for Y
> > But the average profit I want is not ($10 + $20) / 2 ($15). It is $500
> > profit / 30 units = ~$17
> >
> > So two questions:
> > How do I add a average row to the matrix?
> > How do I make this a weighted average?
> >
> > Sorry if my explanation is confusing or the answer is basic... new to
> > this and can't find an answer anywhere!|||Very nice - works now - thanks for your help!
"Chris Hays [MSFT]" <chays@.online.microsoft.com> wrote in message news:<uONOoHsaEHA.1656@.TK2MSFTNGP09.phx.gbl>...
> It sounds like your current expression is something like this:
> =Sum(Fields!ProfitPerUnit.Value)
> What you really need is something more like this:
> =Sum(Fields!TotalProfit.Value)/Sum(Fields!Units.Value)
> If you are only returning ProfitPerUnit and Units in your query, you could
> do this instead:
> =Sum(Fields!ProfitPerUnit.Value*Fields!Units.Value)/Sum(Fields!Units.Value)
> --
> This post is provided 'AS IS' with no warranties, and confers no rights. All
> rights reserved. Some assembly required. Batteries not included. Your
> mileage may vary. Objects in mirror may be closer than they appear. No user
> serviceable parts inside. Opening cover voids warranty. Keep out of reach of
> children under 3.
> "coldfact" <bryan@.coldfact.com> wrote in message
> news:249185cd.0407151323.79162246@.posting.google.com...
> > I have created a matrix that looks like the following example:
> >
> > APRIL MAY JUNE
> > PRODUCT X 10 20 30
> > PRODUCT Y 20 30 40
> >
> > where the data is profit per unit sold (=PROFIT/# of UNITS)
> > I need to add a row that is the average profit for each month. I know
> > how to get the subtotal row to show up, but this justs adds the rows
> > (which is meaningless for me). Even the simple average (e.g. (10 +
> > 20)/2) won't do - I need a weighted average per month (e.g. ALL PROFIT
> > for APRIL/ALL UNITS sold for APRIL). A data example is:
> > for April I sold 10 units of X for a $100 profit and I sold 20 units
> > of Y for a $400 profit
> > As in the table above, the profit per unit is (100/10) $10 for X and
> > (400/20) $20 for Y
> > But the average profit I want is not ($10 + $20) / 2 ($15). It is $500
> > profit / 30 units = ~$17
> >
> > So two questions:
> > How do I add a average row to the matrix?
> > How do I make this a weighted average?
> >
> > Sorry if my explanation is confusing or the answer is basic... new to
> > this and can't find an answer anywhere!

Matrix subtotal label issue (SSRS 2005)

Hello,

We have a matrix that includes two row groups with subtotals for each group, like the following:

<table width="80%">

<tr><td align="center">Unit</td><td align="center">Room</td><td align="center">Data</td></tr>

<tr><td>Unit 1</td><td>Rm 34A</td><td>6</td></tr>

<tr><td>&nbsp;</td><td>&nbsp;</td><td>4</td></tr>

<tr><td>&nbsp;</td><td>Rm 34A Total</td><td>10</td></tr>

<tr><td>&nbsp;</td><td>Rm 50</td><td>7</td></tr>

<tr><td>&nbsp;</td><td>Rm 34A Total</td><td>7</td></tr>

<tr><td>Unit 1 Total</td><td>&nbsp;</td><td>17</td></tr>

</table>

The issue is that the second group's subtotal label doesn't display the correct name, as in the example. It's always "Rm 34A" or whichever room is returned first in the dataset, even if the subtotal is actually for Rm 35. The labels for the first group display correctly, and the issue is only a label problem - the subtotal data seems to be fine. For the subtotal label, we have =Fields!Subgroup.Value + " Total" as the expression. Anybody have any suggestions? Thanks,

RLG

RLGow wrote:

Hello,

We have a matrix that includes two row groups with subtotals for each group, like the following:

<table width="80%">

<tr><td align="center">Unit</td><td align="center">Room</td><td align="center">Data</td></tr>

<tr><td>Unit 1</td><td>Rm 34A</td><td>6</td></tr>

<tr><td>&nbsp;</td><td>&nbsp;</td><td>4</td></tr>

<tr><td>&nbsp;</td><td>Rm 34A Total</td><td>10</td></tr>

<tr><td>&nbsp;</td><td>Rm 50</td><td>7</td></tr>

<tr><td>&nbsp;</td><td>Rm 34A Total</td><td>7</td></tr>

<tr><td>Unit 1 Total</td><td>&nbsp;</td><td>17</td></tr>

</table>

The issue is that the second group's subtotal label doesn't display the correct name, as in the example. It's always "Rm 34A" or whichever room is returned first in the dataset, even if the subtotal is actually for Rm 35. The labels for the first group display correctly, and the issue is only a label problem - the subtotal data seems to be fine. For the subtotal label, we have =Fields!Subgroup.Value + " Total" as the expression. Anybody have any suggestions? Thanks,

RLG

Try

=Fields!Subgroup.Value & " Total"

|||

Hi,

I also have the same problem.

However, I have discovered that this behaviour appears then you have column-subtotals and row-subtotal.

If I remove the subtotal for rows this problem seems to disappear.

Instead I get another problems with empty labels in my subtotal text-fields. There are no NULL or empty strings in used columns in my recordset!

If you have any kind of solution or work around please let me know.

Regards, Jonas

Matrix subtotal label issue (SSRS 2005)

Hello,

We have a matrix that includes two row groups with subtotals for each group, like the following:

<table width="80%">

<tr><td align="center">Unit</td><td align="center">Room</td><td align="center">Data</td></tr>

<tr><td>Unit 1</td><td>Rm 34A</td><td>6</td></tr>

<tr><td>&nbsp;</td><td>&nbsp;</td><td>4</td></tr>

<tr><td>&nbsp;</td><td>Rm 34A Total</td><td>10</td></tr>

<tr><td>&nbsp;</td><td>Rm 50</td><td>7</td></tr>

<tr><td>&nbsp;</td><td>Rm 34A Total</td><td>7</td></tr>

<tr><td>Unit 1 Total</td><td>&nbsp;</td><td>17</td></tr>

</table>

The issue is that the second group's subtotal label doesn't display the correct name, as in the example. It's always "Rm 34A" or whichever room is returned first in the dataset, even if the subtotal is actually for Rm 35. The labels for the first group display correctly, and the issue is only a label problem - the subtotal data seems to be fine. For the subtotal label, we have =Fields!Subgroup.Value + " Total" as the expression. Anybody have any suggestions? Thanks,

RLG

RLGow wrote:

Hello,

We have a matrix that includes two row groups with subtotals for each group, like the following:

<table width="80%">

<tr><td align="center">Unit</td><td align="center">Room</td><td align="center">Data</td></tr>

<tr><td>Unit 1</td><td>Rm 34A</td><td>6</td></tr>

<tr><td>&nbsp;</td><td>&nbsp;</td><td>4</td></tr>

<tr><td>&nbsp;</td><td>Rm 34A Total</td><td>10</td></tr>

<tr><td>&nbsp;</td><td>Rm 50</td><td>7</td></tr>

<tr><td>&nbsp;</td><td>Rm 34A Total</td><td>7</td></tr>

<tr><td>Unit 1 Total</td><td>&nbsp;</td><td>17</td></tr>

</table>

The issue is that the second group's subtotal label doesn't display the correct name, as in the example. It's always "Rm 34A" or whichever room is returned first in the dataset, even if the subtotal is actually for Rm 35. The labels for the first group display correctly, and the issue is only a label problem - the subtotal data seems to be fine. For the subtotal label, we have =Fields!Subgroup.Value + " Total" as the expression. Anybody have any suggestions? Thanks,

RLG

Try

=Fields!Subgroup.Value & " Total"

|||

Hi,

I also have the same problem.

However, I have discovered that this behaviour appears then you have column-subtotals and row-subtotal.

If I remove the subtotal for rows this problem seems to disappear.

Instead I get another problems with empty labels in my subtotal text-fields. There are no NULL or empty strings in used columns in my recordset!

If you have any kind of solution or work around please let me know.

Regards, Jonas

Matrix Subtotal Format

Have several rows in a Matrix, every row has a different format (percentage,
Currently, Number). The Matrix has 3 groups (all groups not displayed below)
Average Shop1 Shop2 Shop3
Technicians 8.0 7 5 2
Efficiency 70.5% 60.0 75.2 70.1
Hours 1,000 500 2000 1000
I do an average for all columns display the average for that column on the
left side
In my development environment (Visual Studio) the average column formatting
works fine and takes the formatting of other columns in the row. After being
deployed to the reporting server and viewing through the report viewer the
formatting does not seem to work and seems to take the formatting of the
first group.
ThanksAfter hours of digging, the issue seems to be because of SP2.
http://forums.microsoft.com/MSDN/showpost.aspx?postid=2215374&siteid=1
"jeo78" wrote:
> Have several rows in a Matrix, every row has a different format (percentage,
> Currently, Number). The Matrix has 3 groups (all groups not displayed below)
> Average Shop1 Shop2 Shop3
> Technicians 8.0 7 5 2
> Efficiency 70.5% 60.0 75.2 70.1
> Hours 1,000 500 2000 1000
> I do an average for all columns display the average for that column on the
> left side
> In my development environment (Visual Studio) the average column formatting
> works fine and takes the formatting of other columns in the row. After being
> deployed to the reporting server and viewing through the report viewer the
> formatting does not seem to work and seems to take the formatting of the
> first group.
> Thankssql

Matrix sub-total column width

I have a matrix report which has a row total at the end e.g.
col1 col2 total
80.1 60.4 140.5
Is there a way in which I can make the total column wider than the data cells in the matrix or alternatively can I change the total format to have no decimal places? At the moment its width seems determined by the data cell width and formatting the column with #0 seems to have no effect.
Thanks1. Is there a way to make a matrix total column wider than the associated
data cells.
No. The width of the total column is bound to the width of the data cells.
2. Is there a way to format a total cell independently of the associated
data cells?
Yes. If you set the format code on the Subtotal cell (click on the green
arrow Total cell) it will be ignored. What you have to is use an expression
in the matrix data cell:
=iif(InScope("matrix1_ColumnGroupName"), "C", "C0")
Your expectation about being able to override the data cell format string
using the subtotal format property seems reasonable. I have passed your
observation along to the development team
-- Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"John H" <John H@.discussions.microsoft.com> wrote in message
news:951BAD17-40E0-4E7C-A76F-06323C08DF1B@.microsoft.com...
> I have a matrix report which has a row total at the end e.g.
> col1 col2 total
> 80.1 60.4 140.5
> Is there a way in which I can make the total column wider than the data
cells in the matrix or alternatively can I change the total format to have
no decimal places? At the moment its width seems determined by the data
cell width and formatting the column with #0 seems to have no effect.
> Thanks|||Many thanks Bruce, the formatting suggestion worked well. John
"Bruce Johnson [MSFT]" wrote:
> 1. Is there a way to make a matrix total column wider than the associated
> data cells.
> No. The width of the total column is bound to the width of the data cells.
> 2. Is there a way to format a total cell independently of the associated
> data cells?
> Yes. If you set the format code on the Subtotal cell (click on the green
> arrow Total cell) it will be ignored. What you have to is use an expression
> in the matrix data cell:
> =iif(InScope("matrix1_ColumnGroupName"), "C", "C0")
> Your expectation about being able to override the data cell format string
> using the subtotal format property seems reasonable. I have passed your
> observation along to the development team
>
> -- Bruce Johnson [MSFT]
> Microsoft SQL Server Reporting Services
>
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "John H" <John H@.discussions.microsoft.com> wrote in message
> news:951BAD17-40E0-4E7C-A76F-06323C08DF1B@.microsoft.com...
> > I have a matrix report which has a row total at the end e.g.
> >
> > col1 col2 total
> > 80.1 60.4 140.5
> >
> > Is there a way in which I can make the total column wider than the data
> cells in the matrix or alternatively can I change the total format to have
> no decimal places? At the moment its width seems determined by the data
> cell width and formatting the column with #0 seems to have no effect.
> >
> > Thanks
>
>|||Hi,
i have used matrix in one report. in that for ex i have Col1 in Row region,
Col2 in Dynamic column region,Col3 in data region. Is there a way to have
subtotal below Col3 and subtotal for Col2 side by Col2. when i right click on
Col3 it doesnt have Subtotal option.Please help me out. My expected output
should be as below
--|--|
| Col2 | Expected subtotal
--|--|
Col1 |Col3 |
--|--|
Expected
Total
"John H" wrote:
> I have a matrix report which has a row total at the end e.g.
> col1 col2 total
> 80.1 60.4 140.5
> Is there a way in which I can make the total column wider than the data cells in the matrix or alternatively can I change the total format to have no decimal places? At the moment its width seems determined by the data cell width and formatting the column with #0 seems to have no effect.
> Thanks

Matrix Subtotal

Hi,

I have a matrix with 3 row groups: Location, Company, Country. I want to add a subtotal of "Company" that will breakdown into "Country" as well.
e.g.
right now if i add subtotal to "Company", it will give me:

New York Company A CAN

100

USA 150 Total 250 Company B CAN 80 USA 90 Total 170 Company C CAN 50 USA 60 Total 110 Total 530 LA Company D etc...

i want something like:

New York Company A CAN

100

USA 150 Total 250 Company B CAN 80 USA 90 Total 170 Company C CAN 50 USA 60 Total 110 Total CAN 230 USA 300 Total 530 LA Company D etc...

is this possible? thanks

kindof solved the problem now.. using rows to do the CAN/USA/Total instead of group.

|||Does anyone know how to actually do this? I have a variable number of members of my groups, so I can't use rows as city_ash did. It seems like relatively basic functionality if people need a matrix report.sql

matrix row total drillthru

How can I get to specify navigation from a matrix row total.
Thanks,
SamIf your question is how to define which detail columns display when you
perform a drill through on a total, then here is the answer:
It is defined in the data model. Open the data model that the report is
based on. Click on the entity that you would like to define the drill
through columns for (you have to do this process for each entity). You
will see a property for the entity called DefaultDetailAttributes.
Click in the proprty and an elips on the right will appear (a small box
with three dots in it). Click on the elips and it will open a dialog
box displaying the drill through attributes. In the dialog box, there
is an add button. Click that and another dialog box will open listing
all the attributes for the entity.
When you are done, redeploy the model. Any existing reports will now
drill through to the detail you defined.
- Steve
Sam wrote:
> How can I get to specify navigation from a matrix row total.
> Thanks,
> Sam|||Then I think my prior response will do the trick. Let me know. However,
I am assuming your report was created with report builder and not
visual studio reporting tool...
Sam wrote:
> Appreciate your response Steve. What I am trying to do is implement a
> drillthru on row subtotal.
> "steve" wrote:
> > If your question is how to define which detail columns display when you
> > perform a drill through on a total, then here is the answer:
> >
> > It is defined in the data model. Open the data model that the report is
> > based on. Click on the entity that you would like to define the drill
> > through columns for (you have to do this process for each entity). You
> > will see a property for the entity called DefaultDetailAttributes.
> > Click in the proprty and an elips on the right will appear (a small box
> > with three dots in it). Click on the elips and it will open a dialog
> > box displaying the drill through attributes. In the dialog box, there
> > is an add button. Click that and another dialog box will open listing
> > all the attributes for the entity.
> >
> > When you are done, redeploy the model. Any existing reports will now
> > drill through to the detail you defined.
> >
> > - Steve
> >
> > Sam wrote:
> > > How can I get to specify navigation from a matrix row total.
> > >
> > > Thanks,
> > > Sam
> >
> >

Matrix row total

I have a count(field!orderstamp.Value) in a field and I would like to
show the percentage that number is of the whole row?
I can add a column next to it, but how do I show a percentage of a row
in a matrix?
Thanks.Assuming you have a matrix row grouping called "MatrixRowGroup", you can try
this:
=Count(Fields!orderstamp.Value) / Count(Fields!orderstamp.Value,
"MatrixRowGroup")
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"John Geddes" <john_g@.alamode.com> wrote in message
news:OVtGb$p8EHA.2804@.TK2MSFTNGP15.phx.gbl...
> I have a count(field!orderstamp.Value) in a field and I would like to
> show the percentage that number is of the whole row?
> I can add a column next to it, but how do I show a percentage of a row
> in a matrix?
> Thanks.
>