asked on

SSRS/SP Varchar/Money

I have been using this stored procedure with my access front end and reporting with no problems. i am migrating my reports to SSRS. In SSRS, when I create a chart from this SP (charting dollars), it won't chart them. I know it is referencing my data because if i do a count, i get 1 charted for each month. I am assuming this is because my dollars are actually formatted as varchar to get the dollar sign. But Access charted it fine, why wouldn't SQL?

Anyway, I am open to ideas. Here is my sp:

ALTER PROCEDURE [dbo].[upOrders_SumCRT]
(
@StartDt datetime,
@EndDt datetime
)
AS
--Create temp table 1
SELECT LEFT( DATENAME( MONTH, Report_M_Date ), 3 )+ '-' +
RIGHT( DATEPART( YEAR, Report_M_Date ), 2 ) As Expr3, vo.Expr1,
SUM(NetPrice) AS TotalNetPrice,Report_M_Date
INTO #TEMP
FROM dbo.vOrders vo
WHERE Report_M_Date BETWEEN @StartDt AND @EndDt
GROUP BY vo.Report_M_Date, vo.ReportMonth, vo.Expr1
--create crosstab
SELECT Expr3,
'Non-Warranty / Exchange' = '$' + CONVERT(varchar(12),(SUM (CASE Expr1
WHEN 'Non-Warranty / Exchange' THEN TotalNetPrice ELSE '' END)),1) ,
'Non-Warranty / Repair' = '$' + CONVERT(varchar(12),(SUM (CASE Expr1
WHEN 'Non-Warranty / Repair' THEN TotalNetPrice ELSE '' END)),1) ,
'Warranty / Repair' = '$' + CONVERT(varchar(12),(SUM (CASE Expr1
WHEN 'Warranty / Repair' THEN TotalNetPrice ELSE '' END)),1) ,
'Warranty / Exchange' = '$' + CONVERT(varchar(12),(SUM (CASE Expr1
WHEN 'Warranty / Exchange' THEN TotalNetPrice ELSE '' END)),1)
FROM #TEMP
GROUP BY Expr3,Report_M_Date
ORDER BY Report_M_Date

RichardCorrie

try changing the sum statements to have 0 in the else instead of ''
eg
SUM (CASE Expr1
WHEN 'Non-Warranty / Exchange' THEN TotalNetPrice ELSE '' END)
to
SUM (CASE Expr1
WHEN 'Non-Warranty / Exchange' THEN TotalNetPrice ELSE 0 END)

/Richard

adraughn

ASKER

The problem was with the varchar, I fixed it by changing my sp. apparently sql can't recognize my varchar as money in SSRS like access did. This is what I ended up with:

ALTER PROCEDURE [dbo].[upOrders_WEBSumCRT]
(
@StartDt datetime,
@EndDt datetime
)
AS
--Create temp table 1
SELECT LEFT( DATENAME( MONTH, Report_M_Date ), 3 )+ '-' +
RIGHT( DATEPART( YEAR, Report_M_Date ), 2 ) As Expr3, vo.Expr1,
SUM(NetPrice) AS TotalNetPrice,Report_M_Date
INTO #TEMP
FROM dbo.vOrders vo
WHERE Report_M_Date BETWEEN @StartDt AND @EndDt
GROUP BY vo.Report_M_Date, vo.ReportMonth, vo.Expr1
--create crosstab
SELECT Expr3,
'Non-Warranty / Exchange' = (SUM (CASE Expr1
WHEN 'Non-Warranty / Exchange' THEN cast(TotalNetPrice/1000 as int) ELSE '' END)) ,
'Non-Warranty / Repair' = (SUM (CASE Expr1
WHEN 'Non-Warranty / Repair' THEN cast(TotalNetPrice/1000 as int) ELSE '' END)) ,
'Warranty / Repair' = (SUM (CASE Expr1
WHEN 'Warranty / Repair' THEN cast(TotalNetPrice/1000 as int) ELSE '' END)) ,
'Warranty / Exchange' = (SUM (CASE Expr1
WHEN 'Warranty / Exchange' THEN cast(TotalNetPrice/1000 as int) ELSE '' END))
FROM #TEMP
GROUP BY Expr3,Report_M_Date
ORDER BY Report_M_Date

ASKER CERTIFIED SOLUTION

Megan Brooks

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial