Hi

Thanks for that.

Current using a mixture of 2012 and 2014.

Thanks for reply.

Can you point me in a direction on creating a script like this.

Fairly new at using script transformations.

All sorted.

Assigned the value to a variable and passed this value to the child's package parameter via the Parameter bindings section of the Execute Package Task Editor.

Thanks, will give it a go.

There already is written documentation 😀

Hi

I agree with you about the NOLOCK statement. But its the standards set by the DBAs to use on the report server. so I have to follow them!

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...

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...

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...

Hi

Tried removing the SUM function, but still no luck!

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...

Sorry my mistake, guess it was a typo.

The statement should have been =SUM(Fields!ThisWeekLatestViewSales.Value)/Fields!TaxRate.Value

Hi

Brackets seem fine as I only need an overall sum of the first field.

Thanks

Hey

Thanks, it worked!

Hi

Thanks for that, got it working in the end by using

((ISNULL(Registered_DateTime) ? (DT_DBTIMESTAMP)"9999-12-31" : Registered_DateTime) != (ISNULL(Dest_Registered_DateTime) ? (DT_DBTIMESTAMP)"9999-12-31" : Dest_Registered_DateTime))