Reporting from two different views

[acessn]

Hi.

I have a report which needs data from two completely different views in my database. Is it possible to use to views _without_ linking them together? Seems like Crystal Reports likes to autolink them.

I use one of the views to count hours worked, and the other view to get the number of new employees. These reports works fine seperatly, but I'd like to display these numbers in a joined report without using subreports.

Regards Bjørn

 

[kskid]

You can create a subreport of the number of new employees and place it in the report footer of the number of hours worked report.

Cheers,
-LW

 

[synapsevampire]

Try posting technical information:

Crystal version
Database/connectivity used
Exampel data
Expected output

There are a few tricks along these lines, one of which is to use a UNION ALL type of query and construct a single data source, as in:

select 'hours' grouptype, empid, sum(hours) val1 from T1
union all
select 'emps' grouptype, empid, sum(emps) val1 from T2

Now you have a single datasource. Of course this is dependent upon software and database version, table layout, etc., which you shared nothing of.

The alternative is an unlinked subreport in either the report header or footer.

-k

 

[acessn]

Thanks for the tips.

The datasource is a Microsoft SQL database, running on Win 2000 server. The database is a Kronos Workforce database, with predefined views. I use CR 9.0.

I'm running the report from Kronos Workforce, which has a few standard parameters ("employee group", "from timeframe" and "to timeframe"). The selection of data is based on these parameters.

I need data from two views; VP_Totals & VP_Person.
I use running totals based on formulas to get hours worked, hours invoiced, hours sick, hours overtime from VP_Totals.
I then have some simple formulas to calculate the percentages. (4% sick, 22% overtime etc)

My trouble starts when I need data from VP_Person. I need to use the same parameters here ("employee group", "from timeframe" and "to timeframe"). I only need two fields; employeestatus and employeestatusdate. I then use a running total with count, based on a formula (empstatus="active" and in "from timeframe" and "to timeframe")

This works fine in two separate reports, but I'm having trouble merging them down to one...

Regards Bjorn

 

[lbass]

What is the goal of the joint report? If you are trying to limit the hours worked, etc., to those employees who are active during a certain period, then you could link the views and use a record selection formula of:

{view.empstatus} = "active" and
{view.date} in {?fromtime} to {?totime}

If you want to show all employees, active or not, you can still link the views and then adjust your running totals to eliminate duplicated values. Your best bet is to do the linking and then lay the fields out on the report canvas, sort the fields in a way that makes sense, and then note where you get the duplication and adjust the evaluation part of the running total to correct for that.

-LB

 

[acessn]

Just to let you know, I solved it by using a sub-report. I got the linking with the main-report correct, and were able to access the correct parameters.

Thanks for your time!

Regards Bjørn