Bringing records from separate rows into one

Question

I have the following layout for my report:

where Cost is my dimension measure field calculated for each year at column level for different Products.
Now I need to add the Year field as one of the columns, which is naturally separating the records into different rows like this:

Is there a way that I can have the Year column without splitting my records into separate rows?

Answer 1

If you have a single cost measure then this is just a simple pivot table of product vs year with cost as the measure.

If you have separate cost measures for each year then it gets messy. You can derive a measure using a case statement like

case 
  when "folder"."year" = 2020 then "folder"."cost(2020)"
  when "folder"."year" = 2021 then "folder"."cost(2021)"
...

but this isn't particularly maintainable or performant.

The real solution there would be to implement a single cost measure which leaves you with a simple pivot table

Answer 2

No. Because you are fixing a measure to a certain year and then trying to do a split by dimension members. Your design and your approach are wrong and don't make sense because they are contradictory. You either have measures which themselves represent years or you have a time dimension which does a split by year.

Answer 3

If this is a question about the design of the table, I would suggest something like this:

create table costs (
   product varchar2(30),
   year number,
   cost number
);
insert into costs (product, year, cost) values (´P1´, 2020, 100);

Answer 4

Thanks for all the answers/comments. I got my issue resolved.