Another Handling the View - 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)

Another Handling the View

Author

Topic

dewacorp.alliances

452 Posts

Posted - 2008-05-07 : 13:56:48

Hi there

I got the following returned simple dataset as follow:


ReportViewType; Category; Name; Units; Price;
LIST_VIEW;  FRUITS; Apple; 1; $10
LIST_VIEW; FRUITS; Banana; 2; $11
LIST_VIEW; CARS; Corolla; 8; $100
LIST_VIEW; CARS; BMW; 10; $200

I've managed to grouped by Category field nicely and added extra calculated column for handling to total plus as well as Total for each group plus added a new row for TOTAL in the TABLE and returned as follow:


FRUITS
Apple; 1; $10; $10
Banana; 2; $11; $22
TOTAL FRUITS; $32

CARS
Corolla; 8; $100; $800
BMW; 10; $200; $2000
TOTAL CARS: $3000

TOTAL: $3032

Now I want to put another extra row underneath TOTAL for handling the calculated but the business rule is only for displaying from the first group for instance:


TOTAL: $3032
Calculated Average per fruits: $1010.67 ($3032 / 3 UNITS).

3 UNITS is coming from Units for Apple and Banana.

I don't know if I can do this cause i did try to use


=SUM(IIF(Fields!Category.value = "FRUITS", Fields!Units.Value, 0))

But it's successfully compiled but the report result error (#Error) on that field only. Any ideas?

I thought a simple like this can be handled quite easily instead of creating a new report view type in the the same dataset to handle this particular extra line.

Waiting your comment/feedback.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-07 : 15:04:48

Use like this:-
=SUM(IIF(Fields!Category.value = "FRUITS", 1, 0))

dewacorp.alliances

452 Posts

Posted - 2008-05-07 : 15:10:16

quote:
Originally posted by visakh16

Use like this:-
=SUM(IIF(Fields!Category.value = "FRUITS", 1, 0))

Hi Visakh16

I don't quite understand why it is 1 for the true condition in this case ?!?!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-08 : 05:19:56

quote:
Originally posted by dewacorp.alliances

quote:
Originally posted by visakh16

Use like this:-
=SUM(IIF(Fields!Category.value = "FRUITS", 1, 0))

Hi Visakh16

I don't quite understand why it is 1 for the true condition in this case ?!?!

Ah sorry i misread the question. Your expression looks fine. Are you sure you got error for this?

dewacorp.alliances

452 Posts

Posted - 2008-05-08 : 15:33:28

quote:
Ah sorry i misread the question. Your expression looks fine. Are you sure you got error for this?

Yes. With RS, can we debugged this?

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources