Design a report in a crosstab

[maas]

Hello Everybody,

I have a problem in designing reports using cross-tabs. My requirement is that I want a report to show the latest updated data with according to the date & time and ignore the history records.
In a more clear way, I have departments (IT-Finance-HR ...etc), employees and Status (Approved-Rejected-No Action) and date & time for every status.

Now, I want to show the departments, employees and the count for each status for each employee ( Suppose 10 Approved-20 Rejected...etc).But, my condition is that to ignore the previous status and count the latest Status according to date & time field).

I developed the report and choose distinct(count) for the number of employees, but the result is calculating the whole number of records for each employee with the history, but not the latest one.

Could you please help me to have the condition with the maximum date and where to put it?

Thanks for your help.

 

[lbass]

Create a SQL expression {%maxdttm} like this:

(
select max(`datetime`)
from table A
where A.`employee` = table.`employee` and
A.`dept` = table.`dept`
)

Then in the record selection formula use:

{table.datetime} = {%maxdttm}

This will return one status per employee per department.

-LB

 

[maas]

Thank you lbass, I will try this and will come back

 

[maas]

When I am trying to get the count for each status....

E.x (an employee was rejected and then approved), so the count will be decreased from the rejected and incremented in the approval.

Is the above query will give the expected result or I have to apply more record selections??

 

[lbass]

The above SQL expression would return only the most recent record per employee--so only the most recent status would be counted. If that is NOT what you meant please provide some sample data at the detail level as it appears now and then show how you want it to appear after filtering.

-LB

 

[maas]

Hello lbass,

I tried that thing, but it did not work properly (I might did something wrong), but fixed it with having a view with the help of my colleagues.

The view is having all the required data (name, modified_date&time,category, status).Now the crosstab ((columns)) have the values of (Approved-Rejected-Submitted)correctly.

On the left, there are the categories ( IT-Finance-HR ....etc) and the report is showing the correct values(numbers) for the approved, rejected, submitted for each category.

Now I have been asked to add one more column (formula)next to the total which is being provided automatically while creating the crosstab.

The formula is to deduct the Grandtotal-Submitted. So, How can I get this total?

In the crosstab, only I can see one field which I can edit it which is the sum of the employees and the other fields as I mentioned it is listing categories.

 

[lbass]

Are you looking for the total - submitted for each category?

Instead of using status as a column field, use it in conditional formulas and add one formula per status as a summary field as in:

if {table.status} = "Approved" then {table.Noofemployees} //or 1 depending upon your data

Repeat for each status. Add the sum of each formula as a summary. Make sure that the total column is on the right (go to the customize style tab and uncheck "show column totals on the left". Then create a fourth formula {@0} which is simply:

whilereadingrecords;
0

This is a holder formula. Add it as your fourth summary. Then select the summary for {@Submitted}->right click->format field->suppress->x+2 and enter:

whileprintingrecords;
numbervar submitted := currentfieldvalue;
false

Then select the row total->right click->format field->suppress->x+2 and enter:

whileprintingrecords;
numbervar tot := currentfieldvalue;
false

Then select {@0}->right click->format field->DISPLAY STRING->x+2 and enter:

whileprintingrecords;
numbervar submitted;
numbervar tot;
totext(tot-submitted,0)

Then suppress the inner cell and label for {@0} and grab the borders and resize it to make it narrow (I'm assuming you are showing the summaries horizontally).

-LB