Showing posts with label iif. Show all posts
Showing posts with label iif. Show all posts

Tuesday, March 20, 2012

Conditionally counting detail field

Hello. I'm trying to conditionally count a field, for example:
iif(Fields!GrantCodeID.Value = 70, 1, 0)
GrantCode is a field and whenever it equals 70, I want to count it so
I can display the count after the detail.
Any help is much appreciated.
Thanks!=Sum(iif(Fields!GrantCodeID.Value = 70, 1, 0))
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Michael" <mike1174@.comcast.net> wrote in message
news:dfd40822.0408181042.401a6798@.posting.google.com...
> Hello. I'm trying to conditionally count a field, for example:
> iif(Fields!GrantCodeID.Value = 70, 1, 0)
> GrantCode is a field and whenever it equals 70, I want to count it so
> I can display the count after the detail.
> Any help is much appreciated.
> Thanks!

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 Image in Page Header

I have an image defined as:
="http://server.name.com/images" & IIf((Left(First(Fields!logo_url.Value,
"DeploymentDeliveryHeader"),1) = "/"),First(Fields!logo_url.Value,
"DeploymentDeliveryHeader"),"/BlueBar_withGraphic.gif")
I want to move this image to the report header. But am getting a message
stating that Fields cannot be used in report headers or footers. I think I
can hide it in the report and reference it in the header. But I don't know
how to reference an image.
Anyone know how to get an image in a header?Stephanie,
Can you store the path to the image in a hidden textbox in the body of the
report, then your image in the header will refer to the textbox for it's
image source value.
Make sense?
--
Andy Potter
blog : http://sqlreportingservices.spaces.live.com
info@.(NOSPAM)lakeclaireenterprises.com
"Stephanie" <Stephanie@.discussions.microsoft.com> wrote in message
news:88A98827-774E-44F6-87B2-1B39A9544C7F@.microsoft.com...
>I have an image defined as:
> ="http://server.name.com/images" & IIf((Left(First(Fields!logo_url.Value,
> "DeploymentDeliveryHeader"),1) = "/"),First(Fields!logo_url.Value,
> "DeploymentDeliveryHeader"),"/BlueBar_withGraphic.gif")
> I want to move this image to the report header. But am getting a message
> stating that Fields cannot be used in report headers or footers. I think
> I
> can hide it in the report and reference it in the header. But I don't
> know
> how to reference an image.
> Anyone know how to get an image in a header?|||Yes, makes sense and I just did that. The report is not erroring out now
(yea!) but the image is blank. This has got to be something people do on a
regular basis, isn't it? I don't want an embedded image because the image
changes based on the database the report is run against.
Any other suggestions on how to put an external image in a page header?
"Andy Potter" wrote:
> Stephanie,
> Can you store the path to the image in a hidden textbox in the body of the
> report, then your image in the header will refer to the textbox for it's
> image source value.
> Make sense?
> --
> Andy Potter
> blog : http://sqlreportingservices.spaces.live.com
> info@.(NOSPAM)lakeclaireenterprises.com
> "Stephanie" <Stephanie@.discussions.microsoft.com> wrote in message
> news:88A98827-774E-44F6-87B2-1B39A9544C7F@.microsoft.com...
> >I have an image defined as:
> > ="http://server.name.com/images" & IIf((Left(First(Fields!logo_url.Value,
> > "DeploymentDeliveryHeader"),1) = "/"),First(Fields!logo_url.Value,
> > "DeploymentDeliveryHeader"),"/BlueBar_withGraphic.gif")
> >
> > I want to move this image to the report header. But am getting a message
> > stating that Fields cannot be used in report headers or footers. I think
> > I
> > can hide it in the report and reference it in the header. But I don't
> > know
> > how to reference an image.
> >
> > Anyone know how to get an image in a header?
>
>|||Nevermind my last reply. I tried it again and it worked just fine. THANK YOU!
Stephanie ; )
"Andy Potter" wrote:
> Stephanie,
> Can you store the path to the image in a hidden textbox in the body of the
> report, then your image in the header will refer to the textbox for it's
> image source value.
> Make sense?
> --
> Andy Potter
> blog : http://sqlreportingservices.spaces.live.com
> info@.(NOSPAM)lakeclaireenterprises.com
> "Stephanie" <Stephanie@.discussions.microsoft.com> wrote in message
> news:88A98827-774E-44F6-87B2-1B39A9544C7F@.microsoft.com...
> >I have an image defined as:
> > ="http://server.name.com/images" & IIf((Left(First(Fields!logo_url.Value,
> > "DeploymentDeliveryHeader"),1) = "/"),First(Fields!logo_url.Value,
> > "DeploymentDeliveryHeader"),"/BlueBar_withGraphic.gif")
> >
> > I want to move this image to the report header. But am getting a message
> > stating that Fields cannot be used in report headers or footers. I think
> > I
> > can hide it in the report and reference it in the header. But I don't
> > know
> > how to reference an image.
> >
> > Anyone know how to get an image in a header?
>
>

Conditional iif statement

Ok I have a sp that returns a number of records, each identfied as being part
of a group based on the Type field.
Type Name Amount
1 Test1 1.00
1 Test2 1.00
2 Test3 2.00
3 Test4 3.00
What I am using is iff(Type.value = 1, Price.value, 0) When I do this it
works fine but when I use them middle records iff(Type.value = 2,
Price.value, 0) it displays 1.00 as the price for type 2, and it should
display 2.00 for type 2.
Any help is greatly apprciated. Also, due to the formatting specifics I am
using single textboxes instead of a table or list.
DigivixTry this...
=iif(Fields!Type.Value=1, Fields!Price.Value,0)
the same with Value =2
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"DigitalVixen" <DigitalVixen@.discussions.microsoft.com> wrote in message
news:2B698793-0708-4538-9DCD-595819A758D8@.microsoft.com...
> Ok I have a sp that returns a number of records, each identfied as being
> part
> of a group based on the Type field.
> Type Name Amount
> 1 Test1 1.00
> 1 Test2 1.00
> 2 Test3 2.00
> 3 Test4 3.00
> What I am using is iff(Type.value = 1, Price.value, 0) When I do this it
> works fine but when I use them middle records iff(Type.value = 2,
> Price.value, 0) it displays 1.00 as the price for type 2, and it should
> display 2.00 for type 2.
> Any help is greatly apprciated. Also, due to the formatting specifics I
> am
> using single textboxes instead of a table or list.
> Digivix|||Sorry for the typo's but that is exactly what i am using and it is giving me
the first record's price only.
"Wayne Snyder" wrote:
> Try this...
> =iif(Fields!Type.Value=1, Fields!Price.Value,0)
> the same with Value =2
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "DigitalVixen" <DigitalVixen@.discussions.microsoft.com> wrote in message
> news:2B698793-0708-4538-9DCD-595819A758D8@.microsoft.com...
> > Ok I have a sp that returns a number of records, each identfied as being
> > part
> > of a group based on the Type field.
> >
> > Type Name Amount
> > 1 Test1 1.00
> > 1 Test2 1.00
> > 2 Test3 2.00
> > 3 Test4 3.00
> >
> > What I am using is iff(Type.value = 1, Price.value, 0) When I do this it
> > works fine but when I use them middle records iff(Type.value = 2,
> > Price.value, 0) it displays 1.00 as the price for type 2, and it should
> > display 2.00 for type 2.
> >
> > Any help is greatly apprciated. Also, due to the formatting specifics I
> > am
> > using single textboxes instead of a table or list.
> >
> > Digivix
>
>|||Can't you go back to a table and simulate the look & feel? I think if you
did it in the detail row it would pull the right values.
--
"Everyone knows something you don't know"
"DigitalVixen" wrote:
> Sorry for the typo's but that is exactly what i am using and it is giving me
> the first record's price only.
> "Wayne Snyder" wrote:
> > Try this...
> >
> > =iif(Fields!Type.Value=1, Fields!Price.Value,0)
> > the same with Value =2
> >
> >
> >
> > --
> > Wayne Snyder, MCDBA, SQL Server MVP
> > Mariner, Charlotte, NC
> > www.mariner-usa.com
> > (Please respond only to the newsgroups.)
> >
> > I support the Professional Association of SQL Server (PASS) and it's
> > community of SQL Server professionals.
> > www.sqlpass.org
> >
> > "DigitalVixen" <DigitalVixen@.discussions.microsoft.com> wrote in message
> > news:2B698793-0708-4538-9DCD-595819A758D8@.microsoft.com...
> > > Ok I have a sp that returns a number of records, each identfied as being
> > > part
> > > of a group based on the Type field.
> > >
> > > Type Name Amount
> > > 1 Test1 1.00
> > > 1 Test2 1.00
> > > 2 Test3 2.00
> > > 3 Test4 3.00
> > >
> > > What I am using is iff(Type.value = 1, Price.value, 0) When I do this it
> > > works fine but when I use them middle records iff(Type.value = 2,
> > > Price.value, 0) it displays 1.00 as the price for type 2, and it should
> > > display 2.00 for type 2.
> > >
> > > Any help is greatly apprciated. Also, due to the formatting specifics I
> > > am
> > > using single textboxes instead of a table or list.
> > >
> > > Digivix
> >
> >
> >|||Hi David,
Thank you for the reply, however I don't understand what is meant by
"simulate the look & feel", can you please be a little more specific?
Thanks
"David Bienstock" wrote:
> Can't you go back to a table and simulate the look & feel? I think if you
> did it in the detail row it would pull the right values.
> --
> "Everyone knows something you don't know"
>
> "DigitalVixen" wrote:
> > Sorry for the typo's but that is exactly what i am using and it is giving me
> > the first record's price only.
> >
> > "Wayne Snyder" wrote:
> >
> > > Try this...
> > >
> > > =iif(Fields!Type.Value=1, Fields!Price.Value,0)
> > > the same with Value =2
> > >
> > >
> > >
> > > --
> > > Wayne Snyder, MCDBA, SQL Server MVP
> > > Mariner, Charlotte, NC
> > > www.mariner-usa.com
> > > (Please respond only to the newsgroups.)
> > >
> > > I support the Professional Association of SQL Server (PASS) and it's
> > > community of SQL Server professionals.
> > > www.sqlpass.org
> > >
> > > "DigitalVixen" <DigitalVixen@.discussions.microsoft.com> wrote in message
> > > news:2B698793-0708-4538-9DCD-595819A758D8@.microsoft.com...
> > > > Ok I have a sp that returns a number of records, each identfied as being
> > > > part
> > > > of a group based on the Type field.
> > > >
> > > > Type Name Amount
> > > > 1 Test1 1.00
> > > > 1 Test2 1.00
> > > > 2 Test3 2.00
> > > > 3 Test4 3.00
> > > >
> > > > What I am using is iff(Type.value = 1, Price.value, 0) When I do this it
> > > > works fine but when I use them middle records iff(Type.value = 2,
> > > > Price.value, 0) it displays 1.00 as the price for type 2, and it should
> > > > display 2.00 for type 2.
> > > >
> > > > Any help is greatly apprciated. Also, due to the formatting specifics I
> > > > am
> > > > using single textboxes instead of a table or list.
> > > >
> > > > Digivix
> > >
> > >
> > >

Conditional Formatting (alternating row colors for groups)

I know you can do alternating row colors by doing something like this:
=iif(RowNumber(Nothing) Mod 2, "PaleGreen", "White")
But how does one do alternating row colors at the group level? The above
expression if placed on the group often colors several adjacent rows the same
color.The report at the end of this posting demonstrates how to alternate colors
at the group level
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"David Swanson (Thomson FindLaw)" <David Swanson (Thomson
FindLaw)@.discussions.microsoft.com> wrote in message
news:313C07F5-C269-41DC-84D1-7510590F910B@.microsoft.com...
> I know you can do alternating row colors by doing something like this:
> =iif(RowNumber(Nothing) Mod 2, "PaleGreen", "White")
> But how does one do alternating row colors at the group level? The above
> expression if placed on the group often colors several adjacent rows the
same
> color.
Table.DetailGroup.AltColor.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>
<Table Name="table1">
<Height>0.75in</Height>
<Style />
<Header>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox1">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>5</ZIndex>
<rd:DefaultName>textbox1</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Country</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox2">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>4</ZIndex>
<rd:DefaultName>textbox2</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Company Name</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Header>
<Details>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox4">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>1</ZIndex>
<rd:DefaultName>textbox4</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="CompanyName">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>=iif(RowNumber(Nothing) Mod 2,
"PaleGreen", "White")</BackgroundColor>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<rd:DefaultName>CompanyName</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!CompanyName.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Details>
<DataSetName>DataSet1</DataSetName>
<Width>4.33334in</Width>
<TableGroups>
<TableGroup>
<Header>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="Country">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>=iif(RunningValue(Fields!Country.Value,CountDistinct,Nothin
g) Mod 2, "Cornsilk", "White")</BackgroundColor>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>3</ZIndex>
<rd:DefaultName>Country</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!Country.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox11">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>2</ZIndex>
<rd:DefaultName>textbox11</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
<RepeatOnNewPage>true</RepeatOnNewPage>
</Header>
<Grouping Name="CountryGroup">
<GroupExpressions>
<GroupExpression>=Fields!Country.Value</GroupExpression>
</GroupExpressions>
<PageBreakAtEnd>true</PageBreakAtEnd>
</Grouping>
</TableGroup>
</TableGroups>
<TableColumns>
<TableColumn>
<Width>2.16667in</Width>
</TableColumn>
<TableColumn>
<Width>2.16667in</Width>
</TableColumn>
</TableColumns>
</Table>
</ReportItems>
<Style />
<Height>0.875in</Height>
</Body>
<TopMargin>1in</TopMargin>
<DataSources>
<DataSource Name="Northwind">
<rd:DataSourceID>b688b025-197a-46a6-ad1e-97cfe7c0d320</rd:DataSourceID>
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>data source=localhost;initial
catalog=Northwind</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
</DataSource>
</DataSources>
<Width>6.5in</Width>
<DataSets>
<DataSet Name="DataSet1">
<Fields>
<Field Name="CustomerID">
<DataField>CustomerID</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="CompanyName">
<DataField>CompanyName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="ContactName">
<DataField>ContactName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="ContactTitle">
<DataField>ContactTitle</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Address">
<DataField>Address</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="City">
<DataField>City</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Region">
<DataField>Region</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="PostalCode">
<DataField>PostalCode</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Country">
<DataField>Country</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Phone">
<DataField>Phone</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Fax">
<DataField>Fax</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>Northwind</DataSourceName>
<CommandText>select * from customers</CommandText>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
</Query>
</DataSet>
</DataSets>
<LeftMargin>1in</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<rd:DrawGrid>true</rd:DrawGrid>
<rd:ReportID>0848d9be-0d3d-4002-8752-e94ca66164e0</rd:ReportID>
<BottomMargin>1in</BottomMargin>
<Language>en-US</Language>
</Report>

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
>

Saturday, February 25, 2012

Conditional Expression - i.e., IIF in Access

I have a query with a conditional expression that I can do just fine in Access but I am having a bear of a time trying to create a similar SQL View. Baiscally I want to say, if column A is null, use value B else use value C.

In Access the SQL is this:

SELECT IIf([Categorycode] Is Null,[tblconstituents].[CASNumber],[categorycode]) AS Casnumber, Sum(qryweldingrod3a.CFume) AS CFume, Sum(qryweldingrod3a.cslag) AS cSlag
FROM qryweldingrod3a INNER JOIN tblconstituents ON qryweldingrod3a.CASNumber = tblconstituents.CASNumber
GROUP BY IIf([Categorycode] Is Null,[tblconstituents].[CASNumber],[categorycode]);

But I know you can't use the IIF statement in SQL so I was trying CASE and was still coming up empty handed. Here is what I produced in SQL but it didn't work:

SELECT SUM(dbo.RecycleWR_qryWeldingRod3a_LBS.CFume) AS CFume, SUM(dbo.RecycleWR_qryWeldingRod3a_LBS.CSlag) AS cSlag,

CASNumber = CASE Type
WHEN categoryCode IS NULL THEN dbo.tblConstituents.CASNumber ELSE CategoryCode
END,
FROM dbo.tblConstituents INNER JOIN
dbo.RecycleWR_qryWeldingRod3a_LBS ON dbo.tblConstituents.CASNumber = dbo.RecycleWR_qryWeldingRod3a_LBS.CASNumber
GROUP BY dbo.RecycleWR_qryWeldingRod3a_LBS.CASNumber

Any ideas would be greatly appreciated.SELECT CASE WHEN ColA IS NULL THEN ColB ELSE ColC END|||Originally posted by Brett Kaiser
SELECT CASE WHEN ColA IS NULL THEN ColB ELSE ColC END

Well, when I do that, I get
"The Query Designer does not support the CASE SQL construct."

Can you even use CASE in a view?|||Also, I need to assign an alias to that column.|||What are you using?

Aren't you using query analyzer?

If you're using Access you may need to make it a PASS THRU query

SELECT CASE WHEN ColA IS NULL THEN ColB ELSE ColC END AS NewCol|||I was creating the query in VIEW but I got around it using a function. Took me awhile but its working fine now. Thanks for your help|||What do you mean in VIEW?

Are you doing this in Enterprise Manager?

I would recommend against that.|||Wether you use the designer in access or in EM, you'll loose the graphical representation of your query when you use CASE (and a bunch of other constructs). This is what the error message says. The query should run fine, anyway and you should see and be able to modify the sql source in access.
However, beware of the designer, especially if you have complex where clauses. All sorts of weird things may happen to your sql ;)|||What s/he said...

Use QA though for SQL Server development...

You'll have a lot less headaches...

Conditional Count for Row Group together with Column Group

Division =Fields!Status.Value
Total
=Fields!Division.Value
=Count(iif("Status"=Fields!Status.Value,1,0),"Division")
Total:
the above is the matrix that i had done to count some values. The
"=Fields!Status.Value" is a column group call Status and the
"=Fields!Division.Value" is a row group call Division. My column goup
contains 3 fields which are "Accepted", "Rejected" and "Pending" and my row
group contais all my division fields. My database had a record of all the
submissions that had been submitted by the different divisions and what is
their submission current status which is either Accepted, Rejected or Pending.
Now what i want to achieved is that i want to display the Number of
Accepted, Rejected and Pending submissions for each Division by using the
Count function. But the expression that i had did does not give me the actual
result. i need some help on what correct expression should i use.
Thansk in advance for all the help and time consumed.ok, managed to solve it with a simple expression =Count(Fields!Status.Value)
Thanks anyway. =)
"JiaN" wrote:
> Division =Fields!Status.Value
> Total
> =Fields!Division.Value
> =Count(iif("Status"=Fields!Status.Value,1,0),"Division")
> Total:
> the above is the matrix that i had done to count some values. The
> "=Fields!Status.Value" is a column group call Status and the
> "=Fields!Division.Value" is a row group call Division. My column goup
> contains 3 fields which are "Accepted", "Rejected" and "Pending" and my row
> group contais all my division fields. My database had a record of all the
> submissions that had been submitted by the different divisions and what is
> their submission current status which is either Accepted, Rejected or Pending.
> Now what i want to achieved is that i want to display the Number of
> Accepted, Rejected and Pending submissions for each Division by using the
> Count function. But the expression that i had did does not give me the actual
> result. i need some help on what correct expression should i use.
> Thansk in advance for all the help and time consumed.

Friday, February 24, 2012

conditional color property based on date

I want to use an IIF statement to set the color property for a text box.
Here is my code that is not working:
=IIf(DateAdd('m', -6, Fields!Orig_expireDate.Value) <
Now(), "red", "black")Use " instead of '
( ' is a comment for vb, even in reporting services)
Mike G.
"ladydi_1226" <ladydi1226@.discussions.microsoft.com> wrote in message
news:92B486DB-EFFA-49BC-AEE3-76CBBF4A24AE@.microsoft.com...
>I want to use an IIF statement to set the color property for a text box.
> Here is my code that is not working:
> =IIf(DateAdd('m', -6, Fields!Orig_expireDate.Value) <
> Now(), "red", "black")