Adding A default value in a column in a declare statment

Hi Can anybody help me.

I have this querey at the minute but when I attempt to put the default value of IND in the Sales Rep Code field I get an error Invalid column name, if I try and put 'IND' AS [Sales Rep Code] I get an incorrect syntax error.

DECLARE @.Query nVarchar(1000)
SET @.Query = N'SELECT NULL AS [GEO UNIT], NULL AS [PMC Invoice Date], IND AS [Sales Rep Code] FROM '+ 'Test' + RIGHT(DATEPART(yy, GETDATE()), 2) + '_' + CASE WHEN DATEPART(m, GETDATE()) IN ('11', '12', '1')
THEN 'Q1' ELSE CASE WHEN DATEPART(m, GETDATE()) IN ('2', '3', '4') THEN 'Q2' ELSE CASE WHEN DATEPART(m, GETDATE()) IN ('5', '6', '7')
THEN 'Q3' ELSE CASE WHEN DATEPART(m, GETDATE()) IN ('8', '9', '10') THEN 'Q4' END END END END + '.dbo.all_data' EXECUTE sp_executesql @.Query, N'@.level tinyint', @.level = 35

Can anybody please help on this.

ThanksHi Can anybody help me.

I have this querey at the minute but when I attempt to put the default value of IND in the Sales Rep Code field I get an error Invalid column name, if I try and put 'IND' AS [Sales Rep Code] I get an incorrect syntax error.

DECLARE @.Query nVarchar(1000)
SET @.Query = N'SELECT NULL AS [GEO UNIT], NULL AS [PMC Invoice Date], IND AS [Sales Rep Code] FROM '+ 'Test' + RIGHT(DATEPART(yy, GETDATE()), 2) + '_' + CASE WHEN DATEPART(m, GETDATE()) IN ('11', '12', '1')
THEN 'Q1' ELSE CASE WHEN DATEPART(m, GETDATE()) IN ('2', '3', '4') THEN 'Q2' ELSE CASE WHEN DATEPART(m, GETDATE()) IN ('5', '6', '7')
THEN 'Q3' ELSE CASE WHEN DATEPART(m, GETDATE()) IN ('8', '9', '10') THEN 'Q4' END END END END + '.dbo.all_data' EXECUTE sp_executesql @.Query, N'@.level tinyint', @.level = 35

Can anybody please help on this.

Thanks

would help us if u could post the ddl for the table|||what is the dataytpe of [Sales Rep Code]?
Off the cuff, it looks like your IND value is supposed to be a string.
Your example would be OK if you use 'IND' as [Sales Rep Code] - with the single quotes around IND.|||hi,

The datatype for SalesRep code is not defined as its in a view and I am creating the column [Sales Rep Code] with the default value of IND,

e.g

Select 'IND' AS [Sales Rep Code] from test would create a [Sales Rep Code] populated by IND for each row.

I have tried the 'IND' and it appears that it is incorrect syntax, this is beacuse it is closing the string value of the declare statment at the fist ' but I dont have a way around this.

Any Ideas|||Hi thanks for everyones help finally got it working but I still have one problem in that it is over 4000 carachters long and the @.test has a maximum of 4000 carachters is there any way around this.

Thanks

Actual and Forecast as One Measure

Hi, there,

I have a measure, Sales Amt which consists of 12 mths from Jan to Dec. Let's say, now is Feb. Sales Amt for Jan is actual amt and Sales Amt from Feb onwards are considered forecasts. Currently, forecast Sales Amt is calculated using Excel formula. I have 12 columns (12 mths) for Sales Amt. Every mth, users will overwrite the forecast with the actual amt.

Is there a way to show both actual Sales Amt and forecast Sales Amt as one measure if I define the formula to calculate the forecast amt in the cube?

Thank you.

Regards,

Yong Hwee

Yes, this can be done. The best way to do it is to write something like that:

SCOPE ( [Date].[February] : NULL );

[Sales Amt] = [Forecast Sales Amt];

END SCOPE;

|||

Hi, Mosha,

Thank you for your reply. One more question, how about keeping several 'versions' of calculations? For instance, the formula to calculate the forecast sales amt may varied from time to time. So, in Feb, the forecast sales amt may be defined by formula A but when it comes to June and onwards, it may be defined by formula B. So, when users retrive the values for diff mths, SSAS must be able to calculate accordingly whether to use formula A or B.

Thank you.

Regards,

Yong Hwee

|||

([Feb]:[May], [Sales Amt]) = formulaA;

([Jun]:NULL, [Sales Amt]) = formulaB;

|||

Hi, Mosha,

Thank you for your immediate reply. Sorry for my ignorance. Currently, in the Excel application, users maintain a set of workbooks for every report version. FYI, we have Business Plan, Forecast and Actual report versions. The formulas used for calculation vary not only between diff period, but also vary among report versions. I will only talk about the Actual version of reports.

([Feb]:[May], [Sales Amt]) = formulaA;

([Jun]:NULL, [Sales Amt]) = formulaB;

The above formulas will be used in Actual Report A (Jan as actual mth and Feb~Dec are forecast mths). When it comes to next mth, Feb, users will prepare Actual Report B (Feb as actual mth and Mar~Dec are forecast mth). In Feb, the formula above will not be valid as I already have actual amt for Feb. Do I need to change the script every mth?

Also, there is another requirement. The results of all submitted reports should not change even the formula most likely to change over the time. In other words, changing the formula should not affect the results of the past submitted reports.

Thank you.

Regards,

Yong Hwee

|||

1. You can have Scenario dimension with Actual, Plan and Forecast members, and scope on each one of them for different rules

2. You can parametrize the current month - this way you won't need to change the MDX Script every month.

3. The requirement about locking the values is more difficult. You will need to do something about it, like writing the results into another table. There is no built-in support for that in AS, and cell writeback is probably not going to work very well in this case either.

|||

Hi, Mosha,

Thank you for your reply. I have got some ideas now.

Thank you.

Regards,

Yong Hwee

|||

Hi, Mosha,

I refer to your previous message in which you mentioned about parameterize the current mth. How should I pass the value for the parameter?

Thank you.

Regards,

Yong Hwee