SSRS/SP Varchar/Money

adraughn
adraughn used Ask the Experts™
on
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





Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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

Author

Commented:
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
SQL Server Consultant
Commented:
You can often pass the original table values through in the dataset and let SSRS do the formatting using .NET formatting codes, such as "$####0". Formatting for the chart labels is controlled in the chart properties, on the X Axis and Y Axis tabs. It is also possible to do formatting in expressions that retrieve data from the report dataset. For example, if you are using an expression "=Fields!Fieldname.Value", you can change it to "=Format(Fields!Fieldname.Value,"formatcode")". Expressions are VB.NET code, and can perform many different types of formatting.

When you have one query feeding another, instead of doing a SELECT INTO into a temp table, you can specify the first query as a subquery in the FROM clause of the second:
SELECT ...
FROM (SELECT ... FROM ...)
Just enclose the subquery in parentheses and it will be treated as if it were a table.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial