Showing posts with label specific. Show all posts
Showing posts with label specific. Show all posts

Monday, March 26, 2012

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

Friday, March 23, 2012

How to use tableadapter wizard with vendor specific keywords?

Hi. I really like the new tableadapter wizards and how they can speed up development. However, I'm running into a problem I need help with. I am using the table adapter against DB2 and there are special keywords that the designer isn't allowing.

i.e.
Select field1, field2
from table
for fetch only with ur;

When I enter this into the query wizard and then move off the SQL pane to another pane, the wizard quotes the sql so it looks like so:
Select field1, field2
from table
FOR "fetch" "only" "with" ur;

Hence my predicament. When I try to run this or press the ok button to save the query, it won't run because the odbc driver isn't expecting fetch, only, and with to be in double quotes. Is this a known issue or is there some key token to indicate that the keywords should be left alone.

In case anyone is wondering why this would be a problem, in DB2 the keywords above indicate to not use record locking on a table. In an enterprise situation with potentially thousands of users, record locking is not acceptable. In order for our enterprise to use the new enhancements made in VS 2005 to the tableadapter, we need a way around what the designer is doing.

Please advise.To the moderator of this forum:
This post was moved from the C# IDE forum by someone who didn't understand what I was asking them. I've reposted it with clearer meaning (hopefully). Please remove this post from this forum. It doesn't belong here. I would delete it if I could, but I don't seem to have the button to do it anymore.

Thanks,
Jonsql