trouble with info - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Analysis Server and Reporting Services (2005)

trouble with info

Author

Topic

gavakie
Posting Yak Master

221 Posts

Posted - 2007-10-23 : 17:39:06

I dont know if this is the right place to post this lets hope. I need to get the total of sales for say marketed.

im trying a script like this but its not doing it right

IIF( Fields!Type.Value = "Non Promotional" then Fields!TotalSales.Value = 0 else Fields!TotalSales.Value)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-10-23 : 17:40:20

So is this a Reporting Services question?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2007-10-23 : 17:41:59

This appears to be Access correct? Are you getting an error or is it just not working right?

gavakie
Posting Yak Master

221 Posts

Posted - 2007-10-23 : 17:44:16

Its just not working right. Its not access it is Visual Studios.
The info my sql gets out is

type totalsales
211 Cross Promotions NCL Tote Promo 2.0000
211 Advertisements Exit Zero Ad 2.0000
211 Cross Promotions Hotel Promo 556.0000
211 Non Promotional 559662.6300

I need to get the total sales of things that arent Non Promotional in my type column.

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2007-10-23 : 17:45:58

Don't think I'll be much help there. Sorry.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-10-23 : 17:48:56

You say it's visual studio, but you don't mention which product of VS it is. Is this Reporting Services, Visual Basic, ...? Is this for a report?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

gavakie
Posting Yak Master

221 Posts

Posted - 2007-10-23 : 17:52:21

Sorry reporting services

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-10-23 : 18:02:57

I'm moving this thread to the RS forum then.

I would think you'd just do what you want in the grouping though.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Will H
Yak Posting Veteran

56 Posts

Posted - 2007-10-24 : 11:32:38

Hi Gavakie, I don't think the syntax for your iif() block is correct. You are treating it like a case statement when its more like a function. Ie, change your then/else to commas:

=IIF( <Boolean Expression> , <TruePart> , <FalsePart> )

=IIF( Fields!Type.Value = "Non Promotional" , Fields!TotalSales.Value = 0 , Fields!TotalSales.Value)

---------------------------------------------------------
SSRS Kills Kittens.

Will H
Yak Posting Veteran

56 Posts

Posted - 2007-10-24 : 11:35:02

Oops, I think I messed up too. Don't "assign" Fields!TotalSales.Value a value ( as in Fields!TotalSales.Value = 0 ) Instead, just tell it what to print.

=IIF( Fields!Type.Value = "Non Promotional" , 0 , Fields!TotalSales.Value)

---------------------------------------------------------
SSRS Kills Kittens.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources