Create a Sequential Line Count for Group...

I have a report that groups by item number but then is filtered by a
parameter to only show the top # the user has entered. Does anyone
know if it is possible to number the results I return so that the user
can see how that particular record ranks with other records? Basically
I want to be able to have the user see 1 for the top item sold, 2 for
the second most items sold, etc...Try using
=Rownumber(Groupname)
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Brent" <Brent.Raymond@.gmail.com> wrote in message
news:1126532242.793502.287600@.o13g2000cwo.googlegroups.com...
>I have a report that groups by item number but then is filtered by a
> parameter to only show the top # the user has entered. Does anyone
> know if it is possible to number the results I return so that the user
> can see how that particular record ranks with other records? Basically
> I want to be able to have the user see 1 for the top item sold, 2 for
> the second most items sold, etc...
>

create a page index for a long crystal report

I have to create a page index for a long crystal report which is grouped by product group. Is there any way to create a page index using crystal report to identify the page number in which the product group is appear in the reportHi,

I think you can create Table of contents for the report in the Report header. I haven't worked on this as it requires CR 10... Im working on CR 8.x.. You can refer this article, which explains how to do it.

http://support.businessobjects.com/library/kbase/articles/c2011950.asp

Let me know if it works.

Bye,

Praveen|||Hi,

yes it can possible in CR 9 and CR 10 version,but we are using CR 8.x
is it possible in CR 8.x

thanks

sujesh

Cr9.0

in my pageheader , got country and cityname.
below it, is a section that has district as a group.
below it, is detail section with game field in it.
query:
select country, CityName, district,
case when (population > 200) then game else null end
from country, province, city
group CityName, district
my problem is how to suppress the page header when game is null.You can conditionaly suppress your Page Header (or a PH section).
In the Section Expert, check the Suppress option for your PageHeader, click the 'x+2' button and print such a formula:

Count({table.game},{table.district})=0

CPU Use Rising

Hello Group,
what is a good starting point setting a trace to troubleshoot CPU usage? My
network admin asked me to look at one of the servers: it seems after our
weekly restarts, the CPU usage keeps rising. What can I begin to look at to
determine what is causing this?Rich,
Start with Task Manager to determine what app is causing the issue. If SQL
Server, then drill down using System Monitor and Profiler.
HTH
Jerry
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:B55F66D8-C41D-4C04-9BC6-87F176891311@.microsoft.com...
> Hello Group,
> what is a good starting point setting a trace to troubleshoot CPU usage?
> My
> network admin asked me to look at one of the servers: it seems after our
> weekly restarts, the CPU usage keeps rising. What can I begin to look at
> to
> determine what is causing this?

CPU Use Rising

Hello Group,
what is a good starting point setting a trace to troubleshoot CPU usage? My
network admin asked me to look at one of the servers: it seems after our
weekly restarts, the CPU usage keeps rising. What can I begin to look at to
determine what is causing this?
Rich,
Start with Task Manager to determine what app is causing the issue. If SQL
Server, then drill down using System Monitor and Profiler.
HTH
Jerry
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:B55F66D8-C41D-4C04-9BC6-87F176891311@.microsoft.com...
> Hello Group,
> what is a good starting point setting a trace to troubleshoot CPU usage?
> My
> network admin asked me to look at one of the servers: it seems after our
> weekly restarts, the CPU usage keeps rising. What can I begin to look at
> to
> determine what is causing this?

CPU Use Rising

Hello Group,
what is a good starting point setting a trace to troubleshoot CPU usage? My
network admin asked me to look at one of the servers: it seems after our
weekly restarts, the CPU usage keeps rising. What can I begin to look at to
determine what is causing this?Rich,
Start with Task Manager to determine what app is causing the issue. If SQL
Server, then drill down using System Monitor and Profiler.
HTH
Jerry
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:B55F66D8-C41D-4C04-9BC6-87F176891311@.microsoft.com...
> Hello Group,
> what is a good starting point setting a trace to troubleshoot CPU usage?
> My
> network admin asked me to look at one of the servers: it seems after our
> weekly restarts, the CPU usage keeps rising. What can I begin to look at
> to
> determine what is causing this?

CPU Usage

What kinds of operations on a db server cause heavy cpu usage as opposed to IO?
Could sorts or group bys use cpu heavily?
tia
MG
On Thu, 21 Feb 2008 09:29:01 -0800, Hurme <michael.geles@.thomson.com>
wrote:

>What kinds of operations on a db server cause heavy cpu usage as opposed to IO?
>Could sorts or group bys use cpu heavily?
>tia
An operation on a lot of rows, where the rows are already in memory
will be CPU intensive. GROUP BY and ORDER BY can fall into that
category.
Roy Harvey
Beacon Falls, CT
|||Almost any SQL operation can cause heavy cpu usage as long as the operation
is applied to data already in cache. So in addition to what Roy mentioned,
your simple SELECT or any of the aggregate functions usch as COUNT and SUM
can all drive up cpu usage, if they are run against a small set of data and
are run repeatedly. 'Small' in this case is relative to the SQL instance
buffer pool size.
Linchi
"Hurme" wrote:

> What kinds of operations on a db server cause heavy cpu usage as opposed to IO?
> Could sorts or group bys use cpu heavily?
> tia
> --
> MG
|||Optimization is one area. Certain kinds of data conversion and comparisons
can also drive CPU usage. SQL will try and trade CPU and memory usage to
drive down I/O requirements as a way to speed up the overall system.
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"Hurme" <michael.geles@.thomson.com> wrote in message
news:0394DA83-9905-4B54-8A52-4BC73EDAE326@.microsoft.com...
> What kinds of operations on a db server cause heavy cpu usage as opposed
> to IO?
> Could sorts or group bys use cpu heavily?
> tia
> --
> MG

CPU Usage

What kinds of operations on a db server cause heavy cpu usage as opposed to IO?
Could sorts or group bys use cpu heavily?
tia
--
MGOn Thu, 21 Feb 2008 09:29:01 -0800, Hurme <michael.geles@.thomson.com>
wrote:
>What kinds of operations on a db server cause heavy cpu usage as opposed to IO?
>Could sorts or group bys use cpu heavily?
>tia
An operation on a lot of rows, where the rows are already in memory
will be CPU intensive. GROUP BY and ORDER BY can fall into that
category.
Roy Harvey
Beacon Falls, CT|||Almost any SQL operation can cause heavy cpu usage as long as the operation
is applied to data already in cache. So in addition to what Roy mentioned,
your simple SELECT or any of the aggregate functions usch as COUNT and SUM
can all drive up cpu usage, if they are run against a small set of data and
are run repeatedly. 'Small' in this case is relative to the SQL instance
buffer pool size.
Linchi
"Hurme" wrote:
> What kinds of operations on a db server cause heavy cpu usage as opposed to IO?
> Could sorts or group bys use cpu heavily?
> tia
> --
> MG|||Optimization is one area. Certain kinds of data conversion and comparisons
can also drive CPU usage. SQL will try and trade CPU and memory usage to
drive down I/O requirements as a way to speed up the overall system.
--
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"Hurme" <michael.geles@.thomson.com> wrote in message
news:0394DA83-9905-4B54-8A52-4BC73EDAE326@.microsoft.com...
> What kinds of operations on a db server cause heavy cpu usage as opposed
> to IO?
> Could sorts or group bys use cpu heavily?
> tia
> --
> MG

Cpu count in the Profiler and Task Manager

Hello Group,
does anyone know if there is a relationship between the CPU count that is
displayed in the SQL Profiler and the Task Manager? I have a trace running
to show when a long insert takes places and the CPU column displays "16".
The same moment I have the Task Manager open and I am looking at the
SQLSERV.exe it displayes "40".
I thought that the numbers would be the same.
Rich
Rich,
I think you're comparing CPU usage numbers for an individual SPID versus the
entire SQL Server application.
HTH
Jerry
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:DEF40479-CBE6-4F6D-A668-641BFEEE8586@.microsoft.com...
> Hello Group,
> does anyone know if there is a relationship between the CPU count that is
> displayed in the SQL Profiler and the Task Manager? I have a trace
> running
> to show when a long insert takes places and the CPU column displays "16".
> The same moment I have the Task Manager open and I am looking at the
> SQLSERV.exe it displayes "40".
> I thought that the numbers would be the same.
> Rich
|||Hello Jerry,
hmmmm, if that is the case, seems like I could add up all of the SPID and it
would equal the total?
Rich
"Jerry Spivey" wrote:

> Rich,
> I think you're comparing CPU usage numbers for an individual SPID versus the
> entire SQL Server application.
> HTH
> Jerry
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:DEF40479-CBE6-4F6D-A668-641BFEEE8586@.microsoft.com...
>
>

Cpu count in the Profiler and Task Manager

Hello Group,
does anyone know if there is a relationship between the CPU count that is
displayed in the SQL Profiler and the Task Manager? I have a trace running
to show when a long insert takes places and the CPU column displays "16".
The same moment I have the Task Manager open and I am looking at the
SQLSERV.exe it displayes "40".
I thought that the numbers would be the same.
RichRich,
I think you're comparing CPU usage numbers for an individual SPID versus the
entire SQL Server application.
HTH
Jerry
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:DEF40479-CBE6-4F6D-A668-641BFEEE8586@.microsoft.com...
> Hello Group,
> does anyone know if there is a relationship between the CPU count that is
> displayed in the SQL Profiler and the Task Manager? I have a trace
> running
> to show when a long insert takes places and the CPU column displays "16".
> The same moment I have the Task Manager open and I am looking at the
> SQLSERV.exe it displayes "40".
> I thought that the numbers would be the same.
> Rich|||Hello Jerry,
hmmmm, if that is the case, seems like I could add up all of the SPID and it
would equal the total?
Rich
"Jerry Spivey" wrote:

> Rich,
> I think you're comparing CPU usage numbers for an individual SPID versus t
he
> entire SQL Server application.
> HTH
> Jerry
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:DEF40479-CBE6-4F6D-A668-641BFEEE8586@.microsoft.com...
>
>

Cpu count in the Profiler and Task Manager

