RowNumber?

I want to add a record number to each record in my report.
I don't have any grouping on my report, so not sure if using
the rownumber() function works in that case'
C.The following should work:
=RowNumber(Nothing)sql

Rownumber with grouping

Hi everyone

I'm having a problem that I hope someone will be able to help me with. I've created a report with a table, and using =Rownumber(nothing) to give each row a number - this works fine.

Now, however, I'm trying to create a summary report. So I've introduced grouping on one of the columns, but unfortunately the Rownumber command now doesn't seem to be taking that into account, and still showing the original row numbers. So basically I'm ending up with numbers like 2, 6, 9, 15, etc.

I've tried changing the scope from nothing to the row name and to the group name, but they all do the same thing.

Does anyone know how to solve this?

Thanks,
Matt

The grouping should make the row numbers add up properly. Are you putting the group name within double quotes, like this?

=RowNumber("MyGroupName")|||

Thanks for your reply Darrell.

Yes I am using double quotes like that. Anything else gives me a syntax error when i try to run it.

Any other thoughts. I can't work out what the problem is here. Would it help if I posted a copy of my report?

|||

Hi again

I apologise if I'm sounding petulant here, but I really am at a loss. I can't find any information anywhere on the web, and I can't work out what else I could be doing wrong. If anyone has any ideas I would really appreciate hearing them.

Thanks,
Matt

|||did you ever solve this? i am having the same challenge. there is a serious lack of documentation on this.|||

Just solved this a few minutes ago. I love it when I answer my own questions.

The key is to use CountDistinct on the value you are grouping on. So use:

=RunningValue(Fields!YourGroupField.Value, CountDistinct, Nothing)

For my whole write-up, visit:

http://maxqtech.com/CS/blogs/david_leibowitz/archive/2006/08/22/3372.aspx

David Leibowitz

Business Intelligence Practice Manager

MaxQ Technologies

|||

Hey David

Thanks for your reply. I never did get it sorted out, so I appreciate your solution. Will remember it for next time :)

Matt

RowNumber reset at start of each grouping

I'm creating a report where I'd like to list out the lines of the report. I
deceided to use RowNumber function but it is keeping a running total when I
use it like this: RowNumber(Nothing)
I used the report wizard to create a report and used the page grouping to
seperate pages by "team". How do I find the scope to tell RowNumber to
startover for each team?
ColinYou can set the scope of RowNumber by replacing Nothing with the name of the
group where you want the reset to occur. If your group name is Team then you
would use RowNumber("Team").
"Colin" wrote:
> I'm creating a report where I'd like to list out the lines of the report. I
> deceided to use RowNumber function but it is keeping a running total when I
> use it like this: RowNumber(Nothing)
> I used the report wizard to create a report and used the page grouping to
> seperate pages by "team". How do I find the scope to tell RowNumber to
> startover for each team?
> Colin
>
>sql

RowNumber count within a group- please help!

I am new to reporting services, so forgive me if this is a simple question.
I have a simple report with 2 levels of grouping such as this:
Group 1
-Group 2
-item 1
-Item 2
I want to add a RowNumber to show the number (1, 2, 3, etc) before each item
within Group 2. I read the help and it said to use RowNumber(Scope) where
scope is the name of the grouping. However, any time I put the name of the
group in, I get an error. How do I refer to the group? I'm using this:
=RowNumber(First(Fields!Objective.Value, "DonorTrac_v3"))
RowNumber (Nothing) gives me the number for the outer group, but i want the
numbers in the inner group.
Any help you can provide is appreciated. Thanks!=RowNumber("Group 2")
Put the group name in double quotes!
Charles Kangai, MCDBA, MCT
"giggleraz" wrote:
> I am new to reporting services, so forgive me if this is a simple question.
> I have a simple report with 2 levels of grouping such as this:
> Group 1
> -Group 2
> -item 1
> -Item 2
> I want to add a RowNumber to show the number (1, 2, 3, etc) before each item
> within Group 2. I read the help and it said to use RowNumber(Scope) where
> scope is the name of the grouping. However, any time I put the name of the
> group in, I get an error. How do I refer to the group? I'm using this:
> =RowNumber(First(Fields!Objective.Value, "DonorTrac_v3"))
> RowNumber (Nothing) gives me the number for the outer group, but i want the
> numbers in the inner group.
> Any help you can provide is appreciated. Thanks!|||ah-hah! Thank you :)
"giggleraz" wrote:
> I am new to reporting services, so forgive me if this is a simple question.
> I have a simple report with 2 levels of grouping such as this:
> Group 1
> -Group 2
> -item 1
> -Item 2
> I want to add a RowNumber to show the number (1, 2, 3, etc) before each item
> within Group 2. I read the help and it said to use RowNumber(Scope) where
> scope is the name of the grouping. However, any time I put the name of the
> group in, I get an error. How do I refer to the group? I'm using this:
> =RowNumber(First(Fields!Objective.Value, "DonorTrac_v3"))
> RowNumber (Nothing) gives me the number for the outer group, but i want the
> numbers in the inner group.
> Any help you can provide is appreciated. Thanks!

Row Total In Table

Hi,
I have a table in my report (not a matrix) that displays 50 rows by
6 columns - fixed, no grouping requirements. The user wants to see the
total of both the vertical column (easy) and also the horizontal row - not
so easy.
First I tried this expression = SUM(Fields!Field1.Value +
Fields!Fields2.Value....etc) but this did not give the correct number.
A co-worker told me that a horizontal row sum was "impossible" to do
with a table and that I had to swap my table for a matrix.
Can anyone tell me how to get the sum of a horizontal row of a
table?
JDJust try this,
= SUM(Fields!Field1.Value +sum(Fields!Fields2.Value) + ....etc
Amarnath
"Joe Delphi" wrote:
> Hi,
> I have a table in my report (not a matrix) that displays 50 rows by
> 6 columns - fixed, no grouping requirements. The user wants to see the
> total of both the vertical column (easy) and also the horizontal row - not
> so easy.
> First I tried this expression = SUM(Fields!Field1.Value +
> Fields!Fields2.Value....etc) but this did not give the correct number.
> A co-worker told me that a horizontal row sum was "impossible" to do
> with a table and that I had to swap my table for a matrix.
> Can anyone tell me how to get the sum of a horizontal row of a
> table?
>
> JD
>
>|||Sorry, Sending you again
Just try this
= SUM(Fields!Field1.Value) + SUM(Fields!Fields2.Value) + ......etc
Amarnath
"Joe Delphi" wrote:
> Hi,
> I have a table in my report (not a matrix) that displays 50 rows by
> 6 columns - fixed, no grouping requirements. The user wants to see the
> total of both the vertical column (easy) and also the horizontal row - not
> so easy.
> First I tried this expression = SUM(Fields!Field1.Value +
> Fields!Fields2.Value....etc) but this did not give the correct number.
> A co-worker told me that a horizontal row sum was "impossible" to do
> with a table and that I had to swap my table for a matrix.
> Can anyone tell me how to get the sum of a horizontal row of a
> table?
>
> JD
>
>|||To get the horizontal row sume miss out the SUM(). Just use the expression
=Fields!Field1.Value+Fields!Field2.Value+...+Fields!Field6.Value
HTH,
Magendo_Man
"Joe Delphi" wrote:
> Hi,
> I have a table in my report (not a matrix) that displays 50 rows by
> 6 columns - fixed, no grouping requirements. The user wants to see the
> total of both the vertical column (easy) and also the horizontal row - not
> so easy.
> First I tried this expression = SUM(Fields!Field1.Value +
> Fields!Fields2.Value....etc) but this did not give the correct number.
> A co-worker told me that a horizontal row sum was "impossible" to do
> with a table and that I had to swap my table for a matrix.
> Can anyone tell me how to get the sum of a horizontal row of a
> table?
>
> JD
>
>

Rounding and Grouping

Perhaps someone can settle an arguement for me ?

I have a set of data that I need to group together. SQL Script below.

CREATE TABLE [dbo].[CommTransactions] (
[ID] [id_type] NOT NULL ,
[TransactionID] [id_type] NULL ,
[ClientID] [id_type] NULL ,
[AccountCode] [varchar] (10) NULL ,
[Amount] [float] NULL ,
[CreateDateTime] [datetime] NULL

For the records I want to group the following applies.

The ID is unique and distinct.
The TransactionId is the same.
The ClientId is the same.
The AccountCode is different.
The Amount will be the same.
The CreateDateTime field is different by a few milliseconds.

I want to create a single line showing two account codes in different
fields. i.e. Staff and Manager (where their ID is the account code).

These can be entered in any order in the table mentioned.

The problem I have is I need to link two records together (that's the
problem in it's most simplistic terms). However, there may be
additional records with the same TransactionId, ClientId, AccountCode
and Amount, but happened at a slightly different time. It could be
done on the same day.

Now, the arguement is that we can group using the CreateDateTime
field. I argue that we can't as it will show down to the millisecond
and any rounding will not always allow for a match. If we added the
matching records once per day, then I can extract the date and group
on it, but if more than one group is added per day, then this would
cause the logic to fail.

So, are there any reliable methods for grouping date/time fields
reliably if there is a small difference (I suspect not)?

Is there anything I have missed ?

Any help or suggestions would be appreciated.

Thanks

RyanRyan,
Forgive me if I am not understanding the question correctly.
But I think the answer is that you don't have to group on a field; you
can group on an expression in most cases.
In this case, you can probably group by
convert(varchar,CreateDateTime,101), which is the date portion of
CreateDateTime.
I hate to suggest this because the performance will probably be terrible
unless your WHERE clause if very specific, but it may be the quick fix you
are looking for.

Best regards,
Chuck Conover
www.TechnicalVideos.net

"Ryan" <ryanofford@.hotmail.com> wrote in message
news:7802b79d.0402020100.41141655@.posting.google.c om...
> Perhaps someone can settle an arguement for me ?
> I have a set of data that I need to group together. SQL Script below.
> CREATE TABLE [dbo].[CommTransactions] (
> [ID] [id_type] NOT NULL ,
> [TransactionID] [id_type] NULL ,
> [ClientID] [id_type] NULL ,
> [AccountCode] [varchar] (10) NULL ,
> [Amount] [float] NULL ,
> [CreateDateTime] [datetime] NULL
> For the records I want to group the following applies.
> The ID is unique and distinct.
> The TransactionId is the same.
> The ClientId is the same.
> The AccountCode is different.
> The Amount will be the same.
> The CreateDateTime field is different by a few milliseconds.
> I want to create a single line showing two account codes in different
> fields. i.e. Staff and Manager (where their ID is the account code).
> These can be entered in any order in the table mentioned.
> The problem I have is I need to link two records together (that's the
> problem in it's most simplistic terms). However, there may be
> additional records with the same TransactionId, ClientId, AccountCode
> and Amount, but happened at a slightly different time. It could be
> done on the same day.
> Now, the arguement is that we can group using the CreateDateTime
> field. I argue that we can't as it will show down to the millisecond
> and any rounding will not always allow for a match. If we added the
> matching records once per day, then I can extract the date and group
> on it, but if more than one group is added per day, then this would
> cause the logic to fail.
> So, are there any reliable methods for grouping date/time fields
> reliably if there is a small difference (I suspect not)?
> Is there anything I have missed ?
> Any help or suggestions would be appreciated.
> Thanks
> Ryan|||Ryan (ryanofford@.hotmail.com) writes:
> Now, the arguement is that we can group using the CreateDateTime
> field. I argue that we can't as it will show down to the millisecond
> and any rounding will not always allow for a match. If we added the
> matching records once per day, then I can extract the date and group
> on it, but if more than one group is added per day, then this would
> cause the logic to fail.
> So, are there any reliable methods for grouping date/time fields
> reliably if there is a small difference (I suspect not)?

I'm not sure that I follow, but it sounds to me more like a business
problem.

You can group by the hour for instance:

SELECT yadadada, d, COUNT(*)
FROM (SELECT yadayada,
d = convert(char(8), CreateDateTime, 112) +
convert(char(5), CreateDateTime, 108)
FROM ...) AS a
GROUP BY yadayada, d

Of course, is a group is inserted so that some rows are inserted before
one o'clock, and others after you lose. Likewise, if two groups are
inserted the same hour.

A more complicated scheme may be devised where you compute the time
between two inserted rows, and if the difference is > some value,
those are two groups.

But you probably get a lot more robust application, by introducing a
marker which is unique for every batch you insert. This could still
be a datetime value, you just need to make sure that all rows in the
same batch gets the the same value.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Yep, pretty much as I suspected. Unfortunately this table is one
supplied by another company so I can't change it as easily as I want
without affecting their app. Our users expectation differs from what
this package does hence the problem.

I want the other company to change this slightly and there will be a
cost (fair enough), only problem is our company doesn't want to pay
for it. So, I'm trying to provide them with everything to prove they
either pay for the change or accept it won't work. They would rather
my team spend several days (at God knows what cost) examining
something I know won't work instead of paying for a days worth of
development.

Daft.

As you have guessed, I'm trying to steer them down the route of a
marker that I can group on.

Thanks for the help.

Ryan

Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns94843E9F3EA3Yazorman@.127.0.0.1>...
> Ryan (ryanofford@.hotmail.com) writes:
> > Now, the arguement is that we can group using the CreateDateTime
> > field. I argue that we can't as it will show down to the millisecond
> > and any rounding will not always allow for a match. If we added the
> > matching records once per day, then I can extract the date and group
> > on it, but if more than one group is added per day, then this would
> > cause the logic to fail.
> > So, are there any reliable methods for grouping date/time fields
> > reliably if there is a small difference (I suspect not)?
> I'm not sure that I follow, but it sounds to me more like a business
> problem.
> You can group by the hour for instance:
> SELECT yadadada, d, COUNT(*)
> FROM (SELECT yadayada,
> d = convert(char(8), CreateDateTime, 112) +
> convert(char(5), CreateDateTime, 108)
> FROM ...) AS a
> GROUP BY yadayada, d
> Of course, is a group is inserted so that some rows are inserted before
> one o'clock, and others after you lose. Likewise, if two groups are
> inserted the same hour.
> A more complicated scheme may be devised where you compute the time
> between two inserted rows, and if the difference is > some value,
> those are two groups.
> But you probably get a lot more robust application, by introducing a
> marker which is unique for every batch you insert. This could still
> be a datetime value, you just need to make sure that all rows in the
> same batch gets the the same value.|||I have another thought that is worth a go. A slightly unusual approach
I must admit, but I think it may work.

I can establish the initial line that I want and take the
CreateDateTime from that. If I then add 1 minute to give me a start
time. Then subtract 1 minute to give me an end time, I can create a
table which holds the various ID fields, the accountcode I need and
the start and end times of a group.

I then use another query to pull out the second accountcode I want and
use a left join to the table I created previously, joining where the
createdatetime is between the start and end date. I add the
accountcode from the first table as a new field on the end of the
results of this query.

It means that the system will have a 2 minute window to commit the
transactions. Normally this is a few seconds, but I can adjust my
window.

I'll have to do some work checking where this can fail though, but
it's worth a little time doing this.

Feel free to pull this apart so I can check how well it will work.