October 10, 2004 at 7:42 pm
Hi, New'ish to Cubes.
Basic Cube (Facts & Dimensions working fine).
I want to report over a host of dimensions (Region, Gender, Status & Age). All except AGE working well.
AGE issue - I have a DOB field in the SQL Datasource and want to know if I can calculate AGE (and ideally Age by groups of 5 yrs) without adding a new field in the data source & using T-SQL to calculate & update such a new field.
Can Cubes calculate something like this at Process time.
Thanks in advance. M.
October 10, 2004 at 10:33 pm
I would create a view but you could just use the same code within the field source definition in the cube editor.
Adding a view with the following should get you close to what you want:
SELECT
[field 1],
[field 2],
........,
DATEDIFF(yy, [DOB], GETDATE()) AS [Age],
CASE
WHEN DATEDIFF(yy, [DOB], GETDATE()) BETWEEN 0 AND 5 THEN '0 - 5'
WHEN DATEDIFF(yy, [DOB], GETDATE()) BETWEEN 6 AND 10 THEN '6-10'
WHEN DATEDIFF(yy, [DOB], GETDATE()) ........
ELSE 'Some Catchall statement here'
END AS [Age_Brackets]
FROM
[Some_Data_Table]
To do the same in AS, just drag the [DOB] field in as the source for the two levels (Bracket and Age) and then change the key and label SQL to reflect what you want. When you do this, you'll prob see that doing it in the tiny field in AS versus using Query Analyser to write the SQL, it's preferable to us QA.
Steve.
October 11, 2004 at 8:22 am
Use AS to create a calulated member and don't forget to set it up as a changing dimension.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply