Vanishing data in CR 10/Oracle 9i back-end (Maybe join problem?) 1

[jayster7]

Hi guru's HELP!

Working a report with 2 types of utilization data, online services and case data. Key fields in each table are a client number which is joined to a third organization table containing client info.

I have a couple of parameter fields to pass in the client number value and then a date range.

Now my selection criteria primarily keys on the online services table:

{@online_services_date} = {?Date_Range} and
{online_services.EMP_NO} = {?Employer};


I can produce an online services summary on this boolean field I created:

local booleanvar curr_ol_services_type := {@online_services_date} = {?Date_range} and
{online_services.PRODUCT_ID} in [100002,100006,200006,200012];

if curr_EAP_type then 1 else 0;



Now! The problem happens when I try to use any of the data from the other case table. I created a boolean value to count similar to the one above:

local booleanvar test_EAP_cases :=

Date({CASE.CASE_OPENED_DATE}) = {?Date_Range}
and {CASE.SERVICE_ID} = 1 TO 22;

if test_EAP_cases then 1 else 0;



Now when I try to lay a summary field for this other case table onto the report with data already showing from the online services table, the report data blanks out? The joins are only inner from the online services table to the organization and the cases table to the organization through the client number, client division number and client location. Here's a copy of the query...


SELECT "ONLINE_SERVICES"."EVENT_DATE",
"ONLINE_SERVICES"."CLIENT_NO",
"ONLINE_SERVICES"."PRODUCT_ID"
FROM "ONLINE_SERVICES"
WHERE "ONLINE_SERVICES"."CLIENT_NO"='1428'


What's also totally weird about this obviously is that the third table I mention (CASES) doesn't even show up.

Where in the heck am I going wrong? Thanks in advance for any help!




The path up and down is one and the same.

-Heraclitus

 

[lbass]

You can't have two tables pointing to the same table. You need to explain whether there are matches for the case number in all tables or whether in some tables there might not be a match. If you do have matches in all, you could use joins like:

case table------->Org table
\
\
\
>onsite services table

If you do this, then when you apply date criteria to one table, you will potentially be limiting the matches that appear in another table.

I think you should explain the purpose of the report in more detail.

-LB

 

[jayster7]

Thanks lbass!

What I'm trying to do is take calculated from each table (online services and cases) and create shared variables of those to be used later on down the report (report is a series of report footers broken into pages). I could actually use the online services report in a sub-report farther down, but I need to have access to the online-services numbers up near the top of the report and won't be able to pass them back up to the header (previous pages).

As far as the date criteria, I'm trying to get date ranges that would apply to each table. That could be my problem! How you would you suggest creating a common date field that would apply to each table that could be passed to a parameter? I really appreciate it folks!

The path up and down is one and the same.

-Heraclitus

 

[lbass]

I'm not following you. Shared variables are only used for passing data between a main report and subreport. If your report is entirely in the report footer, then you must be using subreports exclusively or you are only using grand totals.

For help, you will need to describe your report structure much more specifically. If you are using subreports, you need to explain their exact location and, if you are linking them to the main report, describe your links.

You make this sound like you are trying to report independently on these tables. If so, maybe you should be using separate subreports. You can create date parameters in each subreport and then link each of them to the same parameter set up in the main report, so that you get only one prompt.

-LB

 

[jayster7]

The report structure is all report footers RF-a through RF-am. The report gets broken into 9 pages with some sections being conditionally suppressed.

In the first section, RF-a, there is a product summary of services and here the online services and case services should be combined and percentages are calculated based upon these totals. I tried to create a subreport for the online services and hide it in the report header, but I don't think I can pass a suppressed subreport total back out to the main report.

What I'd like to accomplish is to create the grand totals from either of these 2 tables and create shared variables to pass into other subreports later on down in the report (some subreports are charts, some are more granular breakouts of data on a particular page.) Online services get reported on in RF-w, RF-aa, RF-ae and RF-ai but I also need the GtTot's calculated there in that first section product summary. If I can use both tables in the main report, then i actually wouldn't need to create subreports in these sections and it would spare a little runtime.

So far, I am able to run this report without the incorporation of the online services table, and it works fine.

You said something in the previous post that made me think about the dates. I'm only querying the date of one table to fit within my parameter field's range. I tried

Date{online_service.date} = {?Date_Range} or Date{case_opened_date} = {?Date_Range}

That doesn't seem to work though.

Thanks again for the help.

The path up and down is one and the same.

-Heraclitus

 

[lbass]

If you use a formula like:

Date({online_service.date}) = {?Date_Range} or
Date({case_opened_date}) = {?Date_Range}

...you will get records that match the on-line date, but these could include data from the case table regardless of date. You would also get records where the case_opened date matches the parameter, but these will include data from the online table that do not match the date criterion.

You should be able to place subreports in the report header where you create shared variables. Then suppress all sections within the subreport, instead of trying to hide or suppress the report header.

-LB

 

[jayster7]

Thanks LB I will give it a try!

The path up and down is one and the same.

-Heraclitus