Conditionally hiding column in matrix

I have a matrix with the following format
Date
Month RowGroup1 Group2 Amount
I have month in the rows because I want to page on month when
exporting to excel in order to produce a new sheet. This seem to work
ok except for the following.
1. I'm passing in a date range (7/1/2004 - 8/31/2004) When the report
is displayed, I correctly get a report paged by month but the date
column shows all date between 7/1 and 8/31 on both sheets, regardless
of having a value in the amount field. So for July all August dates
are displayed and for July all August values are displayed. Can I
hide dates where the amount is null or missing?
2. When I export to excel the individual sheets are called sheet1,
sheet2... Is there a way to give the name of each sheet the
corresponding month value?
Thanks for your assistance?
DaveRather than putting the month in the matrix, put the matrix in a list which
groups by month.
You cannot control the Excel sheet names in the current version.
--
This post is provided 'AS IS' with no warranties, and confers no rights. All
rights reserved. Some assembly required. Batteries not included. Your
mileage may vary. Objects in mirror may be closer than they appear. No user
serviceable parts inside. Opening cover voids warranty. Keep out of reach of
children under 3.
"Dave" <davidbr93@.yahoo.com> wrote in message
news:703390f1.0408311012.2310ed6f@.posting.google.com...
> I have a matrix with the following format
> Date
> Month RowGroup1 Group2 Amount
>
> I have month in the rows because I want to page on month when
> exporting to excel in order to produce a new sheet. This seem to work
> ok except for the following.
>
> 1. I'm passing in a date range (7/1/2004 - 8/31/2004) When the report
> is displayed, I correctly get a report paged by month but the date
> column shows all date between 7/1 and 8/31 on both sheets, regardless
> of having a value in the amount field. So for July all August dates
> are displayed and for July all August values are displayed. Can I
> hide dates where the amount is null or missing?
> 2. When I export to excel the individual sheets are called sheet1,
> sheet2... Is there a way to give the name of each sheet the
> corresponding month value?
> Thanks for your assistance?
> Dave

Conditionally format subtotal for matrix report

I have a matrix that will pull out the current quarters sales figures. I want to change the color of the subtotal font ONLY when we are in the current period. I have a boolean field in the matrix report that is true when it is the current month.

For example, at the end of last month it displays January, February and March figures. I want the sub total to display the totals for January and February in white, whilst the totals for March are Yellow.

Any ideas anyone?

You can use expression for the subtotal style. Just check the value of your bool field in the expression, and return the corresponding color.|||

Unfortunately, that doesn't work.

I am using a condition in the color field of the subtotal properties checking the boolean value - and it ALWAYS thinks that the value is true, whether it is or not. Problem is that I have a mixture in my matrix - the previous 2 months hold false, the current month true and I am displaying all 3 months.

I am using the following in the color field in the properties of the subtotal:

=IIf(Fields!CurrentPeriod.Value, "Yellow", "White")

And it is showing yellow for all months.

Any pointers greatfully received

|||Hi there,

Did you find an answer to this? I am stuck with the same problem.

-Thanks a lot|||

Hi Ragas, Just try like this add your expression in differen place.Click in textbox and go to properties window,go to back ground color in that drop down list add your expression.

|||

Hi folks,

I have tried various methods for conditional formating in subtotal. Everything is working in development environment, but when i deploy and see the report in the http://ReportServer/reports, it is not being implemented. But when i print from the report from report server, i can see the conditional format !!!!

Can anyone give solution for this?

Regards,

karthik

|||

Hi there,

This worked for me:

=iif(inscope("Rating_Group") and inscope("RowRating_Group"),"white","Gainsboro")

Use in any of the attributes (Font, Color, Borderstyl) of the Data cell, in other words the cells that will contain the values of the matrix.

"Rating_Group" and "RowRating_Group" simply represent the Group which the subtotal belongs to. I had two subtotals on my report that's why I used both.

This can be a column group or a row group.

You should be able to format the subtotals to your heart's content with this beaut.

Conditionally format subtotal for matrix report

I have a matrix that will pull out the current quarters sales figures. I want to change the color of the subtotal font ONLY when we are in the current period. I have a boolean field in the matrix report that is true when it is the current month.

For example, at the end of last month it displays January, February and March figures. I want the sub total to display the totals for January and February in white, whilst the totals for March are Yellow.

Any ideas anyone?

You can use expression for the subtotal style. Just check the value of your bool field in the expression, and return the corresponding color.|||

Unfortunately, that doesn't work.

I am using a condition in the color field of the subtotal properties checking the boolean value - and it ALWAYS thinks that the value is true, whether it is or not. Problem is that I have a mixture in my matrix - the previous 2 months hold false, the current month true and I am displaying all 3 months.

I am using the following in the color field in the properties of the subtotal:

=IIf(Fields!CurrentPeriod.Value, "Yellow", "White")

And it is showing yellow for all months.

Any pointers greatfully received

|||Hi there,

Did you find an answer to this? I am stuck with the same problem.

-Thanks a lot|||

Hi Ragas, Just try like this add your expression in differen place.Click in textbox and go to properties window,go to back ground color in that drop down list add your expression.

|||

Hi folks,

I have tried various methods for conditional formating in subtotal. Everything is working in development environment, but when i deploy and see the report in the http://ReportServer/reports, it is not being implemented. But when i print from the report from report server, i can see the conditional format !!!!

Can anyone give solution for this?

Regards,

karthik

|||

Hi there,

This worked for me:

=iif(inscope("Rating_Group") and inscope("RowRating_Group"),"white","Gainsboro")

Use in any of the attributes (Font, Color, Borderstyl) of the Data cell, in other words the cells that will contain the values of the matrix.

"Rating_Group" and "RowRating_Group" simply represent the Group which the subtotal belongs to. I had two subtotals on my report that's why I used both.

This can be a column group or a row group.

