Hi
I have a SQL 7 and SQL 2000 instance on one server with a
total of 2Gb of physical ram.
Currently all the physical ram is used up by both
instances causing SQL to run slowly.
My question is: Is the 2Gb ram limit a server limit or a
instance limit ie if I add an additional 2 Gb of ram will
each instance use a maximum of 2Gb each or will the
additional ram be a waste of time?
Thanks in advance.
DarylThanks for your reply. You have confirmed what I believe
is the case so I will proceed and add the additional
memory. For your reference the OS is Win Server 2000 and
the SQL 2000 instance is the standard edition.
Thanks again.
Daryl
>--Original Message--
>It depends on the OS and edition of sql server you have
as to how much ram
>they can access but yes, each instance should be able to
use 2GB.
>--
>Andrew J. Kelly
>SQL Server MVP
>
>"dazza" <darylg@.intergen.co.nz> wrote in message
>news:018001c35228$af340fa0$7d02280a@.phx.gbl...
>> Hi
>> I have a SQL 7 and SQL 2000 instance on one server with
a
>> total of 2Gb of physical ram.
>> Currently all the physical ram is used up by both
>> instances causing SQL to run slowly.
>> My question is: Is the 2Gb ram limit a server limit or a
>> instance limit ie if I add an additional 2 Gb of ram
will
>> each instance use a maximum of 2Gb each or will the
>> additional ram be a waste of time?
>> Thanks in advance.
>> Daryl
>
>.
>sql
Showing posts with label total. Show all posts
Showing posts with label total. Show all posts
Friday, March 30, 2012
Monday, March 26, 2012
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
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
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 with totals at the top and a empty row
Hi,
I would like to have the following,
field 1 field 2 field 3
---
empty row
total 1000 1000 1000
empty row
field 4 2000 2000 2000
field 5 8000 8000 8000
Any advice on how to do that?Set Subtotal position to "Before" and use top and bottom padding to add
space around subtotal
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Reg" <reg@.dsl.za.org> wrote in message
news:eB47v2amEHA.2504@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I would like to have the following,
> field 1 field 2 field 3
> ---
> empty row
> total 1000 1000 1000
> empty row
> field 4 2000 2000 2000
> field 5 8000 8000 8000
> Any advice on how to do that?
>|||Thanks this works.
Now just one more problem. If we use the example below, on the column
grouping(Contains Months) have an iif to check if the date is before a
parameter passed to the report. I then use either one or the other field
from the database depending on whether it is true or false.
When I then add the total it would give me the wrong value, not really sure
which value it is giving me, but it always one value from the column and not
a calculated value.
"Lev Semenets [MSFT]" <levs@.microsoft.com> wrote in message
news:ev416MhmEHA.2504@.TK2MSFTNGP14.phx.gbl...
> Set Subtotal position to "Before" and use top and bottom padding to add
> space around subtotal
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "Reg" <reg@.dsl.za.org> wrote in message
> news:eB47v2amEHA.2504@.TK2MSFTNGP14.phx.gbl...
> > Hi,
> >
> > I would like to have the following,
> >
> > field 1 field 2 field 3
> > ---
> > empty row
> > total 1000 1000 1000
> > empty row
> > field 4 2000 2000 2000
> > field 5 8000 8000 8000
> >
> > Any advice on how to do that?
> >
> >
>|||Would you like to create simple report that exhibits this problem and e-mail
it to me?
Thanks,
Lev
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Reg" <reg@.dsl.za.org> wrote in message
news:%23NDgWQimEHA.3876@.TK2MSFTNGP15.phx.gbl...
> Thanks this works.
> Now just one more problem. If we use the example below, on the column
> grouping(Contains Months) have an iif to check if the date is before a
> parameter passed to the report. I then use either one or the other field
> from the database depending on whether it is true or false.
> When I then add the total it would give me the wrong value, not really
> sure
> which value it is giving me, but it always one value from the column and
> not
> a calculated value.
> "Lev Semenets [MSFT]" <levs@.microsoft.com> wrote in message
> news:ev416MhmEHA.2504@.TK2MSFTNGP14.phx.gbl...
>> Set Subtotal position to "Before" and use top and bottom padding to add
>> space around subtotal
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>>
>> "Reg" <reg@.dsl.za.org> wrote in message
>> news:eB47v2amEHA.2504@.TK2MSFTNGP14.phx.gbl...
>> > Hi,
>> >
>> > I would like to have the following,
>> >
>> > field 1 field 2 field 3
>> > ---
>> > empty row
>> > total 1000 1000 1000
>> > empty row
>> > field 4 2000 2000 2000
>> > field 5 8000 8000 8000
>> >
>> > Any advice on how to do that?
>> >
>> >
>>
>
I would like to have the following,
field 1 field 2 field 3
---
empty row
total 1000 1000 1000
empty row
field 4 2000 2000 2000
field 5 8000 8000 8000
Any advice on how to do that?Set Subtotal position to "Before" and use top and bottom padding to add
space around subtotal
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Reg" <reg@.dsl.za.org> wrote in message
news:eB47v2amEHA.2504@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I would like to have the following,
> field 1 field 2 field 3
> ---
> empty row
> total 1000 1000 1000
> empty row
> field 4 2000 2000 2000
> field 5 8000 8000 8000
> Any advice on how to do that?
>|||Thanks this works.
Now just one more problem. If we use the example below, on the column
grouping(Contains Months) have an iif to check if the date is before a
parameter passed to the report. I then use either one or the other field
from the database depending on whether it is true or false.
When I then add the total it would give me the wrong value, not really sure
which value it is giving me, but it always one value from the column and not
a calculated value.
"Lev Semenets [MSFT]" <levs@.microsoft.com> wrote in message
news:ev416MhmEHA.2504@.TK2MSFTNGP14.phx.gbl...
> Set Subtotal position to "Before" and use top and bottom padding to add
> space around subtotal
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "Reg" <reg@.dsl.za.org> wrote in message
> news:eB47v2amEHA.2504@.TK2MSFTNGP14.phx.gbl...
> > Hi,
> >
> > I would like to have the following,
> >
> > field 1 field 2 field 3
> > ---
> > empty row
> > total 1000 1000 1000
> > empty row
> > field 4 2000 2000 2000
> > field 5 8000 8000 8000
> >
> > Any advice on how to do that?
> >
> >
>|||Would you like to create simple report that exhibits this problem and e-mail
it to me?
Thanks,
Lev
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Reg" <reg@.dsl.za.org> wrote in message
news:%23NDgWQimEHA.3876@.TK2MSFTNGP15.phx.gbl...
> Thanks this works.
> Now just one more problem. If we use the example below, on the column
> grouping(Contains Months) have an iif to check if the date is before a
> parameter passed to the report. I then use either one or the other field
> from the database depending on whether it is true or false.
> When I then add the total it would give me the wrong value, not really
> sure
> which value it is giving me, but it always one value from the column and
> not
> a calculated value.
> "Lev Semenets [MSFT]" <levs@.microsoft.com> wrote in message
> news:ev416MhmEHA.2504@.TK2MSFTNGP14.phx.gbl...
>> Set Subtotal position to "Before" and use top and bottom padding to add
>> space around subtotal
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>>
>> "Reg" <reg@.dsl.za.org> wrote in message
>> news:eB47v2amEHA.2504@.TK2MSFTNGP14.phx.gbl...
>> > Hi,
>> >
>> > I would like to have the following,
>> >
>> > field 1 field 2 field 3
>> > ---
>> > empty row
>> > total 1000 1000 1000
>> > empty row
>> > field 4 2000 2000 2000
>> > field 5 8000 8000 8000
>> >
>> > Any advice on how to do that?
>> >
>> >
>>
>
Friday, March 23, 2012
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.
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 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
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 Line till the end of the dynamic columns & Bold totals and sub-totals
I am using Matrix in one of my reports and I would like to have my
sub-total & total line strech until the end of the last 'dynamic'
column.
Also I would like to display the totals and sub-totals in bold.
Is this possible?
If yes, please let me know the workaround as soon as possible.
Thanking you in advance.Hi Alkesh,
I also want the same thing to be done. Do let me know if you do get a
solution to it.
Thanks,
Param
Capgemini India|||Param,
One way out is to calculate the totals and the subtotals in SP itself.
Then put a conditional iif to get it formatted. Let me know your views.
Rgds,
Alkesh
alkesh.patel@.math.netsql
sub-total & total line strech until the end of the last 'dynamic'
column.
Also I would like to display the totals and sub-totals in bold.
Is this possible?
If yes, please let me know the workaround as soon as possible.
Thanking you in advance.Hi Alkesh,
I also want the same thing to be done. Do let me know if you do get a
solution to it.
Thanks,
Param
Capgemini India|||Param,
One way out is to calculate the totals and the subtotals in SP itself.
Then put a conditional iif to get it formatted. Let me know your views.
Rgds,
Alkesh
alkesh.patel@.math.netsql
Matrix 'Total' formatting
Hey All,
Maybe I have been looking in the wrong places for the last 2 hours but
can someone tell me how to add a borderstyle property to the 'subtotal'
row on a matrix. I can get it to work for the 'total' textbox itself
but not the entire row. I have even looked in the XML (which I never
miss with) and still cannot find anything. Please! Please! Someone help
me! This is for a client. Even if it cannot be done, let me know!!!!
Thanks in advance a billion times over,
KerrieJust click once on the row field then go to view and click properties
window. You can change the borderstyle there to solid. You can do that on
any column or row or just one field.
"Kerrie" <ksorrell@.cincom.com> wrote in message
news:1156788001.882559.169420@.75g2000cwc.googlegroups.com...
> Hey All,
> Maybe I have been looking in the wrong places for the last 2 hours but
> can someone tell me how to add a borderstyle property to the 'subtotal'
> row on a matrix. I can get it to work for the 'total' textbox itself
> but not the entire row. I have even looked in the XML (which I never
> miss with) and still cannot find anything. Please! Please! Someone help
> me! This is for a client. Even if it cannot be done, let me know!!!!
> Thanks in advance a billion times over,
> Kerrie
>|||Ben,
I have tried that and for some reason it will only do it for the Total
testbox, not the whole row. It is driving me nuts. I am not sure where
to go from here.
Thanks for your help though, it is greatly appreciated!
Thanks, Kerrie
Ben Watts wrote:
> Just click once on the row field then go to view and click properties
> window. You can change the borderstyle there to solid. You can do that on
> any column or row or just one field.
> "Kerrie" <ksorrell@.cincom.com> wrote in message
> news:1156788001.882559.169420@.75g2000cwc.googlegroups.com...
> > Hey All,
> >
> > Maybe I have been looking in the wrong places for the last 2 hours but
> > can someone tell me how to add a borderstyle property to the 'subtotal'
> > row on a matrix. I can get it to work for the 'total' textbox itself
> > but not the entire row. I have even looked in the XML (which I never
> > miss with) and still cannot find anything. Please! Please! Someone help
> > me! This is for a client. Even if it cannot be done, let me know!!!!
> >
> > Thanks in advance a billion times over,
> > Kerrie
> >|||You need to right-click on the little green triangle at the top right-hand
corner of the total textbox to get to the properties of the actual total.
HTH,
magendo_man
"Kerrie" wrote:
> Hey All,
> Maybe I have been looking in the wrong places for the last 2 hours but
> can someone tell me how to add a borderstyle property to the 'subtotal'
> row on a matrix. I can get it to work for the 'total' textbox itself
> but not the entire row. I have even looked in the XML (which I never
> miss with) and still cannot find anything. Please! Please! Someone help
> me! This is for a client. Even if it cannot be done, let me know!!!!
> Thanks in advance a billion times over,
> Kerrie
>|||YOU ARE THE GREATEST!!!!!!
THANK YOU! THANK YOU!
It worked!!!!!!
Thanks, Kerrie
magendo_man wrote:
> You need to right-click on the little green triangle at the top right-hand
> corner of the total textbox to get to the properties of the actual total.
> HTH,
> magendo_man
> "Kerrie" wrote:
> > Hey All,
> >
> > Maybe I have been looking in the wrong places for the last 2 hours but
> > can someone tell me how to add a borderstyle property to the 'subtotal'
> > row on a matrix. I can get it to work for the 'total' textbox itself
> > but not the entire row. I have even looked in the XML (which I never
> > miss with) and still cannot find anything. Please! Please! Someone help
> > me! This is for a client. Even if it cannot be done, let me know!!!!
> >
> > Thanks in advance a billion times over,
> > Kerrie
> >
> >
Maybe I have been looking in the wrong places for the last 2 hours but
can someone tell me how to add a borderstyle property to the 'subtotal'
row on a matrix. I can get it to work for the 'total' textbox itself
but not the entire row. I have even looked in the XML (which I never
miss with) and still cannot find anything. Please! Please! Someone help
me! This is for a client. Even if it cannot be done, let me know!!!!
Thanks in advance a billion times over,
KerrieJust click once on the row field then go to view and click properties
window. You can change the borderstyle there to solid. You can do that on
any column or row or just one field.
"Kerrie" <ksorrell@.cincom.com> wrote in message
news:1156788001.882559.169420@.75g2000cwc.googlegroups.com...
> Hey All,
> Maybe I have been looking in the wrong places for the last 2 hours but
> can someone tell me how to add a borderstyle property to the 'subtotal'
> row on a matrix. I can get it to work for the 'total' textbox itself
> but not the entire row. I have even looked in the XML (which I never
> miss with) and still cannot find anything. Please! Please! Someone help
> me! This is for a client. Even if it cannot be done, let me know!!!!
> Thanks in advance a billion times over,
> Kerrie
>|||Ben,
I have tried that and for some reason it will only do it for the Total
testbox, not the whole row. It is driving me nuts. I am not sure where
to go from here.
Thanks for your help though, it is greatly appreciated!
Thanks, Kerrie
Ben Watts wrote:
> Just click once on the row field then go to view and click properties
> window. You can change the borderstyle there to solid. You can do that on
> any column or row or just one field.
> "Kerrie" <ksorrell@.cincom.com> wrote in message
> news:1156788001.882559.169420@.75g2000cwc.googlegroups.com...
> > Hey All,
> >
> > Maybe I have been looking in the wrong places for the last 2 hours but
> > can someone tell me how to add a borderstyle property to the 'subtotal'
> > row on a matrix. I can get it to work for the 'total' textbox itself
> > but not the entire row. I have even looked in the XML (which I never
> > miss with) and still cannot find anything. Please! Please! Someone help
> > me! This is for a client. Even if it cannot be done, let me know!!!!
> >
> > Thanks in advance a billion times over,
> > Kerrie
> >|||You need to right-click on the little green triangle at the top right-hand
corner of the total textbox to get to the properties of the actual total.
HTH,
magendo_man
"Kerrie" wrote:
> Hey All,
> Maybe I have been looking in the wrong places for the last 2 hours but
> can someone tell me how to add a borderstyle property to the 'subtotal'
> row on a matrix. I can get it to work for the 'total' textbox itself
> but not the entire row. I have even looked in the XML (which I never
> miss with) and still cannot find anything. Please! Please! Someone help
> me! This is for a client. Even if it cannot be done, let me know!!!!
> Thanks in advance a billion times over,
> Kerrie
>|||YOU ARE THE GREATEST!!!!!!
THANK YOU! THANK YOU!
It worked!!!!!!
Thanks, Kerrie
magendo_man wrote:
> You need to right-click on the little green triangle at the top right-hand
> corner of the total textbox to get to the properties of the actual total.
> HTH,
> magendo_man
> "Kerrie" wrote:
> > Hey All,
> >
> > Maybe I have been looking in the wrong places for the last 2 hours but
> > can someone tell me how to add a borderstyle property to the 'subtotal'
> > row on a matrix. I can get it to work for the 'total' textbox itself
> > but not the entire row. I have even looked in the XML (which I never
> > miss with) and still cannot find anything. Please! Please! Someone help
> > me! This is for a client. Even if it cannot be done, let me know!!!!
> >
> > Thanks in advance a billion times over,
> > Kerrie
> >
> >
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?
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
> *****************************************
>
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?
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 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
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
Wednesday, March 21, 2012
matrix subtotal
Hi Everyone,
I am trying to get something like this using matrix in reporting services
The problem is, in the total group, the GST value and Net ( Net = Value + GST) can't added into the total group in the matrix.
Can somebody help... Thanks a lot.
You can use InScope in a matrix to determine 'where you are' e.g. are you a subtotal cell, are you within a particular column or row grouping
You can also create hidden groups
This helps to deal with non additive measures
In fact here's another post that may help
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1884435&SiteID=1
|||Thanks a lot..
That help me a lot...
Matrix Sub Total
Hi Experts...
I am using a matrix report. I am showing the subtotals of one of the columns
and it is comming correctly on the Right hand side of the column. My problem
is that I want the sub total to appear on the Left hand side of the selected
column insted of the Right side.
Is there any quick way of achieving this? Can anybody give a suggestion for
this?
Thanks
GaneshHi, Ganesh.
On the upper right corner of the subtotal cell, you should see a green
triangle.
Right-click over that triangle and select "Properties" (it should be
the only option enabled).
Then look for a property named "Position" set to the value "After".
You can switch it to "Before" and voil=E1. :)
Regards, Paulo Cunha|||thanks Paulo for the reply..it was really helpful
"Paulo X" <pjmcunha@.gmail.com> wrote in message
news:1141646924.794781.114380@.j33g2000cwa.googlegroups.com...
Hi, Ganesh.
On the upper right corner of the subtotal cell, you should see a green
triangle.
Right-click over that triangle and select "Properties" (it should be
the only option enabled).
Then look for a property named "Position" set to the value "After".
You can switch it to "Before" and voilá. :)
Regards,
Paulo Cunhasql
I am using a matrix report. I am showing the subtotals of one of the columns
and it is comming correctly on the Right hand side of the column. My problem
is that I want the sub total to appear on the Left hand side of the selected
column insted of the Right side.
Is there any quick way of achieving this? Can anybody give a suggestion for
this?
Thanks
GaneshHi, Ganesh.
On the upper right corner of the subtotal cell, you should see a green
triangle.
Right-click over that triangle and select "Properties" (it should be
the only option enabled).
Then look for a property named "Position" set to the value "After".
You can switch it to "Before" and voil=E1. :)
Regards, Paulo Cunha|||thanks Paulo for the reply..it was really helpful
"Paulo X" <pjmcunha@.gmail.com> wrote in message
news:1141646924.794781.114380@.j33g2000cwa.googlegroups.com...
Hi, Ganesh.
On the upper right corner of the subtotal cell, you should see a green
triangle.
Right-click over that triangle and select "Properties" (it should be
the only option enabled).
Then look for a property named "Position" set to the value "After".
You can switch it to "Before" and voilá. :)
Regards,
Paulo Cunhasql
matrix showing wrong totals
Going bonkers here. I have a matrix that displays total referrals by month
for the year. Two months, Feb and April are off by 1 when I view the matrix,
but other months show correct amt. when just doing a simple query with same
criteria, the dataset is correct. I've looked at the data countless times
but cannot deterine where the issue is. Possible matrix problem? anyone seem
results llike this?
HELP!more food for thought. I changed the query a bit. rather than selecting all
companies who referred to us, I selected just one. Results were accurate with
just one referrer. I'm still perplexed though...
"Brian L" wrote:
> Going bonkers here. I have a matrix that displays total referrals by month
> for the year. Two months, Feb and April are off by 1 when I view the matrix,
> but other months show correct amt. when just doing a simple query with same
> criteria, the dataset is correct. I've looked at the data countless times
> but cannot deterine where the issue is. Possible matrix problem? anyone seem
> results llike this?
> HELP!|||are you using olap or relational?
sounds like a standard olap situation where you need to write some
crazy-ass MDX statement
-Aaron
Brian L wrote:
> more food for thought. I changed the query a bit. rather than selecting all
> companies who referred to us, I selected just one. Results were accurate with
> just one referrer. I'm still perplexed though...
> "Brian L" wrote:
> > Going bonkers here. I have a matrix that displays total referrals by month
> > for the year. Two months, Feb and April are off by 1 when I view the matrix,
> > but other months show correct amt. when just doing a simple query with same
> > criteria, the dataset is correct. I've looked at the data countless times
> > but cannot deterine where the issue is. Possible matrix problem? anyone seem
> > results llike this?
> >
> > HELP!
for the year. Two months, Feb and April are off by 1 when I view the matrix,
but other months show correct amt. when just doing a simple query with same
criteria, the dataset is correct. I've looked at the data countless times
but cannot deterine where the issue is. Possible matrix problem? anyone seem
results llike this?
HELP!more food for thought. I changed the query a bit. rather than selecting all
companies who referred to us, I selected just one. Results were accurate with
just one referrer. I'm still perplexed though...
"Brian L" wrote:
> Going bonkers here. I have a matrix that displays total referrals by month
> for the year. Two months, Feb and April are off by 1 when I view the matrix,
> but other months show correct amt. when just doing a simple query with same
> criteria, the dataset is correct. I've looked at the data countless times
> but cannot deterine where the issue is. Possible matrix problem? anyone seem
> results llike this?
> HELP!|||are you using olap or relational?
sounds like a standard olap situation where you need to write some
crazy-ass MDX statement
-Aaron
Brian L wrote:
> more food for thought. I changed the query a bit. rather than selecting all
> companies who referred to us, I selected just one. Results were accurate with
> just one referrer. I'm still perplexed though...
> "Brian L" wrote:
> > Going bonkers here. I have a matrix that displays total referrals by month
> > for the year. Two months, Feb and April are off by 1 when I view the matrix,
> > but other months show correct amt. when just doing a simple query with same
> > criteria, the dataset is correct. I've looked at the data countless times
> > but cannot deterine where the issue is. Possible matrix problem? anyone seem
> > results llike this?
> >
> > HELP!
matrix row total drillthru
How can I get to specify navigation from a matrix row total.
Thanks,
SamIf your question is how to define which detail columns display when you
perform a drill through on a total, then here is the answer:
It is defined in the data model. Open the data model that the report is
based on. Click on the entity that you would like to define the drill
through columns for (you have to do this process for each entity). You
will see a property for the entity called DefaultDetailAttributes.
Click in the proprty and an elips on the right will appear (a small box
with three dots in it). Click on the elips and it will open a dialog
box displaying the drill through attributes. In the dialog box, there
is an add button. Click that and another dialog box will open listing
all the attributes for the entity.
When you are done, redeploy the model. Any existing reports will now
drill through to the detail you defined.
- Steve
Sam wrote:
> How can I get to specify navigation from a matrix row total.
> Thanks,
> Sam|||Then I think my prior response will do the trick. Let me know. However,
I am assuming your report was created with report builder and not
visual studio reporting tool...
Sam wrote:
> Appreciate your response Steve. What I am trying to do is implement a
> drillthru on row subtotal.
> "steve" wrote:
> > If your question is how to define which detail columns display when you
> > perform a drill through on a total, then here is the answer:
> >
> > It is defined in the data model. Open the data model that the report is
> > based on. Click on the entity that you would like to define the drill
> > through columns for (you have to do this process for each entity). You
> > will see a property for the entity called DefaultDetailAttributes.
> > Click in the proprty and an elips on the right will appear (a small box
> > with three dots in it). Click on the elips and it will open a dialog
> > box displaying the drill through attributes. In the dialog box, there
> > is an add button. Click that and another dialog box will open listing
> > all the attributes for the entity.
> >
> > When you are done, redeploy the model. Any existing reports will now
> > drill through to the detail you defined.
> >
> > - Steve
> >
> > Sam wrote:
> > > How can I get to specify navigation from a matrix row total.
> > >
> > > Thanks,
> > > Sam
> >
> >
Thanks,
SamIf your question is how to define which detail columns display when you
perform a drill through on a total, then here is the answer:
It is defined in the data model. Open the data model that the report is
based on. Click on the entity that you would like to define the drill
through columns for (you have to do this process for each entity). You
will see a property for the entity called DefaultDetailAttributes.
Click in the proprty and an elips on the right will appear (a small box
with three dots in it). Click on the elips and it will open a dialog
box displaying the drill through attributes. In the dialog box, there
is an add button. Click that and another dialog box will open listing
all the attributes for the entity.
When you are done, redeploy the model. Any existing reports will now
drill through to the detail you defined.
- Steve
Sam wrote:
> How can I get to specify navigation from a matrix row total.
> Thanks,
> Sam|||Then I think my prior response will do the trick. Let me know. However,
I am assuming your report was created with report builder and not
visual studio reporting tool...
Sam wrote:
> Appreciate your response Steve. What I am trying to do is implement a
> drillthru on row subtotal.
> "steve" wrote:
> > If your question is how to define which detail columns display when you
> > perform a drill through on a total, then here is the answer:
> >
> > It is defined in the data model. Open the data model that the report is
> > based on. Click on the entity that you would like to define the drill
> > through columns for (you have to do this process for each entity). You
> > will see a property for the entity called DefaultDetailAttributes.
> > Click in the proprty and an elips on the right will appear (a small box
> > with three dots in it). Click on the elips and it will open a dialog
> > box displaying the drill through attributes. In the dialog box, there
> > is an add button. Click that and another dialog box will open listing
> > all the attributes for the entity.
> >
> > When you are done, redeploy the model. Any existing reports will now
> > drill through to the detail you defined.
> >
> > - Steve
> >
> > Sam wrote:
> > > How can I get to specify navigation from a matrix row total.
> > >
> > > Thanks,
> > > Sam
> >
> >
Matrix row total
I have a count(field!orderstamp.Value) in a field and I would like to
show the percentage that number is of the whole row?
I can add a column next to it, but how do I show a percentage of a row
in a matrix?
Thanks.Assuming you have a matrix row grouping called "MatrixRowGroup", you can try
this:
=Count(Fields!orderstamp.Value) / Count(Fields!orderstamp.Value,
"MatrixRowGroup")
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"John Geddes" <john_g@.alamode.com> wrote in message
news:OVtGb$p8EHA.2804@.TK2MSFTNGP15.phx.gbl...
> I have a count(field!orderstamp.Value) in a field and I would like to
> show the percentage that number is of the whole row?
> I can add a column next to it, but how do I show a percentage of a row
> in a matrix?
> Thanks.
>
show the percentage that number is of the whole row?
I can add a column next to it, but how do I show a percentage of a row
in a matrix?
Thanks.Assuming you have a matrix row grouping called "MatrixRowGroup", you can try
this:
=Count(Fields!orderstamp.Value) / Count(Fields!orderstamp.Value,
"MatrixRowGroup")
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"John Geddes" <john_g@.alamode.com> wrote in message
news:OVtGb$p8EHA.2804@.TK2MSFTNGP15.phx.gbl...
> I have a count(field!orderstamp.Value) in a field and I would like to
> show the percentage that number is of the whole row?
> I can add a column next to it, but how do I show a percentage of a row
> in a matrix?
> Thanks.
>
Matrix report question
Hi,
I recently made a matrix report and I have a small problem with it.
I'm trying to add a row that contain the total of each column but I'm not
sure how to do that.
Do I need to do something at the query level or this can be done within the
report designer?
I'm using SQL 2005.
Thanks!
SimonOn Apr 18, 10:08 am, "Simon Renshaw" <s...@.benchmarkconsulting.com>
wrote:
> Hi,
> I recently made a matrix report and I have a small problem with it.
> I'm trying to add a row that contain the total of each column but I'm not
> sure how to do that.
> Do I need to do something at the query level or this can be done within the
> report designer?
> I'm using SQL 2005.
> Thanks!
> Simon
This link may be helpful:
http://groups.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_thread/thread/d3293edae89f51e8/c15839fd5fb7df14?lnk=st&q=matrix+report+subtotal&rnum=4#c15839fd5fb7df14
That said, I prefer to handle the subtotals/totals in the query/stored
procedure that sources the report (via a while loop or cursor). Hope
this is helpful.
Regards,
Enrique Martinez
Sr. Software Consultant|||Yes it is.
Thanks a lot!
Simon
"EMartinez" <emartinez.pr1@.gmail.com> wrote in message
news:1176921521.454724.47460@.l77g2000hsb.googlegroups.com...
> This link may be helpful:
> http://groups.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_thread/thread/d3293edae89f51e8/c15839fd5fb7df14?lnk=st&q=matrix+report+subtotal&rnum=4#c15839fd5fb7df14
> That said, I prefer to handle the subtotals/totals in the query/stored
> procedure that sources the report (via a while loop or cursor). Hope
> this is helpful.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>
I recently made a matrix report and I have a small problem with it.
I'm trying to add a row that contain the total of each column but I'm not
sure how to do that.
Do I need to do something at the query level or this can be done within the
report designer?
I'm using SQL 2005.
Thanks!
SimonOn Apr 18, 10:08 am, "Simon Renshaw" <s...@.benchmarkconsulting.com>
wrote:
> Hi,
> I recently made a matrix report and I have a small problem with it.
> I'm trying to add a row that contain the total of each column but I'm not
> sure how to do that.
> Do I need to do something at the query level or this can be done within the
> report designer?
> I'm using SQL 2005.
> Thanks!
> Simon
This link may be helpful:
http://groups.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_thread/thread/d3293edae89f51e8/c15839fd5fb7df14?lnk=st&q=matrix+report+subtotal&rnum=4#c15839fd5fb7df14
That said, I prefer to handle the subtotals/totals in the query/stored
procedure that sources the report (via a while loop or cursor). Hope
this is helpful.
Regards,
Enrique Martinez
Sr. Software Consultant|||Yes it is.
Thanks a lot!
Simon
"EMartinez" <emartinez.pr1@.gmail.com> wrote in message
news:1176921521.454724.47460@.l77g2000hsb.googlegroups.com...
> This link may be helpful:
> http://groups.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_thread/thread/d3293edae89f51e8/c15839fd5fb7df14?lnk=st&q=matrix+report+subtotal&rnum=4#c15839fd5fb7df14
> That said, I prefer to handle the subtotals/totals in the query/stored
> procedure that sources the report (via a while loop or cursor). Hope
> this is helpful.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>
Monday, March 19, 2012
Matrix Report Columns Grand Total
Hi There i have a Report That Display's data in following format
Areas
1 What ever 3 0 2 1
2 ABC 0 0 1 0
-
Total 3 0 3 1
The Areas are Matric Columns and are dynamically generated .
i dont want sub total of each row , but i want Grand total at the end of the all columns generated by matrix
I have no clue how do to it
Kind Regards
Just Right-click the column header and choose subtotal.
|||No this is what i dont wantthis will add total below each row which i dont want i want the grand totals after the last row|||
OK, we seem to be talking cross purposes and your explanation is not clear enough.
If you want an additional column at the end the has the total value for all areas then right-click the column group and choose subtotal.
If you want an additional row at the very bottom that has the total for all the code/description row category, then right-click on the code group i.e. outermost group, and choose subtotal.
|||let me explain again here is the report's result few row
Total Row 0 African Colum total 0 15000 ( asian) rest of totals
the regions name that you see are actually column of matrix report and i want total in last row for each column
i have added total as column but i also want grand total row at the end
means horizontal total of each row
and vertical total of each column
i hope i got it right this time
Fine. Then doing what I said in my previous reply should work for you. Have you tried it?
Just to be totally explicit, right-click the textbox in your matrix that has the "code" and not the one that has the "description" and select the subtotal option.
|||worked thanks a lot
Subscribe to:
Posts (Atom)