datareport - help needed !

**Stephane Richard** · Jul 17 '05, 07:48 PM

Re: datareport - help needed !

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 programmaticall y 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 :
@ConcatenatedAp ps = @ConcatenatedAp ps + 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" <samuel.berthel ot@voila.fr> wrote in message
news:ca34f197.0 307040330.52a64 6bb@posting.goo gle.com...[color=blue]
> Hi everyone,
> I am currently trying to write a report based on a View of SQL Server.
> Basically, I have 3 tables : Hardware, SoftwareInstall ed and Software
> with SoftwareInstall ed 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[/color]

**Stephane Richard** · Jul 17 '05, 07:48 PM

Re: datareport - help needed !

I believe it's called exec:
EXEC ProcedureName(p arameters)
or maybe just call it by its name:
ProcedureName(p arameters)

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" <samuel.berthel ot@voila.fr> wrote in message
news:ca34f197.0 307070019.6d7bb a3a@posting.goo gle.com...[color=blue]
> 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 :[color=green]
> >you could create a stored procedure to concatenate the
> > applications and call it from a main stored procedure that woudl create[/color][/color]
the[color=blue][color=green]
> > query into a temp table[/color]
>
> 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" <stephane.richa rd@verizon.net> wrote in message[/color]
news:<MgeNa.142 63$U23.4497@nwr dny01.gnilink.n et>...[color=blue][color=green]
> > 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[/color][/color]
as[color=blue][color=green]
> > 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[/color][/color]
since[color=blue][color=green]
> > you one record per application installed it will still show you two[/color][/color]
rows.[color=blue][color=green]
> > Only thing I can think of is to use something else to do the reporting[/color][/color]
and[color=blue][color=green]
> > concatenate the SoftwareNames into a comma seperated string like your
> > desired result show. Maybe programmaticall y in a vb sub. or using a[/color][/color]
cursor[color=blue][color=green]
> > 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 :
> > @ConcatenatedAp ps = @ConcatenatedAp ps + SoftwareName
> >
> > If you really need this done from the database side and not the[/color][/color]
Application[color=blue][color=green]
> > side of things, you could create a stored procedure to concatenate the
> > applications and call it from a main stored procedure that woudl create[/color][/color]
the[color=blue][color=green]
> > 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[/color][/color]
method.[color=blue][color=green]
> >
> > In VB, once you have the resultset as shown in your example then you[/color][/color]
could[color=blue][color=green]
> > loop through them and concatenate them yourself...(Say your recordset[/color][/color]
object[color=blue][color=green]
> > 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[/color][/color]
recordset[color=blue][color=green]
> > ' 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" <samuel.berthel ot@voila.fr> wrote in message
> > news:ca34f197.0 307040330.52a64 6bb@posting.goo gle.com...[color=darkred]
> > > Hi everyone,
> > > I am currently trying to write a report based on a View of SQL Server.
> > > Basically, I have 3 tables : Hardware, SoftwareInstall ed and Software
> > > with SoftwareInstall ed 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[/color][/color][/color]

datareport - help needed !

datareport - help needed !

Comment

Comment