You should be able to format the subtotals to your heart's content with this beaut.

Conditionally format subtotal for matrix report

I have a matrix that will pull out the current quarters sales figures. I want to change the color of the subtotal font ONLY when we are in the current period. I have a boolean field in the matrix report that is true when it is the current month.

For example, at the end of last month it displays January, February and March figures. I want the sub total to display the totals for January and February in white, whilst the totals for March are Yellow.

Any ideas anyone?

You can use expression for the subtotal style. Just check the value of your bool field in the expression, and return the corresponding color.|||

Unfortunately, that doesn't work.

I am using a condition in the color field of the subtotal properties checking the boolean value - and it ALWAYS thinks that the value is true, whether it is or not. Problem is that I have a mixture in my matrix - the previous 2 months hold false, the current month true and I am displaying all 3 months.

I am using the following in the color field in the properties of the subtotal:

=IIf(Fields!CurrentPeriod.Value, "Yellow", "White")

And it is showing yellow for all months.

Any pointers greatfully received

|||Hi there,

Did you find an answer to this? I am stuck with the same problem.

-Thanks a lot|||

Hi Ragas, Just try like this add your expression in differen place.Click in textbox and go to properties window,go to back ground color in that drop down list add your expression.

|||

Hi folks,

I have tried various methods for conditional formating in subtotal. Everything is working in development environment, but when i deploy and see the report in the http://ReportServer/reports, it is not being implemented. But when i print from the report from report server, i can see the conditional format !!!!

Can anyone give solution for this?

Regards,

karthik

|||

Hi there,

This worked for me:

=iif(inscope("Rating_Group") and inscope("RowRating_Group"),"white","Gainsboro")

Use in any of the attributes (Font, Color, Borderstyl) of the Data cell, in other words the cells that will contain the values of the matrix.

"Rating_Group" and "RowRating_Group" simply represent the Group which the subtotal belongs to. I had two subtotals on my report that's why I used both.

This can be a column group or a row group.

You should be able to format the subtotals to your heart's content with this beaut.

Conditionally format subtotal for matrix report

I have a matrix that will pull out the current quarters sales figures. I want to change the color of the subtotal font ONLY when we are in the current period. I have a boolean field in the matrix report that is true when it is the current month.

For example, at the end of last month it displays January, February and March figures. I want the sub total to display the totals for January and February in white, whilst the totals for March are Yellow.

Any ideas anyone?

You can use expression for the subtotal style. Just check the value of your bool field in the expression, and return the corresponding color.|||

Unfortunately, that doesn't work.

I am using a condition in the color field of the subtotal properties checking the boolean value - and it ALWAYS thinks that the value is true, whether it is or not. Problem is that I have a mixture in my matrix - the previous 2 months hold false, the current month true and I am displaying all 3 months.

I am using the following in the color field in the properties of the subtotal:

=IIf(Fields!CurrentPeriod.Value, "Yellow", "White")

And it is showing yellow for all months.

Any pointers greatfully received

|||Hi there,

Did you find an answer to this? I am stuck with the same problem.

-Thanks a lot|||

Hi Ragas, Just try like this add your expression in differen place.Click in textbox and go to properties window,go to back ground color in that drop down list add your expression.

|||

Hi folks,

I have tried various methods for conditional formating in subtotal. Everything is working in development environment, but when i deploy and see the report in the http://ReportServer/reports, it is not being implemented. But when i print from the report from report server, i can see the conditional format !!!!

Can anyone give solution for this?

Regards,

karthik

|||

Hi there,

This worked for me:

=iif(inscope("Rating_Group") and inscope("RowRating_Group"),"white","Gainsboro")

Use in any of the attributes (Font, Color, Borderstyl) of the Data cell, in other words the cells that will contain the values of the matrix.

"Rating_Group" and "RowRating_Group" simply represent the Group which the subtotal belongs to. I had two subtotals on my report that's why I used both.

This can be a column group or a row group.

You should be able to format the subtotals to your heart's content with this beaut.

sqlsql

Conditional row formatting in matrix

I've got a report that uses a matrix (pivot table). I want to format the rows using alternating colors (green-bar style). However, my matrix is not fully populated (some cells are null). Therefore, my formatting does not look correct. I'm using a expression like...
=iif(RowNumber("matrix1_columngroupscope") Mod 2,"Blue","White")
to format the rows. But the problem occurs in that the color does not change if the cell is null (e.g. not entry in the data set). For columns that are fully populated, I get the correct result. If columns that are not, I don't.virtualfergy wrote:
> Sorry if I'm being daft, but you said "attached report". How do I
> view the attachment?
You need to have OE for this or a news reader able to deal with attachment.
If you don't have this pls. find the GreenBar sample code below. Copy and
paste it into your *.rdl
<?xml version="1.0" encoding="utf-8"?>
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefini
tion"
xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<RightMargin>1in</RightMargin>
<Body>
<ReportItems>
<Matrix Name="matrix1">
<Corner>
<ReportItems>
<Textbox Name="textbox1">
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>4</ZIndex>
<rd:DefaultName>textbox1</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</Corner>
<Height>0.5in</Height>
<Style />
<MatrixRows>
<MatrixRow>
<MatrixCells>
<MatrixCell>
<ReportItems>
<Textbox Name="Qty">
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>=ReportItems!Color.Value</BackgroundColor>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<rd:DefaultName>Qty</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Sum(Fields!Qty.Value)</Value>
</Textbox>
</ReportItems>
</MatrixCell>
</MatrixCells>
<Height>0.25in</Height>
</MatrixRow>
</MatrixRows>
<MatrixColumns>
<MatrixColumn>
<Width>0.875in</Width>
</MatrixColumn>
</MatrixColumns>
<DataSetName>DataSet1</DataSetName>
<ColumnGroupings>
<ColumnGrouping>
<DynamicColumns>
<Grouping Name="Category">
<GroupExpressions>
<GroupExpression>=Fields!CategoryName.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<ReportItems>
<Textbox Name="CategoryName">
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>3</ZIndex>
<rd:DefaultName>CategoryName</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!CategoryName.Value</Value>
</Textbox>
</ReportItems>
</DynamicColumns>
<Height>0.25in</Height>
</ColumnGrouping>
</ColumnGroupings>
<Width>2in</Width>
<Top>0.125in</Top>
<Left>0.125in</Left>
<RowGroupings>
<RowGrouping>
<DynamicRows>
<Grouping Name="Country">
<GroupExpressions>
<GroupExpression>=Fields!Country.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<ReportItems>
<Textbox Name="Country">
<Style>
<BorderStyle>
<Default>Solid</Default>
<Right>None</Right>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>=iif(RunningValue(Fields!Country.Value,CountDistinct,Nothin
g) Mod 2, "AliceBlue", "White")</BackgroundColor>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>2</ZIndex>
<rd:DefaultName>Country</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!Country.Value & " " &
RunningValue(Fields!Country.Value,CountDistinct,Nothing)</Value>
</Textbox>
</ReportItems>
</DynamicRows>
<Width>1in</Width>
</RowGrouping>
<RowGrouping>
<DynamicRows>
<Grouping Name="Count">
<GroupExpressions>
<GroupExpression>=1</GroupExpression>
</GroupExpressions>
</Grouping>
<ReportItems>
<Textbox Name="Color">
<Style>
<BorderStyle>
<Default>Solid</Default>
<Left>None</Left>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>=Value</BackgroundColor>
<FontSize>1pt</FontSize>
<Color>=Value</Color>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>1</ZIndex>
<CanGrow>true</CanGrow>
<Value>=iif(RunningValue(Fields!Country.Value,CountDistinct,Nothing) Mod 2,
"AliceBlue", "White")</Value>
</Textbox>
</ReportItems>
</DynamicRows>
<Width>0.125in</Width>
</RowGrouping>
</RowGroupings>
</Matrix>
</ReportItems>
<Style />
<Height>3.25in</Height>
</Body>
<TopMargin>1in</TopMargin>
<DataSources>
<DataSource Name="Northwind">
<rd:DataSourceID>26f1bf87-1fa6-4e77-8d1a-81b0cd940403</rd:DataSourceID>
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>data source=.;initial
catalog=Northwind</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
</DataSource>
</DataSources>
<Code />
<Width>6.875in</Width>
<DataSets>
<DataSet Name="DataSet1">
<Fields>
<Field Name="Country">
<DataField>Country</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Qty">
<DataField>Qty</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="CategoryName">
<DataField>CategoryName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>Northwind</DataSourceName>
<CommandText>SELECT Customers.Country, SUM([Order
Details].Quantity) AS Qty, Categories.CategoryName
FROM Customers INNER JOIN
Orders ON Customers.CustomerID = Orders.CustomerID
INNER JOIN
[Order Details] ON Orders.OrderID = [Order
Details].OrderID INNER JOIN
Products ON [Order Details].ProductID =Products.ProductID INNER JOIN
Categories ON Products.CategoryID =Categories.CategoryID
GROUP BY Customers.Country, Categories.CategoryName</CommandText>
</Query>
</DataSet>
</DataSets>
<LeftMargin>1in</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<rd:DrawGrid>true</rd:DrawGrid>
<Description />
<rd:ReportID>ab2c120b-3169-427d-8ad6-b8716f8c5101</rd:ReportID>
<BottomMargin>1in</BottomMargin>
</Report>|||Whoa... that worked but what a convoluted way to make it happen. I hope you folks at MS make this simpler in the future. Thanks for the help.
"Roland" wrote:
> virtualfergy wrote:
> > Sorry if I'm being daft, but you said "attached report". How do I
> > view the attachment?
> You need to have OE for this or a news reader able to deal with attachment.
> If you don't have this pls. find the GreenBar sample code below. Copy and
> paste it into your *.rdl
> <?xml version="1.0" encoding="utf-8"?>
> <Report
> xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefini
> tion"
> xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
> <RightMargin>1in</RightMargin>
> <Body>
> <ReportItems>
> <Matrix Name="matrix1">
> <Corner>
> <ReportItems>
> <Textbox Name="textbox1">
> <Style>
> <BorderStyle>
> <Default>Solid</Default>
> </BorderStyle>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>4</ZIndex>
> <rd:DefaultName>textbox1</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value />
> </Textbox>
> </ReportItems>
> </Corner>
> <Height>0.5in</Height>
> <Style />
> <MatrixRows>
> <MatrixRow>
> <MatrixCells>
> <MatrixCell>
> <ReportItems>
> <Textbox Name="Qty">
> <Style>
> <BorderStyle>
> <Default>Solid</Default>
> </BorderStyle>
> <PaddingLeft>2pt</PaddingLeft>
> <BackgroundColor>=ReportItems!Color.Value</BackgroundColor>
> <TextAlign>Right</TextAlign>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <rd:DefaultName>Qty</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>=Sum(Fields!Qty.Value)</Value>
> </Textbox>
> </ReportItems>
> </MatrixCell>
> </MatrixCells>
> <Height>0.25in</Height>
> </MatrixRow>
> </MatrixRows>
> <MatrixColumns>
> <MatrixColumn>
> <Width>0.875in</Width>
> </MatrixColumn>
> </MatrixColumns>
> <DataSetName>DataSet1</DataSetName>
> <ColumnGroupings>
> <ColumnGrouping>
> <DynamicColumns>
> <Grouping Name="Category">
> <GroupExpressions>
> <GroupExpression>=Fields!CategoryName.Value</GroupExpression>
> </GroupExpressions>
> </Grouping>
> <ReportItems>
> <Textbox Name="CategoryName">
> <Style>
> <BorderStyle>
> <Default>Solid</Default>
> </BorderStyle>
> <PaddingLeft>2pt</PaddingLeft>
> <TextAlign>Right</TextAlign>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>3</ZIndex>
> <rd:DefaultName>CategoryName</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>=Fields!CategoryName.Value</Value>
> </Textbox>
> </ReportItems>
> </DynamicColumns>
> <Height>0.25in</Height>
> </ColumnGrouping>
> </ColumnGroupings>
> <Width>2in</Width>
> <Top>0.125in</Top>
> <Left>0.125in</Left>
> <RowGroupings>
> <RowGrouping>
> <DynamicRows>
> <Grouping Name="Country">
> <GroupExpressions>
> <GroupExpression>=Fields!Country.Value</GroupExpression>
> </GroupExpressions>
> </Grouping>
> <ReportItems>
> <Textbox Name="Country">
> <Style>
> <BorderStyle>
> <Default>Solid</Default>
> <Right>None</Right>
> </BorderStyle>
> <PaddingLeft>2pt</PaddingLeft>
> <BackgroundColor>=iif(RunningValue(Fields!Country.Value,CountDistinct,Nothin
> g) Mod 2, "AliceBlue", "White")</BackgroundColor>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>2</ZIndex>
> <rd:DefaultName>Country</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>=Fields!Country.Value & " " &
> RunningValue(Fields!Country.Value,CountDistinct,Nothing)</Value>
> </Textbox>
> </ReportItems>
> </DynamicRows>
> <Width>1in</Width>
> </RowGrouping>
> <RowGrouping>
> <DynamicRows>
> <Grouping Name="Count">
> <GroupExpressions>
> <GroupExpression>=1</GroupExpression>
> </GroupExpressions>
> </Grouping>
> <ReportItems>
> <Textbox Name="Color">
> <Style>
> <BorderStyle>
> <Default>Solid</Default>
> <Left>None</Left>
> </BorderStyle>
> <PaddingLeft>2pt</PaddingLeft>
> <BackgroundColor>=Value</BackgroundColor>
> <FontSize>1pt</FontSize>
> <Color>=Value</Color>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>1</ZIndex>
> <CanGrow>true</CanGrow>
> <Value>=iif(RunningValue(Fields!Country.Value,CountDistinct,Nothing) Mod 2,
> "AliceBlue", "White")</Value>
> </Textbox>
> </ReportItems>
> </DynamicRows>
> <Width>0.125in</Width>
> </RowGrouping>
> </RowGroupings>
> </Matrix>
> </ReportItems>
> <Style />
> <Height>3.25in</Height>
> </Body>
> <TopMargin>1in</TopMargin>
> <DataSources>
> <DataSource Name="Northwind">
> <rd:DataSourceID>26f1bf87-1fa6-4e77-8d1a-81b0cd940403</rd:DataSourceID>
> <ConnectionProperties>
> <DataProvider>SQL</DataProvider>
> <ConnectString>data source=.;initial
> catalog=Northwind</ConnectString>
> <IntegratedSecurity>true</IntegratedSecurity>
> </ConnectionProperties>
> </DataSource>
> </DataSources>
> <Code />
> <Width>6.875in</Width>
> <DataSets>
> <DataSet Name="DataSet1">
> <Fields>
> <Field Name="Country">
> <DataField>Country</DataField>
> <rd:TypeName>System.String</rd:TypeName>
> </Field>
> <Field Name="Qty">
> <DataField>Qty</DataField>
> <rd:TypeName>System.Int32</rd:TypeName>
> </Field>
> <Field Name="CategoryName">
> <DataField>CategoryName</DataField>
> <rd:TypeName>System.String</rd:TypeName>
> </Field>
> </Fields>
> <Query>
> <DataSourceName>Northwind</DataSourceName>
> <CommandText>SELECT Customers.Country, SUM([Order
> Details].Quantity) AS Qty, Categories.CategoryName
> FROM Customers INNER JOIN
> Orders ON Customers.CustomerID = Orders.CustomerID
> INNER JOIN
> [Order Details] ON Orders.OrderID = [Order
> Details].OrderID INNER JOIN
> Products ON [Order Details].ProductID => Products.ProductID INNER JOIN
> Categories ON Products.CategoryID => Categories.CategoryID
> GROUP BY Customers.Country, Categories.CategoryName</CommandText>
> </Query>
> </DataSet>
> </DataSets>
> <LeftMargin>1in</LeftMargin>
> <rd:SnapToGrid>true</rd:SnapToGrid>
> <rd:DrawGrid>true</rd:DrawGrid>
> <Description />
> <rd:ReportID>ab2c120b-3169-427d-8ad6-b8716f8c5101</rd:ReportID>
> <BottomMargin>1in</BottomMargin>
> </Report>
>
>
>
>
>

Conditional Formatting with a date

Hi all,
I have some dates in a recordset in english format dd/mm/yyyy
If the date returned is 1st Jan of any year then i just want the year to be
displayed.
i.e date returned = 1/1/2000 i just want to display the year so... (yyyy)
if the date is 1/3/2000 then return the whole date
Is this possible through conditional formatting?
--
Thanks in advance,
Dave HuntDave,
I don't think it will work via conditional formatting.
But you can use the following expresion for your value:
=IIF( date.Value = New Date(Year( date.Value), 1, 1), Year(date.Value),
date.Value) with date.Value as your date return value.
Jan Pieter Posthuma
"DustpanDave" wrote:
> Hi all,
> I have some dates in a recordset in english format dd/mm/yyyy
> If the date returned is 1st Jan of any year then i just want the year to be
> displayed.
> i.e date returned = 1/1/2000 i just want to display the year so... (yyyy)
> if the date is 1/3/2000 then return the whole date
> Is this possible through conditional formatting?
> --
> Thanks in advance,
> Dave Hunt|||hi jan,
=IIF (Fields!StartDate.Value =New Date(Year(Fields!StartDate.Value), 1,
1),'yyyy','MMMM yyyy')
If i do this code in the function of the properties of the field then i get
an error saying 'Expression expected'
would this code work if i put it in the value part of the field?
--
Thanks in advance,
Dave Hunt
"Jan Pieter Posthuma" wrote:
> Dave,
> I don't think it will work via conditional formatting.
> But you can use the following expresion for your value:
> =IIF( date.Value = New Date(Year( date.Value), 1, 1), Year(date.Value),
> date.Value) with date.Value as your date return value.
> Jan Pieter Posthuma
> "DustpanDave" wrote:
> > Hi all,
> >
> > I have some dates in a recordset in english format dd/mm/yyyy
> >
> > If the date returned is 1st Jan of any year then i just want the year to be
> > displayed.
> >
> > i.e date returned = 1/1/2000 i just want to display the year so... (yyyy)
> >
> > if the date is 1/3/2000 then return the whole date
> >
> > Is this possible through conditional formatting?
> > --
> > Thanks in advance,
> > Dave Hunt|||Hi,
Something like this might work...?
=iif(datepart("d",Fields!StartDate.Value) &
datepart("m",Fields!StartDate.Value) =11,datepart("yyyy",Fields!StartDate.Value),Fields!StartDate.Value)
"DustpanDave" wrote:
> hi jan,
> =IIF (Fields!StartDate.Value =New Date(Year(Fields!StartDate.Value), 1,
> 1),'yyyy','MMMM yyyy')
> If i do this code in the function of the properties of the field then i get
> an error saying 'Expression expected'
> would this code work if i put it in the value part of the field?
> --
> Thanks in advance,
> Dave Hunt
>
> "Jan Pieter Posthuma" wrote:
> > Dave,
> >
> > I don't think it will work via conditional formatting.
> > But you can use the following expresion for your value:
> > =IIF( date.Value = New Date(Year( date.Value), 1, 1), Year(date.Value),
> > date.Value) with date.Value as your date return value.
> >
> > Jan Pieter Posthuma
> >
> > "DustpanDave" wrote:
> >
> > > Hi all,
> > >
> > > I have some dates in a recordset in english format dd/mm/yyyy
> > >
> > > If the date returned is 1st Jan of any year then i just want the year to be
> > > displayed.
> > >
> > > i.e date returned = 1/1/2000 i just want to display the year so... (yyyy)
> > >
> > > if the date is 1/3/2000 then return the whole date
> > >
> > > Is this possible through conditional formatting?
> > > --
> > > Thanks in advance,
> > > Dave Hunt|||Dave,
You should use double quotes (") for the date format parts.
Jan Pieter Posthuma
"DustpanDave" wrote:
> hi jan,
> =IIF (Fields!StartDate.Value =New Date(Year(Fields!StartDate.Value), 1,
> 1),'yyyy','MMMM yyyy')
> If i do this code in the function of the properties of the field then i get
> an error saying 'Expression expected'
> would this code work if i put it in the value part of the field?
> --
> Thanks in advance,
> Dave Hunt
>
> "Jan Pieter Posthuma" wrote:
> > Dave,
> >
> > I don't think it will work via conditional formatting.
> > But you can use the following expresion for your value:
> > =IIF( date.Value = New Date(Year( date.Value), 1, 1), Year(date.Value),
> > date.Value) with date.Value as your date return value.
> >
> > Jan Pieter Posthuma
> >
> > "DustpanDave" wrote:
> >
> > > Hi all,
> > >
> > > I have some dates in a recordset in english format dd/mm/yyyy
> > >
> > > If the date returned is 1st Jan of any year then i just want the year to be
> > > displayed.
> > >
> > > i.e date returned = 1/1/2000 i just want to display the year so... (yyyy)
> > >
> > > if the date is 1/3/2000 then return the whole date
> > >
> > > Is this possible through conditional formatting?
> > > --
> > > Thanks in advance,
> > > Dave Hunt

Conditional Formatting - Export

Using the web viewer control in VS 2005, is there a way to conditionally format items for export only?

For example, user runs a report which gets displayed in the viewer. The report contains small images and or decorated text for links to additional reports. If user selects pdf from the export dropdown list and clicks export, I would like hide the images in the pdf and or format the decorated text differently.

If the answer is no, is it due to the report already being in its intermediate state?

The answer to your first question is no. One way of getting close to the desired behavior is to either use different report definitions or use a report parameter to determine if images should be shown etc.

The answer to the second question is yes. When you export the report to PDF, it will just re-render the report from the already processed (and output-format independent) intermediate format.

-- Robert

Conditional Formatting

Hi
I am trying to format a negative number ie -9999 with the following format
and am getting -(9,999).
=iif(Fields!ActualQty.Value < 0, "(0,000)"," 0,000")
Is there a way to get rid of the negative sign and only show the brackets?
And where can I find are some detailed examples for conditional formatting?
Thanks
FionaThe output is culture dependent. For en-US culture just try this formatcode:
0,000;(0,000)
More details and examples can be found on MSDN:
[1]
http://msdn.microsoft.com/library/en-us/cpguide/html/cpconcustomnumericformatstringsoutputexample.asp
[2]
http://msdn.microsoft.com/library/en-us/cpguide/html/cpconcustomnumericformatstrings.asp
[3]
http://msdn.microsoft.com/library/en-us/cpguide/html/cpconstandardnumericformatstringsoutputexample.asp
[4]
http://msdn.microsoft.com/library/en-us/cpguide/html/cpconstandardnumericformatstrings.asp
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Fiona Harrison" <FionaHarrison@.discussions.microsoft.com> wrote in message
news:E9289E77-3BDD-42B3-908F-8918AEAF8904@.microsoft.com...
> Hi
> I am trying to format a negative number ie -9999 with the following format
> and am getting -(9,999).
> =iif(Fields!ActualQty.Value < 0, "(0,000)"," 0,000")
> Is there a way to get rid of the negative sign and only show the brackets?
> And where can I find are some detailed examples for conditional
formatting?
> Thanks
> Fiona|||try
Expression
=iif(Fields!ActualQty.Value < 0, "(" & Fields!ActualQty.Value & ")",
Fields!ActualQty.Value )
And format the field as 0,000
"Fiona Harrison" wrote:
> Hi
> I am trying to format a negative number ie -9999 with the following format
> and am getting -(9,999).
> =iif(Fields!ActualQty.Value < 0, "(0,000)"," 0,000")
> Is there a way to get rid of the negative sign and only show the brackets?
> And where can I find are some detailed examples for conditional formatting?
> Thanks
> Fiona|||Here's what I use.
with two decimals:
$#,#.##;($#,#.##);$0.00
without no decimals:
$#,#;($#,#);$0
one customer likes zeros to be blank, like this:
$#,#;($#,#);
I like zeros to be "zero", like this:
$#,#;($#,#);Zero
BR//Jerry|||Hi people
Thanks for your help.
I put the following in the format place and works really well.
#,##0;(#,##0)
Fiona
"Jerry Nixon" wrote:
> Here's what I use.
> with two decimals:
> $#,#.##;($#,#.##);$0.00
> without no decimals:
> $#,#;($#,#);$0
> one customer likes zeros to be blank, like this:
> $#,#;($#,#);
> I like zeros to be "zero", like this:
> $#,#;($#,#);Zero
> BR//Jerry
>|||Jerry, thanks for your input! You rock! I've emailed several reporting
services "gurus" I know and none of them came as close to getting it as even
I did!
Here's the format I ended up using (after reading your post) to display
negative numbers with 2 decimal points:
#,#.##;(#,#.##);0.00
The negative numbers display like (99.99) with no negative sign.
Many thanks!
"Jerry Nixon" wrote:
> Here's what I use.
> with two decimals:
> $#,#.##;($#,#.##);$0.00
> without no decimals:
> $#,#;($#,#);$0
> one customer likes zeros to be blank, like this:
> $#,#;($#,#);
> I like zeros to be "zero", like this:
> $#,#;($#,#);Zero
> BR//Jerry
>

Conditional Formatting

I have a matrix report that has rows grouped by year, by period, and
columns grouped by sales office.
I would like to conditionally format the b/g colour of the data
values so that if for example period 1 sales in 2007 were less than
period 1 sales in 2006, the b/g would be red.
I have done this in othjer reports but how can it be done in a
matrix table?
--
-- -- -- -
Posted with NewsLeecher v3.7 Final
Web @. http://www.newsleecher.com/?usenet
-- -- -- -- -On May 7, 5:54 am, Jason (j...@.junkiesplace.net) wrote:
> I have a matrix report that has rows grouped by year, by period, and
> columns grouped by sales office.
> I would like to conditionally format the b/g colour of the data
> values so that if for example period 1 sales in 2007 were less than
> period 1 sales in 2006, the b/g would be red.
> I have done this in othjer reports but how can it be done in a
> matrix table?
> --
> -- -- -- -
> Posted with NewsLeecher v3.7 Final
> Web @.http://www.newsleecher.com/?usenet
> -- -- -- -- -
The best way to manage this would be to set a flag column in the query/
stored procedure that is sourcing the report. Then if the sales are
lower for a later year, the LowerFlag column = 1 in the query. Then in
the report, on the Layout tab, select the cells that will be affected
by the background color change and in the Properties window next to
background color, enter in an expression something like:
=iif(Fields!LowerFlag.Value = 1, "Red", "White")
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant

Conditional Formating of axis labels(Urgent!)

Depending on the time interval, that is covered by the report and which will
be provided by parameters, the format of the x-axis labels should be e.g.
hh:mm:ss or dd.mm.yyyy. It seems to me, that the format can only be defined
statically. Why can't I use expressions here?
Any help is appreciated, thanks.You can certainly use expressions for the format code on x-axis labels.
However, make sure that the category grouping expressions (which get applied
to the x-axis) result in objects of type DateTime and not in strings. Format
codes won't have any effect on strings.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Niklas" <Niklas@.discussions.microsoft.com> wrote in message
news:FD03A48E-AA5C-400E-815D-FDE48CD07CD0@.microsoft.com...
> Depending on the time interval, that is covered by the report and which
will
> be provided by parameters, the format of the x-axis labels should be e.g.
> hh:mm:ss or dd.mm.yyyy. It seems to me, that the format can only be
defined
> statically. Why can't I use expressions here?
> Any help is appreciated, thanks.

Conditional formating in Subtotals?

I have subtotals in a matrix and I want to format the background color depending on the subtotal value - > 95 = "Green", < 95 > 90 = "Yellow", < 90 = "Red". I go to the Subtotal properties and put the iif expression in the background color, but it's not detecting the Subtotal value. How do I refer to the Subtotal value in a formating expression?

You have to put the background color expression directly on the matrix cell, similar to the approach described in this thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=648771&SiteID=1

-- Robert

|||That didn't work. I copied the example from the post and I set a different color for each of the three results. It set all the cells as "In Subtotal of entire matrix" color. The odd thing is, I also have a background image which shows up correctly in the preview, but the image doesn't show up when I render the report from a browser.|||

if you are doing alot of testing, make sure to refresh the report in the browser from the view to the print view. I have found that SSRS cache's data and unless a refresh is done it can still show previous view data.

Weird.

|||We need more information.

an iif statement will work, how are you computing the subtotals, is it in a group footer? if so something like

=iif( sum(fields!field.value) < Number, "Green", "Yellow")

should work...|||

Yes, I created the subtotals by right-clicking the row cell of the outer-most group and selecting 'subtotals'. The problem with the simple iif... is that it affects all the cells - not just the subtotals.

I did find out why my background image wasn't showing in the subtotals, if you set a background color in the Subtotal properties, it will overlay the background image from the cell properties.

|||I think the InScope solution may be the key to my problem, but for some reason, it isn't detecting the scope. Any ideas on what I've done wrong?|||

Robert was right. The InScope works, but I needed to add the matrix name and underscore to the row and columns group names since I had more than one matrix in the report.

Thanks Robert!

Conditional format within matrix, depending on subtotal?

Hi there,

I'm having trouble inserting a conditional format to a specific column.

e.g.: Matrix within the rows the "weeks" (1, 2, 3, ... , 52), and in the column a "lastyear revenu", "thisyear revenue" and a difference between them, "delta %", in percent grouped by the stores.
I added a subtotal to it so I get in the latest column the "total lastyear revenue", the "total thisyear revenue" and a difference between them in percent for all stores, "total delta %", for a specific week.

Problem: I want to colour the "delta %" column green when it is greater then the "total delta %" value.

I thougt this would be quite easy, but it really is a pain in the *** because, in the background expression dialog box, I can't refer to the subtotal cells ...

I tried to create a simple report from a cube with Month,Store,Turnover, Previous Year turnover and Delta %. Then I placed on the rows the months and the stores and the values in the colums (that should be the way that you did on the report, am I correct?) and I added the subtotal. Then in the background expression I wrote this:

"=iif(sum(Delta.values) > sum(Delta.values,"Dataset1"),"Green","White")"

Doing this I had the monthly delta background in green when it was higher than the total one.

I hope that I was clear enough!

|||So, am I getting this right:

You simply created another dataset in which you calculate the "total delta %". You then refer in the background expression dialog box to the "total delta %" field of the new dataset?
|||

The dataset is the same, I just refer to the whole dataset in the formula.

So, I have only one dataset (dataset1) and in the % delta for the background I use a formula like:

iif((sum(Fields!CYRevenue.value)-sum(Fields!PYRevenue.value))/sum(fields!PYRevenue.value) > (sum(Fields!CYRevenue.value,"Dataset1")-sum(Fields!PYRevenue.value,"Dataset1"))/sum(fields!PYRevenue.value,"Dataset1"),"Green","White")

Hope it helps!

Conditional format within matrix, depending on subtotal?

Hi there,

I'm having trouble inserting a conditional format to a specific column.

e.g.: Matrix within the rows the "weeks" (1, 2, 3, ... , 52), and in the column a "lastyear revenu", "thisyear revenue" and a difference between them, "delta %", in percent grouped by the stores.
I added a subtotal to it so I get in the latest column the "total lastyear revenue", the "total thisyear revenue" and a difference between them in percent for all stores, "total delta %", for a specific week.

Problem: I want to colour the "delta %" column green when it is greater then the "total delta %" value.

I thougt this would be quite easy, but it really is a pain in the *** because, in the background expression dialog box, I can't refer to the subtotal cells ...

I tried to create a simple report from a cube with Month,Store,Turnover, Previous Year turnover and Delta %. Then I placed on the rows the months and the stores and the values in the colums (that should be the way that you did on the report, am I correct?) and I added the subtotal. Then in the background expression I wrote this:

"=iif(sum(Delta.values) > sum(Delta.values,"Dataset1"),"Green","White")"

Doing this I had the monthly delta background in green when it was higher than the total one.

I hope that I was clear enough!

|||So, am I getting this right:

You simply created another dataset in which you calculate the "total delta %". You then refer in the background expression dialog box to the "total delta %" field of the new dataset?
|||

The dataset is the same, I just refer to the whole dataset in the formula.

So, I have only one dataset (dataset1) and in the % delta for the background I use a formula like:

iif((sum(Fields!CYRevenue.value)-sum(Fields!PYRevenue.value))/sum(fields!PYRevenue.value) > (sum(Fields!CYRevenue.value,"Dataset1")-sum(Fields!PYRevenue.value,"Dataset1"))/sum(fields!PYRevenue.value,"Dataset1"),"Green","White")

Hope it helps!

CONDITIONAL FORMAT IN SUBTOTAL

This is a multi-part message in MIME format.
--=_NextPart_000_0014_01C535DC.35B57990
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Hello!
I'm trying aply conditional format in subtotal fields belongings to a = Matrix. But it seems it doesn't work. I'd like to change the background = color each two rows , I'm using something like that:
=3Diif(RowNumber(Nothing) mod 2=3D1,"Black","White")
Anyone Knows why it doesn't work rowNumber in subtotal fields or any new = idea?
--=_NextPart_000_0014_01C535DC.35B57990
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Hello!

I'm trying aply conditional format in = subtotal fields belongings to a Matrix. But it seems it doesn't work. I'd like to = change the background color each two rows , I'm using something like = that:

= =3Diif(RowNumber(Nothing) mod 2=3D1,"Black","White")

Anyone Knows why it doesn't work = rowNumber in subtotal fields or any new idea?
--=_NextPart_000_0014_01C535DC.35B57990--I haven't tried this yet, but from using Report Services so far... the only
functions recognized in expressions for fields and such are global VB
Functions. So I think that's why RowNumber doesn't work, but I could be
wrong.
Maybe you could try putting your subtotals in a different matrix with a
query that actually calculates the subtotals... and also with said query add
the row numbers (using identity or whatnot) and use those for your formatting
expression.
"Estefania" wrote:
> Hello!
> I'm trying aply conditional format in subtotal fields belongings to a Matrix. But it seems it doesn't work. I'd like to change the background color each two rows , I'm using something like that:
> =iif(RowNumber(Nothing) mod 2=1,"Black","White")
> Anyone Knows why it doesn't work rowNumber in subtotal fields or any new idea?

Conditional Format

I have a report that I would like to highlight some rows in it. I would have
to change the backgound color and set font to bold. The backgound color and
the flag to bold or not would be return by the stored procedure with the
data. For example:
'Gross Sales', 100, 'LightGrey', 'Bold'
'Net Sales', 80, '','' or 'Net Sales', 80, 'Transparent','Normal'
Is there a way to set formatting conditionally like that based on data
returned? Any help is appreciated.
Thanks,
Carmen.Hi Carmen
I assume that you are using a table to provide these rows. If you select
the row and click on the Background Color property, the top option is
'<expression>'. Selecting this takes you to a window that allows you to
access fields from your dataset.
something like '=Fields!BackgroundColor' entered here should do what you are
looking for, provided that your stored procedure returns a value that
definitely matches a color selection.
A similar thing can be done under the Font>FontWeight property for the row.
All the best,
Ed Allison
"Carmen" <Carmen@.discussions.microsoft.com> wrote in message
news:9BFFBF24-99D9-4D05-9C64-C4E0466337BC@.microsoft.com...
>I have a report that I would like to highlight some rows in it. I would
>have
> to change the backgound color and set font to bold. The backgound color
> and
> the flag to bold or not would be return by the stored procedure with the
> data. For example:
> 'Gross Sales', 100, 'LightGrey', 'Bold'
> 'Net Sales', 80, '','' or 'Net Sales', 80, 'Transparent','Normal'
> Is there a way to set formatting conditionally like that based on data
> returned? Any help is appreciated.
> Thanks,
> Carmen.|||They will not bind up automatically based on name... So in the Gross Sales,
background color expression you might place. (ie the query colum names
Select col, whatever, backcolor, fontweight from yourtable..)
=Fields!backcolor.Value
If the backcolor value might be empty you can provide a default value ie
=IIF(Fields!backcolor.Value is nothing,
'SomeDefaultColor',Fields!backcolor.Value)
(it would probably be better to provide a default back color in the query,
so it can be changed more easily than going into each expression tho.)
Hope this helps...
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"Carmen" wrote:
> I have a report that I would like to highlight some rows in it. I would have
> to change the backgound color and set font to bold. The backgound color and
> the flag to bold or not would be return by the stored procedure with the
> data. For example:
> 'Gross Sales', 100, 'LightGrey', 'Bold'
> 'Net Sales', 80, '','' or 'Net Sales', 80, 'Transparent','Normal'
> Is there a way to set formatting conditionally like that based on data
> returned? Any help is appreciated.
> Thanks,
> Carmen.|||Thanks Ed and Wayne for your responses. I decided to manually (in the report)
set backgound and bold since joining two queries on the report seemed a
little too complicated. However, I might use you guys suggestion in the
future.
Thanks again,
Carmen.
"Wayne Snyder" wrote:
> They will not bind up automatically based on name... So in the Gross Sales,
> background color expression you might place. (ie the query colum names
> Select col, whatever, backcolor, fontweight from yourtable..)
> =Fields!backcolor.Value
> If the backcolor value might be empty you can provide a default value ie
> =IIF(Fields!backcolor.Value is nothing,
> 'SomeDefaultColor',Fields!backcolor.Value)
> (it would probably be better to provide a default back color in the query,
> so it can be changed more easily than going into each expression tho.)
> Hope this helps...
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> I support the Professional Association for SQL Server ( PASS) and it''s
> community of SQL Professionals.
>
> "Carmen" wrote:
> > I have a report that I would like to highlight some rows in it. I would have
> > to change the backgound color and set font to bold. The backgound color and
> > the flag to bold or not would be return by the stored procedure with the
> > data. For example:
> >
> > 'Gross Sales', 100, 'LightGrey', 'Bold'
> > 'Net Sales', 80, '','' or 'Net Sales', 80, 'Transparent','Normal'
> >
> > Is there a way to set formatting conditionally like that based on data
> > returned? Any help is appreciated.
> >
> > Thanks,
> >
> > Carmen.

Conditional display

This is a multi-part message in MIME format.
--=_NextPart_000_006A_01C7E655.2B232280
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
How do I get a row to display conditionally? For example below - I = don't want the shirts row to display because it's zero. I'm using = Report Builder against a Cube:
Products: Qty:
shoes 5
shirts 0
gloves 10
--=_NextPart_000_006A_01C7E655.2B232280
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
How do I get a row to display = conditionally? For example below - I don't want the shirts row to display because it's zero. I'm using Report Builder against a Cube:

Products: = Qty:
shoes  = ; 5
shirts = 0
gloves = 10

--=_NextPart_000_006A_01C7E655.2B232280--On Aug 24, 3:46 pm, "Joe" <hortoris...@.gmail.dot.com> wrote:
> How do I get a row to display conditionally? For example below - I don't want the shirts row to display because it's zero. I'm using Report Builder against a Cube:
> Products: Qty:
> shoes 5
> shirts 0
> gloves 10
I'm not sure if this will work in your scenario, but in a standard
SSRS report you could use an expression like this in the Value
property of the table cell/etc:
=iif(Fields!Qty.Value = 0, Nothing, Fields!Qty.Value)
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant

concatenation but with a format...

Hi Consider a table with a decimal and a string varibale.
Dec String
3 fred
23 bill
I need to concatenate them but with leading zeros such as
03Fred
23Bill
Ideas please ??
GerryAs long as you don't need to cope with negative numbers, you can get by with the simple solution of:SELECT Replace(Str([Dec], 2), ' ', '0')
FROM dbo.myTable-PatP|||drop table #tmp
create table #tmp(id int,col1 varchar(10))
go
insert #tmp(id,col1) values(1,'a')
insert #tmp(id,col1) values(11,'b')
insert #tmp(id,col1) values(111,'c')
insert #tmp(id,col1) values(1111,'d')
insert #tmp(id,col1) values(11111,'e')
insert #tmp(id,col1) values(23,'f')
go
select replicate('0',5-len(cast(id as varchar)))+cast(id as varchar)+' '+col1 from #tmp|||Both worked nicely. Thanks a lot

Gerry