how to show summary numbers inside data

Hello, I am very new to Rpt Svcs, and am stuck on a report I have to build.
I have a simple report that shows agent code, state, and zipcode for hte
clients of a given agent. I need to modify this report to show a summary of
how many clients are in each zipcode in each state for each agent.
How do I design my report to allow a first glance to be the agent(s), then
drill down to a state-->count of clients/state rpt, then drill down to number
of clients per zip in that state, then drill down to show the full data for
that zip?
is this a code issue or can rpt svc group my results and display the data
the way I want it?
thank you in advance!!No and yes.
Sort the data by agent, zipcode and client.
Add group for agent and for zipcode.
In the footer of each group, add a field to capture count. For the
expression:
=Count(Fields!ClientId.Value) or whatever uniquely identifies the client in
the result set.
After that, get some beer and sandwiches and do what I do, chase your golf
balls around in the woods all day.
-Tim
"Carl Henthorn" <CarlHenthorn@.discussions.microsoft.com> wrote in message
news:E53C91FB-FC7F-4B29-B91F-5ACB6C4E177C@.microsoft.com...
> Hello, I am very new to Rpt Svcs, and am stuck on a report I have to
> build.
> I have a simple report that shows agent code, state, and zipcode for hte
> clients of a given agent. I need to modify this report to show a summary
> of
> how many clients are in each zipcode in each state for each agent.
> How do I design my report to allow a first glance to be the agent(s), then
> drill down to a state-->count of clients/state rpt, then drill down to
> number
> of clients per zip in that state, then drill down to show the full data
> for
> that zip?
> is this a code issue or can rpt svc group my results and display the data
> the way I want it?
> thank you in advance!!|||Hey pretty simple, can be done using toggle visibility.. If you want some
samples go to adventureWorks Sample and go to "Territory Sales Drilldown"
after seeing that I think you can build your reports.
Amarnath
"Tim Dot NoSpam" wrote:
> No and yes.
> Sort the data by agent, zipcode and client.
> Add group for agent and for zipcode.
> In the footer of each group, add a field to capture count. For the
> expression:
> =Count(Fields!ClientId.Value) or whatever uniquely identifies the client in
> the result set.
> After that, get some beer and sandwiches and do what I do, chase your golf
> balls around in the woods all day.
> -Tim
> "Carl Henthorn" <CarlHenthorn@.discussions.microsoft.com> wrote in message
> news:E53C91FB-FC7F-4B29-B91F-5ACB6C4E177C@.microsoft.com...
> > Hello, I am very new to Rpt Svcs, and am stuck on a report I have to
> > build.
> > I have a simple report that shows agent code, state, and zipcode for hte
> > clients of a given agent. I need to modify this report to show a summary
> > of
> > how many clients are in each zipcode in each state for each agent.
> > How do I design my report to allow a first glance to be the agent(s), then
> > drill down to a state-->count of clients/state rpt, then drill down to
> > number
> > of clients per zip in that state, then drill down to show the full data
> > for
> > that zip?
> > is this a code issue or can rpt svc group my results and display the data
> > the way I want it?
> > thank you in advance!!
>
>|||Rule Number 1. Never respond to peoples' requests for assistance after
beers. Actually, I still think my advice was sound...
"Amarnath" <Amarnath@.discussions.microsoft.com> wrote in message
news:FCE0E20C-2D62-4277-BBBC-FD1437165F8C@.microsoft.com...
> Hey pretty simple, can be done using toggle visibility.. If you want some
> samples go to adventureWorks Sample and go to "Territory Sales Drilldown"
> after seeing that I think you can build your reports.
> Amarnath
>
> "Tim Dot NoSpam" wrote:
>> No and yes.
>> Sort the data by agent, zipcode and client.
>> Add group for agent and for zipcode.
>> In the footer of each group, add a field to capture count. For the
>> expression:
>> =Count(Fields!ClientId.Value) or whatever uniquely identifies the client
>> in
>> the result set.
>> After that, get some beer and sandwiches and do what I do, chase your
>> golf
>> balls around in the woods all day.
>> -Tim
>> "Carl Henthorn" <CarlHenthorn@.discussions.microsoft.com> wrote in message
>> news:E53C91FB-FC7F-4B29-B91F-5ACB6C4E177C@.microsoft.com...
>> > Hello, I am very new to Rpt Svcs, and am stuck on a report I have to
>> > build.
>> > I have a simple report that shows agent code, state, and zipcode for
>> > hte
>> > clients of a given agent. I need to modify this report to show a
>> > summary
>> > of
>> > how many clients are in each zipcode in each state for each agent.
>> > How do I design my report to allow a first glance to be the agent(s),
>> > then
>> > drill down to a state-->count of clients/state rpt, then drill down to
>> > number
>> > of clients per zip in that state, then drill down to show the full data
>> > for
>> > that zip?
>> > is this a code issue or can rpt svc group my results and display the
>> > data
>> > the way I want it?
>> > thank you in advance!!
>>

How to show line numbers in the first column of the report

Hi,
I want to show line numbers on report page in the first column of my
report. I am working on microsoft reporting services 2000.
How i can do this.
Regards
DineshOn Mar 9, 11:59 pm, "Dinesh" <dinesh...@.gmail.com> wrote:
> Hi,
> I want to show line numbers on report page in the first column of my
> report. I am working on microsoft reporting services 2000.
> How i can do this.
> Regards
> Dinesh
This should work: =RowNumber("DataSourceName") as an expression in the
Value property of the first column.
Regards,
Enrique Martinez
Sr. SQL Server Developer

How to sequentially SORT numbers that also have a letter attached?

Hi
I'm hoping this will be straightforward for you guys to point me in the
right direction.
Friend of mine emailed me asking the following:
We have a table with a house plot number on it, which is varchar(5).
Example values are:
1
1a
1b
1c
2
2a
2b
2c
3
3a
3b
3c
11a
21c
When we sort these they come out as follows:
1
1a
1b
1c
11a
2
2a
2b
2c
21c
3
3a
3b
3c
Is there an easy way to get them to sort as per the first example? I.e.
1 first, then 1a, 1b, 1c followed by 2, 2a etc and not 11a?
I thought it'd be simple but not so! Would appreciate your advice on
the simplest way of achieving this please.Try:
declare @.t table
(
x varchar (5) primary key
, y as case when patindex ('%[a-z]', x) = 0 then ''
else right (x, len (x) - patindex ('%[a-z]', x) + 1) end
, z as case when patindex ('%[a-z]', x) = 0 then cast (x as int)
else cast (left (x, patindex ('%[a-z]', x) - 1) as int) end
)
insert @.t values ('1')
insert @.t values ('1a')
insert @.t values ('1b')
insert @.t values ('1c')
insert @.t values ('2')
insert @.t values ('2a')
insert @.t values ('2b')
insert @.t values ('2c')
insert @.t values ('3')
insert @.t values ('3a')
insert @.t values ('3b')
insert @.t values ('3c')
insert @.t values ('11a')
insert @.t values ('21c')
select x from @.t
order by z, y
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"ALI" <kismet110@.yahoo.co.uk> wrote in message
news:1150111751.001833.272460@.f14g2000cwb.googlegroups.com...
Hi
I'm hoping this will be straightforward for you guys to point me in the
right direction.
Friend of mine emailed me asking the following:
We have a table with a house plot number on it, which is varchar(5).
Example values are:
1
1a
1b
1c
2
2a
2b
2c
3
3a
3b
3c
11a
21c
When we sort these they come out as follows:
1
1a
1b
1c
11a
2
2a
2b
2c
21c
3
3a
3b
3c
Is there an easy way to get them to sort as per the first example? I.e.
1 first, then 1a, 1b, 1c followed by 2, 2a etc and not 11a?
I thought it'd be simple but not so! Would appreciate your advice on
the simplest way of achieving this please.|||Thanks for your help Tom, hopefully this will get them what they want,
I don't feel totally stupid now too for thinking it would be _really_
simple!
Tom Moreau wrote:
> Try:
> declare @.t table
> (
> x varchar (5) primary key
> , y as case when patindex ('%[a-z]', x) = 0 then ''
> else right (x, len (x) - patindex ('%[a-z]', x) + 1) end
> , z as case when patindex ('%[a-z]', x) = 0 then cast (x as int)
> else cast (left (x, patindex ('%[a-z]', x) - 1) as int) end
> )
> insert @.t values ('1')
> insert @.t values ('1a')
> insert @.t values ('1b')
> insert @.t values ('1c')
> insert @.t values ('2')
> insert @.t values ('2a')
> insert @.t values ('2b')
> insert @.t values ('2c')
> insert @.t values ('3')
> insert @.t values ('3a')
> insert @.t values ('3b')
> insert @.t values ('3c')
> insert @.t values ('11a')
> insert @.t values ('21c')
> select x from @.t
> order by z, y
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "ALI" <kismet110@.yahoo.co.uk> wrote in message
> news:1150111751.001833.272460@.f14g2000cwb.googlegroups.com...
> Hi
> I'm hoping this will be straightforward for you guys to point me in the
> right direction.
> Friend of mine emailed me asking the following:
> We have a table with a house plot number on it, which is varchar(5).
> Example values are:
> 1
> 1a
> 1b
> 1c
> 2
> 2a
> 2b
> 2c
> 3
> 3a
> 3b
> 3c
> 11a
> 21c
> When we sort these they come out as follows:
> 1
> 1a
> 1b
> 1c
> 11a
> 2
> 2a
> 2b
> 2c
> 21c
> 3
> 3a
> 3b
> 3c
> Is there an easy way to get them to sort as per the first example? I.e.
> 1 first, then 1a, 1b, 1c followed by 2, 2a etc and not 11a?
> I thought it'd be simple but not so! Would appreciate your advice on
> the simplest way of achieving this please.

How to sequentially SORT numbers that also have a letter attached?

Try:
declare @.t table
(
x varchar (5) primary key
, y as case when patindex ('%[a-z]', x) = 0 then ''
else right (x, len (x) - patindex ('%[a-z]', x) + 1) end
, z as case when patindex ('%[a-z]', x) = 0 then cast (x as int)
else cast (left (x, patindex ('%[a-z]', x) - 1) as int) end
)
insert @.t values ('1')
insert @.t values ('1a')
insert @.t values ('1b')
insert @.t values ('1c')
insert @.t values ('2')
insert @.t values ('2a')
insert @.t values ('2b')
insert @.t values ('2c')
insert @.t values ('3')
insert @.t values ('3a')
insert @.t values ('3b')
insert @.t values ('3c')
insert @.t values ('11a')
insert @.t values ('21c')
select x from @.t
order by z, y
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"ALI" <kismet110@.yahoo.co.uk> wrote in message
news:1150111751.001833.272460@.f14g2000cwb.googlegroups.com...
Hi
I'm hoping this will be straightforward for you guys to point me in the
right direction.
Friend of mine emailed me asking the following:
We have a table with a house plot number on it, which is varchar(5).
Example values are:
1
1a
1b
1c
2
2a
2b
2c
3
3a
3b
3c
11a
21c
When we sort these they come out as follows:
1
1a
1b
1c
11a
2
2a
2b
2c
21c
3
3a
3b
3c
Is there an easy way to get them to sort as per the first example? I.e.
1 first, then 1a, 1b, 1c followed by 2, 2a etc and not 11a?
I thought it'd be simple but not so! Would appreciate your advice on
the simplest way of achieving this please.Hi
I'm hoping this will be straightforward for you guys to point me in the
right direction.
Friend of mine emailed me asking the following:
We have a table with a house plot number on it, which is varchar(5).
Example values are:
1
1a
1b
1c
2
2a
2b
2c
3
3a
3b
3c
11a
21c
When we sort these they come out as follows:
1
1a
1b
1c
11a
2
2a
2b
2c
21c
3
3a
3b
3c
Is there an easy way to get them to sort as per the first example? I.e.
1 first, then 1a, 1b, 1c followed by 2, 2a etc and not 11a?
I thought it'd be simple but not so! Would appreciate your advice on
the simplest way of achieving this please.|||Try:
declare @.t table
(
x varchar (5) primary key
, y as case when patindex ('%[a-z]', x) = 0 then ''
else right (x, len (x) - patindex ('%[a-z]', x) + 1) end
, z as case when patindex ('%[a-z]', x) = 0 then cast (x as int)
else cast (left (x, patindex ('%[a-z]', x) - 1) as int) end
)
insert @.t values ('1')
insert @.t values ('1a')
insert @.t values ('1b')
insert @.t values ('1c')
insert @.t values ('2')
insert @.t values ('2a')
insert @.t values ('2b')
insert @.t values ('2c')
insert @.t values ('3')
insert @.t values ('3a')
insert @.t values ('3b')
insert @.t values ('3c')
insert @.t values ('11a')
insert @.t values ('21c')
select x from @.t
order by z, y
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"ALI" <kismet110@.yahoo.co.uk> wrote in message
news:1150111751.001833.272460@.f14g2000cwb.googlegroups.com...
Hi
I'm hoping this will be straightforward for you guys to point me in the
right direction.
Friend of mine emailed me asking the following:
We have a table with a house plot number on it, which is varchar(5).
Example values are:
1
1a
1b
1c
2
2a
2b
2c
3
3a
3b
3c
11a
21c
When we sort these they come out as follows:
1
1a
1b
1c
11a
2
2a
2b
2c
21c
3
3a
3b
3c
Is there an easy way to get them to sort as per the first example? I.e.
1 first, then 1a, 1b, 1c followed by 2, 2a etc and not 11a?
I thought it'd be simple but not so! Would appreciate your advice on
the simplest way of achieving this please.|||Thanks for your help Tom, hopefully this will get them what they want,
I don't feel totally stupid now too for thinking it would be _really_
simple!
Tom Moreau wrote:
> Try:
> declare @.t table
> (
> x varchar (5) primary key
> , y as case when patindex ('%[a-z]', x) = 0 then ''
> else right (x, len (x) - patindex ('%[a-z]', x) + 1) end
> , z as case when patindex ('%[a-z]', x) = 0 then cast (x as int)
> else cast (left (x, patindex ('%[a-z]', x) - 1) as int) end
> )
> insert @.t values ('1')
> insert @.t values ('1a')
> insert @.t values ('1b')
> insert @.t values ('1c')
> insert @.t values ('2')
> insert @.t values ('2a')
> insert @.t values ('2b')
> insert @.t values ('2c')
> insert @.t values ('3')
> insert @.t values ('3a')
> insert @.t values ('3b')
> insert @.t values ('3c')
> insert @.t values ('11a')
> insert @.t values ('21c')
> select x from @.t
> order by z, y
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "ALI" <kismet110@.yahoo.co.uk> wrote in message
> news:1150111751.001833.272460@.f14g2000cwb.googlegroups.com...
> Hi
> I'm hoping this will be straightforward for you guys to point me in the
> right direction.
> Friend of mine emailed me asking the following:
> We have a table with a house plot number on it, which is varchar(5).
> Example values are:
> 1
> 1a
> 1b
> 1c
> 2
> 2a
> 2b
> 2c
> 3
> 3a
> 3b
> 3c
> 11a
> 21c
> When we sort these they come out as follows:
> 1
> 1a
> 1b
> 1c
> 11a
> 2
> 2a
> 2b
> 2c
> 21c
> 3
> 3a
> 3b
> 3c
> Is there an easy way to get them to sort as per the first example? I.e.
> 1 first, then 1a, 1b, 1c followed by 2, 2a etc and not 11a?
> I thought it'd be simple but not so! Would appreciate your advice on
> the simplest way of achieving this please.|||Thanks for your help Tom, hopefully this will get them what they want,
I don't feel totally stupid now too for thinking it would be _really_
simple!
Tom Moreau wrote:
> Try:
> declare @.t table
> (
> x varchar (5) primary key
> , y as case when patindex ('%[a-z]', x) = 0 then ''
> else right (x, len (x) - patindex ('%[a-z]', x) + 1) end
> , z as case when patindex ('%[a-z]', x) = 0 then cast (x as int)
> else cast (left (x, patindex ('%[a-z]', x) - 1) as int) end
> )
> insert @.t values ('1')
> insert @.t values ('1a')
> insert @.t values ('1b')
> insert @.t values ('1c')
> insert @.t values ('2')
> insert @.t values ('2a')
> insert @.t values ('2b')
> insert @.t values ('2c')
> insert @.t values ('3')
> insert @.t values ('3a')
> insert @.t values ('3b')
> insert @.t values ('3c')
> insert @.t values ('11a')
> insert @.t values ('21c')
> select x from @.t
> order by z, y
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "ALI" <kismet110@.yahoo.co.uk> wrote in message
> news:1150111751.001833.272460@.f14g2000cwb.googlegroups.com...
> Hi
> I'm hoping this will be straightforward for you guys to point me in the
> right direction.
> Friend of mine emailed me asking the following:
> We have a table with a house plot number on it, which is varchar(5).
> Example values are:
> 1
> 1a
> 1b
> 1c
> 2
> 2a
> 2b
> 2c
> 3
> 3a
> 3b
> 3c
> 11a
> 21c
> When we sort these they come out as follows:
> 1
> 1a
> 1b
> 1c
> 11a
> 2
> 2a
> 2b
> 2c
> 21c
> 3
> 3a
> 3b
> 3c
> Is there an easy way to get them to sort as per the first example? I.e.
> 1 first, then 1a, 1b, 1c followed by 2, 2a etc and not 11a?
> I thought it'd be simple but not so! Would appreciate your advice on
> the simplest way of achieving this please.