You need to TRY_CAST the columns before you add/sum them.

SUM(TRY_CAST(hts_a_value AS NUMERIC) + TRY_CAST(hts_b_value AS NUMERIC)) AS New,

What is the source data like? If the numbers are...

Does this work? I don't know if there will ever be spaces, but I removed them anyway.

DECLARE @InputString VARCHAR(1000) = '5, 1,6, 1,69,1, 123456, 9'
SELECT REPLACE(CONCAT('''', REPLACE(@InputString,...

Yes, I assumed the problem was on my end and went hunting through my junk folder, but didn't find anything.

It looks like you will need to group by Code and Store, Sum (Value) and use cross apply  select... for...xml path.

I've never been able to write that piece of code...

It sounds like it may be using the service account to connect to the database. If the cube is already deployed, you should be able to edit the Security Settings...

It sounds like you are connecting to a Tabular instance.

If you right click the server name in the SSMS object explorer, the properties should tell you the server mode. If...

If this is Oracle I think the case statement syntax is less flexible than t-sql. I think it needs to be in this form. (That's the only way I have...

What data type is IUWEDT? At the moment the variables are inetgers and your code for last Saturday is returning a datetime.

if IUWEDT is an integer try this, otherwise try...

This is one possible method. Identify the coming Saturday and go back 2 weeks, 53 weeks and 105 weeks.

DECLARE @DateKey1 INT,
      ...

I failed to realise the importance of the Prg1 filter. Is this better? It returns all the rows, but only counts where Prg1 = 'P1'.

I had to choose an order...

Does this work? Replace the NULL with 0 if preferred.

SELECT t2.VID, t2.OrdDate, t2.Prg1,
       CASE WHEN t2.RowNum = 1 THEN VIDCount ELSE...

What is the .xxx part? do you mean it needs to be an integer?

Does this work using Phil's...

I thought this was interesting...

I think it is implicitly converting the sum of OriginalWeight to a numeric format that is too small. If you explicitly cast it to an int it seems to work...

Does this help? If there are other characters you may be able to identify their NCHAR value with UNICODE.

DECLARE @Column NVARCHAR(100) 
SET     @Column...