Hello Group,
does anyone know if there is a relationship between the CPU count that is
displayed in the SQL Profiler and the Task Manager? I have a trace running
to show when a long insert takes places and the CPU column displays "16".
The same moment I have the Task Manager open and I am looking at the
SQLSERV.exe it displayes "40".
I thought that the numbers would be the same.
RichRich,
I think you're comparing CPU usage numbers for an individual SPID versus the
entire SQL Server application.
HTH
Jerry
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:DEF40479-CBE6-4F6D-A668-641BFEEE8586@.microsoft.com...
> Hello Group,
> does anyone know if there is a relationship between the CPU count that is
> displayed in the SQL Profiler and the Task Manager? I have a trace
> running
> to show when a long insert takes places and the CPU column displays "16".
> The same moment I have the Task Manager open and I am looking at the
> SQLSERV.exe it displayes "40".
> I thought that the numbers would be the same.
> Rich|||Hello Jerry,
hmmmm, if that is the case, seems like I could add up all of the SPID and it
would equal the total?
Rich
"Jerry Spivey" wrote:
> Rich,
> I think you're comparing CPU usage numbers for an individual SPID versus the
> entire SQL Server application.
> HTH
> Jerry
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:DEF40479-CBE6-4F6D-A668-641BFEEE8586@.microsoft.com...
> > Hello Group,
> >
> > does anyone know if there is a relationship between the CPU count that is
> > displayed in the SQL Profiler and the Task Manager? I have a trace
> > running
> > to show when a long insert takes places and the CPU column displays "16".
> > The same moment I have the Task Manager open and I am looking at the
> > SQLSERV.exe it displayes "40".
> >
> > I thought that the numbers would be the same.
> >
> > Rich
>
>

counting total rows when using GROUP BY

hi,

i have a stored procedure

SELECT UserNameAS Visitor,COUNT(VisitID)AS TotalVisitFROM UserVisitsWHERE (ProductID = @.ProductID)AND (AnonimIPISNULL)GROUP BY UserNameUNIONSELECT AnonimIPAS Visitor,COUNT(VisitID)AS TotalVisitFROM UserVisitsAS UserVisits_1WHERE (ProductID = @.ProductID)AND (UserNameISNULL)GROUP BY AnonimIP

this will return something like:

zuperboy90 - 4 visits

ANONIMOUS - 6 visits

85.104.103 - 2 visits etc

how can i count the rows returned in both selections (4+6+2 = 12) ?

thank you

Put your whole query as a sub query then get the sum of TotalVisits.(4+6+12) as it is in your example

Select SUM(x.TotalVisit) [TotalVisits]FROM

(

SELECT UserNameAS Visitor,COUNT(VisitID)AS TotalVisit
FROM UserVisits
WHERE (ProductID = @.ProductID)AND (AnonimIPISNULL)
GROUP BY UserName
UNION
SELECT AnonimIPAS Visitor,COUNT(VisitID)AS TotalVisit
FROM UserVisitsAS UserVisits_1
WHERE (ProductID = @.ProductID)AND (UserNameISNULL)
GROUP BY AnonimIP

) x

|||

hi.

thanks for code, but if i write this i'll lost the other columns "TotalVisit" and "Visitor" :(

|||

Try this:

SELECT UserNameAS Visitor,COUNT(VisitID)AS TotalVisit

FROM UserVisits

WHERE(ProductID= @.ProductID)AND(AnonimIPISNULL)

GROUPBY UserName

UNION ALL

SELECT AnonimIPAS Visitor,COUNT(VisitID)AS TotalVisit

FROM UserVisitsAS UserVisits_1

WHERE(ProductID= @.ProductID)AND(UserNameISNULL)

GROUPBY AnonimIP

SELECT'Total'AS Visitor,COUNT(VisitID)AS TotalVisit

FROM UserVisitsAS UserVisits_2

WHERE(ProductID= @.ProductID)

|||

Select Visitor, SUM(x.TotalVisit) [TotalVisits]FROM

(

SELECT UserNameAS Visitor,COUNT(VisitID)AS TotalVisit
FROM UserVisits
WHERE (ProductID = @.ProductID)AND (AnonimIPISNULL)
GROUP BY UserName
UNION
SELECT AnonimIPAS Visitor,COUNT(VisitID)AS TotalVisit
FROM UserVisitsAS UserVisits_1
WHERE (ProductID = @.ProductID)AND (UserNameISNULL)
GROUP BY AnonimIP

) x

Of course, if I decided to give you a username of 101.123.5.15, you would think I was an IP address.

You might want to try this instead:

Select VisitorType, Visitor, SUM(x.TotalVisit) [TotalVisits]FROM

(

SELECT 'UserName' as VisitorType, UserNameAS Visitor,COUNT(VisitID)AS TotalVisit
FROM UserVisits
WHERE (ProductID = @.ProductID)AND (AnonimIPISNULL)
GROUP BY 'UserName', UserName
UNION
SELECT 'AnonimIP' as VisitorType, AnonimIPAS Visitor,COUNT(VisitID)AS TotalVisit
FROM UserVisitsAS UserVisits_1
WHERE (ProductID = @.ProductID)AND (UserNameISNULL)
GROUP BY 'AnonimIP', AnonimIP

) x

|||

hi

david wendelken:

Select Visitor, SUM(x.TotalVisit) [TotalVisits]FROM

(

SELECT UserNameAS Visitor,COUNT(VisitID)AS TotalVisit
FROM UserVisits
WHERE (ProductID = @.ProductID)AND (AnonimIPISNULL)
GROUP BY UserName
UNION
SELECT AnonimIPAS Visitor,COUNT(VisitID)AS TotalVisit
FROM UserVisitsAS UserVisits_1
WHERE (ProductID = @.ProductID)AND (UserNameISNULL)
GROUP BY AnonimIP

) x

i get this error:Column 'x.Visitor' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

david wendelken:

Select VisitorType, Visitor, SUM(x.TotalVisit) [TotalVisits]FROM

(

SELECT 'UserName' as VisitorType, UserNameAS Visitor,COUNT(VisitID)AS TotalVisit
FROM UserVisits
WHERE (ProductID = @.ProductID)AND (AnonimIPISNULL)
GROUP BY 'UserName', UserName
UNION
SELECT 'AnonimIP' as VisitorType, AnonimIPAS Visitor,COUNT(VisitID)AS TotalVisit
FROM UserVisitsAS UserVisits_1
WHERE (ProductID = @.ProductID)AND (UserNameISNULL)
GROUP BY 'AnonimIP', AnonimIP

) x

i get this error:Each GROUP BY expression must contain at least one column that is not outer reference

i am sorry i don't know sql at all:(

|||

hi

jogi:

SELECT UserNameAS Visitor,COUNT(VisitID)AS TotalVisit

FROM UserVisits

WHERE(ProductID= @.ProductID)AND(AnonimIPISNULL)

GROUPBY UserName

UNION ALL

SELECT AnonimIPAS Visitor,COUNT(VisitID)AS TotalVisit

FROM UserVisitsAS UserVisits_1

WHERE(ProductID= @.ProductID)AND(UserNameISNULL)

GROUPBY AnonimIP

SELECT'Total'AS Visitor,COUNT(VisitID)AS TotalVisit

FROM UserVisitsAS UserVisits_2

WHERE(ProductID= @.ProductID)

i get this warrning:Error in GROUP BY clause.
Error in list of function arguments: 'AS' not recognized.
Unable to parse query text.

|||Sorry, it appears that I didn't make a full copy of the original statement.

Select Visitor, SUM(x.TotalVisit) [TotalVisits]FROM

(

SELECT UserNameAS Visitor,COUNT(VisitID)AS TotalVisit
FROM UserVisits
WHERE (ProductID = @.ProductID)AND (AnonimIPISNULL)
GROUP BY UserName
UNION
SELECT AnonimIPAS Visitor,COUNT(VisitID)AS TotalVisit
FROM UserVisitsAS UserVisits_1
WHERE (ProductID = @.ProductID)AND (UserNameISNULL)
GROUP BY AnonimIP

) x

GROUP BY Visitor

Of course, if I decided to give you a username of 101.123.5.15, you would think I was an IP address.

You might want to try this instead:

Select VisitorType, Visitor, SUM(x.TotalVisit) [TotalVisits]FROM

(

SELECT 'UserName' as VisitorType, UserNameAS Visitor,COUNT(VisitID)AS TotalVisit
FROM UserVisits
WHERE (ProductID = @.ProductID)AND (AnonimIPISNULL)
GROUP BY 'UserName', UserName
UNION
SELECT 'AnonimIP' as VisitorType, AnonimIPAS Visitor,COUNT(VisitID)AS TotalVisit
FROM UserVisitsAS UserVisits_1
WHERE (ProductID = @.ProductID)AND (UserNameISNULL)
GROUP BY 'AnonimIP', AnonimIP

) x

Group by Visitor

|||

hi, thanks for code

that stored procedure returns exacly what is in the SELECT clause: :(

127.0.0.124zuperboy903Test1@.yahoo.com1test2@.yahoo.com2

to simplify the problem, i will make a separate stored procedure for storing the total comments...i don't thnik is big deal another comand to database

thanks

|||

Glad that worked for you! Be sure to mark which answer(s) worked, so others will know too!

There is a general lesson to learn here about sql: think in sets of data, not rows of data.

By putting parentheses around our select statement, we created a set of data.

Then we queried from that set.

If you can properly define the sets of data that you need, the sql often becomes extremely simple.

Counting rows in a report

i have a report with 1 group and items under the group.

i want to add a new field called Sl. no. at the group level which keeps the count on groups...by that i mean say if there are 10 groups i want the number from 1 to 10 appear against each group as
1 Grp1
2 Grp2
3 Grp3
.
.
.
10 Grp10

How can i achieve the above. I tried the rownumber but it returns the number of rows the group has. The levels i have is "table1" the main scope and "table1_Group1" the group scope which in the table1 scope.

Any help will be appreciated.

what you are doing is similiar to what I wanted to do to get a generic counter... someone else had posted the below as an answer for me. This should work for you as well.

Put this in your code window:

Dim numGroupRow as Double

Function GetRowNumber() as Double
numGroupRow += 1
Return numGroupRow
End Function

Then put this in your group row textbox

= Code.GetRowNumber()

This will increment every group row. If you need to start over at some point the code will need to change.

|||thanks for ur reply.
It works as desired but i have some sorting defined on a field and i want to this sl.no field to move with the sort.

I will try to alter the code but if the solution is out there i would love to have it.

Thaks
|||

I don't think sorting should matter as this is a generic counter and is only incremented by 1 when you call it.

Since the sort acts before the fields are displayed, it shouldn't matter.

|||I thought so too but it is not sorting with the other fields. This is what happens the initial report comes as:

1....valA.......valB
2....valC.......valD
.
.
.
.
40...valE......valF
41...valG.....valH

now when i sort on first field the output comes as

1...valE......valF
2....valC.......valD
3....valA.......valB
.
.
.
.

41...valG.....valH

instead of
40...valE......valF
2....valC.......valD
1....valA.......valB
.
.
.
.

41...valG.....valH
|||

ah, I see.

That is becuase the counter is working as expected.

you will probobaly have to create a field on the dataset your self, add a new field and refernce the code in your field.

then place that field on your report and try the sort that way. You may have to play around with some grouping.

Counting Groups

I'm trying to count the # of times a group header prints, but am having
little luck. If I create a data set with :
"Declare @.StartCount int
Set @.StartCount = 1
Select @.StartCount as Start"
Then pull the sum of the field into the group header, the very first number
it starts off with is 7, not 1. If I just pull the field itself, it returns
1 every time the group header repeats itself. If I don't use the set
statement, but set the input as a parameter with a default of 1, I get 1
every time the group repeats itself.
So, I went search on how to declare a variable as a counter and found a post
titled "Line Item Count" which contains part of the information I'm looking
for, but no details. David Siebert said:
"You could use custom code (under report properties) to increment a variable
for each row."
Unfortunately, not knowing VB very well, I have no idea how to implement
this. I tried doing a Dim statement in that window, then putting a textbox
on the line in question with an expression of "=Count(Counter + 1)", but I
keep getting a "Counter not declared" error message when I try to preview.
Can anyone help me out?
Thanks in advance,
Catadmin
--
MCDBA, MCSA
Random Thoughts: I only thought I was going crazy. I forgot I was already
there.Not sure why you are doing this but... anyway.
Put this code in your code section of report properties:
Public Shared gintCnt As Integer ' This will hold the count.
Public Shared Function IncDispCnt() As String
' This function will increment count and output result.
gintCnt += 1
Return gintCnt.ToString
End Function
Public Shared Function DisplayCnt() As String
' This function will output result.
Return gintCnt.ToString
End Function
Public Shared Function IncCnt() As String
' This function will increment count.
gintCnt += 1
Return ""
End Function
Public Shared Function ResetCnt() As String
' This function will increment count.
gintCnt = 0
Return ""
End Function
I gave you a few options there. Add a textbox to the header you want to
count and place this in the expression:
=Code.IncCnt()
In the page footer add a textbox and place this in the expression:
=Code.DisplayCnt()
That should do it.
--
Message posted via http://www.sqlmonster.com|||oops, the comment for ResetCnt should be:
' This function will reset the global count.
--
Message posted via http://www.sqlmonster.com|||> Not sure why you are doing this
I'm doing this because I've got a group that repeats its headers halfway
down the page. I just want the headers at the top of the page, so I'm using
the counter to trigger the visibility property on that particular header. If
the counter > 1 on a page, then hide. Else show. Then I reset the counter
at the page footer.
Your code worked perfectly! I just could figure it out to save my life. Of
course, I wasn't looking at using functions either.
Thank you so very very very much. You taught me something valuable today.
Catadmin
--
MCDBA, MCSA
Random Thoughts: If a person is Microsoft Certified, does that mean that
Microsoft pays the bills for the funny white jackets that tie in the back?
@.=)
"Patrick P via SQLMonster.com" wrote:
but... anyway.
> Put this code in your code section of report properties:
> Public Shared gintCnt As Integer ' This will hold the count.
> Public Shared Function IncDispCnt() As String
> ' This function will increment count and output result.
> gintCnt += 1
> Return gintCnt.ToString
> End Function
> Public Shared Function DisplayCnt() As String
> ' This function will output result.
> Return gintCnt.ToString
> End Function
> Public Shared Function IncCnt() As String
> ' This function will increment count.
> gintCnt += 1
> Return ""
> End Function
> Public Shared Function ResetCnt() As String
> ' This function will increment count.
> gintCnt = 0
> Return ""
> End Function
> I gave you a few options there. Add a textbox to the header you want to
> count and place this in the expression:
> =Code.IncCnt()
> In the page footer add a textbox and place this in the expression:
> =Code.DisplayCnt()
> That should do it.
> --
> Message posted via http://www.sqlmonster.com
>

Counting Group fields

Is there a way I can count the number of grouped items in a textbox in a
report?
When I try to use =count(Fields!UserID.Value), the report gives me the
amount of records in the query.
I also tried using count distinct but that returned the same results.
Is there anyway I can just count the grouped listings? Or maybe count the
number of table rows?
TIA,
JacksonI think you can specify a scope for the count method ie
=count(Fields!UserID.Value, "GroupName")
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rscreate/htm/rcr_creating_expressions_v1_9bp0.asp|||Thanks Thomo,
Where would I be able to find the group name? The only group names I've
found is called table1_UserID and table1_details_group but they don't seem
to work.
Thanks again for the help,
Jackson
"Thomo" <greg.thomson@.gmail.com> wrote in message
news:1147102016.645069.295180@.e56g2000cwe.googlegroups.com...
>I think you can specify a scope for the count method ie
> =count(Fields!UserID.Value, "GroupName")
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rscreate/htm/rcr_creating_expressions_v1_9bp0.asp
>|||Those sound like legit group names. You can find group names by right
clicking on the beginning of a group row and selecting edit group from
the context menu. The name will be on the top portion of the general
tab of the Grouping and Sorting Properties window. The names work fine
as the scope argument for the count statement, making sure that the
group name is in double quotes since it needs to be a string argument.
You didn't say what kind of error you're getting, but maybe its the
placement of your aggregate field. Try putting it in the group footer,
for example, and see if it works.|||Putting it into the group footer worked. I was trying it in the Page
footer. But the problem is that when I do a count it counts all of the
records.
I just want to include a count of how many Users are included in the report.
In my report I have UserID's grouped and within the group is the details of
their previous transactions. And for some reason it's counting the records
instead of just the groups.
Any suggestions?
Thanks again,
Jackson
"dba56" <louvig4@.att.net> wrote in message
news:1147115725.622812.317540@.y43g2000cwc.googlegroups.com...
> Those sound like legit group names. You can find group names by right
> clicking on the beginning of a group row and selecting edit group from
> the context menu. The name will be on the top portion of the general
> tab of the Grouping and Sorting Properties window. The names work fine
> as the scope argument for the count statement, making sure that the
> group name is in double quotes since it needs to be a string argument.
> You didn't say what kind of error you're getting, but maybe its the
> placement of your aggregate field. Try putting it in the group footer,
> for example, and see if it works.
>|||doh, nevermind. I took a look at the link that Thomo had sent and I tried
using countdistinct again in the footer and it worked.
I could have sworn I tried countdistinct and it came out different.
Oh well thanks for all of the help.
Jackson
"Jackson" <jackson_num5@.yahoo.com> wrote in message
news:eK%23I1ntcGHA.3632@.TK2MSFTNGP02.phx.gbl...
> Putting it into the group footer worked. I was trying it in the Page
> footer. But the problem is that when I do a count it counts all of the
> records.
> I just want to include a count of how many Users are included in the
> report.
> In my report I have UserID's grouped and within the group is the details
> of their previous transactions. And for some reason it's counting the
> records instead of just the groups.
> Any suggestions?
> Thanks again,
> Jackson
> "dba56" <louvig4@.att.net> wrote in message
> news:1147115725.622812.317540@.y43g2000cwc.googlegroups.com...
>> Those sound like legit group names. You can find group names by right
>> clicking on the beginning of a group row and selecting edit group from
>> the context menu. The name will be on the top portion of the general
>> tab of the Grouping and Sorting Properties window. The names work fine
>> as the scope argument for the count statement, making sure that the
>> group name is in double quotes since it needs to be a string argument.
>> You didn't say what kind of error you're getting, but maybe its the
>> placement of your aggregate field. Try putting it in the group footer,
>> for example, and see if it works.
>

Counting dups

When I run this query:
SELECT orderid, count(*) AS 'DupCount'
FROM orders
GROUP BY orderid
HAVING count(*) > 1
I get this:
Orderid DupCount
-- --
1 2
2 5
3 2
How can I return the count of dup records ( in this case 3) and the total
number of dups (in this case 9)?
Can I use ROLLUP with HAVING?SELECT COUNT(*), SUM(dupcount)
FROM
(SELECT orderid, COUNT(*) AS dupcount
FROM orders
GROUP BY orderid
HAVING COUNT(*) > 1) AS T
--
David Portas
SQL Server MVP
--|||Thanks David
Do you know if it is possible to use WITH ROLLUP with a HAVING clause. To
me it appears that the ROLLUP will return results independant of the HAVING.
"Dave" <david.frickNOtoSPAM@.homestore.com> wrote in message
news:unkRiNPoEHA.3876@.TK2MSFTNGP15.phx.gbl...
> When I run this query:
> SELECT orderid, count(*) AS 'DupCount'
> FROM orders
> GROUP BY orderid
> HAVING count(*) > 1
> I get this:
> Orderid DupCount
> -- --
> 1 2
> 2 5
> 3 2
> How can I return the count of dup records ( in this case 3) and the total
> number of dups (in this case 9)?
> Can I use ROLLUP with HAVING?
>
>|||Yes you can use CUBE/ROLLUP and HAVING together. The GROUPING() function is
useful in conjuction with ROLLUP and HAVING to filter the required summary
rows from the query.
I don't think ROLLUP will help much with your query because I believe you'll
still need a subquery to do a "double aggregation".
--
David Portas
SQL Server MVP
--

counting distinct group rows

I have a report with several groups. the last group (3rd group) displays totals so I only have a group footer displaying. This group only displays 2 rows which are totals for that employee. However in the 2nd Group footer when I try to get a count of the number of rows, which in this case is only 2, it returns 58. I think that the group has a total of 58 records that are summed up into tow rows. I want the number 2 not 58. How can I do this?See the attached example.
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"RobbRoss" <RobbRoss@.discussions.microsoft.com> wrote in message
news:67558989-66BA-4C15-AA3A-7888168523C6@.microsoft.com...
> I have a report with several groups. the last group (3rd group) displays
totals so I only have a group footer displaying. This group only displays 2
rows which are totals for that employee. However in the 2nd Group footer
when I try to get a count of the number of rows, which in this case is only
2, it returns 58. I think that the group has a total of 58 records that are
summed up into tow rows. I want the number 2 not 58. How can I do this?|||Can't see the example here on the MSDN Discussion groups any chance of pasting it in.
"RobbRoss" wrote:
> I have a report with several groups. the last group (3rd group) displays totals so I only have a group footer displaying. This group only displays 2 rows which are totals for that employee. However in the 2nd Group footer when I try to get a count of the number of rows, which in this case is only 2, it returns 58. I think that the group has a total of 58 records that are summed up into tow rows. I want the number 2 not 58. How can I do this?|||Here's an example:
<?xml version="1.0" encoding="utf-8"?>
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefini
tion"
xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<RightMargin>1in</RightMargin>
<Body>
<ReportItems>
<Table Name="table1">
<Height>1.5in</Height>
<Style />
<Header>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox1">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontSize>8pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>17</ZIndex>
<rd:DefaultName>textbox1</rd:DefaultName>
<Value>state</Value>
<CanGrow>true</CanGrow>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox2">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontSize>8pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>16</ZIndex>
<rd:DefaultName>textbox2</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>city</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox3">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontSize>8pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>15</ZIndex>
<rd:DefaultName>textbox3</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>au lname</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Header>
<Details>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="state">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontSize>8pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>2</ZIndex>
<rd:DefaultName>state</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="city">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontSize>8pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>1</ZIndex>
<rd:DefaultName>city</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="au_fname">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontSize>8pt</FontSize>
<Color>DarkGreen</Color>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<rd:DefaultName>au_fname</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!au_fname.Value & " " &
Fields!au_fname.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Details>
<DataSetName>pubs</DataSetName>
<TableGroups>
<TableGroup>
<Header>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox4">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontSize>8pt</FontSize>
<Color>Crimson</Color>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>14</ZIndex>
<rd:DefaultName>textbox4</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!state.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox5">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontSize>8pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>13</ZIndex>
<rd:DefaultName>textbox5</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox6">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontSize>8pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>12</ZIndex>
<rd:DefaultName>textbox6</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Header>
<Grouping Name="table1_Group1">
<GroupExpressions>
<GroupExpression>=Fields!state.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<Footer>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox7">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>PapayaWhip</BackgroundColor>
<BorderStyle>
<Bottom>Solid</Bottom>
<Default>Solid</Default>
<Top>Solid</Top>
<Right>None</Right>
<Left>None</Left>
</BorderStyle>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>8</ZIndex>
<rd:DefaultName>textbox7</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox8">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>PapayaWhip</BackgroundColor>
<BorderStyle>
<Bottom>Solid</Bottom>
<Default>Solid</Default>
<Top>Solid</Top>
<Right>None</Right>
<Left>None</Left>
</BorderStyle>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>7</ZIndex>
<rd:DefaultName>textbox8</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox9">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>PapayaWhip</BackgroundColor>
<BorderStyle>
<Bottom>Solid</Bottom>
<Top>Solid</Top>
</BorderStyle>
<FontSize>8pt</FontSize>
<Color>Crimson</Color>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>6</ZIndex>
<rd:DefaultName>textbox9</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>="State total = " &
CountRows("table1_Group1")</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Footer>
</TableGroup>
<TableGroup>
<Header>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox13">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontSize>8pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>11</ZIndex>
<rd:DefaultName>textbox13</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox14">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontSize>8pt</FontSize>
<Color>Tomato</Color>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>10</ZIndex>
<rd:DefaultName>textbox14</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!city.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox15">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontSize>8pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>9</ZIndex>
<rd:DefaultName>textbox15</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Header>
<Grouping Name="table1_Group2">
<GroupExpressions>
<GroupExpression>=Fields!city.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<Footer>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox10">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>Khaki</BackgroundColor>
<BorderStyle>
<Bottom>Solid</Bottom>
<Top>Solid</Top>
</BorderStyle>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>5</ZIndex>
<rd:DefaultName>textbox10</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox11">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>Khaki</BackgroundColor>
<BorderStyle>
<Bottom>Solid</Bottom>
<Top>Solid</Top>
</BorderStyle>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>4</ZIndex>
<rd:DefaultName>textbox11</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox12">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>Khaki</BackgroundColor>
<BorderStyle>
<Bottom>Solid</Bottom>
<Top>Solid</Top>
</BorderStyle>
<FontSize>8pt</FontSize>
<Color>Tomato</Color>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>3</ZIndex>
<rd:DefaultName>textbox12</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>="City total = " &
CountRows("table1_Group2")</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Footer>
</TableGroup>
</TableGroups>
<TableColumns>
<TableColumn>
<Width>1.66667in</Width>
</TableColumn>
<TableColumn>
<Width>1.66667in</Width>
</TableColumn>
<TableColumn>
<Width>1.66667in</Width>
</TableColumn>
</TableColumns>
</Table>
</ReportItems>
<Style />
<Height>1.75in</Height>
</Body>
<TopMargin>1in</TopMargin>
<DataSources>
<DataSource Name="pubs">
<rd:DataSourceID>dc55d7a5-424d-4a42-8b88-fded81a53060</rd:DataSourceID>
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>initial catalog=pubs</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
</DataSource>
</DataSources>
<Width>5.00001in</Width>
<DataSets>
<DataSet Name="pubs">
<Fields>
<Field Name="au_id">
<DataField>au_id</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="au_lname">
<DataField>au_lname</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="au_fname">
<DataField>au_fname</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="phone">
<DataField>phone</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="address">
<DataField>address</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="city">
<DataField>city</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="state">
<DataField>state</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="zip">
<DataField>zip</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="contract">
<DataField>contract</DataField>
<rd:TypeName>System.Boolean</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>pubs</DataSourceName>
<CommandText>SELECT *
FROM authors</CommandText>
</Query>
</DataSet>
</DataSets>
<LeftMargin>1in</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<rd:DrawGrid>true</rd:DrawGrid>
<rd:ReportID>6b64ca82-e1a6-41df-950f-fe0d4cff930f</rd:ReportID>
<BottomMargin>1in</BottomMargin>
<Language>en-US</Language>
</Report>
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Another frustrated developer"
<Anotherfrustrateddeveloper@.discussions.microsoft.com> wrote in message
news:FF5BFD9B-28DC-4656-90E8-51E28F0C500A@.microsoft.com...
> Can't see the example here on the MSDN Discussion groups any chance of
pasting it in.
> "RobbRoss" wrote:
> > I have a report with several groups. the last group (3rd group)
displays totals so I only have a group footer displaying. This group only
displays 2 rows which are totals for that employee. However in the 2nd
Group footer when I try to get a count of the number of rows, which in this
case is only 2, it returns 58. I think that the group has a total of 58
records that are summed up into tow rows. I want the number 2 not 58. How
can I do this?|||RobbRoss:
I read your June 28 posting about the csv export -- I am having the same
problem that you had back in June. How did you go about solving that problem?
Would you please kindly share it with me.
Thanks,
John|||Append this string to the report URL:
&rs%3aCommand=Render&rs%3AFormat=CSV&rc%3ANoHeader=true
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"John" <John@.discussions.microsoft.com> wrote in message
news:125676B7-25E4-44BA-84D1-2813B6EC767E@.microsoft.com...
> RobbRoss:
> I read your June 28 posting about the csv export -- I am having the same
> problem that you had back in June. How did you go about solving that
problem?
> Would you please kindly share it with me.
> Thanks,
> John
>

Counting a group of data as one

How do I go about counting a group of data as 1 item? For example, I have a user who worked on 11 work orders, but only 10 were completed and I am trying to get the # of work orders completed. The problem I am having is it is counting each work day for each completed work order, so for example, if one work order was worked on for 5 days, it counts that work order as five and not one. I need it to ignore the number of work days and just give me the one work order in the count.use distinct count instead.|||I tried that, but it does not work in this case. I am getting closer with it, but now I have a different problem. It seems that the total I need is always off by 1. For example, if a person worked on 7 work orders and 5 of them are closed or completed, it is giving me a count of six. I need to figure out how if a work order has a certain status, to take that total and subtract 1 from it, otherwise leave it alone. Any ideas?|||It would be good if you would post an example of your current report and your desired result.|||You might be able to use a running total with a formula to determine which records to count; reset at the appropriate level.

counter of the group by statment

hi,

Lets say I have a table called test

create table test(

number int,

[str] nvarchar(50)

)

table content, for example:

8,'a'

8,'b'

8,'c'

5,'a'

5,'h'

I want to get a result with one more column that indicates on the asc counter

of the group by clause. i.e.

somthing like this:

number counter

8, 1

5, 2

and so on...

it does not realy matter regarding the query - All I want is to add one more column to the query that indicates on the count of the group by.so if i have 2 rows with number 8 and 2 rows with number 5 I would like to see in that column 1 (at the 8 number rows) and 2 (on the 5 number rows) and so on.. as the number is changing the counter is going up by 1.

Hi,

I think you might need to take a look at the RANK function: http://msdn2.microsoft.com/en-us/library/ms176102.aspx

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||

You can use the dense_rank function to do this. The sample query assumes that you want to return all rows

create table #test(

number int,

[str] nvarchar(50)

)

insert into #test
select 8,'a'
union all
select 8,'b'
union all
select 8,'c'
union all
select 5,'a'
union all
select 5,'h'

select number,
dense_rank() over (order by number) as dense_rank
from #test

drop table #test
go

number dense_rank
-- --
5 1
5 1
8 2
8 2
8 2

|||

Hi,

Thank you for this info, but I need to use this rank in my where clause:

select number,

dense_rank() over (order by number) as dense_rank

from #test

where dense_rank = 1

but i get an error. How can I use it in the where clause?

thx

Counter by Group

How can I create a counter (line#) by group were it resets = 1 whenever a new group starts.

Customer Line#

A 1

A 2

A 3

B 1

C 1

C 2

Hello,

Try putting this in your detail row:

=RowNumber("GROUPNAME")

Get the GROUPNAME by right-clicking on your group header row and select "Edit Group...". Copy the Name and place it in the RowNumber function.

Hope this helps.

Jarret

|||

This works as specified but it does not solve my real problem.

I have a query - customer master joined to ship-to's.

I am trying to use the RowNumber expression with a Parameter "Show Ship-To's" - Y/N

If Show = N and RowNumber <> 1 then I do not want lines 2 - X to show.

This works. However, it treats the display as if all the lines are there. Page 1 might show only 3 accounts, Page2 might show

10 accounts, Page 3 shows 5 accounts, etc, etc

Using a Parameter how can I tell it to include all the lines or just the 1st line of each group of customer#'s. If Ship-To = N

then each page should have 50 Master accounts with no ship-to data.

Account Ship-to

A

B

C

D

E
F

...

or

A 1

2

3

B 1

2

C 1

It's almost as if I need to condition the query to get the data from Customer Master or Customer Master joined to Ship-To

based on the Parameter "Ship-To" (value = Y/N). That way, the correct data is there before it ever trys to format the report.

Detailed explinations/examples needed as I have no knowledge of VB and Reporting Services other than what I can get out of a book.