November 18, 2011 at 7:09 am
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
November 18, 2011 at 7:21 am
Extra or missing bracket (depending on the calc you want) unless it's a typo?
November 18, 2011 at 7:31 am
Hi
Brackets seem fine as I only need an overall sum of the first field.
Thanks
November 18, 2011 at 7:45 am
rcr69er (11/18/2011)
HiBrackets seem fine as I only need an overall sum of the first field.
Thanks
How are the brackets fine?
=SUM ( Fields!ThisWeekLatestViewSales.Value ) /Fields!TaxRate.Value )
There's no opening bracket to match the closing bracket at the end of the expression.
November 18, 2011 at 8:08 am
Sorry my mistake, guess it was a typo.
The statement should have been =SUM(Fields!ThisWeekLatestViewSales.Value)/Fields!TaxRate.Value
November 18, 2011 at 8:13 am
rcr69er (11/18/2011)
Sorry my mistake, guess it was a typo.The statement should have been =SUM(Fields!ThisWeekLatestViewSales.Value)/Fields!TaxRate.Value
Have you tried converting both to CDec?
November 18, 2011 at 8:18 am
Are the fields both in the same dataset, or are either calculated fields?
November 18, 2011 at 8:35 am
Hi
Yes I have tried converting both field with CDec, but still having no luck.
Both fields are coming from the same dataset and are not calculated.
Getting the following warning when using CDec:
[rsRuntimeErrorInExpression] The Value expression for the textrun ‘Textbox161.Paragraphs[0].TextRuns[0]’ contains an error: Conversion from type 'CalculatedFieldWrapperImpl' to type 'Decimal' is not valid.
Thanks
November 18, 2011 at 8:38 am
It might not like trying to divide an aggregated field by a non-aggregated field in the same dataset.
Does the calc work without the SUM?
November 18, 2011 at 8:41 am
Any non-numerical data in either of them?
November 18, 2011 at 8:45 am
Hi
Tried removing the SUM function, but still no luck!
November 18, 2011 at 8:53 am
Doesnt look like it, I've pasted the query for the dataset below.
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
November 18, 2011 at 9:00 am
Doesnt look like it, I've pasted the query for the dataset below.
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
November 18, 2011 at 9:21 am
What's the datatype of the WeeklyPlanMix.FinanceForecastExVAT field?
Separately, it's the references to 'CalculatedFieldWrapperImpl' that I don't like - looks like RS is doing something odd with the value.
suggests multivalued parameters may be a cause - is the solution there any use to you?
November 18, 2011 at 9:21 am
Sorry, but without DDL and sample data your query doesn't really mean anything.
However, while I'm looking at it. . . you know that NOLOCK is not the magic go faster button? It means you are performing dirty reads so your returned data can not be guarantee'd as accurate.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply