Max Function - But between a fixed value and a column

Dear Sirs / Ladies
In HQL their was a MAX Function that would compare a fixed value
( 1.95 ) with a Column.
An example is MAX( 1.95, RegularBasePrice )
If the RegularBasePrice was 0.95 it would return 1.95 or
If the RegularBasePrice was 2.49 it would return 2.49
Is their anyway to duplicate that in TSQL
Below is an example of the code under HQL
from Item;
where CurrentUnitCost > 0
and CurrentPrice > 0;
update CurrentMargin : ( ( ( CurrentPrice /
Max( 1, CurrentSplit ) ) - CurrentUnitCost ) /
Max( .01, ( CurrentPrice /
Max( 1, CurrentSplit ) ) ) ) * 100;
MarkThis is also the GREATEST( <exp list> ) function in Oracle.In Standard
SQL, you will need a CASE expression like this:
CASE WHEN x > 1.95 THEN x ELSE 1.95 END

Max Column Total in SQL Server 2005

Hello,

Is there a maximum total of columns that SQL Server 2005 limits? I have a rather lengthy import file in regards to column count, and was wondering if there was a total number of columns that a table is limited to.

Thanks.

I think it is 255, I am not so sure

|||

please check this, it may help you.

http://msdn2.microsoft.com/en-us/library/ms143432.aspx

Max characters over column?

I am trying to bring down my column sizes in SQL Server 2000. Original
creater made them all varchar(1000) and that's way to much. A lot of
these have static lengths and some have a range. What I'm trying to do
is to pick a column, say last_name, and find out the maximum amount of
characters used for the existing data and then adjust accordingly. So
say of all the last names in the last_name column, it came out to 75
characters, I would make the column 100 characters. Just an example.
Any way to parse through a column in a query and find out the maximum
character length used for any given value?
Thanks.
JRSelect Max( Len( MyColumn ) )
From MyTable
How's that?
Colin.
"JR" <jriker1@.yahoo.com> wrote in message
news:1142894694.958758.314000@.i39g2000cwa.googlegroups.com...
>I am trying to bring down my column sizes in SQL Server 2000. Original
> creater made them all varchar(1000) and that's way to much. A lot of
> these have static lengths and some have a range. What I'm trying to do
> is to pick a column, say last_name, and find out the maximum amount of
> characters used for the existing data and then adjust accordingly. So
> say of all the last names in the last_name column, it came out to 75
> characters, I would make the column 100 characters. Just an example.
> Any way to parse through a column in a query and find out the maximum
> character length used for any given value?
> Thanks.
> JR
>

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: 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: Need different count of cols in (Sub)total

Please need help!
How to do, to have different count of columns in (Sub)-Total?
eg: The normal (non-subtotal)-Column should only show one value. eg: a count
of something.
In the Subtotal there should be a Sum of this count-field AND a second col
with eg a percentage of this sum to the sum-over-all.
As asked a week before.
IS THIS POSSIBLE '?
Need Help.
--
LG HOLANThe trick is using the InScope function. InScope works with groups and
datasets.
The following expression will check which "part" of the matrix your are:
=IIF(
InScope("matrix1_Time_Year"),
IIF(
InScope("matrix1_Time_Month"),
Fields!Measures_Store_Sales.Value,
sum(cint( Fields!Measures_Store_Sales.Value))
),
avg(cint(Fields!Measures_Store_Sales.Value)))
matrix1_Time_Year is a column group, matrix1_Time_Month is a row group.
In your case, you probably want something a bit more simple, like
=IIF(InScope("ColGroup"), SUM(fields!MyValue.Value"), fields!MyValue.Value)
The IIF(InScope("ColGroup") checks if you are in the subtotal of the column
group. If you are, it will sum your fields. If you're in the detail, it will
ounly show the field.
The following code is a small matrix with data from the Foodmart 2000 OLAP
cube, where the cells are filled according to my first expression.
Kaisa M. Lindahl Lervik
<?xml version="1.0" encoding="utf-8"?>
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefinition"
xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<rd:GridSpacing>0.25cm</rd:GridSpacing>
<RightMargin>2.5cm</RightMargin>
<Body>
<ReportItems>
<Matrix Name="matrix1">
<Corner>
<ReportItems>
<Textbox Name="textbox1">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>8</ZIndex>
<rd:DefaultName>textbox1</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</Corner>
<Height>2.53968cm</Height>
<Style />
<MatrixRows>
<MatrixRow>
<MatrixCells>
<MatrixCell>
<ReportItems>
<Textbox Name="Measures_Store_Sales">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<Format>N0</Format>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>2</ZIndex>
<rd:DefaultName>Measures_Store_Sales</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=IIF(
InScope("matrix1_Time_Year"),
IIF(
InScope("matrix1_Time_Month"),
Fields!Measures_Store_Sales.Value,
sum(cint( Fields!Measures_Store_Sales.Value))
),
avg(cint(Fields!Measures_Store_Sales.Value)))</Value>
</Textbox>
</ReportItems>
</MatrixCell>
<MatrixCell>
<ReportItems>
<Textbox Name="textbox6">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<Format>P0</Format>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>1</ZIndex>
<rd:DefaultName>textbox6</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>= Fields!Measures_Store_Cost.Value/
Fields!Measures_Store_Sales.Value</Value>
</Textbox>
</ReportItems>
</MatrixCell>
</MatrixCells>
<Height>0.63492cm</Height>
</MatrixRow>
</MatrixRows>
<MatrixColumns>
<MatrixColumn>
<Width>2.53968cm</Width>
</MatrixColumn>
<MatrixColumn>
<Width>2.53968cm</Width>
</MatrixColumn>
</MatrixColumns>
<DataSetName>DataSet1</DataSetName>
<ColumnGroupings>
<ColumnGrouping>
<DynamicColumns>
<Grouping Name="matrix1_Time_Year">
<GroupExpressions>
<GroupExpression>=Fields!Time_Year.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<ReportItems>
<Textbox Name="Time_Year">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextAlign>Center</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>7</ZIndex>
<rd:DefaultName>Time_Year</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!Time_Year.Value</Value>
</Textbox>
</ReportItems>
<Subtotal>
<Style>
<BorderStyle>
<Left>Solid</Left>
</BorderStyle>
</Style>
<ReportItems>
<Textbox Name="textbox4">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BorderStyle>
<Left>Solid</Left>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>6</ZIndex>
<rd:DefaultName>textbox4</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Total</Value>
</Textbox>
</ReportItems>
</Subtotal>
</DynamicColumns>
<Height>0.63492cm</Height>
</ColumnGrouping>
<ColumnGrouping>
<Height>0.63492cm</Height>
<StaticColumns>
<StaticColumn>
<ReportItems>
<Textbox Name="textbox3">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BorderStyle>
<Left>Solid</Left>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>5</ZIndex>
<rd:DefaultName>textbox3</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Measures Store Sales</Value>
</Textbox>
</ReportItems>
</StaticColumn>
<StaticColumn>
<ReportItems>
<Textbox Name="textbox2">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>4</ZIndex>
<rd:DefaultName>textbox2</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>%</Value>
</Textbox>
</ReportItems>
</StaticColumn>
</StaticColumns>
</ColumnGrouping>
</ColumnGroupings>
<Width>12.6984cm</Width>
<Top>1.75cm</Top>
<Left>1cm</Left>
<RowGroupings>
<RowGrouping>
<DynamicRows>
<Grouping Name="matrix1_Time_Month">
<GroupExpressions>
<GroupExpression>=Fields!Time_Month.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<ReportItems>
<Textbox Name="Time_Month">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BorderStyle>
<Right>None</Right>
</BorderStyle>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>3</ZIndex>
<rd:DefaultName>Time_Month</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!Time_Month.Value</Value>
</Textbox>
</ReportItems>
<Subtotal>
<ReportItems>
<Textbox Name="textbox5">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<rd:DefaultName>textbox5</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Total</Value>
</Textbox>
</ReportItems>
</Subtotal>
</DynamicRows>
<Width>2.53968cm</Width>
</RowGrouping>
</RowGroupings>
</Matrix>
</ReportItems>
<Style />
<Height>5cm</Height>
<ColumnSpacing>1cm</ColumnSpacing>
</Body>
<TopMargin>2.5cm</TopMargin>
<DataSources>
<DataSource Name="FoodMart 2000">
<rd:DataSourceID>dc66e45a-32ae-46c1-a7c7-228bd141338c</rd:DataSourceID>
<DataSourceReference>FoodMart 2000</DataSourceReference>
</DataSource>
</DataSources>
<Width>16cm</Width>
<DataSets>
<DataSet Name="DataSet1">
<Fields>
<Field Name="Time_Year">
<DataField>[Time].[Year].[MEMBER_CAPTION]</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Time_Quarter">
<DataField>[Time].[Quarter].[MEMBER_CAPTION]</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Time_Month">
<DataField>[Time].[Month].[MEMBER_CAPTION]</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Measures_Store_Cost">
<DataField>[Measures].[Store Cost]</DataField>
<rd:TypeName>System.Object</rd:TypeName>
</Field>
<Field Name="Measures_Store_Sales">
<DataField>[Measures].[Store Sales]</DataField>
<rd:TypeName>System.Object</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>FoodMart 2000</DataSourceName>
<CommandText>with set MonBud as
'{[Time].[1997].[Q3].[7]:[Time].[1997].[Q3].[7].lag(3),
[Time].[1998].[Q3].[7]:[Time].[1998].[Q3].[7].lag(3)}'
Cell Calculation [ForceNull] for '(Measures.AllMembers)' as '0', CONDITION ='IsEmpty(CalculationpassValue(Measures.CurrentMember, -1, RELATIVE))'
select
{[Measures].[Store Cost],[Measures].[Store Sales]} on columns,
{MonBud} on rows
from [Sales]</CommandText>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
</Query>
</DataSet>
</DataSets>
<LeftMargin>2.5cm</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<PageHeight>29.7cm</PageHeight>
<rd:DrawGrid>true</rd:DrawGrid>
<PageWidth>21cm</PageWidth>
<rd:ReportID>df4660d2-024e-426b-8c3a-d23ae01c8e60</rd:ReportID>
<BottomMargin>2.5cm</BottomMargin>
<Language>en-US</Language>
</Report>
"holan" <holan@.noemaol.noemail> wrote in message
news:6A8B3192-1978-4C76-82F0-49965BB847B8@.microsoft.com...
> Please need help!
> How to do, to have different count of columns in (Sub)-Total?
> eg: The normal (non-subtotal)-Column should only show one value. eg: a
> count
> of something.
> In the Subtotal there should be a Sum of this count-field AND a second col
> with eg a percentage of this sum to the sum-over-all.
> As asked a week before.
> IS THIS POSSIBLE '?
> Need Help.
> --
> LG HOLAN
>|||Thank you for this trick!
I know this trick from you from other postings you have done in this forum.
This works fine and was a great help for me.
BUT I can not solve my problem with this.
(I could not find a clean way)
To use this Inscope in the Value-Property will work.
But I need a way to have a different Width depending, if the cell is InScope
of
the Subtotal or not.
Or mutch better to have 2 Colums (a static group under the databound
Col-Group) for eg: Count and Percent. And to hide the Percent-Col if it is
not in the Scope of the Subtotal.
So I can habe a lot of Cols with only a few Subtotals, and only in the
Subtotal I have also (additional to the Sum(Count)) a Percent-Value, and use
ONLY the place I need.
If I can't hide the Percent-Col in not-Subtotal-Scope, I can hide the
content in this Col (with InScope) but this will use to much space in the
width, if I have a lot of cols. This is my current workaround. This with
right-border-color = white in the left col and left-border-color=white in the
right col will simulate one big col, that show in non-subtotal-scope only one
value (the Count) and in subtotal-scope the count and the percent-value.
But this is not a clean way. And it waste a lot of space in the report.
We have tested the same scenario with crystal-reports (we had an old version
inhouse). There its very simple to have in the subtotal-area a different
width and to place there a different count of Textboxes (that will be cols
then).
BUT crystal-report will not be an alternative to us.
We are a new MS ISV and want to use this tools from one hand.
ReportViewer (with local and remote, with WinForms and WebForms) will be the
future.
I think that MAYBE this is a design-weakness of the current version of
ReportViewer/ReportService. (I hope I am wrong and that someone have an other
trick). But if is not possible by design, I hope that MS will find a clean
solution for the next release. Maybe YOU have a good channel to MS to place
this wish, or maybe you can give me the info where to place such a wish, so
that it will also been heared.
LG HOLAN
"Kaisa M. Lindahl Lervik" wrote:
> The trick is using the InScope function. InScope works with groups and
> datasets.|||Not quite sure if I did understand everything you want to, so this is a
fairly general reply.
I think I've read that you can't have dynamic column widths in neither the
2000 or the 2005 edition. I've tried to use the increase / decrease textbox
parameters before, but no luck. You might want to try it though. As long as
the report is rendered as HTML, you are sort of limited to what is possible
in generic HTML. So you can do different expressions for showing and hiding
columns, but if you've already showed a column, it will "be there", visible
or not, in the whole container.
Kaisa M. Lindahl Lervik
"holan" <holan@.noemaol.noemail> wrote in message
news:42646D94-5CA2-4BA4-9A56-819F01CCBAE7@.microsoft.com...
> Thank you for this trick!
> I know this trick from you from other postings you have done in this
> forum.
> This works fine and was a great help for me.
> BUT I can not solve my problem with this.
> (I could not find a clean way)
> To use this Inscope in the Value-Property will work.
> But I need a way to have a different Width depending, if the cell is
> InScope
> of
> the Subtotal or not.
> Or mutch better to have 2 Colums (a static group under the databound
> Col-Group) for eg: Count and Percent. And to hide the Percent-Col if it is
> not in the Scope of the Subtotal.
> So I can habe a lot of Cols with only a few Subtotals, and only in the
> Subtotal I have also (additional to the Sum(Count)) a Percent-Value, and
> use
> ONLY the place I need.
> If I can't hide the Percent-Col in not-Subtotal-Scope, I can hide the
> content in this Col (with InScope) but this will use to much space in the
> width, if I have a lot of cols. This is my current workaround. This with
> right-border-color = white in the left col and left-border-color=white in
> the
> right col will simulate one big col, that show in non-subtotal-scope only
> one
> value (the Count) and in subtotal-scope the count and the percent-value.
> But this is not a clean way. And it waste a lot of space in the report.
> We have tested the same scenario with crystal-reports (we had an old
> version
> inhouse). There its very simple to have in the subtotal-area a different
> width and to place there a different count of Textboxes (that will be cols
> then).
> BUT crystal-report will not be an alternative to us.
> We are a new MS ISV and want to use this tools from one hand.
> ReportViewer (with local and remote, with WinForms and WebForms) will be
> the
> future.
> I think that MAYBE this is a design-weakness of the current version of
> ReportViewer/ReportService. (I hope I am wrong and that someone have an
> other
> trick). But if is not possible by design, I hope that MS will find a clean
> solution for the next release. Maybe YOU have a good channel to MS to
> place
> this wish, or maybe you can give me the info where to place such a wish,
> so
> that it will also been heared.
>
> --
> LG HOLAN
>
> "Kaisa M. Lindahl Lervik" wrote:
>> The trick is using the InScope function. InScope works with groups and
>> datasets.
>|||Hi Kaisa,
Maybe not relative to your comments, but we have an issue to hide a Matrix
column in a report in runtime. So user can select an option on report and we
hide a columnl. Do you have any advice?
Thanks,
Masoud
"Kaisa M. Lindahl Lervik" <kaisaml@.hotmail.com> wrote in message
news:%23HUhnc4YGHA.4144@.TK2MSFTNGP04.phx.gbl...
> Not quite sure if I did understand everything you want to, so this is a
> fairly general reply.
> I think I've read that you can't have dynamic column widths in neither the
> 2000 or the 2005 edition. I've tried to use the increase / decrease
textbox
> parameters before, but no luck. You might want to try it though. As long
as
> the report is rendered as HTML, you are sort of limited to what is
possible
> in generic HTML. So you can do different expressions for showing and
hiding
> columns, but if you've already showed a column, it will "be there",
visible
> or not, in the whole container.
> Kaisa M. Lindahl Lervik
>
> "holan" <holan@.noemaol.noemail> wrote in message
> news:42646D94-5CA2-4BA4-9A56-819F01CCBAE7@.microsoft.com...
> > Thank you for this trick!
> >
> > I know this trick from you from other postings you have done in this
> > forum.
> > This works fine and was a great help for me.
> >
> > BUT I can not solve my problem with this.
> > (I could not find a clean way)
> > To use this Inscope in the Value-Property will work.
> > But I need a way to have a different Width depending, if the cell is
> > InScope
> > of
> > the Subtotal or not.
> > Or mutch better to have 2 Colums (a static group under the databound
> > Col-Group) for eg: Count and Percent. And to hide the Percent-Col if it
is
> > not in the Scope of the Subtotal.
> > So I can habe a lot of Cols with only a few Subtotals, and only in the
> > Subtotal I have also (additional to the Sum(Count)) a Percent-Value, and
> > use
> > ONLY the place I need.
> > If I can't hide the Percent-Col in not-Subtotal-Scope, I can hide the
> > content in this Col (with InScope) but this will use to much space in
the
> > width, if I have a lot of cols. This is my current workaround. This with
> > right-border-color = white in the left col and left-border-color=white
in
> > the
> > right col will simulate one big col, that show in non-subtotal-scope
only
> > one
> > value (the Count) and in subtotal-scope the count and the percent-value.
> > But this is not a clean way. And it waste a lot of space in the report.
> > We have tested the same scenario with crystal-reports (we had an old
> > version
> > inhouse). There its very simple to have in the subtotal-area a different
> > width and to place there a different count of Textboxes (that will be
cols
> > then).
> > BUT crystal-report will not be an alternative to us.
> > We are a new MS ISV and want to use this tools from one hand.
> > ReportViewer (with local and remote, with WinForms and WebForms) will be
> > the
> > future.
> > I think that MAYBE this is a design-weakness of the current version of
> > ReportViewer/ReportService. (I hope I am wrong and that someone have an
> > other
> > trick). But if is not possible by design, I hope that MS will find a
clean
> > solution for the next release. Maybe YOU have a good channel to MS to
> > place
> > this wish, or maybe you can give me the info where to place such a wish,
> > so
> > that it will also been heared.
> >
> >
> > --
> > LG HOLAN
> >
> >
> > "Kaisa M. Lindahl Lervik" wrote:
> >
> >> The trick is using the InScope function. InScope works with groups and
> >> datasets.
> >
>|||"Kaisa M. Lindahl Lervik" wrote:
> I think I've read that you can't have dynamic column widths in neither the
> 2000 or the 2005 edition. I've tried to use the increase / decrease textbox
> parameters before, but no luck. You might want to try it though. As long as
> the report is rendered as HTML, you are sort of limited to what is possible
> in generic HTML. So you can do different expressions for showing and hiding
> columns, but if you've already showed a column, it will "be there", visible
> or not, in the whole container.
I want to show 2 cols in the subtotal (Count and Percent of this Count to
Count of this RowGroup)
AND I want in all ColGroups, that are not in the Scope of Subtotal, ONLY 1
Col (the Count but not the Percent).
I am sure that this is not a problem of HTML-rendering.
Of Course if you have some cell in a col that is visible then the whole col
will be shown. But I the case of not-subtotal-cols I want to hide the whole
col of percent. So the HTML-renderer could hide the whole col and this will
mean that only had to produce NO tags for this col to hide. I have tested it
with the Crystal-Report from VS2005 and there it is possible to have
different counts of cols in the scope of subtotal and not-subtotal. So it
could not be an issue of HTML-rendering.
But we could not (will not) use crystal, because we think that
MS-ReportService will be the future.
Here again my text of posting before (where know one aswers), that will have
more details:
I need something like this:
I Col1 I Col2 I Col3 I ColSum+% I
Row1 I 10 I 20 I 30 I 60 28% I
Row2 I 40 I 50 I 60 I 150 72% I
RowSum I 50 I 70 I 90 I 210 100% I
OR better
I Col1 I Col2 I Col3 I Total I
I Count I Count I Count I Count I % I
Row1 I 10 I 20 I 30 I 60 I 28% I
Row2 I 40 I 50 I 60 I 150 I 72% I
RowSum I 50 I 70 I 90 I 210 I 100% I
So I need a Subtotal with a greater Width like the normal Col.
Because there could be a lot of Cols. If I make the Width of the Col great
enough
that it will hold the 2 Values, then the Report would be to width to print
on one page.
Now I have a formular in the Value-Property like this:
=IIF(InScope("RowGroupName") AND InScope("ColGroupName"),
FormatNumber(Fields!ItemCount.Value,0,False,False,True),
FormatNumber(Sum(Fields!ItemCount.Value),0,True,False,True)
& Chr(13) & Chr(10)
& FormatNumber(Sum(Fields!ItemCount.Value) * 100 /
Sum(Fields!ItemCount.Value, "RowGroupName"),1,True,False,False )
& "%"
)
This is my workaround. Its similar to that what I need.
But the 2 Values in the Subtotal will be placed in 2 rows.
So the Width for the Col/Subtotal could be smaller.
BUT then the Height of the Report will be greater then it must be.
I don´t find a way to have different Width for Subtotal and normal Cols.
In the Properties for Subtotal (green triangle) there is no Width.
In the Properties for the TextBox of the Subtotal there will be a Width but
I could not set a value. (It will always change back to the value of the
Width of
the normal Col)
I also tried to make 2nd Detail-Cols and to show only the second Col if in
scope
of a Subtotal. This would be a nice solution, because of the Headers and
the Subtotal would have 2 seperate Cols instead of 2 values in one col.
I tried this with Visibility.Hidden but this will only hide the content of
this col
but not the whole Col.
I tried to set the Width of the second col with IIF(InScope..., "0pt",
"10pt").
But a formular is not allowed there.
PLEASE has anyone a trick to do this!
Or is this a design-limitation of MS?
So if this is a design-limitation, then this will be a GREAT WISH
for the next release. I hope MS will read this.
--
LG HOLAN

matrix: more than 1 column in static rowgroups ?

Hello,

I am working with a matrix report item and would like to display something like that :

DataGroup1
DataGroup2
StaticText11 StaticText12 Data1
StaticText21 StaticText22 Data2
StaticText31 StaticText32 Data3

But I cannot find a simple way to have 2 columns of static text on the left. The only way I've found is inserting a table report item in the matrix cells, but it cannot be exported in Excel.

The only samples I found do'nt have multiple colums in the static rowgroup.

Is it possible ?

Kind regards,
Xavier Miller.

Xavier,

This is a known issue since Ms release Reporting Services 2000, they promised to fix it on a service pack... but it seems that this has not been fixed even in 2005.

Apart from the table inside the matrix... there's another nasty trick, place a rectanlge and inside textboxes with the headers (you have to adjust the size pixel by pixel), BUT.... you will get horrible columns merged when you export into excel and you cannot use dynamic column sizes....

I'm looking as well for a better solution... but it seems that nobody cares about this.

Regards

Braulio

|||oh cool, that's what I expected...

Thanks anyway for the answer ;)|||

Hi,

It is also possible to add a second Row Group and set the group expression the same value as the first. This way you will receive an additional column. In this column, you can then set the expression to the required value.

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||

Geert Verhoeven wrote:

Hi,

It is also possible to add a second Row Group and set the group expression the same value as the first. This way you will receive an additional column. In this column, you can then set the expression to the required value.

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

Hello,

The expression is static text. How do you proceed, please ?|||

Mmm... but I guess this is only valid in the data area (the right hand side of the matrix report), in the columns that identify the rows (left hand side), I guess you can not set that columns on top of the left area, isn't it ?

|||

Brauliod wrote:

Mmm... but I guess this is only valid in the data area (the right hand side of the matrix report), in the columns that identify the rows (left hand side), I guess you can not set that columns on top of the left area, isn't it ?

Yes, that's why I posted that message to confirm that it is impossible.

In fact, I can double the static rows in the RDL source. The designer shows well 2 columns, but the renderer refuses to show them.|||

Gert I'm trying to group rows, but I get them as an addition column not as a row above the left hand side of columns, could you tell us which steps must be performed ?

Thanks in advance, Regards

Braulio

|||

Hi,

I'll try to give an example. I'm using the following query on the AdventureWorks db:
SELECT Production.Product.Name, Production.ProductSubcategory.Name AS ProductSubCategory
FROM Production.Product INNER JOIN Production.ProductSubcategory ON Production.Product.ProductSubcategoryID = Production.ProductSubcategory.ProductSubcategoryID

If I get it right you want to have something like this:

ProductSubCategory
Product statictext

To do this, you need to follow these steps:

add a matrix to your report|||Yes, that works for dynamic data and grouping, but NOT for static data, multiple columns by row, which was my initial question...|||

Sorry, what I want to get is something like:

May 07 June 07

Project Name Location

Tiger Spain 20 40 (...)

I need the header on top ( the matrix reports that I'm using is more an horizontal report, the data columns are dynamic, but the left hand side columns are static and have the ID column and some attributes).Having the data columns dynamic does not allow me to use tabular reports :-(.

Any idea about how to do this?

Thanks a lot for your help.

Braulio

|||

Sorry, I didn't understand well the query (my problem is putting columns on top)... you want columns in the left hand side... mmm... for horizontal reports there is an interesting post in a blog tell me if this is what you are looking for

Does Reporting Services support horizontal tables (fixed rows and dynamic columns)http://blogs.msdn.com/chrishays/archive/2004/07/23/HorizontalTables.aspx

HTH

Braulio

|||

Brauliod wrote:

Sorry, I didn't understand well the query (my problem is putting columns on top)... you want columns in the left hand side... mmm... for horizontal reports there is an interesting post in a blog tell me if this is what you are looking for

Does Reporting Services support horizontal tables (fixed rows and dynamic columns)http://blogs.msdn.com/chrishays/archive/2004/07/23/HorizontalTables.aspx

HTH

Braulio

Hmm, and does this hack work well for Excel exporting ?|||

Mmmm...

I'm not sure I found this solution meanwhile I was looking for another, I just bookmarked it :-(.

I think it's worth to give a try, it doesn't use subtables or strange stuff, and the guy that wrote that stuff owns a lot of kudos, must be good stuff.

Good luck

Braulio

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

Anyway to put the Totals column to the left of the data column?Subtotals have a Position property (at the bottom of the VS properties
window). By default it is "After". Does "Before" work for you?
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Cindy Lee" <cindylee@.hotmail.com> wrote in message
news:%23aaipLZpEHA.648@.tk2msftngp13.phx.gbl...
> Anyway to put the Totals column to the left of the data column?
>

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 Sub-Totals

Hi,
I have one column group and 2 columns (one Amount & other Text) under
it in a matrix. I added subtotal to that column group and now Amount
total appears fine but first TEXT value appears in total coloumn. I
would like to hide the TEXT value appearing in Subtotal column.
I sure there are lot of threads addressing this sub-total issue but i
was unable to find answer for my query.
Any help would be appreciated.
-SGYou must use the InScope function if you only want text to be displayed in
the details and not the subtotal. There was another posting addressing this
issue. I use something like the following in the expression :
=iif(inscope("ProductGroup"),first(Fields!Price.value,"ProductGroup"),nothing)
"SG" wrote:
> Hi,
> I have one column group and 2 columns (one Amount & other Text) under
> it in a matrix. I added subtotal to that column group and now Amount
> total appears fine but first TEXT value appears in total coloumn. I
> would like to hide the TEXT value appearing in Subtotal column.
> I sure there are lot of threads addressing this sub-total issue but i
> was unable to find answer for my query.
> Any help would be appreciated.
> -SG
>|||Dawie wrote:
> You must use the InScope function if you only want text to be displayed in
> the details and not the subtotal. There was another posting addressing this
> issue. I use something like the following in the expression :
> =iif(inscope("ProductGroup"),first(Fields!Price.value,"ProductGroup"),nothing)
>
> "SG" wrote:
> > Hi,
> >
> > I have one column group and 2 columns (one Amount & other Text) under
> > it in a matrix. I added subtotal to that column group and now Amount
> > total appears fine but first TEXT value appears in total coloumn. I
> > would like to hide the TEXT value appearing in Subtotal column.
> >
> > I sure there are lot of threads addressing this sub-total issue but i
> > was unable to find answer for my query.
> > Any help would be appreciated.
> >
> > -SG
> >
> >Where can i write the Expression for the subtotal?
Actually i am struggling to find a way to find where i can write the
Expression for SubTotal?

Matrix Subtotal is not summing

Hi,
I got a matrix, and a subtotal column (R-click on the column field, and
select "subtotal"), but at runtime the column is showing the value of the
first column, Matrix Subtotal is not summing.
Why is that.. ?
help please.
the same is when i make a subtotal for rows...I solved using SUM() in the data field of the matrix.
Tx.
"JuanCG_Col" wrote:
> Hi,
> I got a matrix, and a subtotal column (R-click on the column field, and
> select "subtotal"), but at runtime the column is showing the value of the
> first column, Matrix Subtotal is not summing.
> Why is that.. ?
> help please.
> the same is when i make a subtotal for rows...

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,

If I use the matrix to dynamically show the columns,but at the last column,I hope to get the average data ,But the subtotal can not help in this case,how can i do ?

thx for help!

Does anyone know how to do this? Doesnt seem possible in a matrix report?|||I think this thread can help you:

http://forums.microsoft.com/MSDN/showpost.aspx?postid=791849&siteid=1&message_id=524412&notification_id=524412
|||thanks for the link.

matrix subtotal

hi,

If I use the matrix to dynamically show the columns,but at the last column,I hope to get the average data ,But the subtotal can not help in this case,how can i do ?

thx for help!

Does anyone know how to do this? Doesnt seem possible in a matrix report?|||I think this thread can help you:

http://forums.microsoft.com/MSDN/showpost.aspx?postid=791849&siteid=1&message_id=524412&notification_id=524412
|||thanks for the link.