Problems to SUM a column at the footer

[auchtum]

Hello, I have this query:

SELECT Propuesta.IdProspecto, Prospecto.Nombre, '$ ' + CONVERT(varchar(20), SUM(Propuesta.PresupuestoCOP), 1) AS TotalCOP, '$ ' + CONVERT(varchar(20),
SUM(Propuesta.PresupuestoUSD), 1) AS TotalUSD, COUNT(Prospecto.Nombre) AS TotalProspectos
FROM Propuesta INNER JOIN
Prospecto ON Propuesta.IdProspecto = Prospecto.IdProspecto
GROUP BY Prospecto.Nombre, Propuesta.IdProspecto
ORDER BY Prospecto.Nombre



And I'd like to SUM and display at the footer the total of the columns: TotalCOP and TotalUSD.



However at the bottom it displays #Error, How can I fix this?

 

[mrdenny]

Thats because you have SQL Server converting the numbers to a string. Remove the dollar signs and the CONVERT parts and leave the data as numbers. Then have SSRS put the $ on the front of the strings.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2008 Implementation and Maintenance / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Admin (SQL 2005/2008) / Database Dev (SQL 2005)

My Blog