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_Dat
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
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_Dat
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
Microsoft SQL Server 2005
Last Comment
Megan Brooks
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_Dat
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
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_Dat
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
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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