hide system tables: report

Showing posts with label report. Show all posts

Wednesday, March 28, 2012

How to insert button control in report manager

Hi
Is it possible to create button in report like we do in VB.net a button
control.
in toolbar their is no button control available ......
Does any one know we can do this thing in report or any alternative way so
we can click on some thing and it do some sort of function
thanksAs a workaround, you can add an image of a button and set its Jump to
URL/Jump to Report properties to make it behave like an actual button.
HTH.
"Amjad" <Amjad@.discussions.microsoft.com> wrote in message
news:C70ABA17-70A2-4DC6-9EFD-B6DE273417BB@.microsoft.com...
> Hi
> Is it possible to create button in report like we do in VB.net a button
> control.
> in toolbar their is no button control available ......
> Does any one know we can do this thing in report or any alternative way so
> we can click on some thing and it do some sort of function
>
> thanks

How to insert an image or logo on the report manager?

Is there anybody who knows how to set up the report manager so that it will display an image on the home page of the report manager?

Thanks!

Report Manger was not built to be customizable by default. The only thing you can do is hack it by overwriting the images or to change the CSS file it uses.

You need to look in "C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportManager\" or whatever your installation directory is. In there you will find a "Styles" directory that has the CSS and an "images" directory in which you need to look for the .JPG files that start with 48 e.g. The home page user 48folderopen.jpg by default. Depending on which page you are RS will use a different image. The main downside is that you have no control over sizing so your image needs to 48x48 otherwise it will look distorted.

|||

Adam,

Thx for your response. I want to confirm one thing with you. In order to use my own image, do I have to delete the 48folderopen.jpg file and paste my own image? Please tell me the proper procedure.

Sincerely,

|||

I got the answer.

Monday, March 26, 2012

How to insert a record in the middle of a DB

Well, how do you do it? I've not seen any report on this... so could it be next to impossible?

Say you have 10 records, 1-10 and you want to insert a new record between record 4 and record 5 in a SQL database, just how do you code it? And oh, each record uses the auto ID increment feature.

Here's an idea:

CREATE TABLE MyTable (id int identity(1,1), col1 varchar(16))
GO

INSERT INTO MyTable SELECT 'value 01'
INSERT INTO MyTable SELECT 'value 02'
INSERT INTO MyTable SELECT 'value 03'
INSERT INTO MyTable SELECT 'value 04'
INSERT INTO MyTable SELECT 'value 05'
INSERT INTO MyTable SELECT 'value 06'
INSERT INTO MyTable SELECT 'value 07'
INSERT INTO MyTable SELECT 'value 08'
INSERT INTO MyTable SELECT 'value 09'
INSERT INTO MyTable SELECT 'value 10'
GO

CREATE TABLE tmpMyTable (id int identity(1,1), col1 varchar(16))
GO

SET IDENTITY_INSERT tmpMyTable ON
GO

INSERT INTO tmpMyTable (id,col1)
SELECT id, col1
FROM MyTable
WHERE id < 5

INSERT INTO tmpMyTable (id,col1) SELECT 5,'value between'
GO

INSERT INTO tmpMyTable (id,col1)
SELECT id+1, col1
FROM MyTable
WHERE id > 4

SET IDENTITY_INSERT tmpMyTable OFF
GO

DROP TABLE MyTable
GO

EXEC sp_rename 'tmpMyTable', 'MyTable'
GO

SELECT * FROM MyTable

|||

Thanks... I'm not very good at SQL but I figure, you create a new table, add those values to it and them insert them back into the old table?

If I can renumber the ID, that would be fine. I deleted record #2 and when I leave record one (view), it still stops where record #2 was deleted. I have to click again to go to record 3. I don't know why it just doesn't jump to record #3, seeing that #2 is deleted.

|||

Take a look at your other question...

Renumbering ID fields is BAD... I really mean it. It can only be done via another table to temporary store the data. There should be no reason to renumber IDs anyway, and if there IS a reson, then the field should not ba an ID ;)

|||

...ooops! That would be "delete" the old table and make the new table the current table.

Okay, ok, this can solve my problem, how can I advance to the next record using code only? I programed my app to go by ID, so if ID #2 is deleted, nothing happens because #2 is deleted. I have to click until the program counter is up to a ID value that isn't deleted before any records show up.

If I say (not really my code, just for example purposes)

CurrRec=CurrRec+1

Select Data from Table1 WHERE ID = CurrRec

Read

While CurrRec is NullDB then

CurrRec=CurrRec+1

Read? 'I could use a read to get pass the deleted record ID

'however, there's a problem if CurrRec > total records

'because it's at the end of the DB

end While

I could put in a time out loop that exits if no record is found in x tries... but surely there must be a way to jump to the NEXT RECORD and a END OF RECORD and so on.

Any takers?

|||

well to move through your records you should do something like this...

Dim oreader As SqlClient.SqlDataReader

Dim oCmd As SqlClient.SqlCommand

oCmd.CommandText = "Select * from mytable"

oreader = oCmd.ExecuteReader

While oreader.Read

'dostuff

End While

|||

I do also need to... move.first, move.last, move.next, ect. With the currRec to move next, I add 1 to currRec, to move back, I subtract 1, to move first, currRec=1.

I'm now thinking I can using CTE functions, if only I can find the syntax for them. That way, I don't use the ID, just the record number.

Thanks.

|||

zdrae,

To select each record, one record at a time, you can use the Top keyword. Each time you get back a record, just save the ID in a variable so that you may use it to select the next record.

EXAMPLE:
Select Top 1 Data from Table1 Where ID > prevID Order by ID

The idea is to select the next record who has an ID greater than the current ID.

Also, just FYI, I think some people in these forums are confused by your use of the term "ID". It seems to me that you really only mean an identity column, but the other posters in this forum are right that you should not attempt to renumber an identity field.

You may be used to programming in a "line-by-line" oriented way, but SQL is really very different and powerful as a data manipulation language. After using it for a while, you'll love how simple and powerful SQL is compared to "line-by-line" data processing.

Hope this helps...

|||

Actually, I didn't like SQL one bit... until I figured out what should actually be in the connection string (I finally got it open); but still I wasn't able to get any data out until I found that I was missing a read statement.

It's on my notebook and the speed is terrible... but everything is slow on it anyway... even games. Thanks.

|||This works great going forward. Any ideas on going backwards? I could store previous IDs but that won't do any good once it goes off the reservation.|||

Instead of a Datareader you could use the Datareader to fill a Dataset or a Datatable.

Those 2 objects will store the data permanently untill you discard them. By doing so they will allow you to scroll foreward and backwards (Or accessing any record you retrieved)

But be aware that you allways should only fetch those records you need...

|||

Yeah, I'm going to put more investment into tableadapters/binding... I just have to figure out how to program it to get the results I need. Wish there was a book... so now I have to make do.

Thanks.

|||

SQL 2005 has a new function called ROW_NUMER. This generates a sequential number, based on another column in the table. So it may eliminate the need for inserting in the middle of the table, and renumbering the IDs. Sample usage:

Select ROW_NUMBER() over (order by MyColumn) as 'SequenceNumber', MyColumn from MyTable

hth

Fluffy

|||

Hi,

As a lot of other replies have said updating an identity column's value is bad. You certainly should not be doing it as part of normal operation of whatever application you are writing to access the database.

Perhaps if you gave a bit of information about what you are actually trying to do with your application in this situation then someone could suggest a better way of doing it. From what you've said so far it simply sounds like you haven't grasped how SQL works and are trying to doing things in an odd way.

Bye,

Dev.

|||In the first place, there's no concept of up or down in space, and there's no concept of 'middle' in a relational database. Indeed, the very essence of a relational database is that the physical location of the rows is irrelevant. So you can't talk about 'inserting a record into the middle of a database' and be talking, sensibly, about a relational database at all. Records go where they go, and that's all there is to it: you leave it up to the RDBMS to work out how to retrieve them, and display them in a user-defined order, when you issue SQL queries against the database.

Second, autoincrementing columns are used to provide meaningless surrogate keys. You suddenly want to start insisting on inserting a new number in between a series of pre-existing auto-incremented ones and what you are actually telling me is that you are reading meaning into what is meant to be, and what was designed to be, meaningless. If you begin changing the auto-assigned numbers to make room for a new entry, and you're actually updating a primary key... and that's just a relational no-no, usually. If the numbers assigned have to have a specific value, they mean something to you, they cannot therefore be meaningless and you shouldn't therefore have used an autoincrementing column to capture them. If, on the other hand, you are happy with autoincrementing numbers, then be happy with them whatever they may be for a particular record, and the question you ask then becomes redundant.

And those who have answered with code samples proposing turning autoincrementing off for a bit, updating the formerly autoincrementing column to make space for the new insert, and the rest... well, frankly, they've missed the point. The request makes no sense as written, demonstrates a fundamental misunderstanding of relational databases and their use, and cannot sensibly be answered.

If there's a specific business problem to be dealt with, that's a different matter ...and one that almost inevitably will come down to fixing a poor application design or being clearer on poorly-understood business requirements.

How to insert a record in the middle of a DB

Well, how do you do it? I've not seen any report on this... so could it be next to impossible?

Say you have 10 records, 1-10 and you want to insert a new record between record 4 and record 5 in a SQL database, just how do you code it? And oh, each record uses the auto ID increment feature.

Here's an idea:

CREATE TABLE MyTable (id int identity(1,1), col1 varchar(16))
GO

CREATE TABLE tmpMyTable (id int identity(1,1), col1 varchar(16))
GO

SET IDENTITY_INSERT tmpMyTable ON
GO

INSERT INTO tmpMyTable (id,col1)
SELECT id, col1
FROM MyTable
WHERE id < 5

INSERT INTO tmpMyTable (id,col1) SELECT 5,'value between'
GO

INSERT INTO tmpMyTable (id,col1)
SELECT id+1, col1
FROM MyTable
WHERE id > 4

SET IDENTITY_INSERT tmpMyTable OFF
GO

DROP TABLE MyTable
GO

EXEC sp_rename 'tmpMyTable', 'MyTable'
GO

SELECT * FROM MyTable

|||

Thanks... I'm not very good at SQL but I figure, you create a new table, add those values to it and them insert them back into the old table?

|||

Take a look at your other question...

|||

...ooops! That would be "delete" the old table and make the new table the current table.

If I say (not really my code, just for example purposes)

CurrRec=CurrRec+1

Select Data from Table1 WHERE ID = CurrRec

Read

While CurrRec is NullDB then

CurrRec=CurrRec+1

Read? 'I could use a read to get pass the deleted record ID

'however, there's a problem if CurrRec > total records

'because it's at the end of the DB

end While

I could put in a time out loop that exits if no record is found in x tries... but surely there must be a way to jump to the NEXT RECORD and a END OF RECORD and so on.

Any takers?

|||

well to move through your records you should do something like this...

Dim oreader As SqlClient.SqlDataReader

Dim oCmd As SqlClient.SqlCommand

oCmd.CommandText = "Select * from mytable"

oreader = oCmd.ExecuteReader

While oreader.Read

'dostuff

End While

|||

I do also need to... move.first, move.last, move.next, ect. With the currRec to move next, I add 1 to currRec, to move back, I subtract 1, to move first, currRec=1.

I'm now thinking I can using CTE functions, if only I can find the syntax for them. That way, I don't use the ID, just the record number.

Thanks.

|||

zdrae,

To select each record, one record at a time, you can use the Top keyword. Each time you get back a record, just save the ID in a variable so that you may use it to select the next record.

EXAMPLE:
Select Top 1 Data from Table1 Where ID > prevID Order by ID

The idea is to select the next record who has an ID greater than the current ID.

Hope this helps...

|||

It's on my notebook and the speed is terrible... but everything is slow on it anyway... even games. Thanks.

|||This works great going forward. Any ideas on going backwards? I could store previous IDs but that won't do any good once it goes off the reservation.|||

Instead of a Datareader you could use the Datareader to fill a Dataset or a Datatable.

Those 2 objects will store the data permanently untill you discard them. By doing so they will allow you to scroll foreward and backwards (Or accessing any record you retrieved)

But be aware that you allways should only fetch those records you need...

|||

Yeah, I'm going to put more investment into tableadapters/binding... I just have to figure out how to program it to get the results I need. Wish there was a book... so now I have to make do.

Thanks.

|||

Select ROW_NUMBER() over (order by MyColumn) as 'SequenceNumber', MyColumn from MyTable

hth

Fluffy

|||

Hi,

Bye,

Dev.

How to insert a record in the middle of a DB

Well, how do you do it? I've not seen any report on this... so could it be next to impossible?

Say you have 10 records, 1-10 and you want to insert a new record between record 4 and record 5 in a SQL database, just how do you code it? And oh, each record uses the auto ID increment feature.

Here's an idea:

CREATE TABLE MyTable (id int identity(1,1), col1 varchar(16))
GO

CREATE TABLE tmpMyTable (id int identity(1,1), col1 varchar(16))
GO

SET IDENTITY_INSERT tmpMyTable ON
GO

INSERT INTO tmpMyTable (id,col1)
SELECT id, col1
FROM MyTable
WHERE id < 5

INSERT INTO tmpMyTable (id,col1) SELECT 5,'value between'
GO

INSERT INTO tmpMyTable (id,col1)
SELECT id+1, col1
FROM MyTable
WHERE id > 4

SET IDENTITY_INSERT tmpMyTable OFF
GO

DROP TABLE MyTable
GO

EXEC sp_rename 'tmpMyTable', 'MyTable'
GO

SELECT * FROM MyTable

|||

Thanks... I'm not very good at SQL but I figure, you create a new table, add those values to it and them insert them back into the old table?

|||

Take a look at your other question...

|||

...ooops! That would be "delete" the old table and make the new table the current table.

If I say (not really my code, just for example purposes)

CurrRec=CurrRec+1

Select Data from Table1 WHERE ID = CurrRec

Read

While CurrRec is NullDB then

CurrRec=CurrRec+1

Read? 'I could use a read to get pass the deleted record ID

'however, there's a problem if CurrRec > total records

'because it's at the end of the DB

end While

I could put in a time out loop that exits if no record is found in x tries... but surely there must be a way to jump to the NEXT RECORD and a END OF RECORD and so on.

Any takers?

|||

well to move through your records you should do something like this...

Dim oreader As SqlClient.SqlDataReader

Dim oCmd As SqlClient.SqlCommand

oCmd.CommandText = "Select * from mytable"

oreader = oCmd.ExecuteReader

While oreader.Read

'dostuff

End While

|||

I do also need to... move.first, move.last, move.next, ect. With the currRec to move next, I add 1 to currRec, to move back, I subtract 1, to move first, currRec=1.

I'm now thinking I can using CTE functions, if only I can find the syntax for them. That way, I don't use the ID, just the record number.

Thanks.

|||

zdrae,

To select each record, one record at a time, you can use the Top keyword. Each time you get back a record, just save the ID in a variable so that you may use it to select the next record.

EXAMPLE:
Select Top 1 Data from Table1 Where ID > prevID Order by ID

The idea is to select the next record who has an ID greater than the current ID.

Hope this helps...

|||

It's on my notebook and the speed is terrible... but everything is slow on it anyway... even games. Thanks.

|||This works great going forward. Any ideas on going backwards? I could store previous IDs but that won't do any good once it goes off the reservation.|||

Instead of a Datareader you could use the Datareader to fill a Dataset or a Datatable.

Those 2 objects will store the data permanently untill you discard them. By doing so they will allow you to scroll foreward and backwards (Or accessing any record you retrieved)

But be aware that you allways should only fetch those records you need...

|||

Yeah, I'm going to put more investment into tableadapters/binding... I just have to figure out how to program it to get the results I need. Wish there was a book... so now I have to make do.

Thanks.

|||

Select ROW_NUMBER() over (order by MyColumn) as 'SequenceNumber', MyColumn from MyTable

hth

Fluffy

|||

Hi,

Bye,

Dev.

How to insert a record in the middle of a DB

Well, how do you do it? I've not seen any report on this... so could it be next to impossible?

Say you have 10 records, 1-10 and you want to insert a new record between record 4 and record 5 in a SQL database, just how do you code it? And oh, each record uses the auto ID increment feature.

Here's an idea:

CREATE TABLE MyTable (id int identity(1,1), col1 varchar(16))
GO

CREATE TABLE tmpMyTable (id int identity(1,1), col1 varchar(16))
GO

SET IDENTITY_INSERT tmpMyTable ON
GO

INSERT INTO tmpMyTable (id,col1)
SELECT id, col1
FROM MyTable
WHERE id < 5

INSERT INTO tmpMyTable (id,col1) SELECT 5,'value between'
GO

INSERT INTO tmpMyTable (id,col1)
SELECT id+1, col1
FROM MyTable
WHERE id > 4

SET IDENTITY_INSERT tmpMyTable OFF
GO

DROP TABLE MyTable
GO

EXEC sp_rename 'tmpMyTable', 'MyTable'
GO

SELECT * FROM MyTable

|||

Thanks... I'm not very good at SQL but I figure, you create a new table, add those values to it and them insert them back into the old table?

|||

Take a look at your other question...

|||

...ooops! That would be "delete" the old table and make the new table the current table.

If I say (not really my code, just for example purposes)

CurrRec=CurrRec+1

Select Data from Table1 WHERE ID = CurrRec

Read

While CurrRec is NullDB then

CurrRec=CurrRec+1

Read? 'I could use a read to get pass the deleted record ID

'however, there's a problem if CurrRec > total records

'because it's at the end of the DB

end While

I could put in a time out loop that exits if no record is found in x tries... but surely there must be a way to jump to the NEXT RECORD and a END OF RECORD and so on.

Any takers?

|||

well to move through your records you should do something like this...

Dim oreader As SqlClient.SqlDataReader

Dim oCmd As SqlClient.SqlCommand

oCmd.CommandText = "Select * from mytable"

oreader = oCmd.ExecuteReader

While oreader.Read

'dostuff

End While

|||

I do also need to... move.first, move.last, move.next, ect. With the currRec to move next, I add 1 to currRec, to move back, I subtract 1, to move first, currRec=1.

I'm now thinking I can using CTE functions, if only I can find the syntax for them. That way, I don't use the ID, just the record number.

Thanks.

|||

zdrae,

To select each record, one record at a time, you can use the Top keyword. Each time you get back a record, just save the ID in a variable so that you may use it to select the next record.

EXAMPLE:
Select Top 1 Data from Table1 Where ID > prevID Order by ID

The idea is to select the next record who has an ID greater than the current ID.

Hope this helps...

|||

It's on my notebook and the speed is terrible... but everything is slow on it anyway... even games. Thanks.

|||This works great going forward. Any ideas on going backwards? I could store previous IDs but that won't do any good once it goes off the reservation.|||

Instead of a Datareader you could use the Datareader to fill a Dataset or a Datatable.

Those 2 objects will store the data permanently untill you discard them. By doing so they will allow you to scroll foreward and backwards (Or accessing any record you retrieved)

But be aware that you allways should only fetch those records you need...

|||

Yeah, I'm going to put more investment into tableadapters/binding... I just have to figure out how to program it to get the results I need. Wish there was a book... so now I have to make do.

Thanks.

|||

Select ROW_NUMBER() over (order by MyColumn) as 'SequenceNumber', MyColumn from MyTable

hth

Fluffy

|||

Hi,

Bye,

Dev.

How to insert a record in the middle of a DB

Well, how do you do it? I've not seen any report on this... so could it be next to impossible?

Say you have 10 records, 1-10 and you want to insert a new record between record 4 and record 5 in a SQL database, just how do you code it? And oh, each record uses the auto ID increment feature.

Here's an idea:

CREATE TABLE MyTable (id int identity(1,1), col1 varchar(16))
GO

CREATE TABLE tmpMyTable (id int identity(1,1), col1 varchar(16))
GO

SET IDENTITY_INSERT tmpMyTable ON
GO

INSERT INTO tmpMyTable (id,col1)
SELECT id, col1
FROM MyTable
WHERE id < 5

INSERT INTO tmpMyTable (id,col1) SELECT 5,'value between'
GO

INSERT INTO tmpMyTable (id,col1)
SELECT id+1, col1
FROM MyTable
WHERE id > 4

SET IDENTITY_INSERT tmpMyTable OFF
GO

DROP TABLE MyTable
GO

EXEC sp_rename 'tmpMyTable', 'MyTable'
GO

SELECT * FROM MyTable

|||

Thanks... I'm not very good at SQL but I figure, you create a new table, add those values to it and them insert them back into the old table?

|||

Take a look at your other question...

|||

...ooops! That would be "delete" the old table and make the new table the current table.

If I say (not really my code, just for example purposes)

CurrRec=CurrRec+1

Select Data from Table1 WHERE ID = CurrRec

Read

While CurrRec is NullDB then

CurrRec=CurrRec+1

Read? 'I could use a read to get pass the deleted record ID

'however, there's a problem if CurrRec > total records

'because it's at the end of the DB

end While

I could put in a time out loop that exits if no record is found in x tries... but surely there must be a way to jump to the NEXT RECORD and a END OF RECORD and so on.

Any takers?

|||

well to move through your records you should do something like this...

Dim oreader As SqlClient.SqlDataReader

Dim oCmd As SqlClient.SqlCommand

oCmd.CommandText = "Select * from mytable"

oreader = oCmd.ExecuteReader

While oreader.Read

'dostuff

End While

|||

I do also need to... move.first, move.last, move.next, ect. With the currRec to move next, I add 1 to currRec, to move back, I subtract 1, to move first, currRec=1.

I'm now thinking I can using CTE functions, if only I can find the syntax for them. That way, I don't use the ID, just the record number.

Thanks.

|||

zdrae,

To select each record, one record at a time, you can use the Top keyword. Each time you get back a record, just save the ID in a variable so that you may use it to select the next record.

EXAMPLE:
Select Top 1 Data from Table1 Where ID > prevID Order by ID

The idea is to select the next record who has an ID greater than the current ID.

Hope this helps...

|||

It's on my notebook and the speed is terrible... but everything is slow on it anyway... even games. Thanks.

|||This works great going forward. Any ideas on going backwards? I could store previous IDs but that won't do any good once it goes off the reservation.|||

Instead of a Datareader you could use the Datareader to fill a Dataset or a Datatable.

Those 2 objects will store the data permanently untill you discard them. By doing so they will allow you to scroll foreward and backwards (Or accessing any record you retrieved)

But be aware that you allways should only fetch those records you need...

|||

Yeah, I'm going to put more investment into tableadapters/binding... I just have to figure out how to program it to get the results I need. Wish there was a book... so now I have to make do.

Thanks.

|||

Select ROW_NUMBER() over (order by MyColumn) as 'SequenceNumber', MyColumn from MyTable

hth

Fluffy

|||

Hi,

Bye,

Dev.

How to insert a record in the middle of a DB

Well, how do you do it? I've not seen any report on this... so could it be next to impossible?

Say you have 10 records, 1-10 and you want to insert a new record between record 4 and record 5 in a SQL database, just how do you code it? And oh, each record uses the auto ID increment feature.

Here's an idea:

CREATE TABLE MyTable (id int identity(1,1), col1 varchar(16))
GO

CREATE TABLE tmpMyTable (id int identity(1,1), col1 varchar(16))
GO

SET IDENTITY_INSERT tmpMyTable ON
GO

INSERT INTO tmpMyTable (id,col1)
SELECT id, col1
FROM MyTable
WHERE id < 5

INSERT INTO tmpMyTable (id,col1) SELECT 5,'value between'
GO

INSERT INTO tmpMyTable (id,col1)
SELECT id+1, col1
FROM MyTable
WHERE id > 4

SET IDENTITY_INSERT tmpMyTable OFF
GO

DROP TABLE MyTable
GO

EXEC sp_rename 'tmpMyTable', 'MyTable'
GO

SELECT * FROM MyTable

|||

Thanks... I'm not very good at SQL but I figure, you create a new table, add those values to it and them insert them back into the old table?

|||

Take a look at your other question...

|||

...ooops! That would be "delete" the old table and make the new table the current table.

If I say (not really my code, just for example purposes)

CurrRec=CurrRec+1

Select Data from Table1 WHERE ID = CurrRec

Read

While CurrRec is NullDB then

CurrRec=CurrRec+1

Read? 'I could use a read to get pass the deleted record ID

'however, there's a problem if CurrRec > total records

'because it's at the end of the DB

end While

I could put in a time out loop that exits if no record is found in x tries... but surely there must be a way to jump to the NEXT RECORD and a END OF RECORD and so on.

Any takers?

|||

well to move through your records you should do something like this...

Dim oreader As SqlClient.SqlDataReader

Dim oCmd As SqlClient.SqlCommand

oCmd.CommandText = "Select * from mytable"

oreader = oCmd.ExecuteReader

While oreader.Read

'dostuff

End While

|||

I do also need to... move.first, move.last, move.next, ect. With the currRec to move next, I add 1 to currRec, to move back, I subtract 1, to move first, currRec=1.

I'm now thinking I can using CTE functions, if only I can find the syntax for them. That way, I don't use the ID, just the record number.

Thanks.

|||

zdrae,

To select each record, one record at a time, you can use the Top keyword. Each time you get back a record, just save the ID in a variable so that you may use it to select the next record.

EXAMPLE:
Select Top 1 Data from Table1 Where ID > prevID Order by ID

The idea is to select the next record who has an ID greater than the current ID.

Hope this helps...

|||

It's on my notebook and the speed is terrible... but everything is slow on it anyway... even games. Thanks.

|||This works great going forward. Any ideas on going backwards? I could store previous IDs but that won't do any good once it goes off the reservation.|||

Instead of a Datareader you could use the Datareader to fill a Dataset or a Datatable.

Those 2 objects will store the data permanently untill you discard them. By doing so they will allow you to scroll foreward and backwards (Or accessing any record you retrieved)

But be aware that you allways should only fetch those records you need...

|||

Yeah, I'm going to put more investment into tableadapters/binding... I just have to figure out how to program it to get the results I need. Wish there was a book... so now I have to make do.

Thanks.

|||

Select ROW_NUMBER() over (order by MyColumn) as 'SequenceNumber', MyColumn from MyTable

hth

Fluffy

|||

Hi,

Bye,

Dev.

how to insert a picture in Crystal Report reading as binary

hi all,
how to insert a picture in Crystal Report reading as binary from database (SQL SERVER).isn't any guru here to solve my problem

i also used the follwing but not working

Picture1.SetoleLocation app.path & "\" & Field1.Value

i have a picture box in crystal report and field in table which stores path name of the picture file.

How to insert a german ole objet (bmg-Image) to a RS Report

Hi all,
how can i get an image from a db (blob) field that was inseret into the DB
with Access (OLE-Object) into a Report?!
On Internet (Microsoft KB) i found the following Source
=System.Convert.FromBase64String(Mid(System.Convert.ToBase64String(Fields!VolvoProductExtLogo.Value),105))
But on my side this source does not work. I have researched that there are
different length on the ole header. How do i get the ole header length for an
german ole bmp object?!
Thanks
Danieli have found a solution
=System.Convert.FromBase64String(Mid(System.Convert.ToBase64String(Fields!Picture.Value),97))
"Daniel Doerfel" wrote:
> Hi all,
> how can i get an image from a db (blob) field that was inseret into the DB
> with Access (OLE-Object) into a Report?!
> On Internet (Microsoft KB) i found the following Source
> =System.Convert.FromBase64String(Mid(System.Convert.ToBase64String(Fields!VolvoProductExtLogo.Value),105))
> But on my side this source does not work. I have researched that there are
> different length on the ole header. How do i get the ole header length for an
> german ole bmp object?!
> Thanks
> Daniel
>sql

Friday, March 23, 2012

how to incorporate data from more than one table(having no relation) in CR9

how to incorporate data from more than one table(having no relation) in detail section of crystal report 9...In CR, on your left you have a "Field Explorer"
In there you have "Database Fields", you right click them and chose "Database Expert".
Then you chose meke new connection and you chose witch tables to import in your CR.
If the tables are in no relationship, thet is not a problem, you just won't be able to connect them together.
Afcourse CR will inform you about that, but just click OK a that should be it.
Afcourse if you don't get data from this tables (in code) you won't be able to show data.

Wednesday, March 21, 2012

How to include data from 2 unlinked tables

I am using VB6 and CR10 & SQL Server 7
I am working on an inventory project nw.. I'm new to crystal reports..

For the report i need to pick data from 2 tables(Sales_Details, Purchase_Details)
There is no link between these 2 tables (except a similarity that both have got a date field)

TABLE 1
Sale_Date... SalesQty
01/01/07...... 50
01/03/07...... 24
01/04/07...... 10

TABLE 2
Purch_Date... Purchase Qty
01/01/07 ....... 100
01/06/07 ....... 100

I need to take a report which shows the movement of a particular Item based on date range
It should look like this

------x----x----x
Date ......... Sales.......Purchase
------x----x----x

01/01/07 ........ 50 ........ 100
01/03/07 ........ 24 ........ 0
01/04/07 ........ 10 ........ 0
01/06/07 ........ 0 ........ 100
--------------
I tried inner and outer joins to links these tables, but all in vein( data is getting repeated then), tried with groups also..no use

Pleeeaaase... help me to do this...
This may be a simple issue for most of you...
But i am a beginner.. i need your valuable help..
Plzzzzz...Just to make sure, but there are no PKeys in either one of your tables?|||You say you need to report by item, so surely there must be some sort of item code in both tables otherwise how do you know what item you're buying / selling?|||Ofcourse item code is there... For sake of simplicity i dint mention that.

Actually both the purchase and sales table has the fileds- Date, Itemcode, Quantity

Sales Table

Date...... ItemCode........Qnty
------------
01/01/07 ..A001............50
01/03/07...A001............24
01/04/07...A001............10

Purchase Table
Date...... ItemCode........Qnty
------------
01/01/07...A001............100
01/06/07...A001............100

and i need a report (like the one mentioned above)based on the movement of Item -A001 ..

Please...........|||First:
You wrote:

There is no link between these 2 tables (except a similarity that both have got a date field)
This is the reason for the questions now I assume itemcode is your link.
Next you will group by date and itemcode if you want, now for grouping by date go to options and choose section to be printed by day.
Then you will need to create formulas that will check to see the quantity of sales and purchases.

Something like:

If {Sales.quantity} > 0
Then {Sales.quantity}
Else 0

Hope that helps,
GJ

How to include a dataset field value in the header

Since I can not directly use a field value of a dataset in the header and
footer section of a report, I'm trying to store this field value in a
variable in the custom code.
Is there a way to do this?
Thanks...I would suggest using parameter with default value from query.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"eralper" <eralper@.discussions.microsoft.com> wrote in message
news:5F028ED8-E8BC-4795-A79E-3481CECC9EA6@.microsoft.com...
> Since I can not directly use a field value of a dataset in the header and
> footer section of a report, I'm trying to store this field value in a
> variable in the custom code.
> Is there a way to do this?
> Thanks...sql

Monday, March 19, 2012

How to improve report performance

Hi,
I have created reports in CRXI with Dynamic & Cascading Prompt. My reports are taking long time 15-20 mins to refressh.
Can sombdy help me to improve my reports performance ?Can Sombdy help me?|||Can anybdy answer this question|||how many records is it selecting? it shows you in the bottom right corner|||When I run the foolowing Query. It runs fine & gives 199 records

SELECT distinct clients.uid,max(da_answer.date_effective) as date,entry_exit.provider_id as provider,
datediff(yyyy,da_answer_val_unsec.val_date,getdate()) as age,
convert(varchar,DATEDIFF ( yyyy,val_date, getdate())) vage,
Case
when DATEDIFF ( yyyy,val_date, getdate()) between 5 and 12 then '5-12'
when datediff(yyyy,val_date,getdate()) between 13 and 17 then '13-17'
when datediff(yyyy,val_date,getdate()) between 18 and 44 then '18-44'
when datediff(yyyy,val_date,getdate()) between 45 and 64 then '45-64'
else 'Over 65'
end as Agegroup

FROM (((clients INNER JOIN da_answer ON clients.uid=da_answer.client_id) INNER JOIN
(da_assessment_question INNER JOIN da_question ON
da_assessment_question.question_id=da_question.uid) ON
da_answer.question_id=da_question.uid) INNER JOIN entry_exit ON
da_answer.client_id=entry_exit.client_id) INNER JOIN da_answer_val_unsec ON da_answer.uid=da_answer_val_unsec.answer_id

WHERE da_answer.date_effective IN( select date_effective FROM da_answer a
WHERE date_effective = (select max(b.date_effective)
FROM da_answer b WHERE b.uid=a.uid) and da_question.uid=893 AND clients.inactive=0 AND
da_assessment_question.assessment_id=21 AND entry_exit.inactive=0)
AND
(((entry_exit.entry_date IS NOT NULL)
AND
(entry_exit.entry_date>={ts '2005-01-01 00:00:00'} AND
entry_exit.entry_date<={ts '2005-03-31 23:59:00'})) and
((entry_exit.exit_date is null) or not
((entry_exit.exit_date >{ts '2004-07-01 00:00:00'} and entry_exit.exit_date <{ts
'2004-12-31 23:59:00'}))))
group by clients.uid,da_answer_val_unsec.val_date,entry_exit.provider_id

After this query I got the clients who have same date_effective but I need distinct clients with max(date_effective) so I create the above query as view(AGE) & applied the following query to this view

select a.uid,a.agegroup,a.provider
from age a
where date in(select max(b.date)
from age b
where a.uid=b.uid
group by uid)
order by a.uid
This query takes 10-15mins to run in sql designer & gives 192 records.|||Have you analysed the execution of your query. It seems that the query optimizer in your DBMS does not work properly when you make the query using the view. Check the plan and make sure query uses indexes and does not perform full table joins.

- Jukka|||Hi

My performance problem is caused by ineffiecient generated HTML - for each item in the reports' table a <DIV><TABLE><TR><TD><TABLE><TR><TD> is generated (??!!) hard to believe !!

Multiply this by a few thousand items to be displayed and you've got a catastrophe.

How can I work around this ??

Thanks

David|||The HTML generated by Crystal is really ugly. We have worked around it by using the pdf format.

How to improve performance of the report's display while updating the table?

Hello, I need help to improve the performance of select statments in my repo
rt. My report is selecting (lots of data) from a attendance table, while a t
rigger is inserting more than 4000 rows of data into the table.
Can I use nolock table hints in my report? How can I use it?
For example,
select * from table a, table b, table c, table d
Should I write like this for the nolock when I want only the table b use nol
ock table hints:-
select * from table a, table b with (nolock), table c, table dAriel
Are you sure that these tables have no JOIN's condition?
This is a mistake which most users make when using the old-style JOIN syntax
is not specifying the join condition. The result set of your query is
Cartesian Product of four tables. it would generate every possible
combination of rows between them.
"@.Ariel" <anonymous@.discussions.microsoft.com> wrote in message
news:CBE065F9-8781-4727-BE82-2ECED244C30A@.microsoft.com...
> Hello, I need help to improve the performance of select statments in my
report. My report is selecting (lots of data) from a attendance table, while
a trigger is inserting more than 4000 rows of data into the table.
> Can I use nolock table hints in my report? How can I use it?
> For example,
> select * from table a, table b, table c, table d
> Should I write like this for the nolock when I want only the table b use
nolock table hints:-
> select * from table a, table b with (nolock), table c, table d|||As Uri says, make sure you have the correct join syntax. (The example you
posted is incorrect.) But the direct answer to your question is yes
select * From table with (nolock)
You WILL see data in the process of being changed in a transaction for which
the transaction has NOT yet committed ( dirty data)... Also look up in books
on line (readpast).
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.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
"@.Ariel" <anonymous@.discussions.microsoft.com> wrote in message
news:CBE065F9-8781-4727-BE82-2ECED244C30A@.microsoft.com...
> Hello, I need help to improve the performance of select statments in my
report. My report is selecting (lots of data) from a attendance table, while
a trigger is inserting more than 4000 rows of data into the table.
> Can I use nolock table hints in my report? How can I use it?
> For example,
> select * from table a, table b, table c, table d
> Should I write like this for the nolock when I want only the table b use
nolock table hints:-
> select * from table a, table b with (nolock), table c, table d|||Thanks Uri and Wayne.
Yes, my table got join statements within.
select * from table a, table b with (nolock), table c and table d
where a.id = b.id and
b.id = c.id and
c.id = d.id;
// the b table I assume is the attendance table.
uri, did you mean that my result will be wrong after I use the nolock syntax
?
Or you mean it will be wrong in terms of some of the data it linked will be
deleted or updated?
Thanks Again|||Uri was saying that your select has an error in it, but it does not. ( He
probably was just looking quickly.) Each one of the tables is in the where
clause, but you are using an OLD style syntax which is VERY prone to the
kinds of errors that Uri mentioned... Why don't you spend some time getting
used to and using the ANSI style join syntax - you'll be better off ... IE
select * from a inner join b with (nolock) on a.id = b.id
inner join c on b.id = c.id
inner join d on c.id = d.id;
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.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
"@.Ariel" <anonymous@.discussions.microsoft.com> wrote in message
news:8647D87B-C082-485D-8BFF-1B822C33FA8D@.microsoft.com...
> Thanks Uri and Wayne.
> Yes, my table got join statements within.
> select * from table a, table b with (nolock), table c and table d
> where a.id = b.id and
> b.id = c.id and
> c.id = d.id;
> // the b table I assume is the attendance table.
> uri, did you mean that my result will be wrong after I use the nolock
syntax?
> Or you mean it will be wrong in terms of some of the data it linked will
be deleted or updated?
> Thanks Again
>

How to improve performance of Reports?

I HAVE REPORT WITH SEVERAL GROUPS AND FIELDS
ITS RUNNING VERY SLOW
I AM EXTRACTING EVERYTHING FROM 1 TABLE ONLY
HOW CAN I IMPROVE PERFORMANCE OF THE REPORT
CAN ANYONE FROM MICROSOFT LET ME KNOW ALL PERFORMANCE OPTIMIZATION TIPS AND TECHNIQUES FOR THIS TOOLYou'll have to be more specific:
1. How long does it take to get the data from the SELECT statement?
2. How long does it take to export the data as XML (RDL processing, from what I understand)?
3. What is the final output format? We are currently have performance problems with large PDF
reports.
I would love to see some tips for performance as well!
Jami
On Mon, 28 Jun 2004 10:27:01 -0700, Raj <Raj@.discussions.microsoft.com> wrote:
>I HAVE REPORT WITH SEVERAL GROUPS AND FIELDS
>ITS RUNNING VERY SLOW
>I AM EXTRACTING EVERYTHING FROM 1 TABLE ONLY
>HOW CAN I IMPROVE PERFORMANCE OF THE REPORT
>CAN ANYONE FROM MICROSOFT LET ME KNOW ALL PERFORMANCE OPTIMIZATION TIPS AND TECHNIQUES FOR THIS TOOL|||Take a look at this
http://blogs.msdn.com/tudortr/archive/2004/06/28/167969.aspx
--
Tudor Trufinescu
Dev Lead
Sql Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Raj" <Raj@.discussions.microsoft.com> wrote in message
news:F3580BB2-C509-40F3-BD9D-062DC03A87A0@.microsoft.com...
> I HAVE REPORT WITH SEVERAL GROUPS AND FIELDS
> ITS RUNNING VERY SLOW
> I AM EXTRACTING EVERYTHING FROM 1 TABLE ONLY
> HOW CAN I IMPROVE PERFORMANCE OF THE REPORT
> CAN ANYONE FROM MICROSOFT LET ME KNOW ALL PERFORMANCE OPTIMIZATION TIPS
AND TECHNIQUES FOR THIS TOOL|||We definitely need more information. This should not be a problem at all (at
least from a complexity viewpoint).
Bruce L-C
"Raj" <Raj@.discussions.microsoft.com> wrote in message
news:F3580BB2-C509-40F3-BD9D-062DC03A87A0@.microsoft.com...
> I HAVE REPORT WITH SEVERAL GROUPS AND FIELDS
> ITS RUNNING VERY SLOW
> I AM EXTRACTING EVERYTHING FROM 1 TABLE ONLY
> HOW CAN I IMPROVE PERFORMANCE OF THE REPORT
> CAN ANYONE FROM MICROSOFT LET ME KNOW ALL PERFORMANCE OPTIMIZATION TIPS
AND TECHNIQUES FOR THIS TOOL

How to import report from report server

Hi,
Is there any way to import existing report from report server (Sql 2005)
into visual studio report designer?.
Thanks in advance.This is not very discoverable. In Report Manager click on the report,
properties tab, edit link. It will ask for a directory. Give it a directory
on your PC (for instance c:\temp). The in the designer right click on the
reports folder for your project, add, existing item.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Radovan Dobriæ" <radovan@.servis24.hr> wrote in message
news:O3CB07mXIHA.5448@.TK2MSFTNGP04.phx.gbl...
> Hi,
> Is there any way to import existing report from report server (Sql 2005)
> into visual studio report designer?.
> Thanks in advance.
>|||Thanks a lot.
I was allways looking in VS for import option,
Radovan
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:OXzqYdpXIHA.4868@.TK2MSFTNGP03.phx.gbl...
> This is not very discoverable. In Report Manager click on the report,
> properties tab, edit link. It will ask for a directory. Give it a
> directory on your PC (for instance c:\temp). The in the designer right
> click on the reports folder for your project, add, existing item.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Radovan Dobriæ" <radovan@.servis24.hr> wrote in message
> news:O3CB07mXIHA.5448@.TK2MSFTNGP04.phx.gbl...
>> Hi,
>> Is there any way to import existing report from report server (Sql 2005)
>> into visual studio report designer?.
>> Thanks in advance.
>

Wednesday, March 7, 2012

how to implement user function/query

Hi,

In my database I have a table which is some kind of history log. The report is designed to group these entries by date. Showing the history data for the entries works fine, but for any history entry I need to show its previous entry, too (that means the table row with maximum date being smaller than the current selected). But what function/query do I have to implement?

Help would be highly appreciated.

Thank you,
gbruse
Select statement with between, and|||But how can I use the result of a select statement in a function? What I wanted to do is getting all dates and getting the latest which is below the current selected (of the group).

gbr|||send me ur code and table structure|||+------+-------+--+--+---+-------+
| Field | Type | Null | Key | Default | Extra |
+------+-------+--+--+---+-------+
| id | int(10) unsigned | | PRI | NULL | auto_increment |
| id_cust | int(10) unsigned | | | 0 | |
| add_data | varchar(20) | YES | | NULL | |
+------+-------+--+--+---+-------+

Imagine you have a table with the dates a user bought something. What I want to achive is showing all items groupes by dates and the date before the selected date.

The report shall look like:

date customer goods
old 06/02/05 Mr. Brown ...
now 06/03/05 Mr. Brown ...
-------------------
old 07/02/05 Mrs. Green ...
new 07/24/05 Mrs. Green ...

Thanks for your replay,

gbr.|||It would help if somebody could tell how to write a query (select xyz from tab where ...) and how to store the return value into a variable. Giving an example to familiarize with the crystal syntax would be enough for the beginning.

gbr|||Is there anyone who can tell how to store an entire column of a table in a variable?

When I use this function

local datetimevar array dates := {tab.date};
count(dates);

Only 1 is displayed though there are more than 1 columns in this table...

How to Implement Subscriptions/My Subscriptions Like Report Manager?

Hello All,
We are using SQL Server 2005 Reporting Services. We are developing an ASP.NET 2.0 (C#) application to be used as an alternative for Report Manager. We're now to the point of developing functionality to allow users to view, edit, and delete subscriptions. Similar to Report Manager, we want to provide both the ability to see all the subscriptions created by a user (My Subscriptions), as well as all subscription per report (Subscriptions).

In our custom application, how can we implement a listing of all subscriptions per report? And, how can we implement a listing of all subscriptions per user?

Thanks in advance.

You have two requirements:

1. Implement a listing of all subscriptions per report

2. And, how can we implement a listing of all subscriptions per user

For #1, you'll need to use some method (maybe ListSubscriptionsUsingDataSource()) to return subscription objects which represent all the subscriptions on your machine...then, you'll need to check each subscription (using the .Report property) to see what report it "belongs" to...I think what you're hoping to do is simply provide the name of a report to some API and get a list of subscriptions back...there's no such function.

For #2, use the ListSubscriptions method and just pass in the username. You'll get a list of list of that users's subscriptions back.

|||I answered this question in the Newsgroup, but will post the answer here as well. You need to call ListSubscriptions.

To get all the subscriptions for a user pass in empty string for the report parameter and the user name for the Owner parameter.

To get all the subscriptions for a given report, pass in the report path for the report parameter and then pass in empty string for the owner.

Please note that the returned subscriptions will only be subscriptions that the user calling ListSubscriptions has permission to see.

How to Implement Subscriptions/My Subscriptions Like Report Manager?

How to Implement Subscriptions/My Subscriptions Like Report Manager?
Hello All,
We are using SQL Server 2005 Reporting Services. We are developing an
ASP.NET 2.0 (C#) application to be used as an alternative for Report
Manager. We're now to the point of developing functionality to allow users
to view, edit, and delete subscriptions. Similar to Report Manager, we want
to provide both the ability to see all the subscriptions created by a user
(My Subscriptions), as well as all subscription per report (Subscriptions).
In our custom application, how can we implement a listing of all
subscriptions per report? And, how can we implement a listing of all
subscriptions per user?
Thanks in advance.You will want to call the ListSubscriptions method passing in different
parameter values. To get a list of all subscriptions for a user, pass in
empty string for the report and the user name for the Owner parameter. To
get a list of all subscriptions for a given report, pass in the report path
and empty string for the owner property.
Note, that the user making the SOAP call will only get back subscriptions
that they have permission to see.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"inland" <nospam@.company.com> wrote in message
news:O9b70ff%23FHA.2320@.TK2MSFTNGP11.phx.gbl...
> How to Implement Subscriptions/My Subscriptions Like Report Manager?
> Hello All,
> We are using SQL Server 2005 Reporting Services. We are developing an
> ASP.NET 2.0 (C#) application to be used as an alternative for Report
> Manager. We're now to the point of developing functionality to allow
> users
> to view, edit, and delete subscriptions. Similar to Report Manager, we
> want
> to provide both the ability to see all the subscriptions created by a user
> (My Subscriptions), as well as all subscription per report
> (Subscriptions).
> In our custom application, how can we implement a listing of all
> subscriptions per report? And, how can we implement a listing of all
> subscriptions per user?
> Thanks in advance.
>

Wednesday, March 28, 2012

How to insert button control in report manager

How to insert an image or logo on the report manager?

Monday, March 26, 2012

How to insert a record in the middle of a DB

How to insert a record in the middle of a DB

How to insert a record in the middle of a DB

How to insert a record in the middle of a DB

How to insert a record in the middle of a DB

How to insert a record in the middle of a DB

how to insert a picture in Crystal Report reading as binary

How to insert a german ole objet (bmg-Image) to a RS Report

Friday, March 23, 2012

how to incorporate data from more than one table(having no relation) in CR9

Wednesday, March 21, 2012

How to include data from 2 unlinked tables

How to include a dataset field value in the header

Monday, March 19, 2012

How to improve report performance

How to improve performance of the report's display while updating the table?

How to improve performance of Reports?

How to import report from report server

Wednesday, March 7, 2012

how to implement user function/query

How to Implement Subscriptions/My Subscriptions Like Report Manager?

How to Implement Subscriptions/My Subscriptions Like Report Manager?

hide system tables

Blog Archive

About Me