How to use the sum to add specific data from the table in ssrs 2000?

Hi All,

I am new to this forum and had checked this forum for this problem that I am having. Although there were a lot of informative articles that I found useful, I still couldn't resolve this problem.

I am having a little trouble trying to use the Sum function to get the totals that I need for a project that I am trying to accomplish. The scenario is below:-

This is the report image that I want to produce.

Region1 policy1 policy2
AgentType1 Total Total
Agenttype2 Total Total
AgentType3 Total Total
Region1's policy1 Total Region1 Policy2 Total

Region2 policy1 policy2
AgentType1 Total Total
Agenttype2 Total Total
AgentType3 Total Total
Region2's policy1 Total Region2 Policy2 Total

(this is where i am having the problem)
AgentTotals
AllAgentType1Totals Policy1Totals Policy2Totals
AllAgentType2Totals Policy1Totals Policy2Totals

TotalforallRegions RegionsTotals

I managed to get all the information I needed in the
Report except for Agent Totals. I am having a little
bit of a logic problem on how to get the totals for a
particular agent type with each policy.

Right now my layout only has ONE grouping which holds
the region types. my dataset basically is how the
report is going to look like without the totals.

Is there an expression I can use that would identify
what type of agent that is and then add the total ?

kinda like the following:-

=IIF(agenttype = "AgentType1", Sum(all agent totals
for policy 1), "")

Am I heading in the right direction ?

Any tips or hints would be greatly appreciated.

Sincerely,
Bernard Ong

After much searching and deciphering, I finally figure out how to do this.

The expression I had used was slightly wrong, though it was in the right direction.

The correct Expression after looking through Heidi's post, is the following:-

=Sum(IIF(Fields!agenttype.value ="AgentType1",Fields!AgentType1.Total.Value, Cint(0)))

This worked perfectly thanks to the people who emailed me the specific link.

the link that helped me out was this

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=930085&SiteID=1

Hope you find this useful !

Sincerely,
Bernard

sql