Matrix: static column which calculates from dynamic column

Hello,
I have a matrix for Turnover that looks like this:
Rows: Department
Columns: Status
Data: Count of employees
It runs beautifully to look like this:
Department Active Terminated
________________________________
Cleaners 6 2
Maintenance 5 1
Painters 4 0
TOTAL 15 3
I would like to add another column to take the # of Active employees
and divide it by the number of Terminated Employees to look like this:
Department Active Terminated Turnover
___________________________________________
Cleaners 6 2 33%
Maintenance 5 1 20%
Painters 4 0 0%
TOTAL 15 3 20%
Does anyone know how I can do that, if possible?
Thank you!
MichelleTo add a new column right click on the last column of the table header
and select *Insert column to the right*. Next, right click on the new
cell, select *Expression*, in the text box on the right enter:
=Fields!Active.Value / Fields.Terminated.Value,
and click ok. Next, right click on the new cell again and this time
select *Properties*, in the Format section on the right select
percentage, click ok and you are done!|||Hi Patrick,
Thank you for your quick reply, unfortunately this doesn't work in my
situation. I cannot have an expression of "=Fields!Active.Value /
Fields.Terminated.Value" because these fields do not exist. There is 1
field called Status which can be either Active or Inactive. Status is
a dynamic colunm on my matrix.
Thank you,
Michelle|||Then reference the value of the textbox,
i.e. =ReportItems!active.Value / ReportItems!terminated.Value|||Add a column to the right, then type = (Fields!Terminated.Value) /
(Fields!Active.Value) in the textbox expression. You may have to format the
percentage by right click the mouse and Select "Properties" and choose
percentage.
or = sum(Fields!Terminated.Value) / sum(Fields!Active.Value).
If you in matrix report, then the formula won't work. you have to
use difference function like the "InScope function" More information is
available at
http://msdn.microsoft.com/library/en-us/RSCREATE/htm/rcr_creating_expressions_v1_0jmt.asp
Good luck!
--
This posting is provided "AS IS" with no warranties, and confers no rights
"Michelle@.bwalk.com" wrote:
> Hello,
> I have a matrix for Turnover that looks like this:
>
> Rows: Department
> Columns: Status
> Data: Count of employees
>
> It runs beautifully to look like this:
>
> Department Active Terminated
> ________________________________
> Cleaners 6 2
> Maintenance 5 1
> Painters 4 0
> TOTAL 15 3
>
> I would like to add another column to take the # of Active employees
> and divide it by the number of Terminated Employees to look like this:
>
> Department Active Terminated Turnover
> ___________________________________________
> Cleaners 6 2 33%
> Maintenance 5 1 20%
> Painters 4 0 0%
> TOTAL 15 3 20%
>
> Does anyone know how I can do that, if possible?
>
> Thank you!
> Michelle
>

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 won't collapse

I have a Matrix displaying my OLAP based dataset. The dataset looks correct
and the MAtrix displays properly with all rows and columns expanded.
I don't have the ability to collapse or expand any of the rows or columns.
No + - icon displays either.
I have tried changing settings for the initial display of columns as
collapsed and expanded.
Any ideas on the problem?
JimOK... I got it.
The visibility needs to be set on the groups.
Jim
"Jim L" <jim@.noaddress.com> wrote in message
news:uwYeUKXrEHA.1204@.TK2MSFTNGP12.phx.gbl...
>I have a Matrix displaying my OLAP based dataset. The dataset looks correct
>and the MAtrix displays properly with all rows and columns expanded.
> I don't have the ability to collapse or expand any of the rows or columns.
> No + - icon displays either.
> I have tried changing settings for the initial display of columns as
> collapsed and expanded.
> Any ideas on the problem?
> Jim
>

Matrix with more than one table possible?

Hi,
I would like to use a Matrix where the first table defines the
Columns, the second table the rows and a third table holds the data -
which needs to be associated by the values from column and row. But
for some reason I can only set one table from a data set as the data
source (e.g.: dsData_tblDefinitions).
How can this be done? If I drag and drop the other fields from the
same or other datasets it can't find the tables / fields.
Thanks,
OlcayOn Apr 30, 10:37 am, olc...@.gmail.com wrote:
> Hi,
> I would like to use a Matrix where the first table defines the
> Columns, the second table the rows and a third table holds the data -
> which needs to be associated by the values from column and row. But
> for some reason I can only set one table from a data set as the data
> source (e.g.: dsData_tblDefinitions).
> How can this be done? If I drag and drop the other fields from the
> same or other datasets it can't find the tables / fields.
> Thanks,
> Olcay
Normally, matrix reports cannot include multiple datasets by default
(if its possible at all, outside of specific referenced aggregates in
a particular cell), etc. Why is the standard matrix report not an
option in this case (one pivot column for the column names, one column
for the row info and the remaining columns for the data)?
Enrique Martinez
Sr. Software Consultant|||On 1 Mai, 05:37, EMartinez <emartinez...@.gmail.com> wrote:
> On Apr 30, 10:37 am, olc...@.gmail.com wrote:
> > Hi,
> > I would like to use a Matrix where the first table defines the
> > Columns, the second table the rows and a third table holds the data -
> > which needs to be associated by the values from column and row. But
> > for some reason I can only set one table from a data set as the data
> > source (e.g.: dsData_tblDefinitions).
> > How can this be done? If I drag and drop the other fields from the
> > same or other datasets it can't find the tables / fields.
> > Thanks,
> >Olcay
> Normally, matrix reports cannot include multiple datasets by default
> (if its possible at all, outside of specific referenced aggregates in
> a particular cell), etc. Why is the standard matrix report not an
> option in this case (one pivot column for the column names, one column
> for the row info and the remaining columns for the data)?
> Enrique Martinez
> Sr. Software Consultant
Hi Enrique,
the problem is, that my colum names are also dynamic. Columns
(tblTasks), Rows (tblObjects) and Data (tblValues - the result of
applying the tasks on those objects) are all dynamic.
Thanks,
Olcay

Matrix with fixed column values (months 1-12)

I have a matrix that shows sales per year (rows) and month (columns).
If the data being used contains no records for a particular month for any
year (e.g. November), that column (i.e. column 11) is completely missing
from the matrix.
Can the matrix be configured to always show a given set of columns, even if
there is no underlying data?
ThanksHi Laurence,
If you don't have any data rows for a certain month, then it will not show
up in the matrix grouping. To ensure that certain groups/data values are
always present, you will need an outer join in your dataset query e.g. with
a simple table that just has 12 rows with one column and values from 1 to
12.
Details on how to use outer joins are available here:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_qd_09_0zqr.asp
http://msdn.microsoft.com/library/en-us/acdata/ac_8_qd_09_1h6b.asp
HTH,
Robert
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
news:O8YpoRDuEHA.1296@.TK2MSFTNGP10.phx.gbl...
> I have a matrix that shows sales per year (rows) and month (columns).
> If the data being used contains no records for a particular month for any
> year (e.g. November), that column (i.e. column 11) is completely missing
> from the matrix.
> Can the matrix be configured to always show a given set of columns, even
if
> there is no underlying data?
> Thanks
>|||OK thanks, I know how to do that.
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:uWTPFZyuEHA.3228@.TK2MSFTNGP12.phx.gbl...
> Hi Laurence,
> If you don't have any data rows for a certain month, then it will not show
> up in the matrix grouping. To ensure that certain groups/data values are
> always present, you will need an outer join in your dataset query e.g.
> with
> a simple table that just has 12 rows with one column and values from 1 to
> 12.
> Details on how to use outer joins are available here:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_qd_09_0zqr.asp
> http://msdn.microsoft.com/library/en-us/acdata/ac_8_qd_09_1h6b.asp
> HTH,
> Robert
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
> news:O8YpoRDuEHA.1296@.TK2MSFTNGP10.phx.gbl...
>> I have a matrix that shows sales per year (rows) and month (columns).
>> If the data being used contains no records for a particular month for any
>> year (e.g. November), that column (i.e. column 11) is completely missing
>> from the matrix.
>> Can the matrix be configured to always show a given set of columns, even
> if
>> there is no underlying data?
>> Thanks
>>
>

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 Totals - left formatted

I have successfully created a matrix consisting of payroll dates as column
headers, with task types as my rows and hours/task/day the detail data. The
columns total perfectly, but the totals only display to the RIGHT of all the
data. We display our totals FIRST, then the detail data. Can I do this?
total hrs day 1 day 2
task 1 16 8 8Yes. Click on the little green triangle in the (row/column) heading to get
the subtotal properties and look at the properties window. There is a
"Position" property which is set to "After" by default. You can set it to
"Before", which gives you the effect you want.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"JeanSA" <JeanSA@.discussions.microsoft.com> wrote in message
news:C4A8ABDC-B776-4CE1-9B4B-1DDC70DD6B8A@.microsoft.com...
>I have successfully created a matrix consisting of payroll dates as column
> headers, with task types as my rows and hours/task/day the detail data.
> The
> columns total perfectly, but the totals only display to the RIGHT of all
> the
> data. We display our totals FIRST, then the detail data. Can I do this?
> total hrs day 1 day 2
> task 1 16 8 8|||Thank you. Wasn't sure what "Position" meant. I appreciate your quick
response.
"Robert Bruckner [MSFT]" wrote:
> Yes. Click on the little green triangle in the (row/column) heading to get
> the subtotal properties and look at the properties window. There is a
> "Position" property which is set to "After" by default. You can set it to
> "Before", which gives you the effect you want.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "JeanSA" <JeanSA@.discussions.microsoft.com> wrote in message
> news:C4A8ABDC-B776-4CE1-9B4B-1DDC70DD6B8A@.microsoft.com...
> >I have successfully created a matrix consisting of payroll dates as column
> > headers, with task types as my rows and hours/task/day the detail data.
> > The
> > columns total perfectly, but the totals only display to the RIGHT of all
> > the
> > data. We display our totals FIRST, then the detail data. Can I do this?
> > total hrs day 1 day 2
> > task 1 16 8 8
>
>

Matrix SubTotal Percentage

I have a matrix with dynamic rows and columns. I got the totals for the
Rows and Columns by
right clicking the groups and selecting the SubTotal option.
Can I display the totals in percentage.
[Column1] [Columns2] [ Total] [C-Percent]
[Row1] 1 2 3 50%
[Row2 ] 2 1 3 50%
----
[Total] 3 3 6
[R-Percent](50%) (50%) (100%)
Any help is appreciated.
Thanks
ReddyCreate a column, and set your expression to Fields!Name.Value /
First(Fields!Amount.Value, "MatrixColumnGroupName") - assuming that your
first row is a total.
If it's not, you might be able to use SUM if you add the Matrix column group
name = Fields!Name.Value / SUM(Fields!Amount.Value, "MatrixColumnGroupName")
Kaisa M. Lindahl Lervik
"reddy" <pparlapa@.gmail.com> wrote in message
news:OklmLNQVGHA.6048@.TK2MSFTNGP11.phx.gbl...
>I have a matrix with dynamic rows and columns. I got the totals for the
>Rows and Columns by
> right clicking the groups and selecting the SubTotal option.
> Can I display the totals in percentage.
> [Column1] [Columns2] [ Total] [C-Percent]
> [Row1] 1 2 3 50%
> [Row2 ] 2 1 3 50%
> ----
> [Total] 3 3 6
> [R-Percent](50%) (50%) (100%)
> Any help is appreciated.
> Thanks
> Reddy
>sql

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 SSAS dimension/hierarchy order problem

I have a have a relatively simple SSAS cube that I'm trying to display in a matrix but the order of the data is not correct. The rows of the matrix are a time hierarchy and the query designer produces the correct MDX and when I run the MDX in the data tab, the order is correct. However in the report preview tab, the order is incorrect. Specifically, the time hierarchy looks like this Year-Month-FullDate. The report is ordering the Months like this 1,10,11,12,2,3,4,5,6,7,8,9. I ran the MDX query in Sql Studio and the order is correct, and I used Excel to consume the same cube and it produces the correct order.

The report is somehow treating the Month like character data instead of numeric, how do I fix this?

It sounds like your MDX query only retrieves the Member_Caption (which is the string label representation), but not the Member_Value property (which is the value representation with the original data type).

For example:

SELECT NON EMPTY { [Measures].[Sales Amount] } ON COLUMNS, NON EMPTY { ([Due Date].[Month Number Of Year].[Month Number Of Year].ALLMEMBERS * [Due Date].[English Month Name].[English Month Name].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME, MEMBER_VALUE ON ROWS FROM [Adventure Works DW] CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

Note that the .Value field property of RDL expressions always maps to the Member_Caption property value for dimensions (the member caption is always a string!). The additional Member_Value intrinsic dimension property is not mapped directly to any of the predefined RDL extended field property names. Therefore, in order to access the MEMBER_VALUE property in the report, you have to use the so-called field property collection syntax in the matrix group expression and sort expression to get the numeric value for the dimension property and specify the property name exactly as defined in the MDX query above (the property name is case-sensitive in this case):
=Fields!Month_Number_Of_Year("MEMBER_VALUE")

Additional information about MDX intrinsic member properties can be found here: http://msdn2.microsoft.com/en-us/library/ms145528.aspx

-- Robert

|||

Robert - that worked perfectly. Thanks a bunch!

To anyone else in the same situation, to get to the matrix group expression and sort expression right-click the matrix object in the report designer and select Properties. Goto the Groups tab and then select dimension member in question and click the Edit button. This brings up another window, goto the Sort tab and enter the expression.

Matrix Rows

Is it possible to move rows in a matrix up and down from its original
position? Preferably in a drag n drop structure.
Everytime I add a new field to a matrix I need to do manually change the
field names in the text box in the position I need the new field name.
Thanks.Do you have list of fields,you want to see on the matrix
I mean when you are saying i am adding new field ,i feel you cant get
additonal column unless you add in the table or stored procedure you
have to compile
So by assuming you have the list of filed names before you run the
report,
1)have a parametre,in that go to Non -Queried values,give the field
name desc in drop down
2)in place of column header ,give expression like
iif(parametre=1,"Desc",IIF(param=2,"Desc2","desc3)))
3)In detailcell
iif(parametre=1,"Fields!Desc.Value",IIF(param=2,"Fields!Desc2","Fields!desc3.value)))
Regards
Raj Deep.A
Brian Shannon wrote:
> Is it possible to move rows in a matrix up and down from its original
> position? Preferably in a drag n drop structure.
> Everytime I add a new field to a matrix I need to do manually change the
> field names in the text box in the position I need the new field name.
> Thanks.

Matrix row number?

I have a matrix. For each person, they may have multiple rows in the dataset
but it is translated to a single row with multiple columns using the matrix.
I want to put a number in the first column indicating the occurence starting
at one and incrementing by 1 for each person: 1, 2, 3, 4, 5, etc. I've tried
RowNumber, Count, RunningValue with no success. Perhaps I am using the wrong
scope.
Does anyone know how to create the row number for the matrix?
StephanieI found it:
RunningValue(Fields!Country.Value,CountDistinct,Nothing)
Stephanie
"Stephanie" wrote:
> I have a matrix. For each person, they may have multiple rows in the dataset
> but it is translated to a single row with multiple columns using the matrix.
> I want to put a number in the first column indicating the occurence starting
> at one and incrementing by 1 for each person: 1, 2, 3, 4, 5, etc. I've tried
> RowNumber, Count, RunningValue with no success. Perhaps I am using the wrong
> scope.
> Does anyone know how to create the row number for the matrix?
> Stephanie

Matrix Row Counts

I have a matrix which sums sales to a customer by Month/Year.
Rows - Customer Name, Sum(NetSales)
Columns - Month/Year
What I want to capture is the total number of rows in the matrix.
For example, I am passing the report 144 records from my query; however
there are only 35 distinct customers.
Is there a function or property of the matrix that will give me this
information? I am currently doing a SELECT DISTICT on customer no in
another query to get this result.Using the CountDistinct Aggregate would do the job so you would not
need the second query.

Matrix Reports Subtotals

Hi,
I have a two parter based on matrix reports
I am trying to develop a report which displays data for weekdays as columns
and Servers as rows. The range of the dates is selectable by the user so is
not fixed. We track start / end times for each server as it starts / ends
backups and the report is displayed somewhat similarly to below.
S M T W T F
S
SQL Server 09:00 15:00 18:00 19:00 19:00 21:00 22:00
11:45 19:45 18:05 etc
IIS Server 1 09:00 15:00 18:00 19:00 19:00 21:00 22:00
11:45 19:45 18:05 etc
The data looks something like this
ServerName varchar(100),
StartTime datetime,
EndTime datetime,
SuccessTF bit
Part 1
--
What i want to do is have subtotals that are for the number of records in
each column; so we can see successfull backups.
Part 2
--
The (first or final) column would be to determine the success of the backups
overall; i have this as a value against every row in sql server but only want
to display it once at the end.I think you want the CountRows function.
CountRows("Department")http://msdn2.microsoft.com/en-us/library/ms156330.aspxOn
the second part, I think the First() function is what you are after.Steve
MunLeeuw"Chris Hoare" <choare@.nospam.nospam> wrote in message
news:8B9F0A4D-E8E6-4B97-BE1F-16D7A0F8D0A9@.microsoft.com...
> Hi,
> I have a two parter based on matrix reports
>
> I am trying to develop a report which displays data for weekdays as
> columns
> and Servers as rows. The range of the dates is selectable by the user so
> is
> not fixed. We track start / end times for each server as it starts / ends
> backups and the report is displayed somewhat similarly to below.
> S M T W T F
> S
> SQL Server 09:00 15:00 18:00 19:00 19:00 21:00 22:00
> 11:45 19:45 18:05 etc
> IIS Server 1 09:00 15:00 18:00 19:00 19:00 21:00 22:00
> 11:45 19:45 18:05 etc
> The data looks something like this
> ServerName varchar(100),
> StartTime datetime,
> EndTime datetime,
> SuccessTF bit
> Part 1
> --
> What i want to do is have subtotals that are for the number of records in
> each column; so we can see successfull backups.
> Part 2
> --
> The (first or final) column would be to determine the success of the
> backups
> overall; i have this as a value against every row in sql server but only
> want
> to display it once at the end.|||Steve,
I agree, only a subtotal row puts a value out for every column and doesnt
seem to let me edit the contents of it. They are all greyed out and display a
strange calculation based on the dates / times in the columns above.
Chris
"Steve MunLeeuw" wrote:
> I think you want the CountRows function.
> CountRows("Department")http://msdn2.microsoft.com/en-us/library/ms156330.aspxOn
> the second part, I think the First() function is what you are after.Steve
> MunLeeuw"Chris Hoare" <choare@.nospam.nospam> wrote in message
> news:8B9F0A4D-E8E6-4B97-BE1F-16D7A0F8D0A9@.microsoft.com...
> > Hi,
> > I have a two parter based on matrix reports
> >
> >
> > I am trying to develop a report which displays data for weekdays as
> > columns
> > and Servers as rows. The range of the dates is selectable by the user so
> > is
> > not fixed. We track start / end times for each server as it starts / ends
> > backups and the report is displayed somewhat similarly to below.
> >
> > S M T W T F
> > S
> > SQL Server 09:00 15:00 18:00 19:00 19:00 21:00 22:00
> > 11:45 19:45 18:05 etc
> > IIS Server 1 09:00 15:00 18:00 19:00 19:00 21:00 22:00
> > 11:45 19:45 18:05 etc
> >
> > The data looks something like this
> >
> > ServerName varchar(100),
> > StartTime datetime,
> > EndTime datetime,
> > SuccessTF bit
> >
> > Part 1
> > --
> > What i want to do is have subtotals that are for the number of records in
> > each column; so we can see successfull backups.
> >
> > Part 2
> > --
> > The (first or final) column would be to determine the success of the
> > backups
> > overall; i have this as a value against every row in sql server but only
> > want
> > to display it once at the end.
>
>|||Chris,
I am looking for the same type of answer and have a similar issue. When
at the subtotal level I want to do something different. In my example I have
several detail cells (they refer to as "static" or mutiple data fields
because they are all meant to be grouped by the same row/column constraints).
Anyway ... one of these fields is a flag that is either a 1 or 0 depending on
whether a field value (threshold) is one that matches a parameter value
entered to run the report. So in the subtotal I want to SUM all the values
and get a count of how many thresholds are tracking. I cant figure out how
to SUM this value when the detail cell expression is not a SUM it is an IIF
statement. In other words, like you , I want to do something different on
the subtotal but there isnt an option to alter the expression.
"Chris Hoare" wrote:
> Steve,
> I agree, only a subtotal row puts a value out for every column and doesnt
> seem to let me edit the contents of it. They are all greyed out and display a
> strange calculation based on the dates / times in the columns above.
> Chris
>
> "Steve MunLeeuw" wrote:
> > I think you want the CountRows function.
> >
> > CountRows("Department")http://msdn2.microsoft.com/en-us/library/ms156330.aspxOn
> > the second part, I think the First() function is what you are after.Steve
> > MunLeeuw"Chris Hoare" <choare@.nospam.nospam> wrote in message
> > news:8B9F0A4D-E8E6-4B97-BE1F-16D7A0F8D0A9@.microsoft.com...
> > > Hi,
> > > I have a two parter based on matrix reports
> > >
> > >
> > > I am trying to develop a report which displays data for weekdays as
> > > columns
> > > and Servers as rows. The range of the dates is selectable by the user so
> > > is
> > > not fixed. We track start / end times for each server as it starts / ends
> > > backups and the report is displayed somewhat similarly to below.
> > >
> > > S M T W T F
> > > S
> > > SQL Server 09:00 15:00 18:00 19:00 19:00 21:00 22:00
> > > 11:45 19:45 18:05 etc
> > > IIS Server 1 09:00 15:00 18:00 19:00 19:00 21:00 22:00
> > > 11:45 19:45 18:05 etc
> > >
> > > The data looks something like this
> > >
> > > ServerName varchar(100),
> > > StartTime datetime,
> > > EndTime datetime,
> > > SuccessTF bit
> > >
> > > Part 1
> > > --
> > > What i want to do is have subtotals that are for the number of records in
> > > each column; so we can see successfull backups.
> > >
> > > Part 2
> > > --
> > > The (first or final) column would be to determine the success of the
> > > backups
> > > overall; i have this as a value against every row in sql server but only
> > > want
> > > to display it once at the end.
> >
> >
> >|||MJT:
In the end I gave up and wrote some asp to write the report out to excel.
We looked and using a sub report to do the totaling but the sql became overly
complex (and getting things to line up was a total pain)
Chris
"MJT" wrote:
> Chris,
> I am looking for the same type of answer and have a similar issue. When
> at the subtotal level I want to do something different. In my example I have
> several detail cells (they refer to as "static" or mutiple data fields
> because they are all meant to be grouped by the same row/column constraints).
> Anyway ... one of these fields is a flag that is either a 1 or 0 depending on
> whether a field value (threshold) is one that matches a parameter value
> entered to run the report. So in the subtotal I want to SUM all the values
> and get a count of how many thresholds are tracking. I cant figure out how
> to SUM this value when the detail cell expression is not a SUM it is an IIF
> statement. In other words, like you , I want to do something different on
> the subtotal but there isnt an option to alter the expression.
> "Chris Hoare" wrote:
> > Steve,
> >
> > I agree, only a subtotal row puts a value out for every column and doesnt
> > seem to let me edit the contents of it. They are all greyed out and display a
> > strange calculation based on the dates / times in the columns above.
> >
> > Chris
> >
> >
> > "Steve MunLeeuw" wrote:
> >
> > > I think you want the CountRows function.
> > >
> > > CountRows("Department")http://msdn2.microsoft.com/en-us/library/ms156330.aspxOn
> > > the second part, I think the First() function is what you are after.Steve
> > > MunLeeuw"Chris Hoare" <choare@.nospam.nospam> wrote in message
> > > news:8B9F0A4D-E8E6-4B97-BE1F-16D7A0F8D0A9@.microsoft.com...
> > > > Hi,
> > > > I have a two parter based on matrix reports
> > > >
> > > >
> > > > I am trying to develop a report which displays data for weekdays as
> > > > columns
> > > > and Servers as rows. The range of the dates is selectable by the user so
> > > > is
> > > > not fixed. We track start / end times for each server as it starts / ends
> > > > backups and the report is displayed somewhat similarly to below.
> > > >
> > > > S M T W T F
> > > > S
> > > > SQL Server 09:00 15:00 18:00 19:00 19:00 21:00 22:00
> > > > 11:45 19:45 18:05 etc
> > > > IIS Server 1 09:00 15:00 18:00 19:00 19:00 21:00 22:00
> > > > 11:45 19:45 18:05 etc
> > > >
> > > > The data looks something like this
> > > >
> > > > ServerName varchar(100),
> > > > StartTime datetime,
> > > > EndTime datetime,
> > > > SuccessTF bit
> > > >
> > > > Part 1
> > > > --
> > > > What i want to do is have subtotals that are for the number of records in
> > > > each column; so we can see successfull backups.
> > > >
> > > > Part 2
> > > > --
> > > > The (first or final) column would be to determine the success of the
> > > > backups
> > > > overall; i have this as a value against every row in sql server but only
> > > > want
> > > > to display it once at the end.
> > >
> > >
> > >

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'?

Matrix Question

I have a 2 column matrix, (2 months) with sales totals by day in the rows. Is there a way to add a 3rd column and show the +/- % for each day?

Thanks!

BobP

You'll need to add a calculation column in the query first, then add the column to the matrix.

Matrix PDF

Is there some sort of calculation to make a matrix export to pdf without page breaks?
I have
2 dynamic rows
1 dynamic column
1 value field
Please help
thanksPlease make sure that your report is setup as follows:
Report.PageWidth - Report.LeftMargin - Report.Right Margin) >=Body.Width
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"AHH" <AHH@.discussions.microsoft.com> wrote in message
news:CE79F248-E5B9-4500-84C6-5CB91195BA6C@.microsoft.com...
> Is there some sort of calculation to make a matrix export to pdf without
page breaks?
> I have
> 2 dynamic rows
> 1 dynamic column
> 1 value field
> Please help
> thanks
>|||Would you please post or send me your RDL? I will need it to understand your
situation better.
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"AHH" <AHH@.discussions.microsoft.com> wrote in message
news:8F4A4F3C-63DD-4F66-B24C-952BBF52A728@.microsoft.com...
> Report.PageWidth - Report.LeftMargin - Report.Right Margin = 8
> My body.width is 3.5
> Thanks
>
> "Bruce Johnson [MSFT]" wrote:
> > Please make sure that your report is setup as follows:
> > Report.PageWidth - Report.LeftMargin - Report.Right Margin) >=> > Body.Width
> >
> > --
> > Bruce Johnson [MSFT]
> > Microsoft SQL Server Reporting Services
> >
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> >
> >
> > "AHH" <AHH@.discussions.microsoft.com> wrote in message
> > news:CE79F248-E5B9-4500-84C6-5CB91195BA6C@.microsoft.com...
> > > Is there some sort of calculation to make a matrix export to pdf
without
> > page breaks?
> > >
> > > I have
> > > 2 dynamic rows
> > > 1 dynamic column
> > > 1 value field
> > >
> > > Please help
> > >
> > > thanks
> > >
> >
> >
> >|||How many ReferringPhysicians do you have? If you are expecting the matrix to
fit to a specific size as column groups are added, this is not supported.
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"AHH" <AHH@.discussions.microsoft.com> wrote in message
news:8F4A4F3C-63DD-4F66-B24C-952BBF52A728@.microsoft.com...
> Report.PageWidth - Report.LeftMargin - Report.Right Margin = 8
> My body.width is 3.5
> Thanks
>
> "Bruce Johnson [MSFT]" wrote:
>> Please make sure that your report is setup as follows:
>> Report.PageWidth - Report.LeftMargin - Report.Right Margin) >=>> Body.Width
>> --
>> Bruce Johnson [MSFT]
>> Microsoft SQL Server Reporting Services
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "AHH" <AHH@.discussions.microsoft.com> wrote in message
>> news:CE79F248-E5B9-4500-84C6-5CB91195BA6C@.microsoft.com...
>> > Is there some sort of calculation to make a matrix export to pdf
>> > without
>> page breaks?
>> >
>> > I have
>> > 2 dynamic rows
>> > 1 dynamic column
>> > 1 value field
>> >
>> > Please help
>> >
>> > thanks
>> >
>>|||I made all of the column widths (the data) the same to no avail.
I also tried making the data conatained within the dynamic rows the same width in addition to making the dynamic column's data the same width and still get blank pages
from the documentation, the report should wrap to the next page
This only happens for PDF export - all other formats are fine
Still working on it
if you have anymore ideas please let me know - been at this for 30 hours now. :)
Later, allen|||I took this issue offline with AHH and was able to determine that a bug is
causing the problem.
ISSUE:
Periodically, blank pages are inserted into the a PDF rendering.
DISCUSSION:
The report that caused the problem contained a list that contained a matrix.
What appears to be occurring is that the list did not resize to the width of
the matrix - its width continued to expand until it was wider than the
report page width. At this point a blank page was inserted into the
rendering. If a workaround is discovered it will be posted on this thread. A
fix for this should appear in a future service pack or release.
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"AHH" <AHH@.discussions.microsoft.com> wrote in message
news:CE79F248-E5B9-4500-84C6-5CB91195BA6C@.microsoft.com...
> Is there some sort of calculation to make a matrix export to pdf without
page breaks?
> I have
> 2 dynamic rows
> 1 dynamic column
> 1 value field
> Please help
> thanks
>

Matrix formatting

I want to know if this is possible. I have a Matrix that could have 5 to 100
columns in it. Each column will have 5 rows.
A B C D E F G H
1 X X X X X X X X
2 X X X X X X X X
3 X X X X X X X X
4 X X X X X X X X
5 X X X X X X X X
What I want to be able to do is only show 4 colums and then have the matrix
go to the "next line".
A B C D
1 X X X X
2 X X X X
3 X X X X
4 X X X X
5 X X X X
E F G H
1 X X X X
2 X X X X
3 X X X X
4 X X X X
5 X X X X
This is probably an easy thing to do but I haven't been able to find the
property that gives this to me. Any help would be appreciated.David,
Well if you export it as PDF it is smart enough to do that for you. I
know this may not be the answer you are looking for but thought I would
mention it.|||Hey Craig,
Thanks for the stab at it but I think I found my answer here:
http://blogs.msdn.com/chrishays/archive/2004/07/23/HorizontalTables.aspx
"Craig" wrote:
> David,
> Well if you export it as PDF it is smart enough to do that for you. I
> know this may not be the answer you are looking for but thought I would
> mention it.

Matrix Duplicates

I have a matrix created that is displaying duplicate rows, the underlying
data is fine, not sure what's going on . It looks something like this.
Label Job#1 Job#2
Shoes 10 15
Shirts 50 45
Pants 25
Pants 40
I can't understand why "pants" is displaying twice. It seems to be
happening randomly in the matrix.Most likely, in one case the label field has a contents like "Pants", and in
the other case there is some whitespace at the end, such as "Pants ".
Try changing the grouping expression to e.g. =Trim(Fields!Label.Value)
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"FL Jim" <FLJim@.discussions.microsoft.com> wrote in message
news:0BA709D4-88AF-4126-943D-829CDD76B869@.microsoft.com...
>I have a matrix created that is displaying duplicate rows, the underlying
> data is fine, not sure what's going on . It looks something like this.
> Label Job#1 Job#2
> Shoes 10 15
> Shirts 50 45
> Pants 25
> Pants 40
> I can't understand why "pants" is displaying twice. It seems to be
> happening randomly in the matrix.|||I tried this and now I'm getting a blank label for the duplicate row, but
it's still there. I did a Len(Label) for these rows in SQL and they are the
exact same length. Quite strange.
Label Job#1 Job#2
Shoes 10 15
Shirts 50 45
Pants 25
40
"Robert Bruckner [MSFT]" wrote:
> Most likely, in one case the label field has a contents like "Pants", and in
> the other case there is some whitespace at the end, such as "Pants ".
> Try changing the grouping expression to e.g. =Trim(Fields!Label.Value)
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "FL Jim" <FLJim@.discussions.microsoft.com> wrote in message
> news:0BA709D4-88AF-4126-943D-829CDD76B869@.microsoft.com...
> >I have a matrix created that is displaying duplicate rows, the underlying
> > data is fine, not sure what's going on . It looks something like this.
> >
> > Label Job#1 Job#2
> > Shoes 10 15
> > Shirts 50 45
> > Pants 25
> > Pants 40
> >
> > I can't understand why "pants" is displaying twice. It seems to be
> > happening randomly in the matrix.
>
>|||I realized it's now blank becuase the 'hide duplicates' toggle was on for the
group in the matrix; however, it doesn't explain why it recognizes the row as
a duplicate, but doesn't just display the data together in one row.
"Robert Bruckner [MSFT]" wrote:
> Most likely, in one case the label field has a contents like "Pants", and in
> the other case there is some whitespace at the end, such as "Pants ".
> Try changing the grouping expression to e.g. =Trim(Fields!Label.Value)
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "FL Jim" <FLJim@.discussions.microsoft.com> wrote in message
> news:0BA709D4-88AF-4126-943D-829CDD76B869@.microsoft.com...
> >I have a matrix created that is displaying duplicate rows, the underlying
> > data is fine, not sure what's going on . It looks something like this.
> >
> > Label Job#1 Job#2
> > Shoes 10 15
> > Shirts 50 45
> > Pants 25
> > Pants 40
> >
> > I can't understand why "pants" is displaying twice. It seems to be
> > happening randomly in the matrix.
>
>|||There may be something else in the query or the report design that results
in that behavior. Can you post a small report (e.g. based on Northwind data)
that reproduces the issue you are experiencing?
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"FL Jim" <FLJim@.discussions.microsoft.com> wrote in message
news:46268052-7531-4F0C-A2BE-FE27AAA8E7A3@.microsoft.com...
>I realized it's now blank becuase the 'hide duplicates' toggle was on for
>the
> group in the matrix; however, it doesn't explain why it recognizes the row
> as
> a duplicate, but doesn't just display the data together in one row.
> "Robert Bruckner [MSFT]" wrote:
>> Most likely, in one case the label field has a contents like "Pants", and
>> in
>> the other case there is some whitespace at the end, such as "Pants ".
>> Try changing the grouping expression to e.g. =Trim(Fields!Label.Value)
>> -- Robert
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "FL Jim" <FLJim@.discussions.microsoft.com> wrote in message
>> news:0BA709D4-88AF-4126-943D-829CDD76B869@.microsoft.com...
>> >I have a matrix created that is displaying duplicate rows, the
>> >underlying
>> > data is fine, not sure what's going on . It looks something like
>> > this.
>> >
>> > Label Job#1 Job#2
>> > Shoes 10 15
>> > Shirts 50 45
>> > Pants 25
>> > Pants 40
>> >
>> > I can't understand why "pants" is displaying twice. It seems to be
>> > happening randomly in the matrix.
>>

Matrix Drill Down Issue

I have a matrix with dynamic columns and rows. If I apply any filters to the
row groups then the higher levels only reflect the first "store" result set
in the higher levels instead of the aggregate total. If I remove the filters
on each group then the values at each level are correct but my first
[member_caption] at each level is null.
Expression: =Fields!location_Division.Value="" operator: = value: =false
Thanks in advance for your help.
col1 col2 col3 col4 col5 col6
division 5 5 4 4 5 4
region 5 5 4 4 5 4
district 1 5 5 4 4 5 4
store 10 5 5 4 4 5 4
store 12 3 1 2 3 2 3Found a way around this problem....have to select the leaves in your MDX
and sum the column value......
"Raz" wrote:
> I have a matrix with dynamic columns and rows. If I apply any filters to the
> row groups then the higher levels only reflect the first "store" result set
> in the higher levels instead of the aggregate total. If I remove the filters
> on each group then the values at each level are correct but my first
> [member_caption] at each level is null.
> Expression: =Fields!location_Division.Value="" operator: = value: =false
> Thanks in advance for your help.
> col1 col2 col3 col4 col5 col6
> division 5 5 4 4 5 4
> region 5 5 4 4 5 4
> district 1 5 5 4 4 5 4
> store 10 5 5 4 4 5 4
> store 12 3 1 2 3 2 3