Help with Report
- Hi
Can you help with Report. It conts MP3 files and i need to add to this report "v_GS_OPERATING_SYSTEM.Description0"
select Sys.Name0, Sys.User_Name0,
Count(SF.FileName) as FileCount,
Sum(SF.FileSize/1024/1024) as 'Wasted Megs'
from v_R_System Sys INNER JOIN v_GS_SoftwareFile SF on
Sys.ResourceID = SF.ResourceID
where SF.FileName like '%'+'.MP3' and SF.FileSize/1024/1024 > 0
Group by Sys.Name0, Sys.User_Name0
having Count(SF.FileName) > 50 order by Sys.Name0 Desc
but be siply adding it I got error "Msg 8120, Level 16, State 1, Line 1
Column 'v_GS_OPERATING_SYSTEM.Description0' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."
Answers
- Sorry for the bad formatting :
select Sys.Name0, Sys.User_Name0,
OS.Description0 as Description,
Count(SF.FileName) as FileCount,
Sum(SF.FileSize/1024/1024) as 'Wasted Megs'
from v_R_System Sys INNER JOIN v_GS_SoftwareFile SF on
Sys.ResourceID = SF.ResourceID inner join v_GS_OPERATING_SYSTEM OS
on Sys.ResourceID = OS.ResourceID
where SF.FileName like '%'+'.MP3' and SF.FileSize/1024/1024 > 0
Group by Sys.Name0, Sys.User_Name0, OS.Description0
having Count(SF.FileName) > 50 order by Sys.Name0 Desc- Proposed As Answer byFabrizio Guaitolini Monday, October 19, 2009 8:07 AM
- Marked As Answer byKaspars_ Monday, October 19, 2009 8:34 AM
All Replies
Hi, if I have understood your question, SQL query should be something like the following.
select
Sys.Name0, Sys.User_Name0,
OS
.Description0 as Description,
Count
(SF.FileName) as FileCount,
Sum
(SF.FileSize/1024/1024) as 'Wasted Megs'
from
v_R_System Sys INNER JOIN v_GS_SoftwareFile SF on
Sys.ResourceID
= SF.ResourceID inner join v_GS_OPERATING_SYSTEM OS
on
Sys.ResourceID = OS.ResourceID
where
SF.FileName like '%'+'.MP3' and SF.FileSize/1024/1024 > 0
Group
by Sys.Name0, Sys.User_Name0, OS.Description0
having
Count(SF.FileName) > 50 order by Sys.Name0 Desc
Regards
Fabrizio Guaitolini
--------------------------------------------------------------------------------
- This posting is provided "AS IS" with no warranties, and confers no rights. http://nocentdocent.wordpress.com http://www.progel.it- Sorry for the bad formatting :
select Sys.Name0, Sys.User_Name0,
OS.Description0 as Description,
Count(SF.FileName) as FileCount,
Sum(SF.FileSize/1024/1024) as 'Wasted Megs'
from v_R_System Sys INNER JOIN v_GS_SoftwareFile SF on
Sys.ResourceID = SF.ResourceID inner join v_GS_OPERATING_SYSTEM OS
on Sys.ResourceID = OS.ResourceID
where SF.FileName like '%'+'.MP3' and SF.FileSize/1024/1024 > 0
Group by Sys.Name0, Sys.User_Name0, OS.Description0
having Count(SF.FileName) > 50 order by Sys.Name0 Desc- Proposed As Answer byFabrizio Guaitolini Monday, October 19, 2009 8:07 AM
- Marked As Answer byKaspars_ Monday, October 19, 2009 8:34 AM
- Thanks.
I also figured it out.
By the way do you know how to narrow column width in reports? - That should work:
SELECT
Sys.Name0,
Sys.User_Name0,
COUNT(SF.FileName) AS FileCount,
SUM(SF.FileSize / 1024 / 1024) AS 'Wasted Megs',
v_GS_OPERATING_SYSTEM.Description0
FROM
v_R_System AS Sys
INNER JOIN
v_GS_SoftwareFile AS SF
ON Sys.ResourceID = SF.ResourceID
INNER JOIN
v_GS_OPERATING_SYSTEM
ON Sys.ResourceID = v_GS_OPERATING_SYSTEM.ResourceID
WHERE
(
(SF.FileName LIKE '%' + '.MP3')
AND
(SF.FileSize / 1024 / 1024 > 0)
)
GROUP BY Sys.Name0, Sys.User_Name0, v_GS_OPERATING_SYSTEM.Description0
HAVING (COUNT(SF.FileName) > 50)
ORDER BY Sys.Name0 DESC Description column is too width.
- you can use substring to include only first n charaters from descriotion
for example substring(OS.Description0,1,15) dispaly only 15 charaters
select Sys.Name0, Sys.User_Name0,
substring(OS.Description0,1,15) as Description,
Count(SF.FileName) as FileCount,
Sum(SF.FileSize/1024/1024) as 'Wasted Megs'
from v_R_System Sys INNER JOIN v_GS_SoftwareFile SF on
Sys.ResourceID = SF.ResourceID inner join v_GS_OPERATING_SYSTEM OS
on Sys.ResourceID = OS.ResourceID
where SF.FileName like '%'+'.MP3' and SF.FileSize/1024/1024 > 0
Group by Sys.Name0, Sys.User_Name0, OS.Description0
having Count(SF.FileName) > 50 order by Sys.Name0 Desc- Proposed As Answer byFabrizio Guaitolini Monday, October 19, 2009 8:33 AM
- No
You understand me wrong.
When I open my report. Description column is too width. It is 3 times wider than text in it. - Are you using Reporting Services or pre R2 Reports?
I am not using Reporting Services.
- As soon as I know you can specify column width in using Reporting Services but not using reports pre R2.
If you edit HTML source of your report you should find that no column width is enforced in HTML table, it is responsability of the browser to display content based on column content and page size.
May be you have a description full of spaces? - In computer description I write users Name and Surname thats it nothing else.
