troubleshooting Question

SSRS/SP Varchar/Money

Avatar of adraughn
adraughnFlag for United States of America asked on
Microsoft SQL Server 2005
3 Comments1 Solution1589 ViewsLast Modified:
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





ASKER CERTIFIED SOLUTION
Megan Brooks
SQL Server Consultant

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 3 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros