Using Cross Tab with Subreports

[Micko78]

I have prepared two reports -
one showing the number of issues raised each month
the other report showing the number of issues closed each month (querying database of fault tracking software)

What i would like to do is have the information from both reports available in one combined report in a cross tab (with a view to creating graphs based on the summary within the cross tab)

Currently i have two seperate cross tabs and graphs - ideally I would like one graph with two cross tabs in the main report

 

[synapsevampire]

Post your version of Crystal, as the solution varies (as with any software package, features change with different versions).

A simple solution here is to use a Union query, either in the Add Command for versions 9 and up, or by modifying the Database->Show SQL Query for previous versions.

As part of the Union you'd add a Type to indicate which table it came from, and group on that.

So the graph would have combined data, and then you can group on Type to show the individual data, example:

select 'raised' Type, table.field1, table.field3 from table1
union all
select 'Closed' Type, table.field1, table.field3 from table2

-k

 

[synapsevampire]

I neglected to mention the better solution which would be to build a View or SP on the database for the Union.

-k

 

[lbass]

In 8.0, I don't believe you can modify the first part of the "Show Query" statement to accept the language: "'raised' Type," although you can add additional fields.

In order to identify results of the first query for grouping purposes, you could instead add an otherwise unused string field to the first query and then add the language:

'Closed' as Type

...in the corresponding position in the second query. In your report, if you add {table.string} to your canvas, it will then show the actual results of the string field for the first query, and "Closed" for the second query. So that then you could create a formula like the following to group on:

if {table.string} = "Closed" then "Closed" else "Raised"

-LB