one cell for each date between start and end date

94 Views Asked by At

I searched the web for an answer to this question but wasnt able to find one. I am trying to create a Dashboard using SS2012 Report builder 3.0. My Table looks kind of like this:

Machine Task    Start_Date  End_Date

M01     T01     01.11.2014  02.11.2014
M01     T02     02.11.2014  05.11.2014
M02     T02     01.11.2014  01.11.2014

The Dashboard is essentially a matrix, where the row headers are 'Machine' and 'Task' while the column headers are the Dates.
The cells in the matrix consist of Information about the Tasks (not shown in my example Database) What I am trying to achieve is, that the contents of the cells are shown in every Date-column between the start and end_date of a task.
One way to theoretically solve this problem would be dublicating every row for each day it is active, but that would create a huge table.
It would be great if someone could provide me with an idea how to solve this problem.

I am still working on this problem and my best approach still seems to be creating new rows for a task if Start_Date <> End_Date

I tried working with the INSERT INTO command, but that does not seem to work with datasets but only with tables. Something like

IFF(Fields!Start_Date.Value <> Fields!End_Date.Value, INSERT INTO dataset  
VALUES(Fields!Machine.Value, Fields!Task.Value, (Fields!Start_Date+1),Fields!End_Date.Value), <Do Nothing>)
1

There are 1 best solutions below

0
On BEST ANSWER

I found a similar question regarding the problem with duplication rows for each date between start_date and end_date which is already answered. For those who also had this problem click here