Create the mounth from a year column

building semantic layer
1

Hi,

My question may be impossible but is it possible to get the mounths from a column year. I have a table for budgets that contain a column budget and year.

I have created two objects in my universe:

  • budget amount
  • year

I want to create a mounth object that will contain all mounths of a year as it will divide the budget later on 12 (one for each mounth).

Is it possible?

Regards,

zizou :tunisia: (BOB member since 2008-07-31)

2

No.

You can work around it but basically you’ve got 2010 as a single value.
You can split it up at the database level and indeed should do.

3

Ok you mean that I have to do the work at the database level by creating a new column mounth.

Thank you!

zizou :tunisia: (BOB member since 2008-07-31)

4

Hi,

You can try forcing a Cartesian product between your year budget table and a dummy table that contains 12 rows (1 row per each month). So the query (for Oracle DB) will look like this:

  SELECT year_budget_table.year,
         my_dummy_table.month, 
         ROUND (year_budget_table.budget / 12, 2) AS monthly_budet
    FROM ( SELECT 2008 AS year, 100 AS budget
             FROM DUAL
           UNION ALL
           SELECT 2009 AS year, 110 AS budget
             FROM DUAL
           UNION ALL
           SELECT 2010 AS year, 120 AS budget
             FROM DUAL
         ) year_budget_table,
         ( SELECT ROWNUM AS month
             FROM all_objects
            WHERE ROWNUM <= 12
         ) my_dummy_table
ORDER BY   1, 2

Marek Chladny :slovakia: (BOB member since 2003-11-27)

5

It would be better for you to do so.
Create a budget table and you can plot graphs of budget v actual. Yes, budget may be a straight line, but it will be easy for you to build a graph and/or alerters about figures significantly under or over budget.

6

For the moment, I have not right to touch the data base.

Marek I have tried to creat a derived table that using your code (after converting it to SQL server) but it tells me that the code is wrong:


SELECT
jiraschema.PROJECT_BUDGET.ANNEE,
my_dummy_table.month,
ROUND(jiraschema.PROJECT_BUDGET.BUDGET_DISCRETIONNAIRE / CONVERT(FLOAT, 12), 2) AS monthly_budet
FROM (	SELECT 2008 AS year, 100 AS budget
		UNION ALL
	 	SELECT 2009 AS year, 110 AS budget
		UNION ALL
	 	SELECT 2010 AS year, 120 AS budget 
	) jiraschema.PROJECT_BUDGET,
		(	SELECT TOP 12
		FROM  all_objects 	
	) my_dummy_table 
	ORDER BY 1, 2

do you know the reason?

Regards,

zizou :tunisia: (BOB member since 2008-07-31)

7

Possible reasons:

  1. You don’t use this in the query
(   SELECT 2008 AS year, 100 AS budget
      UNION ALL
       SELECT 2009 AS year, 110 AS budget
      UNION ALL
       SELECT 2010 AS year, 120 AS budget
   )

you just need to replace it with your real table where year budger are stored

  1. The month column from the inner select (my_dummy_table) is used in the outer select. However, your inner select selects TOP 12 - I think you need to add a column alias so the outer selects “knows” it as the month column.

Marek Chladny :slovakia: (BOB member since 2003-11-27)

8

Here what I did for your first point :


SELECT 
jiraschema.PROJECT_BUDGET.ANNEE, 
my_dummy_table.month,
ROUND(jiraschema.PROJECT_BUDGET.BUDGET_DISCRETIONNAIRE CONVERT(FLOAT, 12), 2) AS monthly_budget 
FROM jiraschema.PROJECT_BUDGET, 
      (   SELECT TOP 12 
      FROM  all_objects     
   ) my_dummy_table 
   ORDER BY 1, 2

But I don’t get what I can do to fix the second point.

Regards,

zizou :tunisia: (BOB member since 2008-07-31)

9

Try this:

SELECT jiraschema.PROJECT_BUDGET.ANNEE,
       my_dummy_table.month,
       ROUND(jiraschema.PROJECT_BUDGET.BUDGET_DISCRETIONNAIRE CONVERT(FLOAT, 12), 2) AS monthly_budget
  FROM jiraschema.PROJECT_BUDGET,
       ( SELECT TOP 12 AS month
           FROM  all_objects     
       ) my_dummy_table
ORDER BY 1, 2

If it did not work then perhaps all_objects system table does not exists in SQL server, I don’t know.

Marek Chladny :slovakia: (BOB member since 2003-11-27)