Help creating a 3 level report from 3 related tables

[krwcox]

I'm using Crystal 8.5.
I'm trying to display trending and variance information at three different levels - All Devices for specified day, each Device for day, Device by Hour for day.

Each level has its own table with precalculated trending and variances.
I need to make a single point of entry that will allow me to go from:
Enterprise for day (single row)
---to Devices for that day
---to Device by hour for that day

If I see an out-of-sync condition at the enterprise, I want to view at the device level.
If I see the out-of-sync condition at device #11, I want the report to show the hours for only device #11 when I click on it.

I haven't been able to see how to accomplish this with Drill down reports because the trending needs to be calculated at each level.

I've tried subreports, but I can only have one subreport.

What other options does Crystal offer to accomplish this?

 

[synapsevampire]

You can have more than one subreport, you just can't nest more than one level.

Perhaps if you had shared the database type we might be able to approach it from that side, which is generally the better idea.

Without knowing the relationships of the tables, what the data looks like, nor the required output, it's fairly tricky to help.

Try posting:

Crystal version
Database/connectivity used
Example data
Expected output (using the example data)

You might create a Union and just combine the data into a flat single data source, then it might be easier for you.

But you'd need to share what constitutes an out of synch condition.

I would limit the rows in the database to those with out of synch and do away with the need to suppress data.

Report->Edit Selection Formula->Record and place something like:

{table.outofsynch} = 1

Or whatever constitutes out of synch.

Again, try sharing technical information, text often just muddies the waters.

-k

 

[krwcox]

Thanks. Here is what I have. I'm not familiar on how to input situation without text so please bare with me.

Crystal version = 8.5 Proffesional

Database/connectivity used = Oracle connection

Example data:
Control Point table - common point that builds relationship to each of the 59 devices; a control_id links all of the tables;
The following tables all have same name columns with daya being calcaulated by SAS before entry.
Trended, Actual, Variance, % Variance, Severity Level;
Trend by Day - Populated once a day to store trending info for the control point;
Trend by device table - Populated once a day to store trending info for each device per day;
Trend by hour table - Populated once a day for each device by hour to store trending info by the hour;

A separate View in Oracle has been created that builds a single view for Trend by Device and Trend by Hour.

Expected output (using the example data):

First report view would be Trend by Day for a Control Point.
From that view, user should be able to view Trend by Device Information. This displays a 24 hr period.
Fom this view, user should be able to click on a device and view that device by the hour.

I've created a Trend by Day report and inserted a subreport from the single View using Da dilldown style. My issue is that the data output in the Grouping Footer is not correct. It is already calculated in the Oracle tables, but Cyrstal seems to be mucking with it.
Group 1 is by Device (Trended, Actual, Variance, % Variance, Severity Level)
Group 2 is by Hour (Trended, Actual, Variance, % Variance, Severity Level)

I think my biggest issue is the architecture to get the output view I need. Please let me know if you see a better way to build the report that outputs ths data.

Thanks