Showing posts with label formatting. Show all posts
Showing posts with label formatting. Show all posts

Sunday, March 11, 2012

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

Thursday, March 8, 2012

Conditional number formatting

How can I use conditional number formatting if one of the formats contain a
comma?
The iif statement conditions are separated by commas. If one of the
conditionals should have the number format:
#,##0,;(#,##0,);0
While the other condition should format to percentage:
#.0%;(#.0%);0.0%
how would I write the iif statement? Or is there another way to format this?I just answered my own question. I needed to put the number format syntax in
double qoutes.
"RJB" wrote:
> How can I use conditional number formatting if one of the formats contain a
> comma?
> The iif statement conditions are separated by commas. If one of the
> conditionals should have the number format:
> #,##0,;(#,##0,);0
> While the other condition should format to percentage:
> #.0%;(#.0%);0.0%
> how would I write the iif statement? Or is there another way to format this?

Wednesday, March 7, 2012

Conditional Formatting?

Is there the equivalent of MS Access 'conditional formatting' for fields in report services, if so how do I access it?

Thanks

You will need to use expressions - check the following links:

* http://www.sqlservercentral.com/columnists/bknight/reportingservicesconditionalformatting.asp

* http://defdeveloper.blogspot.com/2005/10/conditional-row-formatting-in.html

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

-- Robert

conditional formatting WITHIN textbox

Hi,
I have a report containing a table like this:
----
|=Fields!callRef.Value | ="Problem:" & Fields!callDesc.Value |
----
My boss wants me to make "Problem" bold while leaving the call description
(callDesc) in plain font.
--> Am I correct in thinking that this cannot be done?
My only solution is to do something like (add a new column):
----
|=Fields!callRef.Value | "Problem:" |= Fields!callDesc.Value |
----
Is there a better solution?
Cheers,
JoeSplitting these in to 2 text boxes are your only option as far as I know. I
have had to do that as well
Craig
"Joe Dillon" <JoeDillon@.discussions.microsoft.com> wrote in message
news:CA72895F-20A8-4963-B28B-94EAA9DB5185@.microsoft.com...
> Hi,
> I have a report containing a table like this:
> ----
> |=Fields!callRef.Value | ="Problem:" & Fields!callDesc.Value |
> ----
> My boss wants me to make "Problem" bold while leaving the call description
> (callDesc) in plain font.
> --> Am I correct in thinking that this cannot be done?
> My only solution is to do something like (add a new column):
> ----
> |=Fields!callRef.Value | "Problem:" |= Fields!callDesc.Value |
> ----
> Is there a better solution?
> Cheers,
> Joe|||You should be able to enter an expression into the format property
box...I have some stuff that shows a $ in a money column if its the
first or last row...like that or were you talking more about the font
and size?
--
Ben Sullins
http://bensullins.com|||Thanks for replies.
Craig - acutally my boss prefers it split so I am ok!
Sullins - yes I want to make the font bold
"sullins602" wrote:
> You should be able to enter an expression into the format property
> box...I have some stuff that shows a $ in a money column if its the
> first or last row...like that or were you talking more about the font
> and size?
> --
> Ben Sullins
> http://bensullins.com
>

Conditional Formatting With DateDiff

I am attemting to write an expression that changes the background color if the difference between two dates is less than 60 days. I have this expression but it does not work. The color never changes.

=IIF (DateDiff("Day", Fields!pract_start_date.Value, Fields!project_start_date.Value) < 60,"Yellow","White")

Maybe you should wrap the DateDiff() with an ABS() in case the dates are in the wrong order in your function, resulting in a DateDiff of -60 rather than 60?

|||

That's exactly what was wrong. I had them in the wrong order. I thought I had checked for that but, evidently, did not. Thanks.

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 when visability = True or False

I have groupings in which the first column of each new group hides duplicates
(all in one report). I would like the first listing (row) of each new group
to be highlighted (i.e. the one without the hidden field).
Is there any way to accomplish this task?you might test to see if a value in the table is = the min of a column's values (if your data is ordered on one of your columns)
you could also include a rowtype column in your datasource to examine
Your hidden expression would be like:
=iif(Fields!fieldsortedby.value = min(Fields!fieldsortedby.value),false,true)
--
Message posted via http://www.sqlmonster.com

Conditional Formatting Question RS 2005

Hi,

Does anyone know how to implement conditional formatting with three criteria (e.g. If a value is <83 then BGcolour Green, between 83 and 90 the BGcolour is Yellow, greater than 90 the BGcolour is green).

I have tried approaching it in two ways. One was with a case statement as follows:

CASE

WHEN TargetPer < 83 THEN 'Red'

WHEN TargetPer >= 83 AND TargetPer < 90 THEN 'Yellow'

WHEN TargetPer >= 90 THEN 'Green'

END AS BGColor

TargetPer being the value i wish to examine. I returned this in the query dataset i am using to populate the report. I then used the following expression to set the backgroundcolor property:

=Fields!BGColor.Value

Unfortunately I get no yellow fields for the appropriate values (even though the BGcolor value says yellow!!!)

The other approach i have used is the following expression to set the background colour

=IIf(Fields!TargetPer.Value>90,"Green",IIf(Fields!BGColor.Value>=83 and Fields!BGColor.Value <=90, "Yellow","Red"))

For this i still have no yellows just reds and greens!

I am using a matrix report that has subgroups on the columns for table!

Any ideas anyone?

Thanks

Marek

You have 2 options to implement CASE logic. Using nested Iif() calls or using the Switch() method. The definition is Switch(<condition>, <value>[,<condition>, <value>],....). So for the first condition that evaluates to true it's corresponding value is returned.

In your case the problem is that you are not applying any aggregation function around your field references. Any groupping naturally means that some aggregation will be applied. If not specified, the default aggregation function is First() rather than Sum(). Also you seem to have Fields!BGColor.Value in your condition which I believe should be Fields!TargetPer.Value.

So the way I see it you have 2 options to re-write your statement:

Using Iif():

=Iif

( Sum(Fields!TargetPer.Value) > 90

, "Green"

, Iif

( Sum(Fields!TargetPer.Value) >= 83

, "Yellow"

, "Red"

)

)

Using Switch():

=Switch

( Sum(Fields!TargetPer.Value) > 90

, "Green"

, Sum(Fields!TargetPer.Value) >= 83

, "Yellow"

, Sum(Fields!TargetPer.Value) < 83 'You could also put True here (CASE ELSE)

, "Red"

)

Conditional Formatting Question

I have a column that contains either a test score or the state abbreviation where the student passed a particular test. I want the text to be red between 100-159, green greater than or equal to 160 and black otherwise.

This is what I've tried to take care of the scores:

=IIf(Fields!Score.Value <= 159 and Fields!Score.Value > 100,"Red", "Green")

Two issues:
I want all other scores (< 100) to be Black and
I'm not sure how to deal with the state abbreviations. I'm assuming this is why I receive this error:

[rsRuntimeErrorInExpression] The Color expression for the textbox ‘Score’ contains an error: Input string was not in a correct format.
Preview complete -- 0 errors, 1 warnings

All data is stored as varchar.

Hello,

Yes, the reason you get that warning is because your field is stored as varchar. I think this will do what you want, you can add additional conditions for different colors.

=Switch(

IsNumeric(Fields!Score.Value) and cInt(Fields!Score.Value) >= 160, "Green",

IsNumeric(Fields!Score.Value) and cInt(Fields!Score.Value) > 100, "Red",

1=1, "Black"

)

Jarret

|||Thanks. That did it.

Conditional Formatting Problem when Rendering to Excel vs. PDF/HTML

Hello,

I need some quick help. I have conditional formating for color in some of the cells in my reports. When I render the reports to HTML or PDF, colors work great. When I render the report to Excel, all the cells come through in one color. How do I fix this in RS 2000? Or how do make the rendered Excel file just one specific color, like black?

Hi,

There was a similar problem with the hyperlinks used in the reports.

When HTML or I guess .pdf works fine Excel was not working as desired.

Targeting the output format that will be generally rendered by users was my solution then.

Eralper

Conditional formatting on drill down

I have a report that when the user drills down I need to change the formatting to include borders and/or colours. I tried adding the following statement to top border of the element without luck. Since I don't know when the detail is the last line of the drop down, I am uncertain how to try adding it there.

I do not want the underline to show when there is no expanded drill down.

=iif((Previous(Level) > 0, "Dotted", "None")

g1 name1 total1

g1 name2 total2

d po#3a total3a

d po#3b total3b <<-- want to add underline between total3b and total3

g1 name3 total3

Thank you

Just a quick stab...but couldn't you include the "underline" as a border on the top of your "g1 name total3" line as opposed to on the bottom of the last subtotal? That way it looks like it's rendering on the lowest one, but in reality it's not.

|||

I'm sorry I wasn't clear enough. I need the correct statement to go on the border style TOP of the g1 entry so that it will only show when there is a drill down.

I have tried "=iff((previous(level)) > 0), "Dotted", "None")" however it did not work. Along with variations on a theme.

Conditional Formatting on Datetime Field

I am building a report with a query that includes a field of type datetime.
I would like to change the color of the text in this column to RED if the
value contained in the field is earlier than now() - 10 minutes. I would
appreciate direction as to the appropriate function to use in this scenario.
I have tried to work with datediff, but without positive results.
Regards,
Pete Zerger, MCSE(Messaging)
Co-founder and Webmaster, MOMReourcs.org
URL:http://www.momresources.org
mailto:pete.zerger@.gmail.comWill something like this work for you?
=iif(Fields!EventDate.Value < dateadd("n", -10, Now()), "Red", "Black")
Regards,
Dan

Conditional formatting on a text box

Hi all -
I have a report that I need to duplicate in RS. What I'm trying to do is put
some conditional formatting on my detail textboxes.
I want the text to be bold if the field = certain things. ie. If the line
detail on the report = "Invoiced Sales", "Trade Sales", "Net Sales"..etc I
need that line to be bolded.
I'm trying to use an IIF statement as an expression for the textbox's
Format/FontWeight property, but am having trouble compiling.
Here's what I've tried:
=IIF(Fields!linedetail.Value = "NET INCOME", fontweight = BOLD, fontweight = NORMAL)
=IIF(Fields!linedetail.Value = "NET INCOME", BOLD, NORMAL)
Of course, I will eventually need to convert this into some kind of OR
statment because there are 7 different possible line details that should be
bolded.
I appreciate any help you could give. Thanks in advance!
-KellyHi Kelly
I have found the following works.
= iif(rtrim(Fields!ObjectCode.Value) = "Your text", "Bold","Normal")
But is has to go in the "FontWeight" property area.
"Kelly" wrote:
> Hi all -
> I have a report that I need to duplicate in RS. What I'm trying to do is put
> some conditional formatting on my detail textboxes.
> I want the text to be bold if the field = certain things. ie. If the line
> detail on the report = "Invoiced Sales", "Trade Sales", "Net Sales"..etc I
> need that line to be bolded.
> I'm trying to use an IIF statement as an expression for the textbox's
> Format/FontWeight property, but am having trouble compiling.
> Here's what I've tried:
> =IIF(Fields!linedetail.Value = "NET INCOME", fontweight = BOLD, fontweight => NORMAL)
> =IIF(Fields!linedetail.Value = "NET INCOME", BOLD, NORMAL)
> Of course, I will eventually need to convert this into some kind of OR
> statment because there are 7 different possible line details that should be
> bolded.
> I appreciate any help you could give. Thanks in advance!
> -Kelly
>
>|||For more complex conditionals you can use switch() in place of an iif(). For
example:
=Switch(Fields!linedetail.Value="Trade Sales", "Bold",
Fields!linedetail.Value="Invoiced Sales", "Bold",
Fields!linedetail.Value="Net Sales", "Normal")
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Fiona Harrison" <FionaHarrison@.discussions.microsoft.com> wrote in message
news:9E52BF14-C73D-4C2F-ABC7-5844C6ABBE19@.microsoft.com...
> Hi Kelly
> I have found the following works.
> = iif(rtrim(Fields!ObjectCode.Value) = "Your text", "Bold","Normal")
> But is has to go in the "FontWeight" property area.
> "Kelly" wrote:
>> Hi all -
>> I have a report that I need to duplicate in RS. What I'm trying to do is
>> put
>> some conditional formatting on my detail textboxes.
>> I want the text to be bold if the field = certain things. ie. If the line
>> detail on the report = "Invoiced Sales", "Trade Sales", "Net Sales"..etc
>> I
>> need that line to be bolded.
>> I'm trying to use an IIF statement as an expression for the textbox's
>> Format/FontWeight property, but am having trouble compiling.
>> Here's what I've tried:
>> =IIF(Fields!linedetail.Value = "NET INCOME", fontweight = BOLD,
>> fontweight =>> NORMAL)
>> =IIF(Fields!linedetail.Value = "NET INCOME", BOLD, NORMAL)
>> Of course, I will eventually need to convert this into some kind of OR
>> statment because there are 7 different possible line details that should
>> be
>> bolded.
>> I appreciate any help you could give. Thanks in advance!
>> -Kelly
>>

Conditional Formatting on a table

Hi all,

Any ideas how to apply conditional formatting depending on the value of a particular cell in a table. Basically i want to say that if a value is less than 0, then that figure should have a red background, if the figure is = 0, then leave it white, and then if the figure is more than 0, then put a green background on it. Any ideas how to do this??

Miles

Here is an article that may help you outhttp://blogs.msdn.com/swisowaty/attachment/661446.ashx

About half way into the article it talks about conditional formating with colors.

conditional formatting in reporting services (2005)

I am new to reporting services and am unable to get cell colouring conditional formatting to work in of a report. Basically I need to have the following:

>-5% = "Red"

Between -5% and -1% = "Green"

0% = "White"

Between 1% and 5% = "Green"

>5% = "Red"

Here is what I have tried to use:

=iif((Fields!TrueUpPercQ1.Value >0 and

Fields!TrueUpPercQ1.Value <=0.05) or (Fields!TrueUpPercQ1.Value < 0 and

Fields!TrueUpPercQ1.Value >=-0.05), "Green",

iif(Fields!TrueUpPercQ1.Value =0, "White","Red"))

Any ideas?

Thanks

Try this....

=iif((Fields!TrueUpPercQ1.Value > 0, "Red", IIF(Fields!TrueUpPercQ1.Value <=0.05, "Red", IIF(Fields!TrueUpPercQ1.Value < 0, "Blue,

IIF(Fields!TrueUpPercQ1.Value >=-0.05, "Green", iif(Fields!TrueUpPercQ1.Value =0, "White","Red")))))

You may want to tweak this to suit your need. Also make sure the number of closing brackets are correct. This is the way the IIF is used. Hope it helps. Let me know.

|||

Iif would work, but I think switch would be better

Try this...you have to have even number of arguments for it to work correctly. The first is your statement to evaluate to true then the second argument is executed, if false it proceeds to the next (3rd) argument until something evaluates as true.

=SWITCH(Fields!TrueUpPercQ1.Value <=0.05, "Red", Fields!TrueUpPercQ1.Value < 0, "Green", Fields!TrueUpPercQ1.Value =0, "White")

1 2 3 4 5 6

|||

Iif would work, but I think switch would be better

Try this...you have to have even number of arguments for it to work correctly. The first is your statement to evaluate to true then the second argument is executed, if false it proceeds to the next (3rd) argument until something evaluates as true.

=SWITCH(Fields!TrueUpPercQ1.Value <=0.05, "Red", Fields!TrueUpPercQ1.Value < 0, "Green", Fields!TrueUpPercQ1.Value =0, "White")

Conditional Formatting in Report Builder

Hi all!

I would like to know if Conditional Formatting in Report Builder is supported in Service Pack 2 CTP.

Thanks for your time.Conditional formatting in Report Builder is not in SP2. We are considering adding it to a future release.

-Josh|||

I am interested in conditional formatting for ad hoc reports as well. Is this something that will be available in the SQL 2008 release?

Thanks,

Lisa

Conditional Formatting in Report Builder

Hi all!

I would like to know if Conditional Formatting in Report Builder is supported in Service Pack 2 CTP.

Thanks for your time.
Conditional formatting in Report Builder is not in SP2. We are considering adding it to a future release.

-Josh
|||

I am interested in conditional formatting for ad hoc reports as well. Is this something that will be available in the SQL 2008 release?

Thanks,

Lisa

Conditional Formatting in a Matrix Control

Hi there.

I am creating a report that the requirements need different background colors based on the row or column as shown below:

Community Total # of Respondents Resident's Overall Satisfaction Rating Quality of Repair May '07 41 3.6 5.0 April '07 14 1.8 3.0 Q2 '07 55 2.7 4.0 March '07 36 3.6 3.0 February '07 28 4.0 1.2 January '07 22 2.2 4.0 Q1 '07 86 3.3 2.7 YTD '07 141 3.0 3.2 December '06 33 3.8 4.2 November '06 27 2.6 5.0 October '06 42 1.8 3.0 Q4 '06 102 2.7 4.1 September '06 58 4.0 2.2 August '06 84 2.0 1.6 July '06 52 3.2 3.4 Q3 '06 194 3.1 2.4 June '06 40 2.4 4.2 May '06 41 3.6 5.0 April '06 14 1.8 3.0 Q2 '06 95 2.6 4.1 March '06 67 N/A 3.8 February '06 38 N/A 2.8 January '06 N/A 3.8 N/A Q1 '06 105 3.8 3.3 YTD '06 496 2.9 3.5 Rolling 12 Month Average 477 2.9 3.3

'

I can get the row colors to work great with an expression, but when I try to add the gray column with conditional formatting for the Resident's Overall Satisfaction Rating question, it clobbers my row formatting. I am thinking that I will have to do some gnarly expression in each of the rows and columns using the InScope function. Does that sound about right, or is there an easier way?

Thanks, Mike

Actually, this was easy once I looked at it again. On the detail cell, I just added another condition that identified the column in question and set the color appropriately. Works great.

Sometimes it just takes another look!

- Mike

Conditional formatting for regular (non-calculated) measures?

The examples of conditional formatting I have found have all related to calculated measures -- i.e., ones defined on the "calculations" tab of the cube designer. On the calculation definition panel there is a "format string" (which deals with currency symbols and thousands separators and such), "color expressions," and "font expressions." Examples in various places show using an IIF to set color based on the calculated measure's value. I assume that any valid boolean-yielding expression could be used in the IIF, that there is no scope limit that restricts the test to the measure's value.

Okay. Is there some way, for display within the SSAS cube browser, to do conditional formatting for regular (non-calculated) measures?

In the properties panel of such measures I see a "format string" property and a separate "format" property but no "color expressions" or "font expressions" and I have seen no examples of putting IIFs into a regular measure's properties. Perhaps this is only a user-interface issue and not an architectural issue. Perhaps there is some MDX way to set the "fore color" property programmatically and have the color assignment be visible in the SSAS cube browser. Perhaps if one puts certain magical strings into the "Format" property conditional formatting will occur.

I have tried to RTFM. If this is all answered in documentation someplace, just point me to it.

CW

You can do this in the Calculations script for the cube, using a Scope assignment and the Fore_Color and Back_Color properties.

For example, in the Adventure Works sample, I added this to the Caculations script for the cube to set the foreground color of the [Internet Sales Amount] measure (a standard measure) to red:

Scope

(

[Measures].[Internet Sales Amount]

);

Fore_Color ( This ) = "255";

EndScope;

The following link in BOL explains (somewhat) the values to use for setting the Fore_Color and Back_Color properties (using standard RGB numeric values):

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/mdxref9/html/ff8f40cb-2ac4-4fc2-9761-7f1b14c17c8c.htm

HTH,

Dave F.

|||

Or, a simpler syntax (and also fixing the color to be int, not a string)

Fore_Color([Measures].[Internet Sales Amount]) = 255;

|||

Okay, using this info and the supplemental info from Mosha I have been able to turn a relevant measure red.

However, I am an MDX noob and am not making progress so I am back with a follow-up question. Let me know if this is more appropriately posted as a new thread.

My goal is to use the SCOPE technique to set the fore color and back color of all measures associated with a dimension's members ("items") that have a certain attribute ("Is Exception") with a value of "True." The true/false attribute is assigned in the source database and is merely loaded into the cube. My success was that I was able to set the fore color of at least one measure but I see the redness only if I drag the "Is Exception" attribute into the browser as a row and expand it. "Is Exception" measure is red but the "Total" measure is black as is everything else. But I want the measures associated with the item to be red without having to drag the true/false attribute into the browser.

Anyhow, here is a sample (sanitized) query that correctly lists the items that have the flag as "True":

SELECT {[Item Dimension].[Is Exception].[True]} ON 0, NON EMPTY {[Item Dimension].[Item Name].Children } on 1 FROM ItemCube where {[Measures].[Item Distinct Count]}

When I run this I see the item names as row labels, "True" as the column label, and 1 as the cell value, and the only items listed have the Is Exception attribute value of True.

But I haven't been able to turn this into a SCOPE that colors the measures associated with the Item member.

Here's my SCOPE thingy:

SCOPE( [Item Dimension].[Is Exception].[True]);

Back_Color(This) = 255;

END SCOPE;

CW

|||

I came up with a SCOPE piece of code that seems to do what I want. Here it is, sanitized. With yellow instead of blazing red.

SCOPE(

Filter([Item Dimension].[Item].Members, [Item Dimension.[Item].Properties("Is Exception", TYPED) = True), [Measures].Members

);

Back_Color(This) = 8454143; /*R=255, G=255, B=128*/

END SCOPE;

CW

|||

I was able to use the above approach to format each account in a parent/child accounts dimension. My cube only has a single measure and the accounts dim has members that require different formatting including percentages (with 0 to 2 decimals), integers, currency (with 0 to 2 decimals). This approach works, but it is a pain in the neck to implement as I have over 500 accounts.

I have a "custom rollup" column in my parent child table that I use to specify calculations. It seems I should be able to pick a "format_string" column in the same way. Is this possible? If not, what is the best way to format an accounts dimension in a cube that has a single measure?

TIA,

-Jon

Conditional formatting for regular (non-calculated) measures?

The examples of conditional formatting I have found have all related to calculated measures -- i.e., ones defined on the "calculations" tab of the cube designer. On the calculation definition panel there is a "format string" (which deals with currency symbols and thousands separators and such), "color expressions," and "font expressions." Examples in various places show using an IIF to set color based on the calculated measure's value. I assume that any valid boolean-yielding expression could be used in the IIF, that there is no scope limit that restricts the test to the measure's value.

Okay. Is there some way, for display within the SSAS cube browser, to do conditional formatting for regular (non-calculated) measures?

In the properties panel of such measures I see a "format string" property and a separate "format" property but no "color expressions" or "font expressions" and I have seen no examples of putting IIFs into a regular measure's properties. Perhaps this is only a user-interface issue and not an architectural issue. Perhaps there is some MDX way to set the "fore color" property programmatically and have the color assignment be visible in the SSAS cube browser. Perhaps if one puts certain magical strings into the "Format" property conditional formatting will occur.

I have tried to RTFM. If this is all answered in documentation someplace, just point me to it.

CW

You can do this in the Calculations script for the cube, using a Scope assignment and the Fore_Color and Back_Color properties.

For example, in the Adventure Works sample, I added this to the Caculations script for the cube to set the foreground color of the [Internet Sales Amount] measure (a standard measure) to red:

Scope

(

[Measures].[Internet Sales Amount]

);

Fore_Color ( This ) = "255";

End Scope;

The following link in BOL explains (somewhat) the values to use for setting the Fore_Color and Back_Color properties (using standard RGB numeric values):

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/mdxref9/html/ff8f40cb-2ac4-4fc2-9761-7f1b14c17c8c.htm

HTH,

Dave F.

|||

Or, a simpler syntax (and also fixing the color to be int, not a string)

Fore_Color([Measures].[Internet Sales Amount]) = 255;

|||

Okay, using this info and the supplemental info from Mosha I have been able to turn a relevant measure red.

However, I am an MDX noob and am not making progress so I am back with a follow-up question. Let me know if this is more appropriately posted as a new thread.

My goal is to use the SCOPE technique to set the fore color and back color of all measures associated with a dimension's members ("items") that have a certain attribute ("Is Exception") with a value of "True." The true/false attribute is assigned in the source database and is merely loaded into the cube. My success was that I was able to set the fore color of at least one measure but I see the redness only if I drag the "Is Exception" attribute into the browser as a row and expand it. "Is Exception" measure is red but the "Total" measure is black as is everything else. But I want the measures associated with the item to be red without having to drag the true/false attribute into the browser.

Anyhow, here is a sample (sanitized) query that correctly lists the items that have the flag as "True":

SELECT {[Item Dimension].[Is Exception].[True]} ON 0, NON EMPTY {[Item Dimension].[Item Name].Children } on 1 FROM ItemCube where {[Measures].[Item Distinct Count]}

When I run this I see the item names as row labels, "True" as the column label, and 1 as the cell value, and the only items listed have the Is Exception attribute value of True.

But I haven't been able to turn this into a SCOPE that colors the measures associated with the Item member.

Here's my SCOPE thingy:

SCOPE( [Item Dimension].[Is Exception].[True]);

Back_Color(This) = 255;

END SCOPE;

CW

|||

I came up with a SCOPE piece of code that seems to do what I want. Here it is, sanitized. With yellow instead of blazing red.

SCOPE(

Filter([Item Dimension].[Item].Members, [Item Dimension.[Item].Properties("Is Exception", TYPED) = True), [Measures].Members

);

Back_Color(This) = 8454143; /*R=255, G=255, B=128*/

END SCOPE;

CW

|||

I was able to use the above approach to format each account in a parent/child accounts dimension. My cube only has a single measure and the accounts dim has members that require different formatting including percentages (with 0 to 2 decimals), integers, currency (with 0 to 2 decimals). This approach works, but it is a pain in the neck to implement as I have over 500 accounts.

I have a "custom rollup" column in my parent child table that I use to specify calculations. It seems I should be able to pick a "format_string" column in the same way. Is this possible? If not, what is the best way to format an accounts dimension in a cube that has a single measure?

TIA,

-Jon