Can we get the Next Row Value ?

In SSRS 2005 we have First,Last and Previous functions which get the related values based on the use. I want to compare with the NEXT row value and print it according to my requirement.

My question is How do I get the Next Row Value. Please Help.

Could be tricky, however try using a protected variable in the Code view... something like this (untested):

protected myprevValue as string

public function setString(myString as string) as string

myprevValue = myString
return ""

end function

public function getString() as string
return myprevValue

end function

Then you use a hidden text box with the expression =setString(myString)

If you have multiple values or want to lookup by row number you can use a dictionary object to store and retrieve them.

No guarantees on this and placement of the text boxes are key. I have not tried this with getting the Next value however it did work for doing things like lookups against data outside of the current dataset.

cheers,

Andrew

|||

Hello Andrew,

I tried the way you explained but I was able to get the previous row value only. Is there any other way wherein we can get the Next row's field value.

Please help,

|||

Looking into the future is tough.

You have to iterate through the set once to store the values in a lookup dictionary object, and then iterate again to retrieve. This becomes unmanageable with many fields... hopefully you're only looking up a couple of values here. I have not tried this rownumber method - I was using it to look up descriptive names for keys.

1st table - will contain the set function in a textbox expression passing in the row number and value. This table can be hidden.

=setValue(RowNumber(Nothing),myfield.value)

2nd table - will contain the get function passing in the row number.

=getValue(RowNumber(Nothing)

Code Below

public dictLookup as new System.Collections.Generic.Dictionary(Of String, String)

Function setValue(value as object, value2 as object) as object

dictLookup.Add(value, value2)

return value

End Function

Function getValue(value as object) as object

If dictLookup.ContainsKey(value)

return dictLookup(value)

else

return value

end if

End Function

Can we create distinct values for document map on a particular level?

Hi, all experts here,

Thank you very much for your kind attention.

I am having a question about document map on SQL Server 2005 Reporting Services. I found one problem with the values retrived for the document level. That is, the values are not distinct, they are duplicate for the document level. Is it possible for us to get the distinct values for a particular document level? Hope my question is clear for your help.

Thank you very much in advance for your help and advices. I am looking forward to hearing from you.

With best regards,

Yours sincerely,

Group your data in either the table or list (whatever you are using) and use document map for the group.

Shyam

|||

Hi, Shyam,

Thanks a lot for your kind advices.

Yes, jsut found out that cause I got more than one groups in my reports. And the document map level is not the first group there, so once I run the report, the document map on that level got duplicate values. Now it is clear.

Thank you very much.

With best regards,

Yours sincerely,

Can this be optimized? Newbie question

Hi,
I'm running the following SQL to get values for 4 fields. It is
unacceptably slow. I have no control over the structure of the
database, field names, indexes etc. - what I'm given as far as DB
design is all I'm going to get. If anyone could make any suggestions
I'd really appreciate it!

Thanks,
Bill

SELECT DISTINCT
T_MULTILIST_GRADE.grade, T_MULTILIST.description, T_MULTILIST.code,
T_RECEIVING_DETAIL.amount
FROM T_MULTILIST, T_RECEIVING_DETAIL,
T_MULTILIST_GRADE,T_REQUISITION,T_REQUISITION_DETA IL,T_ORDER,T_DEPOSITORY,
T_RECEIVING
WHERE
(
T_RECEIVING_DETAIL.invoice_number =T_RECEIVING.invoice_number
AND T_RECEIVING_DETAIL.order_id =T_ORDER.id
AND T_ORDER.depository_id =T_DEPOSITORY.id
AND T_REQUISITION.id =T_ORDER.requisition_id
AND T_REQUISITION_DETAIL.requisition_id =T_REQUISITION.id
AND T_REQUISITION_DETAIL.multilist_code
=T_MULTILIST_GRADE.multilist_code
AND T_MULTILIST_GRADE.multilist_code =T_MULTILIST.code

AND T_ORDER.requisition_time_stamp BETWEEN '05/31/2005' AND
'06/01/2006'
AND T_MULTILIST.expiration_year > '2005'
AND T_MULTILIST.code IN ('0043','1043')
AND T_DEPOSITORY.depository_type = 'PRIVATE'
AND T_RECEIVING.status <> 'PAID'
)
wgblackmon@.yahoo.com wrote:

> Hi,
> I'm running the following SQL to get values for 4 fields. It is
> unacceptably slow. I have no control over the structure of the
> database, field names, indexes etc. - what I'm given as far as DB
> design is all I'm going to get. If anyone could make any suggestions
> I'd really appreciate it!
> Thanks,
> Bill

Well, it sounds like you're pretty much screwed. How many rows
does the query return? Is it appreciably faster if you remove
the 'DISTINCT' and do you have the opportunity to detect
and ignore duplicates at the client? Can you even find out
what indexes are on the tables or get the query plan for this?
There may be other query criteria that you could drop, and
instead post-qualify rows in the client.
Hope this (or someone else smarter) helps,
Joe Weinstein at BEA Systems

> SELECT DISTINCT
> T_MULTILIST_GRADE.grade, T_MULTILIST.description, T_MULTILIST.code,
> T_RECEIVING_DETAIL.amount
> FROM T_MULTILIST, T_RECEIVING_DETAIL,
> T_MULTILIST_GRADE,T_REQUISITION,T_REQUISITION_DETA IL,T_ORDER,T_DEPOSITORY,
> T_RECEIVING
> WHERE
> (
> T_RECEIVING_DETAIL.invoice_number =T_RECEIVING.invoice_number
> AND T_RECEIVING_DETAIL.order_id =T_ORDER.id
> AND T_ORDER.depository_id =T_DEPOSITORY.id
> AND T_REQUISITION.id =T_ORDER.requisition_id
> AND T_REQUISITION_DETAIL.requisition_id =T_REQUISITION.id
> AND T_REQUISITION_DETAIL.multilist_code
> =T_MULTILIST_GRADE.multilist_code
> AND T_MULTILIST_GRADE.multilist_code =T_MULTILIST.code
> AND T_ORDER.requisition_time_stamp BETWEEN '05/31/2005' AND
> '06/01/2006'
> AND T_MULTILIST.expiration_year > '2005'
> AND T_MULTILIST.code IN ('0043','1043')
> AND T_DEPOSITORY.depository_type = 'PRIVATE'
> AND T_RECEIVING.status <> 'PAID'
> )|||I'm using this query (and up to 20 similar ones combined with 'UNION')
in a Crystal Report. The report may or may not be able to remove dupes,
but I doubt it (I'm new at Crystal Reports). I'm using DBArtisan to
design the query. The database is an undocumented nightmare with few
indexes. I know it's hideous, but I was hoping I was missing something
really obvious...:)|||Bill,

There is nothing wrong with the query, except that maybe the DISTINCT is
not necessary and could save some time if you dropped it.

The key of this query's performance is in the available indexes (and
maybe the hardware configuration). If no usuable indexes are available
and the tables are large then this query will run like a dog. You should
really turn to the DBA who can put the proper indexes in place...

