Group By / Hiding Report Items (Newbie)

Hi. First, I am VERY new to SQL Queries and Reporting. A co-worker is "mentoring" me, but I am trying not to fill his day with questions.

I HAVE read the help files, searched the forums, looked at books, and done general web searches, but any answers I have found have either no addressed my issue, or the answers are way over my head.

Furthermore, the (SQL 2000) DB is built into proprietary software (ISS Proventia Intrusion Prevention System), and the database may NOT be modified outside of the software.

With that said, I am querying multiple tables within the DB. I am using Business Intelligence Dev Studio, and placing my queries on a reporting server maintained by my co-worker. My goal is not only to get a solution, but also to UNDERSTAND it so I can continue to learn. Of course, the solution takes precedence over my understanding!

My Primary key is dbo.SensorData1.SensorDataID. dbo.SensorDataAVP.AttributeText returns a different number of rows, containing different data depending upon the value of dbo.SensorData1.AlertName. I need to return all rows, hence the Left Joins.

Depending upon my query, I might have 1000 events, and due to the many rows of data from dbo.SensorData1.AlertName I might return 20,000 rows (or more.)

I would like to return a report that "groups" events by dbo.SensorData1.SensorDataID., BUT, rather than simply providing these in groups, provides me single rows with a plus sign next to each even, that can be expanded for the additional data.

My co-worker has discussed sub-tables, but since I cannot modify the DB, it will be difficult / complex to do so, AND, for me to understand.

One of my queries follows. I have thirteen queries, total, that use various groupings of attributes. I have chosen one of the more complex combinations so I can generally apply the concept to the queries with fewer parameters more easily.

Note, I'll be asking the same question on www.sqlservercentral.com in the hopes of getting an answer I can understand one of these two places - If you answer here, there's obviously no need answering there answering there.

Thank you in advance.



SELECT
convert(nvarchar(20), AlertDateTime,120)
AlertDateTime,
AlertName,
AlertPriority,
AlertCount,
convert(varchar,(convert(bigint,SrcAddressInt) / 256 / 65536)) + '.' +
convert(varchar,((convert(bigint,SrcAddressInt) /65536) % 256)) + '.' +
convert(varchar,(convert(bigint,SrcAddressInt) /256) % 256) + '.' +
convert(varchar,((convert(bigint,SrcAddressInt) % 256)))
SrcAddressInt,
SourcePort,
SourcePortName,
convert(varchar,(convert(bigint,DestAddressInt) / 256 / 65536)) + '.' +
convert(varchar,((convert(bigint,DestAddressInt) /65536) % 256)) + '.' +
convert(varchar,(convert(bigint,DestAddressInt) /256) % 256) + '.' +
convert(varchar,((convert(bigint,DestAddressInt) % 256)))
DestAddressInt,
DestPortName,
dbo.SensorData1.ObjectName,
SensorName,
SensorInterfaceName,
AlertTypeID,
convert(varchar,(convert(bigint,SensorAddressInt) / 256 / 65536)) + '.' +
convert(varchar,((convert(bigint,SensorAddressInt) /65536) % 256)) + '.' +
convert(varchar,(convert(bigint,SensorAddressInt) /256) % 256) + '.' +
convert(varchar,((convert(bigint,SensorAddressInt) % 256)))
SensorAddressInt,
ProtocolID,
Cleared,
VulnStatus,
dbo.SensorDataAVP.SensorDataID,
dbo.SensorDataAVP.AttributeName,
dbo.SensorDataAVP.AttributeDataType,
dbo.SensorDataAVP.AttributeText,
dbo.SensorDataAVP.AttributeValue,
dbo.SensorDataAVP.AttributeBlob,
ResponseTypeName,
ResponseName

from
dbo.SensorData

LEFT JOIN

dbo.SensorDataAVP
ON dbo.SensorDataAVP.SensorDataID =
dbo.SensorData1.SensorDataID

LEFT JOIN
dbo.SensorDataResponse
ON dbo.SensorDataResponse.SensorDataID =
dbo.SensorData1.SensorDataID

LEFT JOIN
dbo.ObjectView
ON dbo.ObjectView.ObjectName=
dbo.SensorData1.ObjectName


WHERE
convert(nvarchar(20), AlertDateTime,120) between @StartDate and @EndDate

AND
convert(varchar,(convert(bigint,SrcAddressInt) / 256 / 65536)) + '.' +
convert(varchar,((convert(bigint,SrcAddressInt) /65536) % 256)) + '.' +
convert(varchar,(convert(bigint,SrcAddressInt) /256) % 256) + '.' +
convert(varchar,((convert(bigint,SrcAddressInt) % 256)))

between @LowerIP and @UpperIP

AND
AlertName = @EventName