Report Builder - Software Count report

[bubblie]

I would like to have more information on the report builder.
I have the following report that I am trying to build and its returning no data. However when I run it in query analyzer I am getting values returned.

[Hemoco]

This error is reported to the third party vendor of the query component.
We are now waiting for a fix.

[bubblie]

Here is another query that works great but when you put it in report builder it errors out.

SELECT TOP 100 PERCENT dbo.tblLicenses.softwareName, dbo.tblLicenses.softwareVersion, dbo.web30repUsedSoftware.SoftwarePublisher AS Publisher,
dbo.web30repUsedSoftware.[number in use], dbo.tblLicenses.Nrlicenses, dbo.tblLicenses.Priceperlicense, CASE WHEN ([number in use] > nrlicenses)
THEN ([number in use] - nrlicenses) ELSE NULL END AS Missing, CASE WHEN ([number in use] > nrlicenses) THEN ([number in use] - nrlicenses)
* priceperlicense ELSE NULL END AS Price
FROM dbo.web30repUsedSoftware INNER JOIN
dbo.tblLicenses ON dbo.web30repUsedSoftware.softwareName = dbo.tblLicenses.softwareName AND
dbo.web30repUsedSoftware.softwareVersion = dbo.tblLicenses.softwareVersion
ORDER BY CASE WHEN ([number in use] > nrlicenses) THEN ([number in use] - nrlicenses) * priceperlicense ELSE NULL END DESC,
dbo.tblLicenses.softwareName, dbo.tblLicenses.softwareVersion

[bubblie]

Here is the SQL

SELECT DISTINCT
dbo.tblSoftware.softwareName AS Software,
dbo.tblSoftware.ComputerName,
dbo.tblSoftware.Installdate,
tblComputers.LastknownIP,
tblSerialnumber.ProductKey
FROM
dbo.tblSoftware
INNER JOIN tblComputers ON (dbo.tblSoftware.ComputerName = tblComputers.Computername)
INNER JOIN tblSerialnumber ON (tblComputers.Computername = tblSerialnumber.Computername)
WHERE
dbo.tblSoftware.softwareName = 'Microsoft Office 2000 Premium' OR
dbo.tblSoftware.softwareName = 'Microsoft Office 2000 Professional' OR
dbo.tblSoftware.softwareName = 'Microsoft Office 2000 SR-1 Professional' OR
dbo.tblSoftware.softwareName = 'Microsoft Office 2000 SR-1 Standard' OR
dbo.tblSoftware.softwareName = 'Microsoft Office 2000 Standard' OR
dbo.tblSoftware.softwareName = 'Microsoft Office 97, Professional Edition' OR
dbo.tblSoftware.softwareName = 'Microsoft Office Access 2003 Runtime' OR
dbo.tblSoftware.softwareName = 'Microsoft Office Access Runtime (English) 2007' OR
dbo.tblSoftware.softwareName = 'Microsoft Office Enterprise 2007' OR
dbo.tblSoftware.softwareName = 'Microsoft Office FrontPage 2003' OR
dbo.tblSoftware.softwareName = 'Microsoft Office OneNote 2003' OR
dbo.tblSoftware.softwareName = 'Microsoft Office Professional Edition 2003' OR
dbo.tblSoftware.softwareName = 'Microsoft Office Professional Plus 2007' OR
dbo.tblSoftware.softwareName = 'Microsoft Office Project Professional 2003' OR
dbo.tblSoftware.softwareName = 'Microsoft Office Project Professional 2007' OR
dbo.tblSoftware.softwareName = 'Microsoft Office Project Standard 2003' OR
dbo.tblSoftware.softwareName = 'Microsoft Office Project Standard 2007' OR
dbo.tblSoftware.softwareName = 'Microsoft Office Standard 2007' OR
dbo.tblSoftware.softwareName = 'Microsoft Office Visio Professional 2003' OR
dbo.tblSoftware.softwareName = 'Microsoft Office Visio Professional 2007' OR
dbo.tblSoftware.softwareName = 'Microsoft Office Visio Standard 2003' OR
dbo.tblSoftware.softwareName = 'Microsoft Office Visio Standard 2007' OR
dbo.tblSoftware.softwareName = 'Microsoft Office Web Components' OR
dbo.tblSoftware.softwareName = 'Microsoft Office XP Standard' OR
dbo.tblSoftware.softwareName = 'Microsoft Office XP Professional' OR
dbo.tblSoftware.softwareName = 'Microsoft Office XP Professional with FrontPage'
ORDER BY
tblSerialnumber.ProductKey,
Software
--Also is there a way to get totals for the different Product Codes/Software with this query.

[Hemoco]

Is it possible to attach your sql code (so I can copy/paste to test)

[bubblie]

Lansweeper wrote:
Is it possible to attach your sql code (so I can copy/paste to test)

Here is the SQL

SELECT DISTINCT
dbo.tblSoftware.softwareName AS Software,
dbo.tblSoftware.ComputerName,
dbo.tblSoftware.Installdate,
tblComputers.LastknownIP,
tblSerialnumber.ProductKey
FROM
dbo.tblSoftware
INNER JOIN tblComputers ON (dbo.tblSoftware.ComputerName = tblComputers.Computername)
INNER JOIN tblSerialnumber ON (tblComputers.Computername = tblSerialnumber.Computername)
WHERE
dbo.tblSoftware.softwareName = 'Microsoft Office 2000 Premium' OR
dbo.tblSoftware.softwareName = 'Microsoft Office 2000 Professional' OR
dbo.tblSoftware.softwareName = 'Microsoft Office 2000 SR-1 Professional' OR
dbo.tblSoftware.softwareName = 'Microsoft Office 2000 SR-1 Standard' OR
dbo.tblSoftware.softwareName = 'Microsoft Office 2000 Standard' OR
dbo.tblSoftware.softwareName = 'Microsoft Office 97, Professional Edition' OR
dbo.tblSoftware.softwareName = 'Microsoft Office Access 2003 Runtime' OR
dbo.tblSoftware.softwareName = 'Microsoft Office Access Runtime (English) 2007' OR
dbo.tblSoftware.softwareName = 'Microsoft Office Enterprise 2007' OR
dbo.tblSoftware.softwareName = 'Microsoft Office FrontPage 2003' OR
dbo.tblSoftware.softwareName = 'Microsoft Office OneNote 2003' OR
dbo.tblSoftware.softwareName = 'Microsoft Office Professional Edition 2003' OR
dbo.tblSoftware.softwareName = 'Microsoft Office Professional Plus 2007' OR
dbo.tblSoftware.softwareName = 'Microsoft Office Project Professional 2003' OR
dbo.tblSoftware.softwareName = 'Microsoft Office Project Professional 2007' OR
dbo.tblSoftware.softwareName = 'Microsoft Office Project Standard 2003' OR
dbo.tblSoftware.softwareName = 'Microsoft Office Project Standard 2007' OR
dbo.tblSoftware.softwareName = 'Microsoft Office Standard 2007' OR
dbo.tblSoftware.softwareName = 'Microsoft Office Visio Professional 2003' OR
dbo.tblSoftware.softwareName = 'Microsoft Office Visio Professional 2007' OR
dbo.tblSoftware.softwareName = 'Microsoft Office Visio Standard 2003' OR
dbo.tblSoftware.softwareName = 'Microsoft Office Visio Standard 2007' OR
dbo.tblSoftware.softwareName = 'Microsoft Office Web Components' OR
dbo.tblSoftware.softwareName = 'Microsoft Office XP Standard' OR
dbo.tblSoftware.softwareName = 'Microsoft Office XP Professional' OR
dbo.tblSoftware.softwareName = 'Microsoft Office XP Professional with FrontPage'
ORDER BY
tblSerialnumber.ProductKey,
Software
--Also is there a way to get totals for the different Product Codes/Software with this query.