merging two sets of data into one bar graph

[deeya]

Hi,

CRXI, SQL server.

Hopefully I can explain this enough for the forum to understand. We have a helpdesk call logging database which we need stats from. The report needs to show calls created and resolved by an analyst in a bar graph (each analyst will have two bars calls resolved count and calls created count) and each graph needs to be grouped by team.

My dilema being how to merge the data from two different tables based off one date range which the user enters(call creation and call resolved).

Tables are as follows:

analyst_table - both tables hold foreign keys to link
team_table - both tables hold foreign keys to link
incident_table - hold all calls regardless of status including the creation date and call id
resolution_table - holds only resolved calls including resolution date and foreign key that links to the incident_table


I understand I may need to add tables more than once havent a clue how or a subreport for each set of data and merge them somehow.

Please let me know if you need any other information.

Thanks

Deeya

 

[deeya]

pretty please?

 

[lbass]

One approach would be to use a command as a datasource where you can use a union all to place the creation and resolved dates in the same field, e.g.:

select 'Created' as datetype, incident.creationdate as datex, incident.callID
from incident
union all
select 'Resolved' as datetype, resolution.resolveddate, resolution.callID
from resolution

This is a simplified version. The easiest way to do this would be to create a draft report using the incident table with the other relevant tables and selection criteria, and then copy the SQL query and paste it into a new report using the add command feature. Then add "union all" and paste the query a second time, but then editing it to reflect the resolved table fields. You can then use {command.datetype} to distinguish the dates for the chart, but all relevant dates (command.datex) will be available in the chart.

-LB