Just to addd few more things
In that Support group there are 29 users
If in that week or in that month, say the user20 has not yet all created any incident/s then that user20 will not appear in the report. But I need to see this user20 with the value as 0 for his/her CNT
so basically all 29 users needs to be listed and irrespective of the incident creation and let it show the CNT if availble or 0 if not.
In the month we have overlapping weeks
the report always starts from Monday to Sunday
So in the October week 1 the Monday starts in the month of october, so it is half September and half october.
How to fix in the report for populating in such data?
Within the Crosstab, when you group by a date field it is possible to change what period of time to group by, the default is a single day.
In Crosstab Expert select the field you are grouping by and press the Group Options button, this will give you the option to group the date field by Wekk, Month, Year, etc.
To get all Users on a report regardless of whether they have logged an Incident, you will need to use an Outer Join on the link between pm_process and tps_user.
The Left or Right setting should be the side tps_user is on, so you are selecting all values from the table on that side. Because tps_user holds Analysts, End Users, etc, you will need to select the correct Users.
thank you for your reply.
I checked the "Group Option" and that button enables itsself when the row value is selected and it is disabled for the column.
Actually, the dates are placed on the column (horizontal data) and rows contains Analysts names (vertical data)
This is the format we need.
There are two "Group Options" buttons, one under Row and one under Column.
Sounds like your looking at the wrong one.
I have seen that button after posting here. Still the button was not enabling when i selected the column value, I deleted the Cross tab and re-added it, still not working...
So I created new report, I can see that enabled.
Thanks a lot for the hint. really helped.
About the all users displaying in the report irrespective of the creation of incident, showing total count or 0 (zero)...
just changing the join type i dont think that will work...
even I have tried writing the SQL query.. which did not work using the joings, i wrote it in different way to see the complete list which is showing as i need, but i cannot replicate this query like in the Crystal reports.
Becuase, the entry of that Analyst/User comes in the list only if the entry is found in the DB, if not then the name is not selected using the join and grouping on them - to get the final total.
any further thoughts?
(thanks lot for the inputs)
Attached is a very basic report showing all the Users from the tps_user table, with a count of the Incidents they have created (in this test database only SA and Email have created Incidents).
In the Table Links you can see tps_user is joined to pm_process with a LEFT OUTER JOIN to select all records from tps_user. pm_process is joined to IM_INCIDENT with a LEFT OUTER JOIN also because pm_process is on the other side of an OUTER JOIN. Any further tables joined to pm_process or im_incident should also be using OUTER JOIN because if a User doesn't have an Incident then it won't have a record on the additional tables.