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
Geert Verhoeven wrote: