How do I exclude data using the IIF statement in report builder

I have data in my dataset that I want to exclude in the average calculation 
for the final report (those with a key.value of 999).  Here is how my 
statement is written but it is not returning any value.  What am I doing 
wrong?

=iif((Fields!MeasureID.Value = 1 or Fields!MeasureID.Value = 16) and NOT 
Fields!Key.Value = 999, round(Avg(Fields!CY_Rate.Value),0),
iif((Fields!MeasureID.Value = 19 or Fields!MeasureID.Value = 20
or Fields!MeasureID.Value = 21 or Fields!MeasureID.Value = 22
or Fields!MeasureID.Value = 23 or Fields!MeasureID.Value = 24) and NOT 
Fields!Key.Value = 999, round(Avg(Fields!CY_Rate.Value),0) & "%",nothing))
0
Utf
12/11/2009 5:20:01 PM
sqlserver.reportingsvcs 542 articles. 0 followers. Follow

2 Replies
2306 Views

Similar Articles

[PageSpeed] 41

dela
I think  you need to write custom function to get the code back
http://www.simple-talk.com/sql/learn-sql-server/beginning-sql-server-reporting-services-part-4/ - 
--Reporting Services




"dela" <dela@discussions.microsoft.com> wrote in message 
news:FDA1A48B-020A-406D-A756-BF6248F207E4@microsoft.com...
>I have data in my dataset that I want to exclude in the average calculation
> for the final report (those with a key.value of 999).  Here is how my
> statement is written but it is not returning any value.  What am I doing
> wrong?
>
> =iif((Fields!MeasureID.Value = 1 or Fields!MeasureID.Value = 16) and NOT
> Fields!Key.Value = 999, round(Avg(Fields!CY_Rate.Value),0),
> iif((Fields!MeasureID.Value = 19 or Fields!MeasureID.Value = 20
> or Fields!MeasureID.Value = 21 or Fields!MeasureID.Value = 22
> or Fields!MeasureID.Value = 23 or Fields!MeasureID.Value = 24) and NOT
> Fields!Key.Value = 999, round(Avg(Fields!CY_Rate.Value),0) & "%",nothing)) 


0
Uri
12/14/2009 9:29:42 AM
On Dec 11, 11:20=A0am, dela <d...@discussions.microsoft.com> wrote:
> I have data in my dataset that I want to exclude in the average calculati=
on
> for the final report (those with a key.value of 999). =A0Here is how my
> statement is written but it is not returning any value. =A0What am I doin=
g
> wrong?
>
> =3Diif((Fields!MeasureID.Value =3D 1 or Fields!MeasureID.Value =3D 16) an=
d NOT
> Fields!Key.Value =3D 999, round(Avg(Fields!CY_Rate.Value),0),
> iif((Fields!MeasureID.Value =3D 19 or Fields!MeasureID.Value =3D 20
> or Fields!MeasureID.Value =3D 21 or Fields!MeasureID.Value =3D 22
> or Fields!MeasureID.Value =3D 23 or Fields!MeasureID.Value =3D 24) and NO=
T
> Fields!Key.Value =3D 999, round(Avg(Fields!CY_Rate.Value),0) & "%",nothin=
g))

It might be easier to add a couple columns (COL_1, COL_2) to your
dataset.  I would use the CASE statement to derive each column.  You
would place all the logic from your IF statement into the CASE
statement.
If true, then COL_1 would return cy_rate.value, else it would return
0.... also, if TRUE, then COL_2 would return 1, else it would return
0.... Then in your report, SUM(COL_1)/SUM(COL_2) should be the average
you are looking for... just add a little logic to avoid dividing by
zero...

HTH,
Mark
0
Mark_Seven
12/15/2009 8:43:48 PM
Reply:

Similar Artilces:

SQL in Excel data
Hi all, Is there a possibility/way to run an SQL query in an excel data sheet? I have quite some data like the sample below, now i would like to have the sum of spending for each person. Like it is possible in Access. A1 B1 Field1 Field2 Chuck 12,89 Mike 23,09 Jean 9,34 Chuck 30,00 Mike 3,80 Chuck 22,00 Mike 7,23 Jean 10,55 Jean 10,75 Jean 31,45 Chuck 19,99 Result Field1 SumOfField2 Chuck 84,88 Jean 62,09 Mike 34,12 Advice would be appriciated. Cheers, Ludovic Hi You could use a formula like this ...

Using iterations to calculate circular references.
I am working with data that requires using circular references in some calculations. I checked the "iterations" box to allow for these calculations to work, and Ecxel seems to be calculating everything correctly. The problem is that after I close the workbook, when I later reopen it to continue working, all the cells that are a part of the circular reference display the #VALUE! error. I can fix this by deleting one cell that is part of the loop and then undoing that command, or by copying the formula from a cell, deleting it, and pasting it back into the same cell. I have to d...

How to get XML data out of an XML file
I am trying to retrieve the Parameters first or second (0, 1 ,2) node from the following XML file: <?xml version="1.0" encoding="utf-8" ?> <Robot xmlns="http://tempuri.org/RobotDefaults.xsd"> <Parameters> <Name>Decker</Name> </Parameters> <Parameters> <Name>A</Name> </Parameters> <Parameters> <Name>B</Name> </Parameters> </Robot> There will be more data than just a name for each Parameters node. Here is my code: Dim node As XmlNode = xmlDocument.SelectSingleNode("/P...

NDR Report
We get this NDR error when someone sends to a particular address: 550 Only 1 recipients accepted with null sender address Again, the emails are sent using Outlook to a listing in our GAL for the Custom Recipient. I have checked the outgoing messages and they do contain a sender address. Any ideas? Thanks. BSchmidt Have you contacted the recipient's admin folk on this? BSchmidt wrote: > We get this NDR error when someone sends to a particular address: > > 550 Only 1 recipients accepted with null sender address > > Again, the emails are sent using Out...

NESTED IF STATEMENTS #2
Having a little problem in an excel class, hoping someone might be able to help. I'm trying to fill in the discount column on a spreadsheet with the following info. The problem states use a nested form of the IF function to calculate 15% if Amount is greater that $300., 10% if Amount is greater than $100., and 0% if Amount is less than $100. The Amount column is cell D5. Anyone know how to write the formula and explain how to go about it? Any help would be much appreciated. Let's see. This is homework so you should come up with the answer yourself. Did you start by looking in the he...

Custom Item Movement Report
Anyone out there have a custom item movement report and item movement history report that includes extended description that they would be willing to share? Thanks! -JIM That was a pretty tricky request but I was able to make the change. Download the reports here: http://www.newestech.com/downloads/itemmovement.zip If you cannot download from there shoort me an email and I will send over. Check out our RMS add-ons website in exchange.. http://www.newestech.com/POS/extensions.htm Casey Hanson New West Technologies chanson@newestech.com Hi, Can you do one with "Transferred To"...

Creating a template using Publisher
I need help creating a neighborhood directory template using Publisher and merging an Excel spreadsheet into it. It keeps printing multiple pages of the first page after I merge. What version Publisher? If you have 2003 use the catalog merge for your directory. http://office.microsoft.com/en-us/assistance/CH010504381033.aspx Otherwise you need to setup your page as though it is labels. Determine the size of the area you need for each entry, select labels in page setup, type the size you determined in the page setup. Adjust the gaps and margins when you are ready to print (2000 and bel...

Microsoft CRM using Firefox
Hello, Would anyone by chance know if CRM will work correctly on Firefox. Currently, I have employees that are using Firefox and are encountering the same issue as IE [Internet Explorer bombs out and states that it has encountered problems...]. I am just having employees use IE to prevent problem. Thank you for your comments in advance. Install a Firefox extension called IETab "Marcos" <Marcos@discussions.microsoft.com> wrote in message news:19B93E29-757D-46FF-B747-A17EB112ED94@microsoft.com... > Hello, > > Would anyone by chance know if CRM will work cor...

Is anybody using Symantec Enterprise Vault?
Hi, A vendor has recommended this solution to us, I have looked at the datasheet for the product and it speaks the right language but I was wondering if anybody on here uses it in a live environment and what their opinion of it is. Cheers Sean ...

iPhone Email to Entourage Using Exchange
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel When I answer an email on my iPhone, it does not register it in Entourage as answered. This function worked fine until the business I work for put everyone on Exchange. Is there a setting for this that I can change? <br><br>Thanks! ...

Z Report shows zeros for an entire shift
How do I correct the problem described above? All receipts are printed correctly, and all transactions seem to be successful. However, the Z report that is run at the end of the shift prints all zeros? Any ideas??? I am having the same problem. All the transactions are successfull and I can see the sale amounts and etc at crstal reports but all the z, zz and x reports print "$0.00" amount. Does anyone know how to solve this problem? "Maakus" wrote: > How do I correct the problem described above? All receipts are printed > correctly, and all transactions seem t...

Using Sumproduct when some of the values are null
I am having a problem using Sumproduct when some of the cells have null values. I will try to describe the problem. Please let me know if you need additional information. Do any of you have any ideas of how I can fix that sumproduct to get it to work? A B C Row Trend Claims 5 Emergency $0 6 Emergency $0 7 Emergency $0 8 Emergency 81.68% $24,444 9 Emergency 35.00% $164,758 10 Emergency 35.00% ...

Unable to repy using word editor
When repyling to an e-mail I receive the following message, "This form requires Word as your e-mail editor, but Word is either busy, or cannot be found. The form will be opened in the Outlook editor instead". I found article 284900, that states you may get this message when you have office 2000 and office xp installed on the same computer. This is not the case on my computer, Office 2000 does not exist, only office xp. Can anyone help me? There appear to be a whole host of causes for this error message. Most have no solution. Here are a few that do: http://support.microsoft...

Operating a small passenger service
I am hoping someone has managed to customise outlook in a way that allows you to use the programe to manage bookings on a small passenger service. I.e input a new booking, number of people, location etc. Then email the days bookings to the different drivers. Maybe it could also be set up to know how many seats are avialale to reduce the risk of double booking? Im using Outlook 2003. ...

Locking cell color while allowing data changes in cell
In excel 2000, I created an attendance worksheet for my classes.(Alphabetized names down left vertical column. Dates across top of horizontal row.) I added a different color to all cells in every other row to make for easier reading of each student's name and absences. Every other row stays with a white background. My question: I wondered if it was possible to lock row colors while allowing data to change on top of them. If a new student is added to my class in alphabetical order, the alternating color pattern is often lost. It is a pain to rechange row and cell colors. Any shortcut ...

How refresh imported data automatically?
In Excel, I've imported data into a worksheet. I know I can click Data.. Refresh Data to requery the source, but I want to be able to do it in a more automated way. Is there some sort of macro I can write when opened or something? Thanks, Ron Ron, Select the cell the data starts in, select Data/Import External Data/Data Range Properties Check the box "Refresh on file open" and anything else that needs checking off... Other than that, you could record a new Macro that selects your cell, refreshes data, and assign the macro to a keystroke, or even an icon. Beege ...

Question About Missing Data
So, this is probably really easy, but I just want to ask and see if I may be missing something here. Some data on vendor numbers changed. Let’s say IBM used to have a vendor number 12345 and now it’s vendor number is 56789. I can identify IBM as IBM, but I really want to use the number, not the name. Should I set up a table that ties the numbers together, so that Access knows 12345 = 56789? Or, should I do some kind of Update Query and change all incidences of 12345 to be 56789? Or, is there some other, method, like a ‘best practices for missing data’? Thanks! Ryan--- ...

Outlook Data Files #4
I've done some reading but I'm still confused about the use of Office Outlook Personal Folders File (.pst) versus Outlook 97-2002 Personal Folders File (.pst). I understand the basic "Office Outlook Personal Folders File (.pst) to create a new Outlook 2003 .pst file. Click Outlook 97-2002 Personal Folders File (.pst) to create a new Outlook .pst file that is compatible with earlier versions of Outlook." When we have upgraded to Outlook 2003 or set up a new PC with Outlook 2003, when adding PST's you can chosse either of the two file type options and browse to chos...

New Named Range Created Each Time Data Imported into Excel via Macro
I have noticed that each time I import data into an Excel spreadsheet via a macro, a new named range (for the same range) is created. This does not pose a problem, but after a while, I'll have a huge number of named ranges that will never be used. Why does Excel name the range and how can I stop this? I noticed in the recorded macro, there was a line .Name = "drd_5". I commented this out to see what would happen, but it just renamed the range "ExternalData_5". The next one was "ExternalData_6", etc. Thanks, Carroll Rinehart You could refresh with differen...

using dates Part 2
Karl was great in helping me get to this point with dates, now I'm wondering if we can take it 1 step further? For Activity Dates prior to 2/1/2007 they are using a normal reporting year and the formulas below take care of Activity dates >2/1/2007? So for example prior to 2/1/2007 1/1/2006 would have a B_Qtr of 2006-1 1) B_Qtr - 2011-1 --- Format(DateAdd("m",11,[ActivityDate]), "yyyy - q") 2) Year - 2011 ---- Year(DateAdd("m",11,[ActivityDate])) 3) Qtr - Q1 ---- Format(DateAdd("m",11,[ActivityDate]), "q") -- Than...

Unicode "private use" glyphs in Powerpoint and Word 2008 #2
Office 12.0.0.1 on Leopard 10.5.2, all updates applied to both. A friend is migrating from Windows to Mac, and has come across something that we're trying to work around. He has some .ppt files with font glyphs in from a Unicode .ttf font (a specific purpose font for showing the LCD display characters on a piece of monitoring equipment). When we bring the font and .ppt over to the Mac, the glyphs from the normal ASCII range come over into Powerpoint 2008 fine, but the glyphs from the "private use" range from F000 upwards a way don't transfer. In Powerpoint they show as spac...

trouble using signature picker
The signature picker is ghosted out does anyone know how to correct this ? Signature picker was working earlier. Thanks ...

XP Look and Feel in Windows 2000 using ComCtrl32.dll
Hai I Want to have a XP Look and feel under windows2000 using VC++ and ComCtrl32.dll and does any one have any idea please help me as basically iam looking to develop a Skin Waiting for your reply suresh You will have to draw many of your own components. You could purchase a library like the one from www.codejock.com or www.dundas.com or or www.bcgsoft.com use something like this: These are for Office XP, but you'll get the idea. It's a ton of work: http://www.codeproject.com/miscctrl/officexp.asp Tom "suresh" <s...

Find what control is using a data item
I built a form, then deleted 2 columns from the source table and now I get a popup asking for the value of those 2 columns. The problem is, I don't use those columns so I need to find what on the form is refferencing the deleted columns. I have looked at the control drop down on the properties window and the tab order window and can't find a control with either one of the missing column names. How do I determine what is trying to refference the deleted columns? I found the problem, the column was still referenced in the underlying query that fed the form. "MeSteve"...

Outlook 2007: is there a way to use it as a Diary
I'd like to keep a daily journal of important notes, etc. In other words , I would like to keep a Diary. Does Outlook 2007 have this capability? TIA Sure - Ctrl+8 to view the journal or you can create a Mail and Post folder type and post to it. -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com/ Outlook Tips by email: mailto:dailytips-subscribe-request@lists.outlooktips.net EMO - a weekly newsletter about Outlook and Exchange: mailto:EMO-NEWSLETTER-SUBSCRIBE-REQUEST@PEACH...