Gert-Jan

"wgblackmon@.yahoo.com" wrote:
> Hi,
> I'm running the following SQL to get values for 4 fields. It is
> unacceptably slow. I have no control over the structure of the
> database, field names, indexes etc. - what I'm given as far as DB
> design is all I'm going to get. If anyone could make any suggestions
> I'd really appreciate it!
> Thanks,
> Bill
> SELECT DISTINCT
> T_MULTILIST_GRADE.grade, T_MULTILIST.description, T_MULTILIST.code,
> T_RECEIVING_DETAIL.amount
> FROM T_MULTILIST, T_RECEIVING_DETAIL,
> T_MULTILIST_GRADE,T_REQUISITION,T_REQUISITION_DETA IL,T_ORDER,T_DEPOSITORY,
> T_RECEIVING
> WHERE
> (
> T_RECEIVING_DETAIL.invoice_number =T_RECEIVING.invoice_number
> AND T_RECEIVING_DETAIL.order_id =T_ORDER.id
> AND T_ORDER.depository_id =T_DEPOSITORY.id
> AND T_REQUISITION.id =T_ORDER.requisition_id
> AND T_REQUISITION_DETAIL.requisition_id =T_REQUISITION.id
> AND T_REQUISITION_DETAIL.multilist_code
> =T_MULTILIST_GRADE.multilist_code
> AND T_MULTILIST_GRADE.multilist_code =T_MULTILIST.code
> AND T_ORDER.requisition_time_stamp BETWEEN '05/31/2005' AND
> '06/01/2006'
> AND T_MULTILIST.expiration_year > '2005'
> AND T_MULTILIST.code IN ('0043','1043')
> AND T_DEPOSITORY.depository_type = 'PRIVATE'
> AND T_RECEIVING.status <> 'PAID'
> )|||T_MULTILIST.description may be wide. Sorting wide result sets may be
slow. try removing duplicates before joining with T_MULTILIST. Look up
article "The Less SQL Server Sorts, the Faster It Responds"|||wgblackmon@.yahoo.com (wgblackmon@.yahoo.com) writes:
> I'm running the following SQL to get values for 4 fields. It is
> unacceptably slow. I have no control over the structure of the
> database, field names, indexes etc. - what I'm given as far as DB
> design is all I'm going to get. If anyone could make any suggestions
> I'd really appreciate it!

My newsserver had an outage, so the reply I posted originally got lost.
What I said in that post was not that fantastic:

Without know the tables and indexes it's about impossible to give
suggestions. If you post the CREATE TABLE and CREATE INDEX statements
(don't forget constraints!), as well some indication of table sizes,
we might be able to give some tips.

Even better if you can run:

SET STATISTICS PROFILE ON
go
-- query goes here
go
SET STATISTICS PROFILE OFF
go

and post the output. (Preferably in an attachment, as the output is far too
wide for news article).

However, a few minutes later one more thing occurred to me, and that was
when I discovered that the newsserver was sick.

Anyway, what you could try is to run DBCC DBREINDEX on all involved tables.
While it is not going to cause the query to run with the speed of light
all of a sudden, you could see an improvement with 20-30% if there is
serious fragmentation of the tables.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx