Interim Summing every x number of rows

Using SQL Server 2005 Standard & SQL Server Reporting Services.

First off, here is the application.

An airport baggage handling system distributes bags using multiple conveyors. Bag counts are logged every 15 minutes. There is a count for each conveyor. Example Log Table layout is as follows (The TIME column is DateTime, the Convx columns are TinyInt)

Time Conv1 Conv2 Conv3 Conv4 Conv5 Conv6

i 3 2 3 4 2 1

i+15min 2 3 4 2 2 2

i+30min etc.....................

i+45min

i+60min

etc...

The management team wants a throughput report which will take the following parameters in order to filter the results:

Begin Date End Date Time Interval (selectable as 15mins, 30 mins, 45mins, 60 mins and Daily)

My question is this. Given that my raw data has 1 row for every 15 minutes, if they select 60 minutes as their interval I need to run the query with the start and end dates but Sum every 4 rows and display it as 1 row, likewise if they select 30 minute interval, I need to sum every 2 rows. How do I run a query and SUM the Conv count data for every x number of rows and use the 1st TIME value in the returned x row summary?

Thanks for your help and let me know if I need to clarify anything

For this kind of question / problem, it is very very very helpful to post the estructure of the tables, including constraints and indexes, sample data (insert statements) and expected result.

AMB

|||

OK, I'm a newbie at all of this so I will try and give you what you asked for:

In the Tables below, RecordID is my Key, Identity field, Increment 1, seed 1 - Data Type Int.

Conv columns are all SmallInt and TimeStampVal is DateTime with DefaultValue of (getdate()) to log the time when the record is inserted. 1 Record will be insertedevery 15 mins via an OPC Server.

Example of table including sample data - for easy explanation of Math I have presumed that each conveyor will have a throughput of 2 bags every 15 minutes

RecordID Conv1 Conv2 Conv3 TimeStampVal 1 2 2 2 2007-06-29 07:00:00.000 2 2 2 2 2007-06-29 07:15:00.000 3 2 2 2 2007-06-29 07:30:00.000 4 2 2 2 2007-06-29 07:45:00.000 5 2 2 2 2007-06-29 08:00:00.000 6 2 2 2 2007-06-29 08:15:00.000 7 2 2 2 2007-06-29 08:30:00.000 8 2 2 2 2007-06-29 08:45:00.000 9 2 2 2 2007-06-29 09:00:00.000 10 2 2 2 2007-06-29 09:15:00.000 11 2 2 2 2007-06-29 09:30:00.000 12 2 2 2 2007-06-29 09:45:00.000

Expected result Data for Query ran with Interval parameter set to 15 mins will be identical to the table above (without the RecordID column.)

Expected result Data for Query ran with Interval Parameter set to 30 Mins

Conv1 Conv2 Conv3 TimeStampVal 4 4 4 2007-06-29 07:00:00.000 4 4 4 2007-06-29 07:30:00.000 4 4 4 2007-06-29 08:00:00.000 4 4 4 2007-06-29 08:30:00.000 4 4 4 2007-06-29 09:00:00.000 4 4 4 2007-06-29 09:30:00.000

Expected Result Set for Query Ran with Interval Parameter set to 60 Mins

Conv1 Conv2 Conv3 TimeStampVal 8 8 8 2007-06-29 07:00:00.000 8 8 8 2007-06-29 08:00:00.000 8 8 8 2007-06-29 09:00:00.000

Hope this helps explain it a little more, if not let me know how I can clarify.

Thanks for your time.

|||

This is a good start, however, given the information in this format, anyone wanting to help would need to spend some time creating the create table statements and insert statements before they can start helping you. You will get a lot more help if you provide the create table and insert statements like this.

Code Snippet

CREATE TABLE BagCounts
(
RecordID INT IDENTITY (1,1) PRIMARY KEY,
Conv1 smallint,
Conv2 smallint,
Conv3 smallint,
TimeStampVal DateTime

)
GO

INSERT INTO BagCounts (Conv1, Conv2, Conv3, TimeStampVal)
SELECT 2, 2, 2, '2007-06-29 07:00:00.000'
UNION SELECT 2, 2, 2, '2007-06-29 07:15:00.000'
UNION SELECT 2, 2, 2, '2007-06-29 07:30:00.000'
UNION SELECT 2, 2, 2, '2007-06-29 07:45:00.000'
UNION SELECT 2, 2, 2, '2007-06-29 08:00:00.000'
UNION SELECT 2, 2, 2, '2007-06-29 08:15:00.000'
UNION SELECT 2, 2, 2, '2007-06-29 08:30:00.000'
UNION SELECT 2, 2, 2, '2007-06-29 08:45:00.000'
UNION SELECT 2, 2, 2, '2007-06-29 09:00:00.000'
UNION SELECT 2, 2, 2, '2007-06-29 09:15:00.000'
UNION SELECT 2, 2, 2, '2007-06-29 09:30:00.000'
UNION SELECT 2, 2, 2, '2007-06-29 09:45:00.000'
GO

I will see if I can come up with a procedure to show you how to get what you want.

|||

Try:

Code Snippet

createtable #t (

RecordID intnotnulluniqueclustered,

Conv1 smallintnotnull,

Conv2 smallintnotnull,

Conv3 smallintnotnull,

TimeStampVal datetimenotnull

)

go

setnocounton

insertinto #t values(1, 2, 2, 2,'2007-06-29 07:00:00.000')

insertinto #t values(2, 2, 2, 2,'2007-06-29 07:15:00.000')

insertinto #t values(3, 2, 2, 2,'2007-06-29 07:30:00.000')

insertinto #t values(4, 2, 2, 2,'2007-06-29 07:45:00.000')

insertinto #t values(5, 2, 2, 2,'2007-06-29 08:00:00.000')

insertinto #t values(6, 2, 2, 2,'2007-06-29 08:15:00.000')

insertinto #t values(7, 2, 2, 2,'2007-06-29 08:30:00.000')

insertinto #t values(8, 2, 2, 2,'2007-06-29 08:45:00.000')

insertinto #t values(9, 2, 2, 2,'2007-06-29 09:00:00.000')

insertinto #t values(10, 2, 2, 2,'2007-06-29 09:15:00.000')

insertinto #t values(11, 2, 2, 2,'2007-06-29 09:30:00.000')

insertinto #t values(12, 2, 2, 2,'2007-06-29 09:45:00.000')

setnocountoff

go

declare @.sd datetime

declare @.interval int

set @.sd = '2007-06-29 07:00:00.000'

set @.interval = 30

;with cte_1

as

(

select

*,

datediff(minute, @.sd, TimeStampVal)/ @.interval as grp

from

#t

)

select

sum(Conv1)as Conv1,

sum(Conv2)as Conv2,

sum(Conv3)as Conv3,

min(TimeStampVal)as TimeStampVal

from

cte_1

group by

grp

order by

min(TimeStampVal)

go

droptable #t

go

AMB

|||

Hunchback, Thanks for the solution, it was just what I was looking for. It took me a while to look some stuff up to figure out what you were doing (I've only been using SQL Server for around 2 months now).

I do have a quick question for clarification though if you would indulge me:

In this part of the code i think you are creating a Common Table Expression (cte_1) which basically replicates the original table and adds a new column onto it called grp.

Code Snippet

with cte_1

as

(

select

*,

datediff(minute, @.sd, TimeStampVal) / @.interval as grp

from

#t

)

I had never heard of CTE's until now so please forgive any dumb questions but why do you not have to specify a datatype for the grp column? I know that it somehow defaults to an int from playing with your codesnippet to view the entire cte_1 table but why does it do this when the values returned from the datediff/@.interval function are floating point?

By the way your solution is pure genious - I am still trying to get my brain thinking in t-sql, some of the problems and solutions I am reading on this forum are really opening my eyes as to what is possible.

Thanks again

Colin

|||

Hi Colin,

Glad it helped.

> why do you not have to specify a datatype for the grp column? I know that it somehow defaults to an int from playing with your codesnippet to view

> the entire cte_1 table but why does it do this when the values returned from the datediff/@.interval function are floating point?

The function DATEDIFF returns int, the variable @.interval is int and the division of integers yields integer.

select 1 / 2, 1 / 2.

go

I will suggest, if you want to learn more T-SQL, to get the books:

- Inside SQL server 2005: T-SQL Querying

- Inside SQL server 2005: T-SQL Programming

you will not regret having those books.

AMB

Interim Summing every x number of rows

Using SQL Server 2005 Standard & SQL Server Reporting Services.

First off, here is the application.

An airport baggage handling system distributes bags using multiple conveyors. Bag counts are logged every 15 minutes. There is a count for each conveyor. Example Log Table layout is as follows (The TIME column is DateTime, the Convx columns are TinyInt)

Time Conv1 Conv2 Conv3 Conv4 Conv5 Conv6

i 3 2 3 4 2 1

i+15min 2 3 4 2 2 2

i+30min etc.....................

i+45min

i+60min

etc...

The management team wants a throughput report which will take the following parameters in order to filter the results:

Begin Date End Date Time Interval (selectable as 15mins, 30 mins, 45mins, 60 mins and Daily)

My question is this. Given that my raw data has 1 row for every 15 minutes, if they select 60 minutes as their interval I need to run the query with the start and end dates but Sum every 4 rows and display it as 1 row, likewise if they select 30 minute interval, I need to sum every 2 rows. How do I run a query and SUM the Conv count data for every x number of rows and use the 1st TIME value in the returned x row summary?

Thanks for your help and let me know if I need to clarify anything

For this kind of question / problem, it is very very very helpful to post the estructure of the tables, including constraints and indexes, sample data (insert statements) and expected result.

AMB

|||

OK, I'm a newbie at all of this so I will try and give you what you asked for:

In the Tables below, RecordID is my Key, Identity field, Increment 1, seed 1 - Data Type Int.

Conv columns are all SmallInt and TimeStampVal is DateTime with DefaultValue of (getdate()) to log the time when the record is inserted. 1 Record will be insertedevery 15 mins via an OPC Server.

Example of table including sample data - for easy explanation of Math I have presumed that each conveyor will have a throughput of 2 bags every 15 minutes

RecordID Conv1 Conv2 Conv3 TimeStampVal 1 2 2 2 2007-06-29 07:00:00.000 2 2 2 2 2007-06-29 07:15:00.000 3 2 2 2 2007-06-29 07:30:00.000 4 2 2 2 2007-06-29 07:45:00.000 5 2 2 2 2007-06-29 08:00:00.000 6 2 2 2 2007-06-29 08:15:00.000 7 2 2 2 2007-06-29 08:30:00.000 8 2 2 2 2007-06-29 08:45:00.000 9 2 2 2 2007-06-29 09:00:00.000 10 2 2 2 2007-06-29 09:15:00.000 11 2 2 2 2007-06-29 09:30:00.000 12 2 2 2 2007-06-29 09:45:00.000

Expected result Data for Query ran with Interval parameter set to 15 mins will be identical to the table above (without the RecordID column.)

Expected result Data for Query ran with Interval Parameter set to 30 Mins

Conv1 Conv2 Conv3 TimeStampVal 4 4 4 2007-06-29 07:00:00.000 4 4 4 2007-06-29 07:30:00.000 4 4 4 2007-06-29 08:00:00.000 4 4 4 2007-06-29 08:30:00.000 4 4 4 2007-06-29 09:00:00.000 4 4 4 2007-06-29 09:30:00.000

Expected Result Set for Query Ran with Interval Parameter set to 60 Mins

Conv1 Conv2 Conv3 TimeStampVal 8 8 8 2007-06-29 07:00:00.000 8 8 8 2007-06-29 08:00:00.000 8 8 8 2007-06-29 09:00:00.000

Hope this helps explain it a little more, if not let me know how I can clarify.

Thanks for your time.

|||

This is a good start, however, given the information in this format, anyone wanting to help would need to spend some time creating the create table statements and insert statements before they can start helping you. You will get a lot more help if you provide the create table and insert statements like this.

Code Snippet

CREATE TABLE BagCounts
(
RecordID INT IDENTITY (1,1) PRIMARY KEY,
Conv1 smallint,
Conv2 smallint,
Conv3 smallint,
TimeStampVal DateTime

)
GO

INSERT INTO BagCounts (Conv1, Conv2, Conv3, TimeStampVal)
SELECT 2, 2, 2, '2007-06-29 07:00:00.000'
UNION SELECT 2, 2, 2, '2007-06-29 07:15:00.000'
UNION SELECT 2, 2, 2, '2007-06-29 07:30:00.000'
UNION SELECT 2, 2, 2, '2007-06-29 07:45:00.000'
UNION SELECT 2, 2, 2, '2007-06-29 08:00:00.000'
UNION SELECT 2, 2, 2, '2007-06-29 08:15:00.000'
UNION SELECT 2, 2, 2, '2007-06-29 08:30:00.000'
UNION SELECT 2, 2, 2, '2007-06-29 08:45:00.000'
UNION SELECT 2, 2, 2, '2007-06-29 09:00:00.000'
UNION SELECT 2, 2, 2, '2007-06-29 09:15:00.000'
UNION SELECT 2, 2, 2, '2007-06-29 09:30:00.000'
UNION SELECT 2, 2, 2, '2007-06-29 09:45:00.000'
GO

I will see if I can come up with a procedure to show you how to get what you want.

|||

Try:

Code Snippet

create table #t (

RecordID int not null unique clustered,

Conv1 smallint not null,

Conv2 smallint not null,

Conv3 smallint not null,

TimeStampVal datetime not null

)

go

set nocount on

insert into #t values(1, 2, 2, 2, '2007-06-29 07:00:00.000')

insert into #t values(2, 2, 2, 2, '2007-06-29 07:15:00.000')

insert into #t values(3, 2, 2, 2, '2007-06-29 07:30:00.000')

insert into #t values(4, 2, 2, 2, '2007-06-29 07:45:00.000')

insert into #t values(5, 2, 2, 2, '2007-06-29 08:00:00.000')

insert into #t values(6, 2, 2, 2, '2007-06-29 08:15:00.000')

insert into #t values(7, 2, 2, 2, '2007-06-29 08:30:00.000')

insert into #t values(8, 2, 2, 2, '2007-06-29 08:45:00.000')

insert into #t values(9, 2, 2, 2, '2007-06-29 09:00:00.000')

insert into #t values(10, 2, 2, 2, '2007-06-29 09:15:00.000')

insert into #t values(11, 2, 2, 2, '2007-06-29 09:30:00.000')

insert into #t values(12, 2, 2, 2, '2007-06-29 09:45:00.000')

set nocount off

go

declare @.sd datetime

declare @.interval int

set @.sd = '2007-06-29 07:00:00.000'

set @.interval = 30

;with cte_1

as

(

select

*,

datediff(minute, @.sd, TimeStampVal) / @.interval as grp

from

#t

)

select

sum(Conv1) as Conv1,

sum(Conv2) as Conv2,

sum(Conv3) as Conv3,

min(TimeStampVal) as TimeStampVal

from

cte_1

group by

grp

order by

min(TimeStampVal)

go

drop table #t

go

AMB

|||

Hunchback, Thanks for the solution, it was just what I was looking for. It took me a while to look some stuff up to figure out what you were doing (I've only been using SQL Server for around 2 months now).

I do have a quick question for clarification though if you would indulge me:

In this part of the code i think you are creating a Common Table Expression (cte_1) which basically replicates the original table and adds a new column onto it called grp.

Code Snippet

with cte_1

as

(

select

*,

datediff(minute, @.sd, TimeStampVal) / @.interval as grp

from

#t

)

I had never heard of CTE's until now so please forgive any dumb questions but why do you not have to specify a datatype for the grp column? I know that it somehow defaults to an int from playing with your codesnippet to view the entire cte_1 table but why does it do this when the values returned from the datediff/@.interval function are floating point?

By the way your solution is pure genious - I am still trying to get my brain thinking in t-sql, some of the problems and solutions I am reading on this forum are really opening my eyes as to what is possible.

Thanks again

Colin

|||

Hi Colin,

Glad it helped.

> why do you not have to specify a datatype for the grp column? I know that it somehow defaults to an int from playing with your codesnippet to view

> the entire cte_1 table but why does it do this when the values returned from the datediff/@.interval function are floating point?

The function DATEDIFF returns int, the variable @.interval is int and the division of integers yields integer.

select 1 / 2, 1 / 2.

go

I will suggest, if you want to learn more T-SQL, to get the books:

- Inside SQL server 2005: T-SQL Querying

- Inside SQL server 2005: T-SQL Programming

you will not regret having those books.

AMB

InteractiveHeigt and 6.000 rows on first HTML page?

First the good news:
A Matrix receives around 55.000 Datarows and produces 1.300 pages of PDF.
This happens in:
- SQL-Query: 5 Seconds
- IIS rendering: 10 Seconds for the first HTML-page
- PDF export: 120 seconds
Really great product.
OK, now the problem:
When the data of a customer in the database is rendered with:
- this customer has around 6.000 rows in the matrix
- AND the first column in the Matrix is continuous
(that means: all other about 10 columns are subdata to the first column)
--> it happens the unexpected:
ALL the 6.000 rows appear on the first HTML-page
Changes in InteractiveHeight do not affect this HTML-Rendering on this first
page.
The InteractiveHeight works perfectly from the second page as expected, but
not on this first.
What happens:
Case 1 in IE (6 or 7):
- SQL-Query 5 Seconds
(sqlserv.exe goes up to 150 MB)
- IIS rendering: 10 seconds
(w3wp.exe goes up to 135 MB)
- IExplorer.exe runs for 60 seconds with 100% CPU-usage
(iexplorer.exe memory footprint 100 MB)
After this the first page appears and all is almost well.
Because of 6.000 rows in one IE-page, scrolling is worse than on an Intel
286, but exporting to PDF is no pain.
Case 2 the same in ReportViewerControl (Redistributable 2005):
- SQL-Query 5 Seconds as above
(sqlserv.exe goes up to 150 MB)
- IIS rendering: indefinitely <-- = the problem
w3wp.exe consumes indefinitely 250 MB
- ReportViewer-Control message after 10 minutes:
"The request was aborted: The operation has timed out."
I can shut down the ReportViewer-Control WinApp, IIS continues with 250 MB
and 100% CPU. Poor Windows Enterpise Server 2003R2
----
Questions:
A: How do I achieve pagebreaks for the first 6.000 rows in the Matrix
despite the first column being continuous?
(There is a bug in the HTML-Render-Engine that doesn't accept
InteractiveHeight in this case.)
B: Please supply me with the newest ReportViewer.exe Redistributable March
2007
(it's ready, but will only be distributed to the public in one week
because of the need to write a KB-article)
We need to know, if this will remedy our problem!
(There is probably a bug in the SOAP implementation or the render-engine
of the old ReportViewer-Control.)
Thanks in advance, HenryHello Henry,
I will answer your second question first.
We did not have the new Redistributable package for public now. I will
provide you the download link once I got the information from the internal
team.
As for the first question,
I would like to get the report definition so that I can provide further
assistance on this.
Could you please send the report to me?
To reach me, please remove the ONLINE in my display email address.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Hallo Wei Lu,
any news about the thousands of rows in the first HTML page?
Greetings, Henry|||Hi Wei Lu,
new Info:
Test with the new Microsoft Report Viewer Redistributable 2005 SP1,
published on 03/27/2007 under
http://www.microsoft.com/downloads/details.aspx?FamilyId=35F23B3C-3B3F-4377-9AE1-26321F99FDF0&displaylang=en
unfortunately did NOT alleviate the problem.
Greetings, Henry|||Hello Henry,
Since I did not have the datasource, I could not reproduce this issue.
My suggestion is that you may increase the page Size of the report.
The page size of the report is the InteractiveSize plus the Margins. So
since your interactivesize is 29.7cm * 21cm, and your margin setting is 1cm
top and 0.5cm bottom, could you please increase your Page Size to 29.7cm *
22.5cm to have a try?
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Dear Wei Lu,
thanks for the tip with the page size, but it didn't work.
I've made lots of test with varying page sizes and different combinations of
ReportViewer-Control Versions (042=original DLLs, 762=VSSP1 and 817=Report
Viewer Distr.2005 SP1) and SQL-Server 2005-Versions (SP1 and SP2).
No way.
I've compiled a test-RDL with Access-DB that I've sent you by e-mail.
Did you receive it?
Yours Henry|||Hello Herry,
I reproduce this issue that the HTML render will cost a long time and only
one page are generated.
Currently, I found a workaround is that you may try to add a group
expression in the matrix1_intCustomerID group.
=Int((RowNumber(nothing)-1)/30)
And I use the following in the statement
select * from t0blhbu1502 order by intCustomerID,
intInvoiceNo,DtmInvoiceDate,intOrderNo
Then the report will only show 30 records in one page. You could modify the
setting to allow more records.
Hope this helps!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Wei Lu,
thank you very much for this information.
Weeks ago I already tried this but couldn't achieve what I'd have liked and
abandoned it.
I thought it'd exist some shortcut or official way.
Since there's not, I returned to dig in this grouping direction.
In our case we use a matrix inside a list with subtotals, so your proposal
didn't work.
After another lots of hours fighting with variable scope problems, I figured
it finally out, how to get it done.
Put the following Expression to group on it in the first matrix column:
=code.setRowAggregate()
The associated function:
Function setRowAggregate() As Integer
Static Dim a As Integer = 0
Static Dim b As Integer = 1
If a < 100 Then
a += 1
Else
a = 1
b += 1
End If
Return b
End Function
and yapadapadu it is finally working.
Saludos, Henry|||Hi Henry,
Glad to hear you resolved this issue. And your detailed information of your
resolution is helpful.
If you have any question, please feel free to let me know.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================(This posting is provided "AS IS", with no warranties, and confers no
rights.)

Interactive sort, forcing a row to remain at the end still cant get it to work

Hi,

Im trying to do an interactive sort , one of the rows returned from my datasource called 'Total' i wish to display at the bottom always. is there a way i can do this?

I've tried the below on the column header but the total is either at the bottom or the top how can i check the ordering if Ascending or Descending? Then i cld swop the 1 and the 2 around.

=iif(Fields!Item.Value <> "Total", 1 ,2) & Fields!Item.Value

Otherwise doesnt anyone know how to palce a row from the detail section into the footer?

Many thanks

Dave

This approach should work fine. Maybe the item value contains blanks. You may want to try to just add another detail textbox that shows the result of =(Trim(Fields!Item.Value) <> "Total").

Once you get the expected results, modify the sort expression for the interactive sort accordingly.

-- Robert

|||

Hi All

I'm Climbing the walls here, does anyone know how to do an interactive sort but keep one of the returned rows pinned at the end of the result set?

I've tried =(Trim(Fields!Item.Value) <> "Total") & Fields!Item.Value but the Total ends ups either at the top or bottom of the result set.

E.g

Results

d

e

a

b

c

Sort would return

a

b

d

e

c

with c remaining at the bottom

i get the above when ascending sort which is fine but descending i get

c

a

b

d

e

Thanks in advance

Dave

|||Anyone?

Interactive sort, forcing a row to remain at the end

Hi,

Im trying to do an interactive sort , one of the rows returned from my datasource called 'Total' i wish to display at the bottom always. is there a way i can do this?

I've tried the below on the column header but the total is either at the bottom or the top how can i check the ordering if Ascending or Descending? Then i cld swop the 1 and the 2 around.

=iif(Fields!Item.Value <> "Total", 1 ,2) & Fields!Item.Value

Otherwise doesnt anyone know how to palce a row from the detail section into the footer?

Many thanks

Dave

This approach should work fine. Maybe the item value contains blanks. You may want to try to just add another detail textbox that shows the result of =(Trim(Fields!Item.Value) <> "Total").

Once you get the expected results, modify the sort expression for the interactive sort accordingly.

-- Robert

|||

Hi All

I'm Climbing the walls here, does anyone know how to do an interactive sort but keep one of the returned rows pinned at the end of the result set?

I've tried =(Trim(Fields!Item.Value) <> "Total") & Fields!Item.Value but the Total ends ups either at the top or bottom of the result set.

E.g

Results

d

e

a

b

c

Sort would return

a

b

d

e

c

with c remaining at the bottom

i get the above when ascending sort which is fine but descending i get

c

a

b

d

e

Thanks in advance

Dave

|||Anyone?

Interactive sort, forcing a row to remain at the end

Hi,

Im trying to do an interactive sort , one of the rows returned from my datasource called 'Total' i wish to display at the bottom always. is there a way i can do this?

I've tried the below on the column header but the total is either at the bottom or the top how can i check the ordering if Ascending or Descending? Then i cld swop the 1 and the 2 around.

=iif(Fields!Item.Value <> "Total", 1 ,2) & Fields!Item.Value

Otherwise doesnt anyone know how to palce a row from the detail section into the footer?

Many thanks

Dave

This approach should work fine. Maybe the item value contains blanks. You may want to try to just add another detail textbox that shows the result of =(Trim(Fields!Item.Value) <> "Total").

Once you get the expected results, modify the sort expression for the interactive sort accordingly.

-- Robert

|||

Hi All

I'm Climbing the walls here, does anyone know how to do an interactive sort but keep one of the returned rows pinned at the end of the result set?

I've tried =(Trim(Fields!Item.Value) <> "Total") & Fields!Item.Value but the Total ends ups either at the top or bottom of the result set.

E.g

Results

d

e

a

b

c

Sort would return

a

b

d

e

c

with c remaining at the bottom

i get the above when ascending sort which is fine but descending i get

c

a

b

d

e

Thanks in advance

Dave

|||Anyone?

Interactive sort

Hi,

Im trying to do an interactive sort , one of the rows returned from my datasource called 'Total' i wish to display at the bottom always. is there a way i can do this?

I've tried the below on the column header but the total is either at the bottom or the top how can i check the ordering if Ascending or Descending? Then i cld swop the 1 and the 2 around.

=iif(Fields!Item.Value <> "Total", 1 ,2) & Fields!Item.Value

Many thanks

Dave

*Bump*, anyone?

Interactive column sort

Hi All

I'm Climbing the walls here, does anyone know how to do an interactive sort but keep one of the returned rows pinned at the end of the result set?

i've tried this

=iif(Fields!Item.Value <> "c", 1 ,2) & Fields!Item.Value

Doesn't work to well

Results to Sort

d

e

a

b

c

Sort should return

which works fine on the ascending sort

a

b

d

e

c

with c remaining at the bottom

But i get this on the descending sort

c

a

b

d

e

Thanks in advance

Dave

Dont use sort expression, just use the direction...

then exclude 'c' from detail records (display if item <> 'c') and include 'c' in the footer (if item='c')

Shyam