By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,445 Members | 3,172 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,445 IT Pros & Developers. It's quick & easy.

datareport - help needed !

P: n/a
Hi everyone,
I am currently trying to write a report based on a View of SQL Server.
Basically, I have 3 tables : Hardware, SoftwareInstalled and Software
with SoftwareInstalled that keeps track of all the software installed
on each piece of hardware by referencing the primary keys of each
table.
So now, I have a request that retrieve information from those 3 tables
giving a list of all the hardware with their details + the software
installed.
ex:
hardware name IPAddress Software name
---------------------------------------------
PC1 192.168.0.1 Winamp
PC1 192.168.0.1 Nero
PC2 192.168.0.2 Winamp

I can link this View in VB with a DataEnvironment object and then I
display the result with a DataReport. Ok. The problem is that PC1 is
displayed twice and this is a waste of place. I want PC1 being
displayed only once with the list of software like this :
Hardware Name : PC1
IPAddress : 192.168.0.1
Software : Winamp, Nero

I am really desesperate to get that working...
If someone knows how to do this, please let me know !
Thanx.
Sam
Jul 17 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Hi Sam,

Alrighty....You can probably do this using a GROUP BY clause in your SQL
squery to get those records and also add a DISTINCT to your select query as
follows:

SELECT DISTINCT * FROM <TableName> GROUP BY HarwareName

or by IP Address
SELECT DISTINCT * FROM <TableName> GROUP BY IPAddress

This will eliminate unnecessary duplicates from your result: However since
you one record per application installed it will still show you two rows.
Only thing I can think of is to use something else to do the reporting and
concatenate the SoftwareNames into a comma seperated string like your
desired result show. Maybe programmatically in a vb sub. or using a cursor
object in SQL Server to select everything then loop through the records
(which is the purpose of a cursor object) and say that a variable is :
@ConcatenatedApps = @ConcatenatedApps + SoftwareName

If you really need this done from the database side and not the Application
side of things, you could create a stored procedure to concatenate the
applications and call it from a main stored procedure that woudl create the
query into a temp table
then the stored procedure could loop each object contitionned on the IP
Address for example and when that changes create a noew row of result
data....

It's doable...but be warned that depending on the amount of data you're
dealing with, performances might be dramatically affected using this method.

In VB, once you have the resultset as shown in your example then you could
loop through them and concatenate them yourself...(Say your recordset object
is called AppsSet):

Dim IPAddress As String
Dim PCName As String
Dim Installed As String

Do while not AppSet.EOF
PC Name = AppSet.Fields('HardwareName')
IPAddress = AppSet.Fields('IPAddress')
Do While IPAddress = AppSet.Fields('IPAddress')
Installed = Installed & AppSet.Fields('SoftwareName') & ", "
AppSet.MoveNext
Loop
' When you exit this last loop, you have all the info for one PC
' Get rid of the last comma added with:
Installed = Left(Installed, Len(Installed) - 1)
' the Information is now ready to print or add to a temporary recordset
' for eventual showing or printing.
' Then you loop up and start over with the next PC (IP address)
Loop

Hope this helps

Stéphane Richard
Senior Software and Technology Supervisor
http://www.totalweb-inc.com
For all your hosting and related needs

"berthelot samuel" <sa**************@voila.fr> wrote in message
news:ca**************************@posting.google.c om...
Hi everyone,
I am currently trying to write a report based on a View of SQL Server.
Basically, I have 3 tables : Hardware, SoftwareInstalled and Software
with SoftwareInstalled that keeps track of all the software installed
on each piece of hardware by referencing the primary keys of each
table.
So now, I have a request that retrieve information from those 3 tables
giving a list of all the hardware with their details + the software
installed.
ex:
hardware name IPAddress Software name
---------------------------------------------
PC1 192.168.0.1 Winamp
PC1 192.168.0.1 Nero
PC2 192.168.0.2 Winamp

I can link this View in VB with a DataEnvironment object and then I
display the result with a DataReport. Ok. The problem is that PC1 is
displayed twice and this is a waste of place. I want PC1 being
displayed only once with the list of software like this :
Hardware Name : PC1
IPAddress : 192.168.0.1
Software : Winamp, Nero

I am really desesperate to get that working...
If someone knows how to do this, please let me know !
Thanx.
Sam

Jul 17 '05 #2

P: n/a
I believe it's called exec:
EXEC ProcedureName(parameters)
or maybe just call it by its name:
ProcedureName(parameters)

but yes you can call procedures within procedures within procedures etc..:-)
I know I've done it many times.

like I said cursors should do the trick and give you teh results, it just
might not be the quickest way possible....but I'm not saying cursors are
terribly slow either....so far when I've had to use cursors the speed was
"adequate" to say the least :-).

let me know if you have any more questions :-). I'll be happy to help
--
Stéphane Richard
Senior Software and Technology Supervisor
http://www.totalweb-inc.com
For all your hosting and related needs
"berthelot samuel" <sa**************@voila.fr> wrote in message
news:ca**************************@posting.google.c om...
Thanx a lot Stephane for your help.
The point is that I cannot do it on the vb side cuz the datareport's
fields must be linked to the field of either a table or a view or to
the results of a stored procedure. They cannot be linked dynamically
to a recordset. This is a crap feature of th DataReport...
Besides, I did not really understand this part of your explanations :
you could create a stored procedure to concatenate the
applications and call it from a main stored procedure that woudl create the query into a temp table


How can I call a stored procedure from another one ? And if can do so
why do I need a temp table then ? I could simply link my DataReport's
fields to the second procedure (assuming we can call 'nested'
procedure...)

I will try the cursor thing anyway .
Thanx again.
Sam

"Stephane Richard" <st**************@verizon.net> wrote in message

news:<Mg******************@nwrdny01.gnilink.net>.. .
Hi Sam,

Alrighty....You can probably do this using a GROUP BY clause in your SQL
squery to get those records and also add a DISTINCT to your select query as follows:

SELECT DISTINCT * FROM <TableName> GROUP BY HarwareName

or by IP Address
SELECT DISTINCT * FROM <TableName> GROUP BY IPAddress

This will eliminate unnecessary duplicates from your result: However since you one record per application installed it will still show you two rows. Only thing I can think of is to use something else to do the reporting and concatenate the SoftwareNames into a comma seperated string like your
desired result show. Maybe programmatically in a vb sub. or using a cursor object in SQL Server to select everything then loop through the records
(which is the purpose of a cursor object) and say that a variable is :
@ConcatenatedApps = @ConcatenatedApps + SoftwareName

If you really need this done from the database side and not the Application side of things, you could create a stored procedure to concatenate the
applications and call it from a main stored procedure that woudl create the query into a temp table
then the stored procedure could loop each object contitionned on the IP
Address for example and when that changes create a noew row of result
data....

It's doable...but be warned that depending on the amount of data you're
dealing with, performances might be dramatically affected using this method.
In VB, once you have the resultset as shown in your example then you could loop through them and concatenate them yourself...(Say your recordset object is called AppsSet):

Dim IPAddress As String
Dim PCName As String
Dim Installed As String

Do while not AppSet.EOF
PC Name = AppSet.Fields('HardwareName')
IPAddress = AppSet.Fields('IPAddress')
Do While IPAddress = AppSet.Fields('IPAddress')
Installed = Installed & AppSet.Fields('SoftwareName') & ", "
AppSet.MoveNext
Loop
' When you exit this last loop, you have all the info for one PC
' Get rid of the last comma added with:
Installed = Left(Installed, Len(Installed) - 1)
' the Information is now ready to print or add to a temporary recordset ' for eventual showing or printing.
' Then you loop up and start over with the next PC (IP address)
Loop

Hope this helps

Stéphane Richard
Senior Software and Technology Supervisor
http://www.totalweb-inc.com
For all your hosting and related needs

"berthelot samuel" <sa**************@voila.fr> wrote in message
news:ca**************************@posting.google.c om...
Hi everyone,
I am currently trying to write a report based on a View of SQL Server.
Basically, I have 3 tables : Hardware, SoftwareInstalled and Software
with SoftwareInstalled that keeps track of all the software installed
on each piece of hardware by referencing the primary keys of each
table.
So now, I have a request that retrieve information from those 3 tables
giving a list of all the hardware with their details + the software
installed.
ex:
hardware name IPAddress Software name
---------------------------------------------
PC1 192.168.0.1 Winamp
PC1 192.168.0.1 Nero
PC2 192.168.0.2 Winamp

I can link this View in VB with a DataEnvironment object and then I
display the result with a DataReport. Ok. The problem is that PC1 is
displayed twice and this is a waste of place. I want PC1 being
displayed only once with the list of software like this :
Hardware Name : PC1
IPAddress : 192.168.0.1
Software : Winamp, Nero

I am really desesperate to get that working...
If someone knows how to do this, please let me know !
Thanx.
Sam

Jul 17 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.