[wwilliams]

I am creating a matrix of tasks and the week due. I'd like to loop through vs. hardcoding for all 52 rows (weeks) date1 to date52
I am using this, if (BirtDateTime.week(dataSetRow["date12"]) ==6) (6 corresponds to week 6)
is there a way I can (I have tried many combinations) use a variable for the dataSetRow?
I have also tried utilizing an array, but the values do not reflect the current row when I use it in the detail section of the report.

if (!DataSet.fetch())
	return (false);
.
.

row["date51"] = DataSet.getTimestamp("date51");
row["date52"] = DataSet.getTimestamp("date52");
row["laststartdate"] = DataSet.getTimestamp("laststartdate");	

myArray[1] = row["date1"];
myArray[2] = row["date2"];
myArray[3] = row["date3"];

Is there a means to accomplish this with a crosstab?
I'd like columns reflecting weeks or months, rows reflecting the equipment and the scheduled tasks under the appropriate column.


TIA

[mwilliams]

Can you explain what you're trying to do, a little more in detail?

[wwilliams]

mwilliams, on 02 February 2012 - 09:34 PM, said:

Can you explain what you're trying to do, a little more in detail?

I will give it a shot.
The end result is a report that would show weekly or monthly detail in the following format:

equipment week1 week2 week3 week4 .....
eq1 data data data data
eq2 nodata data nodata
eq2
eq3
.
.
.
It is a visual depiction of what scheduled work and week/month it is due.
I originally used an array, but I didn't find a way to keep the results in a single row for each piece of equipment.
I then changed the SQL to provide 52 columns (one row problem solved), but then I had the task of filtering for each corresponding week.The only solution I found was to hard-code the lot, which I don't want to do.
I guess the plus is I am learning BIRT. BIRT 2.3.2

-W

[mwilliams]

Before writing the SQL to get 52 columns, what did your data look like? Did you ever try a crosstab before using SQL to make you 52 columns? The output you're wanting looks a lot like what a crosstab would do for you if you had data somewhat like:

equipment | project | due date
eq1 | p1 | date1
eq1 | p2 | date2
eq2 | p3 | date1
eq2 | p4 | date2
etc.

Let me know.

[wwilliams]

Michael, the data was rows of the dates due and it spans years. I have to process each record for 52 weeks so weekly tasks are covered, but I need to ignore those outside of current year which would be monthly, quarterly etc,.
I first toyed with a cube and a cross-tab, but didn't find a good way to present the due dates.
I have gone back the original SQL with an array and I seem to be making progress.


eq1 date1
eq1 date2
eq1 date3
.
.
eq1 date52
eq2 date1
eq2 date2
eq2 date3
.
.
eq2 date52

[mwilliams]

So, there's no job description, just a due date that needs to be in the table? If your data is like you show above, you can definitely use a crosstab. I'll make a quick example and post it in here when I'm done. Maybe it'll help.

[mwilliams]

Ok, with data similar to what you gave above, I created what I think you're looking for with a crosstab. I used a scripted dataSet to create a dataSet with 4 pieces of equipment with 52 rows each. I made each "due date" a saturday, starting Jan 8, 2011. To make it random as to which equipment had a due date each week, to better simulate your data, I used a random number generator to decide if there was a due date for the given row. If not, I output a null value. I then made a crosstab with equipment as a dimension, the date field as a dimension (I chose week of year for the grouping), and then used the date as the measure. Hopefully this will get you started in the right direction. Let me know if I'm assuming something incorrectly. If I am, please show me a small sample of what your actual data would look like. Thanks!

Attached File(s)

•  crosstabReport.zip (2.27K)
  Number of downloads: 2

[wwilliams]

Hi Michael,
could you possibly provide the example in version 2.3.2?
Thanks.

[mwilliams]

Sure. Here ya go!

Attached File(s)

•  crosstabReport.zip (2.28K)
  Number of downloads: 1

[wwilliams]

Michael, it is looking good!

Thanks from another Williams!

[mwilliams]

No problem. You could clean this up by having another column that definitely has a date for each week and use it as the dimension column. That will get rid of the "0" dimension. Let us know whenever you have questions!