Calculation\Conversion Problem

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

Reporting Services Development

Calculation\Conversion Problem

Author

Topic

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2011-11-18 : 09:18:07

Hi

I am currently having a problem with one of the expressions I have created for a calculation.

The expression is as follow:

=SUM(Fields!ThisWeekLatestViewSales.Value)/Fields!TaxRate.Value)

When I run the report there is the text #Error in the field that I placed the expression in.

The following warning also appears in the error list:

[rsRuntimeErrorInExpression] The Value expression for the textrun ‘Textbox161.Paragraphs[0].TextRuns[0]’ contains an error: Overload resolution failed because no Public '/' can be called with these arguments: 'Public Shared Operator /(d1 As Decimal, d2 As Decimal) As Decimal': Argument matching parameter 'd2' cannot convert from 'CalculatedFieldWrapperImpl' to 'Decimal'.

I’ve found hardcoding the value in place of the Fields!TaxRate.Value part seems to work even tho the warning still remains. But I want to avoid hard coding as the tax rate may change.

=SUM(Fields!ThisWeekLatestViewSales.Value)/ 1.200

I’ve also tried converting the TaxRate field to deciaml using CDec but still having no joy.

Does anyone have any ideas on what I can do?

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-18 : 10:55:42

is this any better?
=SUM(val(Fields!ThisWeekLatestViewSales.Value)/val(Fields!TaxRate.Value))

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2011-11-18 : 11:06:51

Hi

Thanks for the reply but still no luck.

I have pasted the query used for the dataset below.

Thanks

DECLARE @TaxRate DECIMAL(6,3)

SELECT @TaxRate =(TaxRate/100) + 1
FROM dbo.TaxSetup WITH (NOLOCK)
WHERE TaxSetupID = 1

SELECT
cbds.WDID
,cbds.WeekDay
,cbds.LastWeekOrders
,cbds.ThisWeekOrders
,cbds.LastYearLWOrders
,cbds.LastYearTWOrders
,cbds.LastWeekSaleUnits
,cbds.ThisWeekSaleUnits
,cbds.LastYearLWSaleUnits
,cbds.LastYearTWSaleUnits
,cbds.LastWeekRetail
,cbds.ThisWeekRetail
,cbds.LastYearLWRetail
,cbds.LastYearTWRetail
,cbds.LastWeekSalesNet
,cbds.ThisWeekSalesNet
,cbds.LastYearLWSalesNet
,cbds.LastYearTWSalesNet
,cbds.LastWeekCost
,cbds.ThisWeekCost
,cbds.LastYearLWCost
,cbds.LastYearTWCost
,cbds.LastWeekProfit
,cbds.ThisWeekProfit
,cbds.LastYearLWProfit
,cbds.LastYearTWProfit
,cbds.LastWeekForeCast
,cbds.ThisWeekForeCast
,cbds.HourlySalesFCast
,cbds.HourlyOrdersFCast
,cbds.DiscountThisWeek
,cbds.DiscountLastWeek
,cbds.ThisWeekProfitPlan
,cbds.LastWeekProfitPlan

,ThisWeek.FinanceForecastExVAT AS 'ThisWeekLatestViewSales' --LV Sales
,LastWeek.FinanceForecastExVAT AS 'LastWeekLatestViewSales'
,(cbds.ThisWeekRetail - ThisWeek.FinanceForecastExVAT) / ThisWeek.FinanceForecastExVAT AS 'ThisWeekActualSalesVsLatestView' --Actual Sales Vs LV%
,(cbds.LastWeekRetail - LastWeek.FinanceForecastExVAT) / LastWeek.FinanceForecastExVAT AS 'LastWeekActualSalesVsLatestView'
,(cbds.ThisWeekRetail - cbds.LastYearTWRetail) / cbds.LastYearTWRetail AS 'ThisWeekActualVsLastYear'
,(cbds.LastWeekRetail - cbds.LastYearLWRetail) / cbds.LastYearLWRetail AS 'LastWeekActualVsLastYear'
,ThisWeek.FinanceForecastMargin AS 'ThisWeekCashMarginLatestView'
,LastWeek.FinanceForecastMargin AS 'LastWeekCashMarginLatestView'

,ThisWeek.FinanceForecastMargin /(ThisWeek.FinanceForecastExVAT / @TaxRate) AS 'ThisWeekMarginLatestView'
,LastWeek.FinanceForecastMargin / (LastWeek.FinanceForecastExVAT / @TaxRate) AS 'LastWeekMarginLatestView'

,cbds.ThisWeekProfitPlan / (cbds.ThisWeekForeCast / @TaxRate) AS 'ThisWeekMarginForecast'
,cbds.LastWeekProfitPlan / (cbds.LastWeekForeCast / @TaxRate) AS 'LastWeekMarginForecast'

,@TaxRate AS 'TaxRate'

FROM dbo.cbasedaliysales AS cbds WITH (NOLOCK)

INNER JOIN(

SELECT
wpm.WeekID
,wpm.DateKey
,wpm.MixID
,wpm.[WeekDay]
,wpm.[Percent]
,wpm.WeekType
,wpm.FinanceForecast
,wpm.FinanceForecastMargin
,wpm.FinanceForecastExVAT

FROM dbo.WeeklyPlanMix AS wpm WITH (NOLOCK)
INNER JOIN DataWarehouse.dbo.Dim_Time_Week AS dtw WITH (NOLOCK)
ON wpm.WeekId = dtw.Week_ID
AND dtw.Week_Flag = 0

) AS ThisWeek
ON cbds.WeekDay = ThisWeek.WeekDay

INNER JOIN(

SELECT
wpm.WeekID
,wpm.DateKey
,wpm.MixID
,wpm.[WeekDay]
,wpm.[Percent]
,wpm.WeekType
,wpm.FinanceForecast
,wpm.FinanceForecastMargin
,wpm.FinanceForecastExVAT

FROM dbo.WeeklyPlanMix AS wpm WITH (NOLOCK)
INNER JOIN DataWarehouse.dbo.Dim_Time_Week AS dtw WITH (NOLOCK)
ON wpm.WeekId = dtw.Week_ID
AND dtw.Week_Flag = 1

) AS LastWeek
ON cbds.WeekDay = LastWeek.WeekDay

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-18 : 11:42:48

still the same error?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2011-11-18 : 12:01:37

Hi, yes still the same error

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-18 : 12:06:46

are you sure taxrate is purely numeric or does it have some non numeric part too?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2011-11-18 : 12:18:32

Hi

The column that is being referenced from the taxsstup table always references a numerical value.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-18 : 12:28:42

first print the quantities seperately in two cells and see what they return

=SUM(val(Fields!ThisWeekLatestViewSales.Value))

=SUM(val(Fields!TaxRate.Value))

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2011-11-21 : 03:49:34

HI

I get the values 150000 and #Error.

Even if I remove the val statement from the TaxRate field it still states #Error.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-21 : 03:52:52

that means TaxRate is having nonumeric data

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2011-11-21 : 04:13:02

Hi

The same seems to be occuring if I put it in a table by itself without any mathematical functions.

Thanks

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2011-11-21 : 04:30:12

Ok I think I have found the solution.

I created a new dataset with just the tax value and used it the calculation below:

(SUM(Fields!ThisWeekLatestViewSales.Value)/First(Fields!TaxRate.Value, "Tax"))

Seems to work now.

Thanks for the help with this.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-21 : 04:43:32

quote:
Originally posted by rcr69er

Ok I think I have found the solution.

I created a new dataset with just the tax value and used it the calculation below:

(SUM(Fields!ThisWeekLatestViewSales.Value)/First(Fields!TaxRate.Value, "Tax"))

Seems to work now.

Thanks for the help with this.

hmm...thats strange

How then this threw error?

=SUM(val(Fields!TaxRate.Value))

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources