Agregate Issue - 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)

Agregate Issue

Author

Topic

dewacorp.alliances

452 Posts

Posted - 2008-05-04 : 18:56:33

Hi there

I am trying to figure out this query below to displaying in the RS:

ORDERBY, SECTIONAREA, RATE, VALUE
1, AREA1, 'Rate1', $10
2, AREA1, 'Rate2', $11
3, AREA2, 'Rate3', $20
4, AREA2, 'Rate4', $21

I've managed to display into RS as follow by putting grouping for SECTIONAREA field and SUM the VALUE field and turns out ok:

AREA1
Rate1       | $10
Rate2       | $11
Total Area1 | $21

AREA2
Rate3       | $20
Rate4       | $21
Total Area2 | $41

Now, I want to add extra lines which are basically TAX and TOTAL COST so it will be like this:

AREA1
Rate1       | $10
Rate2       | $11
Total Area1 | $21

AREA2
Rate3       | $20
Rate4       | $21
Total Area2 | $41

TAX         | (Total Area1 + Total Area2) * 0.1

TOTAL COST  | (Total Area1 + Total Area2) * 1.1

Any ideas? I am not sure how to add a new line for handling TAX and TOTAL COST. I can probabaly put every detail as a data set in RS or built another data set and stick in but that but that's defeat the purpose ... I guess.

I am appreciated your commment.

Thank you

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-05-04 : 20:44:46

I assume you are using reporting services? Just create a footer, and put your totals and the calculations there.

- Jeff
http://weblogs.sqlteam.com/JeffS

dewacorp.alliances

452 Posts

Posted - 2008-05-04 : 22:32:34

quote:
I assume you are using reporting services? Just create a footer, and put your totals and the calculations there.

Don't realise that you can put =FormatCurrency((SUM(Fields!TotalCost.Value) * 1.1), 2) of the agregrate area in the footer section. :)

Thanks a lot.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources