Associating Independent row with a Crosstab

(BIRT 2.3.1) I have a time sheet crosstab where the column dimensions are year/month/day of mth and the row dimension is task/work type. Hours worked is the measure. I need to add a row that indicates manager approval by day for each column. Since the crosstab columns are dynamic, I don't see how to associate the approval flag to the crosstab columns. I have attached a jpg with an illustration of the desired output. I have suggested color coding the unapproved time entries, but the users would prefer printing the approval row. Any suggestions will be appreciated.

Find more posts tagged with

Comments

mwilliams

Hi jasheets,

How does your data look in your dataSet? This way I can run accurate tests on this.

UtahJerry

Here is the Oracle 10i Data set
EMPLOYEE --- string
EMPL_NAME --string
SHOP

string
SCHEDULE_DATE - Date Time (Col Dim)
FUNDING_CODE - string (row Dim 1)
ACTUAL_HOURS - decimal (measure)
POSTED_FLAG --string (Y/N Approval code)
PWORK

string (Row Dim 2)
PLEAVE

string (Row Dim 3)

I have also atttached a screen shot of the report as it currently exists.

Thanks, Jerry Sheets

mwilliams

Jerry,

Can you post a few lines of actual data that I can put in a flat file for testing? This will give me the best chance of coming up with a solution. Thanks.

UtahJerry

Here is the data that matches the screen shot.

Jerry

mwilliams

Jerry,

Are each of the reports individual? Or is there a master report that will have multiple employees on it? I found a way to get the approval code on there for just the one employee, but multiple employees on one report would cause another issue with variable names. It's kinda a hack, but here's what I did. I brought the data into a table so I could set all the approvals to persistent global variables with row["SCHEDULE_DATE"].toString() being the name. You can hide this table so it's not on the report, but I needed it to set the global variables. This also might be able to be done on the dataSet. Then, under the total data element on the crosstab, you can add a dynamic text box. In here, I just used the year, date, and month dimension values to set up the name of the variable in the getPersistentGlobalVariable call and it worked. Attached is the output I got.

UtahJerry

That's exactly what I am looking for, but I do need to be able to run groups. The employee with only need to see their own data, but the manager will select by Shop and HR may want to run the complete list of all employees.

mwilliams

Jerry,

You could probably easily add their employee number or something to the variable name as long as you can access it in the crosstab to be able to create the variable name in the getPersistentGlobalVariable() call.

UtahJerry

Mike, thanks for the time you spent on this, it was well beyond my expectations. Would you send me the rptdesign you used to create the sample output you sent earlier? It would help me understand exactly how you accomplished it. You can post it here or email to me directly at <a href='mailto:' title='E-mail Link' class='bbc_email'>jasheets@utah.gov</a>.

mwilliams

Jerry,

No problem whatsoever. I have attached the BIRT 2.3.1 report design that I showed a screenshot of along with the .csv flat file data that I used. If you have any questions, let me know.