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

Similar Articles

[PageSpeed] 24

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:

gci and -include and -exclude
I am trying get a limited set of files from a directory tree. The following is what i want but it doesn't work. I get everything. get-childitem -LiteralPath \rm5_0_code_h\source -include *.frm,*.ctl,*.bas,*.cls,*.cs,*.cpp,*.hpp,*.sql,*.java -Recurse I have even tried to exclude what i don't want but it doesn't seem to matter. get-childitem -LiteralPath $path -include *.frm,*.ctl,*.bas,*.cls,*.cs,*.cpp,*.hpp,*.sql,*.java -recurse -exclude *alter*.sql,*.dll,*.pdb,*.ncb,*.tlb,*.scc,*.bmp,*.ico i still get them all. what am i doing wrong? dan you have to a...

Datedif exclude weekends
Hello newsgroup gurus and users. Your kind assistance please. This question has probably been asked a millions times but I cant find it. Does any one have a solution to this. I want the date difference between two dates but exclude weekends. Many thanks Paul Paul, The NETWORKDAYS function will return the number of days, excluding weekend and optionally holidays, between two dates. See help for NETWORKDAYS for more information. This function is part of the Analysis Tool Pack add-in so you must have that add-in loaded; otherwise, you'll get a #NAME error. -- Cordially, Chip Pe...

Using '02 on '00 database
I've upgraded to '02 on my machine, but the office standard is still '00. I know there are some features that are only available on '02, such as new kinds of graphs, that would be wothless to implement because they'd be invisible to everyone else. But for very basic editing of forms, reports, and code, will I be okay making changes using '02. Will it cause problems for '00 or could it possibly have bad repercussions if there end up being '02 changes on top of '00 changes on top of '02 changes and so on...? You are OK if you do not use any 20...

Requesting Data from Old Server Name
We recently switched servers in a company with over 300 workstations. Everyone can get the mail off of the new server. Many outlook clients are requesting data from the old server for some unknown reason. They get that annoying box that pops up in the right corner of the screen. So why are some peoples outlooks requesting data from the old server? how do i fix this? ...

Large Quantity of Data, Graphed in Time Intervals
I have a very large set of data (over 20,000 points) listed in minute intervals. I'd like to be able to create charts using different time intervals such as 5 minutes, 15 minutes etc. by using a pull down menu to select the interval and have the graph make itself. Is this possible? You can use a pivot table to group times by such intervals. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Kirsten" <Kirsten@discussions.microsoft.com> wrote in message news:811D6E3E-C457-4D36-BA47-1C835D6D670D@microsoft.com....

exporting data
Is it possible to export account information to another user of a Money file and preserve all the reconciliation and category information? Use the file->export option and export as Loose QIF. The export file contains the reconciled status and the category. You can then import this into another Money file. -- Glyn Simpson, Microsoft MVP - Money Check http://support.microsoft.com/default.aspx?scid=fh;EN-GB;mny for UK tips and fixes for MS Money. To send Microsoft your wishes or suggestions, use http://register.microsoft.com/mswish/suggestion.asp or email mnyukwsh@microsoft.com espe...

Can't see categories in Budget Planner/Won't load Monthly Budget report
In the last couple of days, Money stopped showing the categories under the budget details. In the "Review your current budget status" screen, under budget details, it usually shows each income and expense category and how I'm doing so far in the month. Now none of that shows. I have made sure to check View all budget items and show expense and income in the view menu, but this does not help. In addition, Money will no longer load the monthly budget report when I click "Other Tasks, view reports, monthly budget." I have this problem no matter what budget I...

Exclude a cell
Excel 2002 Any one know how I can sum all the cell values in a column except the value of the last cell to be entered which could be say half-way down the column? Is there a function to exclude a particular cell from "sum" ? I know a lot of ppl are far more advanced than I am but we all start somewhere lol Thanks for your help Dale To exclude cell A16: =SUM(A:A)-A16 Not very elegant, but effcient! Cheers, -- AP "DF2507" <nospamthx@blueyonder.co.uk> a �crit dans le message de news: xB6sg.51172$181.12831@fe3.news.blueyonder.co.uk... > Excel 2002 > &...

passing some string using postmessage
Hello How can I pass some string as WPARAM of PostMessage to another window ? I can't use CString due to problem of local scope. I can use it by allocating and passing address. But I don't want to do so.Is there any other way to do this using TCHAR or BSTR. >How can I pass some string as WPARAM of PostMessage to another window >? >I can't use CString due to problem of local scope. I can use it by >allocating and passing address. But I don't want to do so. Why not? >Is there any other way to do this using TCHAR or BSTR. There's no magic solution. The only...

Error on new Data Base Query
I am tring to acces an excel Data base Query but the following error mesage appears : "UNEXPECTED ERROR FROM EXTERNAL DATABASE DRIVER (22)" This is after i selected the File to be used, Database Type Excel Files * HELP!!! I would try searching the MS Knowledge Base (www.support.microsoft.com) and/or perhaps repairing or reinstalling Excel. On Wed, 9 Feb 2005 10:05:05 -0800, "MESTRELLA29" <MESTRELLA29@discussions.microsoft.com> wrote: >I am tring to acces an excel Data base Query but the following error mesage >appears : > >"UNEXPECTED ERROR F...

Data sort popup box
I am using the data/sort functionality in Excel 2007. Every time i click on the Sort icon, the pop up box is bigger. If I maek as small as possible, and reopen, it gets bigger each time. Any idea how to keep this as small as possible? ...

Data sort, > 3 columns
Hi Excel has the Data sort option. Data>> Sort This allows you to sort 3 columns Is it possible to sort more than 3 columns?? I would appreciate any advice. Thanks Clive This link explains how to do it: http://www.contextures.com/xlSort01.html#Sort04 Basically, you sort on the least important fields first, and then sort on the more important fields. Alternatively, you can combine some fields together in a helper column. Hope this helps. On Jun 4, 11:04=A0am, Clive_S <clives...@yahoo.co.uk> wrote: > Hi > > Excel has the Data sort option. > > Data>> S...

Report question in Money 98
I am using Money 98 in Windows 2000 and preparing for taxes. I would like to print a "terse" report of donations, printing Category and Sub-category and a subtotal for each payee but not the individual transactions. Can I do that? Thanks In microsoft.public.money, Gualtier Malde wrote: >I am using Money 98 in Windows 2000 and preparing for taxes. I would like to >print a "terse" report of donations, printing Category and Sub-category and a >subtotal for each payee but not the individual transactions. > >Can I do that? Try Customizing...

Assigning software using GPO
I am working in a windows 2003 enviornment I tried to assign adobe acrobat reader which is in MSI format to a OU. I created the GPO and then linked it to the respective OU. When I log in from the client side the software does not get installed eventhough it shows installing software while logging in before the desktop comes (Client side) Please help Thanks in advance -- sahasram ------------------------------------------------------------------------ sahasram's Profile: http://forums.techarena.in/members/161728.htm View this thread: http://forums.techarena.in/ac...

How to gather data from different excel workbooks into 1?
Suppose we have several different workbooks (not shared) containing the same fields on a server location.Each user update his own workbook daily.I need (the controler) to gather the data in another workbook and report the daily updates for all the users in a table,but needs it to be automatic, and showing these results in detail.I have a perfect background but don't know how to connect the points.Please help,thank you. Ron de Bruin has lots of code at: http://www.rondebruin.nl/copy3.htm Maybe you could steal some of it to merge the worksheets into one giant worksheet and process you...

Import Data from the Web to a SpreadSheet
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) I just want to import data from a regular web site onto my spreadsheet. I have downloaded iodbc and no luck there...can anyone help...i have just switched over from a pc and have been using ms excel 07...all i had to do was to click data-from the web and just enter the webaddress...please help im going crazy with this...thanks in advanced... Excel for Mac isn't equipped with the Web Query features provided in the Windows versions. You have to write them as Text Files. This article gives you the 'how-to' but it ...

Using command button to pull up form
I would like to have a command button pull up a form that is linked to the main form. The main form displays milestones and I would like to command button to pull the progress notes form with the record that is linked to the corresponding milestone. The expression that I have tried (which I know is wrong) is: [tblMilestonemthprgntes]![strID]=[tblMlestnes]![Progress Notes] I would like to eliminate the user's need to enter a parameter value to pull the corresponding progress note record. I was going to have a subform inserted on the main form but I was having a hard time linking the in...

How to handle lots of data
Hi people, My problem is I have a graph with lots of data on it, so much that it is hard to spot useful inofrmation in it. Short of simply expanding the graph width, or making multiple graphs of different sections of data, how else can I sort this out? I have found a scroll bar object that one can add to charts that looks promising but can only get it to add more data to the graph, so the graph displays point1, if you move the scroll bar one unit the graph displays point 1 and point 2, then point 1,2,3 and so on. how can you st it up to show a max of say 10 points, such that when you move t...

Pulling data from yyyyMMMMdd.xls
My file names are as follows yyyymmmmdd.xls - 2005August21.xls,2005August22.xls Presently the data (E5:E33) then (H5:H49) is copied then pasted (Value+Transposed) into '2005 Daily Production.xls' August! H25 then AL25 (Row 25 is the row which falls on August22 date line that's why the data is pasted Transposed.) Is there a function that can pull the data based on a entered date into the '2005 Daily Production.xls' worksheet? ...

Problem Changing Data Source In Pivot Table
Hello all. I have a workbook with multiple pivot tables pulling from a odbc datasource. The database has been moved to a new server and I a finding that even when I update the SQL with the new location in an one query, it tells me it cannot graphically represent the SQL an cannot pull the table. I am unsure as to whether or not I am mis-typin something (although not really sure how i could do that) or if i a missing some other place i need to update the link. Also, is there way to do it for all of the pivot tables at once? I have Excel 2000. Thank you!!! An example of the sql as it is (scr...

HR data import
I recently upgraded from Dynamics 7.5 SQL with Human Resources to 8.0 SQL. Apparently, a large swath of HR data did not come across in the upgrade (for example: past review dates, applicants, attendance, time codes, pay codes linked to positions, etc). Our Payroll staff waited several business days to alert IT of this problem (!!!). As such, "Restore from your backup and try again" is not an option for me. Oh, how I wish it were... I have restored the "just before upgrade" company and Dynamics backups to a new location. I can examine tables and see the data in my...

Can't use external POP3
Ok, i'm setting up Exchange2000 on Win2000SBS, the internal mail is all ok, but i don't know at all how to configure external mail. I've been looking everywhere on the net, but ther's nothing about this... I heard of POP3 connector..., but in my exchange doesn't exist... There is STMP connector, but not POP3 connector... Please help me, and if can reply to my address too. Thanx Luca Much better to host your own mail & avoid having to use a POP connector. See http://www.swinc.com/resource/exch_smtp_diatribe.htm For Exchange 2000, see http://www.msexchange...

how do i learn the basics for useing excel 2003 #2
I am using a computer in community collage lab, it has excel 2003 installed I would like to get a very basic idea of how to use it P. S. I have been out of school 17 years would like some head start before entering class next spring. A web site were I could print instructions would be nice –NOTHING ON MICROSOFT OFFICE ONLINE IS PRINTER FRINDLEY TO WORD PROGRAM and there really isn’t any helpful basic info on there site. I’ve tried for other products DON’T SEND ME THERE IT SUCKS ...

Back-Up Data & Delete Data
I am curious, is there a way to back up my current data and then go into RMS Admin, so that I can delete it and start fresh? Would I be able to recover my database once it's been deleted? "Vince" <Vince@discussions.microsoft.com> ha scritto nel messaggio news:7D52BA8C-FF21-4947-94EE-6E9732146984@microsoft.com... >I am curious, is there a way to back up my current data and then go into >RMS > Admin, so that I can delete it and start fresh? Would I be able to > recover > my database once it's been deleted? No... You could backup your current da...

Eliminate the Time from the Date for Pivot Table Use
When I import date and time of day information from another program into Excel the new cell includes both the date and time of day. I would like to eliminate the time portion of the cell and only show the date. Reformatting the cell only changes the view in the cell. The time of day is still maintained in the background. I subsequently am using pivot tables and want all days with the same date to be viewed as the same even though the time of day for that date may vary. When the time is carried along with the date the pivot table thinks each cell is different because the time of day is dif...