Max Function - But between a fixed value and a column

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

max degree of parallelism Option

Dear all,
What is the ideal value for "max degree of parallelism" if we have very long
running queries which eat up lots of resources in peak hours.
We have 4 processors Dell machine. Our last DBA recommended it to 4 but new
DBA suggests it to set it to 1.
Need your expert opinion.
Kay
The only way to see what is best is to test each of them. It depends a lot
on what you are doing, schemas etc. It could be any of them.
Andrew J. Kelly SQL MVP
"Kay" <CallDBA@.hotmail.com> wrote in message
news:OPcIup92FHA.2800@.TK2MSFTNGP10.phx.gbl...
> Dear all,
> What is the ideal value for "max degree of parallelism" if we have very
> long running queries which eat up lots of resources in peak hours.
> We have 4 processors Dell machine. Our last DBA recommended it to 4 but
> new DBA suggests it to set it to 1.
> Need your expert opinion.
> Kay
>
|||On Fri, 28 Oct 2005 21:12:47 +0500, Kay wrote:

> Dear all,
> What is the ideal value for "max degree of parallelism" if we have very long
> running queries which eat up lots of resources in peak hours.
> We have 4 processors Dell machine. Our last DBA recommended it to 4 but new
> DBA suggests it to set it to 1.
> Need your expert opinion.
> Kay
Hello,
Just to add a few things.
If you have hyperthreading procs, MS recommends to set the max degree of p.
to the number of physical procs. Ref here :
http://support.microsoft.com/default.aspx/kb/322385
Look also at the evolution over time of CXPACKET wait types ( DBCC SQLPERF
(waitstats) ), indicating if parallelized queries wait for their threads to
finish.
Look also, on the CPUs, the percentage of Kernel Time. That could give you
a hint if the parallelism is detrimental. E.g. if decreasing your maxdop
show a drop of the kernel time, that could be a good thing.
But as it was said, a good answer is, try not to have very long running
queries at peak hours...
sql

max degree of parallelism Option

Dear all,
What is the ideal value for "max degree of parallelism" if we have very long
running queries which eat up lots of resources in peak hours.
We have 4 processors Dell machine. Our last DBA recommended it to 4 but new
DBA suggests it to set it to 1.
Need your expert opinion.
Kay
On Fri, 28 Oct 2005 21:12:47 +0500, "Kay" <CallDBA@.hotmail.com> wrote:
>What is the ideal value for "max degree of parallelism" if we have very long
>running queries which eat up lots of resources in peak hours.
>We have 4 processors Dell machine. Our last DBA recommended it to 4 but new
>DBA suggests it to set it to 1.
>Need your expert opinion.
If you have hyperthreading turned on, you may have 8!
Well, y'know, I guess it's obvious, but you don't really want to have
long-running queries during peak hours, do you? There are various
tuning things you can do, but that's more a matter of design and
operations management.
J.

max degree of parallelism Option

Dear all,
What is the ideal value for "max degree of parallelism" if we have very long
running queries which eat up lots of resources in peak hours.
We have 4 processors Dell machine. Our last DBA recommended it to 4 but new
DBA suggests it to set it to 1.
Need your expert opinion.
KayOn Fri, 28 Oct 2005 21:12:47 +0500, "Kay" <CallDBA@.hotmail.com> wrote:
>What is the ideal value for "max degree of parallelism" if we have very long
>running queries which eat up lots of resources in peak hours.
>We have 4 processors Dell machine. Our last DBA recommended it to 4 but new
>DBA suggests it to set it to 1.
>Need your expert opinion.
If you have hyperthreading turned on, you may have 8!
Well, y'know, I guess it's obvious, but you don't really want to have
long-running queries during peak hours, do you? There are various
tuning things you can do, but that's more a matter of design and
operations management.
J.

max degree of parallelism Option

Dear all,
What is the ideal value for "max degree of parallelism" if we have very long
running queries which eat up lots of resources in peak hours.
We have 4 processors Dell machine. Our last DBA recommended it to 4 but new
DBA suggests it to set it to 1.
Need your expert opinion.
KayThe only way to see what is best is to test each of them. It depends a lot
on what you are doing, schemas etc. It could be any of them.
--
Andrew J. Kelly SQL MVP
"Kay" <CallDBA@.hotmail.com> wrote in message
news:OPcIup92FHA.2800@.TK2MSFTNGP10.phx.gbl...
> Dear all,
> What is the ideal value for "max degree of parallelism" if we have very
> long running queries which eat up lots of resources in peak hours.
> We have 4 processors Dell machine. Our last DBA recommended it to 4 but
> new DBA suggests it to set it to 1.
> Need your expert opinion.
> Kay
>|||On Fri, 28 Oct 2005 21:12:47 +0500, Kay wrote:
> Dear all,
> What is the ideal value for "max degree of parallelism" if we have very long
> running queries which eat up lots of resources in peak hours.
> We have 4 processors Dell machine. Our last DBA recommended it to 4 but new
> DBA suggests it to set it to 1.
> Need your expert opinion.
> Kay
Hello,
Just to add a few things.
If you have hyperthreading procs, MS recommends to set the max degree of p.
to the number of physical procs. Ref here :
http://support.microsoft.com/default.aspx/kb/322385
Look also at the evolution over time of CXPACKET wait types ( DBCC SQLPERF
(waitstats) ), indicating if parallelized queries wait for their threads to
finish.
Look also, on the CPUs, the percentage of Kernel Time. That could give you
a hint if the parallelism is detrimental. E.g. if decreasing your maxdop
show a drop of the kernel time, that could be a good thing.
But as it was said, a good answer is, try not to have very long running
queries at peak hours...

max degree of parallelism Option

Dear all,
What is the ideal value for "max degree of parallelism" if we have very long
running queries which eat up lots of resources in peak hours.
We have 4 processors Dell machine. Our last DBA recommended it to 4 but new
DBA suggests it to set it to 1.
Need your expert opinion.
KayThe only way to see what is best is to test each of them. It depends a lot
on what you are doing, schemas etc. It could be any of them.
Andrew J. Kelly SQL MVP
"Kay" <CallDBA@.hotmail.com> wrote in message
news:OPcIup92FHA.2800@.TK2MSFTNGP10.phx.gbl...
> Dear all,
> What is the ideal value for "max degree of parallelism" if we have very
> long running queries which eat up lots of resources in peak hours.
> We have 4 processors Dell machine. Our last DBA recommended it to 4 but
> new DBA suggests it to set it to 1.
> Need your expert opinion.
> Kay
>|||On Fri, 28 Oct 2005 21:12:47 +0500, Kay wrote:

> Dear all,
> What is the ideal value for "max degree of parallelism" if we have very lo
ng
> running queries which eat up lots of resources in peak hours.
> We have 4 processors Dell machine. Our last DBA recommended it to 4 but ne
w
> DBA suggests it to set it to 1.
> Need your expert opinion.
> Kay
Hello,
Just to add a few things.
If you have hyperthreading procs, MS recommends to set the max degree of p.
to the number of physical procs. Ref here :
http://support.microsoft.com/default.aspx/kb/322385
Look also at the evolution over time of CXPACKET wait types ( DBCC SQLPERF
(waitstats) ), indicating if parallelized queries wait for their threads to
finish.
Look also, on the CPUs, the percentage of Kernel Time. That could give you
a hint if the parallelism is detrimental. E.g. if decreasing your maxdop
show a drop of the kernel time, that could be a good thing.
But as it was said, a good answer is, try not to have very long running
queries at peak hours...

max degree of parallelism Option

Dear all,
What is the ideal value for "max degree of parallelism" if we have very long
running queries which eat up lots of resources in peak hours.
We have 4 processors Dell machine. Our last DBA recommended it to 4 but new
DBA suggests it to set it to 1.
Need your expert opinion.
KayOn Fri, 28 Oct 2005 21:12:47 +0500, "Kay" <CallDBA@.hotmail.com> wrote:
>What is the ideal value for "max degree of parallelism" if we have very lon
g
>running queries which eat up lots of resources in peak hours.
>We have 4 processors Dell machine. Our last DBA recommended it to 4 but new
>DBA suggests it to set it to 1.
>Need your expert opinion.
If you have hyperthreading turned on, you may have 8!
Well, y'know, I guess it's obvious, but you don't really want to have
long-running queries during peak hours, do you? There are various
tuning things you can do, but that's more a matter of design and
operations management.
J.sql

MATRIX: Need different count of cols in (Sub)total

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

Matrix: Hide null value row

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

Thanks for taking the time to read.

The database tables looks like:

Month Sales Product

- - --

August 2007 700.00 Apples

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

The matrix looks like:

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

Apples 700.00 380.00

Oranges 400.00 1200.00

Grapfruit 400.00

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

|||

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

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

Thanks for your help.sql

Matrix Zero Values

My Matrix presently shows results as follows.
column 1 column 2
row 1 23
row 2 34 34
How would I make the default null value to display as zero. Eg. Row 1, Column 1 would have a value of 0You will need an expression:
=iif(First(Fields!<FieldName>.Value) is Nothing, 0,
First(Fields!<FieldName>.Value))
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"SAcanuck" <SAcanuck@.discussions.microsoft.com> wrote in message
news:9D9A293F-1E84-439E-B1D5-EF6E76DFD068@.microsoft.com...
> My Matrix presently shows results as follows.
> column 1 column 2
> row 1 23
> row 2 34 34
> How would I make the default null value to display as zero. Eg. Row 1,
Column 1 would have a value of 0

Matrix subtotals only getting first value

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

Matrix SubTotals

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

Matrix Subtotal is not summing

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

matrix subtotal

Hi Everyone,

I am trying to get something like this using matrix in reporting services

SE CP CG Total Qty Value Qty Value Qty Value Qty Value GST Net 7/07/2006 124 233 552 525 141 2544 563 4215 321 4536 8/07/2006 1245 211 11 142 555 1444 1987 1854 124 1978

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

Matrix Report Textbox Expression

=IIF( Sum(Fields!Percentile.Value, "RegionalSnaps") >= .10, "Yellow", "White")

I only want the ones above 10% to be yellow, otherwise white.

Any ideas anyone on why the entire range of cells are yellow and not just the one above 10%?

Ive tried both .10 and 10 for the value, and still the same thing

Because you're doing a sum, it's doing it for the entire dataset. If this expression is in a grouping e.g. table, matrix, list etc... then try changing the expresison to

=IIF( Fields!Percentile.Value >= .10, "Yellow", "White")

Effectively evalueated for every row in the group.

|||WOW, I've been searching for an answer for this for a long time, I really appreciate your time.|||You're welcome.

Matrix Report Not Showing All Columns

I have a matrix report that I am using with MonthName(Fields!Month.Value) across the top of the report. However, when there is no data under the column, the month is skipped. So, for example, I get this:

January March

Actual Goal Actual Goal

25 25 30 35

I have tried adding

iif(Sum(Fields!Total.Value, "DatasetName")> 0, Sum(Fields!Total.Value, "DatasetName"),"N/A")

But it still is not showing up.

Any ideas?

Thanks,

SHP

SHP,

You will need to make sure in your SQL statement you are returning data for that month. If there is no data then you will need to return "Zero" and or "Spaces"

Ham

|||

Thanks, Ham.

Yeah, I set up the month table in the stored procedure and then returned isnull(datafield,0) and that took care of the problem.

Thanks for your help. I found a similiar question that directed the person to the SQL statement and realized that was where I needed to make my changes.

Thanks again.

SHP

|||

Not a problem glad to help,

Also can you mark my reply as answer so I can get credit for the solution. Thanks.

Ham

|||Sure. No problem.sql

Matrix Report Background Color

I have the following applied as an expression to the background color
on a cell
=IIF( Sum(Fields!Percentile.Value, "RegionalSnaps") >= 10, "Yellow",
"White")
I only want the ones above 10% to be yellow, otherwise white.
Any ideas anyone on why the entire range of cells are yellow and not
just the one above 10%?Is percentile an integer or a float. If float test is >= .10
"duhaas@.gmail.com" wrote:
> I have the following applied as an expression to the background color
> on a cell
> =IIF( Sum(Fields!Percentile.Value, "RegionalSnaps") >= 10, "Yellow",
> "White")
> I only want the ones above 10% to be yellow, otherwise white.
> Any ideas anyone on why the entire range of cells are yellow and not
> just the one above 10%?
>|||here is the query being called:
SELECT s.babr, s.SicDescription, SUM(s.AccountMV) AS mv,
SUM(s.AccountMV) / q.ps AS Percentile
FROM SnapsRaw s INNER JOIN
(SELECT babr, SUM(accountmv) ps
FROM snapsraw
WHERE monthend = '08/01/2006'
GROUP BY babr) q ON q.babr = s.babr
WHERE s.MonthEnd = '08/01/2006'
GROUP BY s.Babr, s.SicDescription, q.ps, q.babr
percentile is the number being displayed, changing it to .10 didnt seem
to help
On Nov 10, 4:12 pm, William <Will...@.discussions.microsoft.com> wrote:
> Is percentile an integer or a float. If float test is >= .10
> "duh...@.gmail.com" wrote:
> > I have the following applied as an expression to the background color
> > on a cell
> > =IIF( Sum(Fields!Percentile.Value, "RegionalSnaps") >= 10, "Yellow",
> > "White")
> > I only want the ones above 10% to be yellow, otherwise white.
> > Any ideas anyone on why the entire range of cells are yellow and not
> > just the one above 10%?|||Unless AccountMV & q.ps are both integers, Percentile is a decimal number so
your test should use the decimal as in:
IIF( Sum(Fields!Percentile.Value, "RegionalSnaps") >= .10, "Yellow", "White")
"duhaas@.gmail.com" wrote:
> here is the query being called:
> SELECT s.babr, s.SicDescription, SUM(s.AccountMV) AS mv,
> SUM(s.AccountMV) / q.ps AS Percentile
> FROM SnapsRaw s INNER JOIN
> (SELECT babr, SUM(accountmv) ps
> FROM snapsraw
> WHERE monthend = '08/01/2006'
> GROUP BY babr) q ON q.babr = s.babr
> WHERE s.MonthEnd = '08/01/2006'
> GROUP BY s.Babr, s.SicDescription, q.ps, q.babr
> percentile is the number being displayed, changing it to .10 didnt seem
> to help
>
> On Nov 10, 4:12 pm, William <Will...@.discussions.microsoft.com> wrote:
> > Is percentile an integer or a float. If float test is >= .10
> >
> > "duh...@.gmail.com" wrote:
> > > I have the following applied as an expression to the background color
> > > on a cell
> >
> > > =IIF( Sum(Fields!Percentile.Value, "RegionalSnaps") >= 10, "Yellow",
> > > "White")
> >
> > > I only want the ones above 10% to be yellow, otherwise white.
> >
> > > Any ideas anyone on why the entire range of cells are yellow and not
> > > just the one above 10%?
>|||William
Appreciate all the help, I have made the change, and its still
highlighting the entire range of cells instead of just those above 10%,
here is the code for the report, its textbox2 that im playing with:
<?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">
<Body>
<ReportItems>
<Matrix Name="matrix1">
<Corner>
<ReportItems>
<Textbox Name="textbox3">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextDecoration>Underline</TextDecoration>
<FontSize>7pt</FontSize>
<TextAlign>Left</TextAlign>
<Color>Blue</Color>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>3</ZIndex>
<rd:DefaultName>textbox3</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>SIC ID/ Industry</Value>
</Textbox>
</ReportItems>
</Corner>
<Style />
<MatrixRows>
<MatrixRow>
<MatrixCells>
<MatrixCell>
<ReportItems>
<Textbox Name="textbox2">
<Style>
<PaddingLeft>1pt</PaddingLeft>
<Format>g</Format>
<BackgroundColor>=IIF(Sum(Fields!Percentile.Value, "RegionalSnaps")
>= .10, "Yellow", "White")</BackgroundColor>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<FontSize>7pt</FontSize>
<TextAlign>Center</TextAlign>
<PaddingBottom>1pt</PaddingBottom>
<PaddingTop>1pt</PaddingTop>
<PaddingRight>1pt</PaddingRight>
</Style>
<rd:DefaultName>textbox2</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=sum(Fields!Percentile.Value)</Value>
</Textbox>
</ReportItems>
</MatrixCell>
</MatrixCells>
<Height>0.15in</Height>
</MatrixRow>
</MatrixRows>
<MatrixColumns>
<MatrixColumn>
<Width>0.625in</Width>
</MatrixColumn>
</MatrixColumns>
<DataSetName>RegionalSnaps</DataSetName>
<ColumnGroupings>
<ColumnGrouping>
<DynamicColumns>
<Grouping Name="matrix1_babr">
<GroupExpressions>
<GroupExpression>=Fields!babr.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<Sorting>
<SortBy>
<SortExpression>=Fields!babr.Value</SortExpression>
<Direction>Ascending</Direction>
</SortBy>
</Sorting>
<ReportItems>
<Textbox Name="babr">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BorderWidth>
<Bottom>1pt</Bottom>
</BorderWidth>
<BorderColor>
<Bottom>Black</Bottom>
</BorderColor>
<BorderStyle>
<Bottom>Solid</Bottom>
</BorderStyle>
<FontSize>7pt</FontSize>
<TextAlign>Center</TextAlign>
<Color>Blue</Color>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>2</ZIndex>
<rd:DefaultName>babr</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!babr.Value</Value>
</Textbox>
</ReportItems>
</DynamicColumns>
<Height>0.15in</Height>
</ColumnGrouping>
</ColumnGroupings>
<Left>0.125in</Left>
<RowGroupings>
<RowGrouping>
<DynamicRows>
<Grouping Name="matrix1_SicDescription">
<GroupExpressions>
<GroupExpression>=Fields!SicDescription.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<Sorting>
<SortBy>
<SortExpression>=Fields!SicDescription.Value</SortExpression>
<Direction>Ascending</Direction>
</SortBy>
</Sorting>
<ReportItems>
<Textbox Name="SicDescription">
<Style>
<PaddingLeft>1pt</PaddingLeft>
<BorderWidth>
<Right>1pt</Right>
</BorderWidth>
<BorderColor>
<Right>Black</Right>
</BorderColor>
<BorderStyle>
<Right>Solid</Right>
</BorderStyle>
<FontSize>7pt</FontSize>
<TextAlign>Left</TextAlign>
<PaddingBottom>1pt</PaddingBottom>
<PaddingTop>1pt</PaddingTop>
<PaddingRight>1pt</PaddingRight>
</Style>
<ZIndex>1</ZIndex>
<rd:DefaultName>SicDescription</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!SicDescription.Value</Value>
</Textbox>
</ReportItems>
</DynamicRows>
<Width>1.75in</Width>
</RowGrouping>
</RowGroupings>
</Matrix>
</ReportItems>
<Style />
<Height>0.3in</Height>
</Body>
<DataSources>
<DataSource Name="RegionalSnaps">
<rd:DataSourceID>74b2d458-9f2d-4e29-8375-2ed584778c1c</rd:DataSourceID>
<DataSourceReference>RegionalSnaps</DataSourceReference>
</DataSource>
</DataSources>
<Code />
<Width>2.5in</Width>
<DataSets>
<DataSet Name="RegionalSnaps">
<Fields>
<Field Name="babr">
<DataField>babr</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="SicDescription">
<DataField>SicDescription</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="mv">
<DataField>mv</DataField>
<rd:TypeName>System.Decimal</rd:TypeName>
</Field>
<Field Name="Percentile">
<DataField>Percentile</DataField>
<rd:TypeName>System.Decimal</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>RegionalSnaps</DataSourceName>
<CommandText>SELECT s.babr, s.SicDescription,
SUM(s.AccountMV) AS mv, SUM(s.AccountMV) / q.ps AS Percentile
FROM SnapsRaw s INNER JOIN
(SELECT babr, SUM(accountmv) ps
FROM snapsraw
WHERE monthend = '08/01/2006'
GROUP BY babr) q ON q.babr = s.babr
WHERE s.MonthEnd = '08/01/2006'
GROUP BY s.Babr, s.SicDescription, q.ps, q.babr</CommandText>
</Query>
</DataSet>
</DataSets>
<rd:SnapToGrid>true</rd:SnapToGrid>
<rd:DrawGrid>true</rd:DrawGrid>
<rd:ReportID>f4c1c559-ee3a-43cb-9c9d-27f23c7b8cc8</rd:ReportID>
<Language>en-US</Language>
</Report>
On Nov 15, 8:35 am, William <Will...@.discussions.microsoft.com> wrote:
> Unless AccountMV & q.ps are both integers, Percentile is a decimal number so
> your test should use the decimal as in:
> IIF( Sum(Fields!Percentile.Value, "RegionalSnaps") >= .10, "Yellow", "White")"duh...@.gmail.com" wrote:
> > here is the query being called:
> > SELECT s.babr, s.SicDescription, SUM(s.AccountMV) AS mv,
> > SUM(s.AccountMV) / q.ps AS Percentile
> > FROM SnapsRaw s INNER JOIN
> > (SELECT babr, SUM(accountmv) ps
> > FROM snapsraw
> > WHERE monthend = '08/01/2006'
> > GROUP BY babr) q ON q.babr = s.babr
> > WHERE s.MonthEnd = '08/01/2006'
> > GROUP BY s.Babr, s.SicDescription, q.ps, q.babr
> > percentile is the number being displayed, changing it to .10 didnt seem
> > to help
> > On Nov 10, 4:12 pm, William <Will...@.discussions.microsoft.com> wrote:
> > > Is percentile an integer or a float. If float test is >= .10
> > > "duh...@.gmail.com" wrote:
> > > > I have the following applied as an expression to the background color
> > > > on a cell
> > > > =IIF( Sum(Fields!Percentile.Value, "RegionalSnaps") >= 10, "Yellow",
> > > > "White")
> > > > I only want the ones above 10% to be yellow, otherwise white.
> > > > Any ideas anyone on why the entire range of cells are yellow and not
> > > > just the one above 10%?|||I took your IIF statement and applied it to a cell in a report I have and a
value of .111 came back with yellow background. So I would go back to the
format of the value coming in and verify that you have a number with decimals
coming in. Also verify the scope of the IIF test if you have multiple data
sources with the same field names.
"duhaas@.gmail.com" wrote:
> William
> Appreciate all the help, I have made the change, and its still
> highlighting the entire range of cells instead of just those above 10%,
> here is the code for the report, its textbox2 that im playing with:
> <?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">
> <Body>
> <ReportItems>
> <Matrix Name="matrix1">
> <Corner>
> <ReportItems>
> <Textbox Name="textbox3">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <TextDecoration>Underline</TextDecoration>
> <FontSize>7pt</FontSize>
> <TextAlign>Left</TextAlign>
> <Color>Blue</Color>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> <FontWeight>700</FontWeight>
> </Style>
> <ZIndex>3</ZIndex>
> <rd:DefaultName>textbox3</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>SIC ID/ Industry</Value>
> </Textbox>
> </ReportItems>
> </Corner>
> <Style />
> <MatrixRows>
> <MatrixRow>
> <MatrixCells>
> <MatrixCell>
> <ReportItems>
> <Textbox Name="textbox2">
> <Style>
> <PaddingLeft>1pt</PaddingLeft>
> <Format>g</Format>
> <BackgroundColor>=IIF(Sum(Fields!Percentile.Value, "RegionalSnaps")
> >= .10, "Yellow", "White")</BackgroundColor>
> <BorderStyle>
> <Default>Solid</Default>
> </BorderStyle>
> <FontSize>7pt</FontSize>
> <TextAlign>Center</TextAlign>
> <PaddingBottom>1pt</PaddingBottom>
> <PaddingTop>1pt</PaddingTop>
> <PaddingRight>1pt</PaddingRight>
> </Style>
> <rd:DefaultName>textbox2</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>=sum(Fields!Percentile.Value)</Value>
> </Textbox>
> </ReportItems>
> </MatrixCell>
> </MatrixCells>
> <Height>0.15in</Height>
> </MatrixRow>
> </MatrixRows>
> <MatrixColumns>
> <MatrixColumn>
> <Width>0.625in</Width>
> </MatrixColumn>
> </MatrixColumns>
> <DataSetName>RegionalSnaps</DataSetName>
> <ColumnGroupings>
> <ColumnGrouping>
> <DynamicColumns>
> <Grouping Name="matrix1_babr">
> <GroupExpressions>
> <GroupExpression>=Fields!babr.Value</GroupExpression>
> </GroupExpressions>
> </Grouping>
> <Sorting>
> <SortBy>
> <SortExpression>=Fields!babr.Value</SortExpression>
> <Direction>Ascending</Direction>
> </SortBy>
> </Sorting>
> <ReportItems>
> <Textbox Name="babr">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <BorderWidth>
> <Bottom>1pt</Bottom>
> </BorderWidth>
> <BorderColor>
> <Bottom>Black</Bottom>
> </BorderColor>
> <BorderStyle>
> <Bottom>Solid</Bottom>
> </BorderStyle>
> <FontSize>7pt</FontSize>
> <TextAlign>Center</TextAlign>
> <Color>Blue</Color>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> <FontWeight>700</FontWeight>
> </Style>
> <ZIndex>2</ZIndex>
> <rd:DefaultName>babr</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>=Fields!babr.Value</Value>
> </Textbox>
> </ReportItems>
> </DynamicColumns>
> <Height>0.15in</Height>
> </ColumnGrouping>
> </ColumnGroupings>
> <Left>0.125in</Left>
> <RowGroupings>
> <RowGrouping>
> <DynamicRows>
> <Grouping Name="matrix1_SicDescription">
> <GroupExpressions>
> <GroupExpression>=Fields!SicDescription.Value</GroupExpression>
> </GroupExpressions>
> </Grouping>
> <Sorting>
> <SortBy>
> <SortExpression>=Fields!SicDescription.Value</SortExpression>
> <Direction>Ascending</Direction>
> </SortBy>
> </Sorting>
> <ReportItems>
> <Textbox Name="SicDescription">
> <Style>
> <PaddingLeft>1pt</PaddingLeft>
> <BorderWidth>
> <Right>1pt</Right>
> </BorderWidth>
> <BorderColor>
> <Right>Black</Right>
> </BorderColor>
> <BorderStyle>
> <Right>Solid</Right>
> </BorderStyle>
> <FontSize>7pt</FontSize>
> <TextAlign>Left</TextAlign>
> <PaddingBottom>1pt</PaddingBottom>
> <PaddingTop>1pt</PaddingTop>
> <PaddingRight>1pt</PaddingRight>
> </Style>
> <ZIndex>1</ZIndex>
> <rd:DefaultName>SicDescription</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>=Fields!SicDescription.Value</Value>
> </Textbox>
> </ReportItems>
> </DynamicRows>
> <Width>1.75in</Width>
> </RowGrouping>
> </RowGroupings>
> </Matrix>
> </ReportItems>
> <Style />
> <Height>0.3in</Height>
> </Body>
> <DataSources>
> <DataSource Name="RegionalSnaps">
> <rd:DataSourceID>74b2d458-9f2d-4e29-8375-2ed584778c1c</rd:DataSourceID>
> <DataSourceReference>RegionalSnaps</DataSourceReference>
> </DataSource>
> </DataSources>
> <Code />
> <Width>2.5in</Width>
> <DataSets>
> <DataSet Name="RegionalSnaps">
> <Fields>
> <Field Name="babr">
> <DataField>babr</DataField>
> <rd:TypeName>System.String</rd:TypeName>
> </Field>
> <Field Name="SicDescription">
> <DataField>SicDescription</DataField>
> <rd:TypeName>System.String</rd:TypeName>
> </Field>
> <Field Name="mv">
> <DataField>mv</DataField>
> <rd:TypeName>System.Decimal</rd:TypeName>
> </Field>
> <Field Name="Percentile">
> <DataField>Percentile</DataField>
> <rd:TypeName>System.Decimal</rd:TypeName>
> </Field>
> </Fields>
> <Query>
> <DataSourceName>RegionalSnaps</DataSourceName>
> <CommandText>SELECT s.babr, s.SicDescription,
> SUM(s.AccountMV) AS mv, SUM(s.AccountMV) / q.ps AS Percentile
> FROM SnapsRaw s INNER JOIN
> (SELECT babr, SUM(accountmv) ps
> FROM snapsraw
> WHERE monthend = '08/01/2006'
> GROUP BY babr) q ON q.babr = s.babr
> WHERE s.MonthEnd = '08/01/2006'
> GROUP BY s.Babr, s.SicDescription, q.ps, q.babr</CommandText>
> </Query>
> </DataSet>
> </DataSets>
> <rd:SnapToGrid>true</rd:SnapToGrid>
> <rd:DrawGrid>true</rd:DrawGrid>
> <rd:ReportID>f4c1c559-ee3a-43cb-9c9d-27f23c7b8cc8</rd:ReportID>
> <Language>en-US</Language>
> </Report>
> On Nov 15, 8:35 am, William <Will...@.discussions.microsoft.com> wrote:
> > Unless AccountMV & q.ps are both integers, Percentile is a decimal number so
> > your test should use the decimal as in:
> >
> > IIF( Sum(Fields!Percentile.Value, "RegionalSnaps") >= .10, "Yellow", "White")"duh...@.gmail.com" wrote:
> > > here is the query being called:
> >
> > > SELECT s.babr, s.SicDescription, SUM(s.AccountMV) AS mv,
> > > SUM(s.AccountMV) / q.ps AS Percentile
> > > FROM SnapsRaw s INNER JOIN
> > > (SELECT babr, SUM(accountmv) ps
> > > FROM snapsraw
> > > WHERE monthend = '08/01/2006'
> > > GROUP BY babr) q ON q.babr = s.babr
> > > WHERE s.MonthEnd = '08/01/2006'
> > > GROUP BY s.Babr, s.SicDescription, q.ps, q.babr
> >
> > > percentile is the number being displayed, changing it to .10 didnt seem
> > > to help
> >
> > > On Nov 10, 4:12 pm, William <Will...@.discussions.microsoft.com> wrote:
> > > > Is percentile an integer or a float. If float test is >= .10
> >
> > > > "duh...@.gmail.com" wrote:
> > > > > I have the following applied as an expression to the background color
> > > > > on a cell
> >
> > > > > =IIF( Sum(Fields!Percentile.Value, "RegionalSnaps") >= 10, "Yellow",
> > > > > "White")
> >
> > > > > I only want the ones above 10% to be yellow, otherwise white.
> >
> > > > > Any ideas anyone on why the entire range of cells are yellow and not
> > > > > just the one above 10%?
>|||Thanks again for your feedback, in running the query standalone the
results appear like this:
STATE GROUP
MV PERCENT
KC Engineering & Management Services 336044936.72 0.0556
STL Wholesale Trade-Nondurable Goods 221851565.42 0.1078
The accountmv field is a datatype money, just dont understand what the
deal is.
On Nov 15, 9:15 am, William <Will...@.discussions.microsoft.com> wrote:
> I took your IIF statement and applied it to a cell in a report I have and a
> value of .111 came back with yellow background. So I would go back to the
> format of the value coming in and verify that you have a number with decimals
> coming in. Also verify the scope of the IIF test if you have multiple data
> sources with the same field names."duh...@.gmail.com" wrote:
> > William
> > Appreciate all the help, I have made the change, and its still
> > highlighting the entire range of cells instead of just those above 10%,
> > here is the code for the report, its textbox2 that im playing with:
> > <?xml version="1.0" encoding="utf-8"?>
> > <Report
> > xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefini..."
> > xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
> > <Body>
> > <ReportItems>
> > <Matrix Name="matrix1">
> > <Corner>
> > <ReportItems>
> > <Textbox Name="textbox3">
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <TextDecoration>Underline</TextDecoration>
> > <FontSize>7pt</FontSize>
> > <TextAlign>Left</TextAlign>
> > <Color>Blue</Color>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingTop>2pt</PaddingTop>
> > <PaddingRight>2pt</PaddingRight>
> > <FontWeight>700</FontWeight>
> > </Style>
> > <ZIndex>3</ZIndex>
> > <rd:DefaultName>textbox3</rd:DefaultName>
> > <CanGrow>true</CanGrow>
> > <Value>SIC ID/ Industry</Value>
> > </Textbox>
> > </ReportItems>
> > </Corner>
> > <Style />
> > <MatrixRows>
> > <MatrixRow>
> > <MatrixCells>
> > <MatrixCell>
> > <ReportItems>
> > <Textbox Name="textbox2">
> > <Style>
> > <PaddingLeft>1pt</PaddingLeft>
> > <Format>g</Format>
> > <BackgroundColor>=IIF(Sum(Fields!Percentile.Value, "RegionalSnaps")
> > >= .10, "Yellow", "White")</BackgroundColor>
> > <BorderStyle>
> > <Default>Solid</Default>
> > </BorderStyle>
> > <FontSize>7pt</FontSize>
> > <TextAlign>Center</TextAlign>
> > <PaddingBottom>1pt</PaddingBottom>
> > <PaddingTop>1pt</PaddingTop>
> > <PaddingRight>1pt</PaddingRight>
> > </Style>
> > <rd:DefaultName>textbox2</rd:DefaultName>
> > <CanGrow>true</CanGrow>
> > <Value>=sum(Fields!Percentile.Value)</Value>
> > </Textbox>
> > </ReportItems>
> > </MatrixCell>
> > </MatrixCells>
> > <Height>0.15in</Height>
> > </MatrixRow>
> > </MatrixRows>
> > <MatrixColumns>
> > <MatrixColumn>
> > <Width>0.625in</Width>
> > </MatrixColumn>
> > </MatrixColumns>
> > <DataSetName>RegionalSnaps</DataSetName>
> > <ColumnGroupings>
> > <ColumnGrouping>
> > <DynamicColumns>
> > <Grouping Name="matrix1_babr">
> > <GroupExpressions>
> > <GroupExpression>=Fields!babr.Value</GroupExpression>
> > </GroupExpressions>
> > </Grouping>
> > <Sorting>
> > <SortBy>
> > <SortExpression>=Fields!babr.Value</SortExpression>
> > <Direction>Ascending</Direction>
> > </SortBy>
> > </Sorting>
> > <ReportItems>
> > <Textbox Name="babr">
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <BorderWidth>
> > <Bottom>1pt</Bottom>
> > </BorderWidth>
> > <BorderColor>
> > <Bottom>Black</Bottom>
> > </BorderColor>
> > <BorderStyle>
> > <Bottom>Solid</Bottom>
> > </BorderStyle>
> > <FontSize>7pt</FontSize>
> > <TextAlign>Center</TextAlign>
> > <Color>Blue</Color>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingTop>2pt</PaddingTop>
> > <PaddingRight>2pt</PaddingRight>
> > <FontWeight>700</FontWeight>
> > </Style>
> > <ZIndex>2</ZIndex>
> > <rd:DefaultName>babr</rd:DefaultName>
> > <CanGrow>true</CanGrow>
> > <Value>=Fields!babr.Value</Value>
> > </Textbox>
> > </ReportItems>
> > </DynamicColumns>
> > <Height>0.15in</Height>
> > </ColumnGrouping>
> > </ColumnGroupings>
> > <Left>0.125in</Left>
> > <RowGroupings>
> > <RowGrouping>
> > <DynamicRows>
> > <Grouping Name="matrix1_SicDescription">
> > <GroupExpressions>
> > <GroupExpression>=Fields!SicDescription.Value</GroupExpression>
> > </GroupExpressions>
> > </Grouping>
> > <Sorting>
> > <SortBy>
> > <SortExpression>=Fields!SicDescription.Value</SortExpression>
> > <Direction>Ascending</Direction>
> > </SortBy>
> > </Sorting>
> > <ReportItems>
> > <Textbox Name="SicDescription">
> > <Style>
> > <PaddingLeft>1pt</PaddingLeft>
> > <BorderWidth>
> > <Right>1pt</Right>
> > </BorderWidth>
> > <BorderColor>
> > <Right>Black</Right>
> > </BorderColor>
> > <BorderStyle>
> > <Right>Solid</Right>
> > </BorderStyle>
> > <FontSize>7pt</FontSize>
> > <TextAlign>Left</TextAlign>
> > <PaddingBottom>1pt</PaddingBottom>
> > <PaddingTop>1pt</PaddingTop>
> > <PaddingRight>1pt</PaddingRight>
> > </Style>
> > <ZIndex>1</ZIndex>
> > <rd:DefaultName>SicDescription</rd:DefaultName>
> > <CanGrow>true</CanGrow>
> > <Value>=Fields!SicDescription.Value</Value>
> > </Textbox>
> > </ReportItems>
> > </DynamicRows>
> > <Width>1.75in</Width>
> > </RowGrouping>
> > </RowGroupings>
> > </Matrix>
> > </ReportItems>
> > <Style />
> > <Height>0.3in</Height>
> > </Body>
> > <DataSources>
> > <DataSource Name="RegionalSnaps">
> > <rd:DataSourceID>74b2d458-9f2d-4e29-8375-2ed584778c1c</rd:DataSourceID>
> > <DataSourceReference>RegionalSnaps</DataSourceReference>
> > </DataSource>
> > </DataSources>
> > <Code />
> > <Width>2.5in</Width>
> > <DataSets>
> > <DataSet Name="RegionalSnaps">
> > <Fields>
> > <Field Name="babr">
> > <DataField>babr</DataField>
> > <rd:TypeName>System.String</rd:TypeName>
> > </Field>
> > <Field Name="SicDescription">
> > <DataField>SicDescription</DataField>
> > <rd:TypeName>System.String</rd:TypeName>
> > </Field>
> > <Field Name="mv">
> > <DataField>mv</DataField>
> > <rd:TypeName>System.Decimal</rd:TypeName>
> > </Field>
> > <Field Name="Percentile">
> > <DataField>Percentile</DataField>
> > <rd:TypeName>System.Decimal</rd:TypeName>
> > </Field>
> > </Fields>
> > <Query>
> > <DataSourceName>RegionalSnaps</DataSourceName>
> > <CommandText>SELECT s.babr, s.SicDescription,
> > SUM(s.AccountMV) AS mv, SUM(s.AccountMV) / q.ps AS Percentile
> > FROM SnapsRaw s INNER JOIN
> > (SELECT babr, SUM(accountmv) ps
> > FROM snapsraw
> > WHERE monthend = '08/01/2006'
> > GROUP BY babr) q ON q.babr = s.babr
> > WHERE s.MonthEnd = '08/01/2006'
> > GROUP BY s.Babr, s.SicDescription, q.ps, q.babr</CommandText>
> > </Query>
> > </DataSet>
> > </DataSets>
> > <rd:SnapToGrid>true</rd:SnapToGrid>
> > <rd:DrawGrid>true</rd:DrawGrid>
> > <rd:ReportID>f4c1c559-ee3a-43cb-9c9d-27f23c7b8cc8</rd:ReportID>
> > <Language>en-US</Language>
> > </Report>
> > On Nov 15, 8:35 am, William <Will...@.discussions.microsoft.com> wrote:
> > > Unless AccountMV & q.ps are both integers, Percentile is a decimal number so
> > > your test should use the decimal as in:
> > > IIF( Sum(Fields!Percentile.Value, "RegionalSnaps") >= .10, "Yellow", "White")"duh...@.gmail.com" wrote:
> > > > here is the query being called:
> > > > SELECT s.babr, s.SicDescription, SUM(s.AccountMV) AS mv,
> > > > SUM(s.AccountMV) / q.ps AS Percentile
> > > > FROM SnapsRaw s INNER JOIN
> > > > (SELECT babr, SUM(accountmv) ps
> > > > FROM snapsraw
> > > > WHERE monthend = '08/01/2006'
> > > > GROUP BY babr) q ON q.babr = s.babr
> > > > WHERE s.MonthEnd = '08/01/2006'
> > > > GROUP BY s.Babr, s.SicDescription, q.ps, q.babr
> > > > percentile is the number being displayed, changing it to .10 didnt seem
> > > > to help
> > > > On Nov 10, 4:12 pm, William <Will...@.discussions.microsoft.com> wrote:
> > > > > Is percentile an integer or a float. If float test is >= .10
> > > > > "duh...@.gmail.com" wrote:
> > > > > > I have the following applied as an expression to the background color
> > > > > > on a cell
> > > > > > =IIF( Sum(Fields!Percentile.Value, "RegionalSnaps") >= 10, "Yellow",
> > > > > > "White")
> > > > > > I only want the ones above 10% to be yellow, otherwise white.
> > > > > > Any ideas anyone on why the entire range of cells are yellow and not
> > > > > > just the one above 10%?

Matrix Report and Expression on field

I have a matrix report that has two columns, and one of the colums has the following expression for background color:

=IIF( Fields!Percentile.Value >= .10, "Yellow", "White")

Basically if the percent is greater than 10 highlight the field, for some reason i have some fields that dont show up yellow, see below:

http://duhaas.googlepages.com/percent.Jpg

your colour expression needs to wrap the field reference with the same aggregate function as the textbox value expression.

So, if you are using: =sum(Fields!Percentile.Value) as your value then change the colour expression to

=IIF( SUM(Fields!Percentile.Value) >= .10, "Yellow", "White")

this is because when no aggregate function is specified the the first value returned is used by default to evaluate the expression.

|||YOU DA MAN

Matrix PDF

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