Linking NULL fields in subreports

[glalsop]

I am using CR 8.5 connecting to SQL Server via ODBC. The reports I create track employee completion of federal compliance training courses for a financial institution. SQL Server tables are set up as follows:

Person
emp_id - pk
emp_status
group
region
market
cost_ctr
auto_event - fk

Event
event_status
auto_event - pk
locator_num - fk

Class
locator_num - pk
course_code

The intention of the reports I have just created is to list all of the employees in a given cost_ctr who are active, and then show which of those employees have completed compliance training for a predetermined course.

So I have a left-outer join like this:
Person -> Event -> Class

In my report I have the following grouping:
{Person.Group}
{Person.Region}
{Person.Market}
{Person.cost_ctr}

In the cost_ctr Group Header I have a subreport that is linked to the main report by ALL of the grouping fields. In the subreport I list all of the employees that are active for the cost_ctr. In the details I list the employees who have completed the course.

This all works as expected until ANY one of the fields that my subreport is linked on is NULL. If this happens then my subreport returns nothing.

Subreport Selection formula:
{Person.emp_status} = "A" and
{Person.group} = {?Pm-person.group} and
{Person.region} = {?Pm-Person.region} and
{Person.market} = {Pm-Person.market} and
{Person.cost_ctr} = {?Pm-Person.cost_ctr}

Even though I am using subreports, this runs at an acceptable speed (just a few minutes), but again it is inaccurate if one of the linked fields in null.

I tested this selection formula on a report that selects a small data set:

{Person.emp_status} = "A" and
(
isnull({Person.group}) or
{Person.group} = {?Pm-person.group}
) and
(
isnull({Person.region}) or
{Person.region} = {?Pm-Person.region}
) and
etc, and my results were correct. The problem is when I put this in my 'real' report (where the subreport fires several hundred times) it is unacceptably slow.

So 2 questions:

1) Is it possible to do this without subreports (SQL Server tables are stored on an external vendors server and I do not have access to creating Views or Stored Procs)

2) If not, is my subreport functioning in the expected way, i.e. can subreports not link on NULL values?


-Gary

 

[hilfy]

From your description, it looks like it should be possible to do this without the subreport - especially if what you've described is all the report is and there isn't any other processing done in the main report.

- Use a left join from person to event to make sure that you have all people including those without events.

- Put your employee and course information in the detail section.

-D

 

[synapsevampire]

I would suspect that the linking is akin to variables in Crystal in that they aren't null (without using trickery).

I guess an important question here is why would you have nulls, or why would you want results from those?

Anyway, you might try altering the sub selection to OR nulls, as in:

{Person.emp_status} = "A" and
{Person.group} = {?Pm-person.group} and
{Person.region} = {?Pm-Person.region} and
{Person.market} = {Pm-Person.market} and
(
isnull({Person.cost_ctr})
or
{Person.cost_ctr} = {?Pm-Person.cost_ctr}
)

Adding in the appropriate null clauses for whatever groups make sense.

-k

 

[glalsop]

Hi D -

Thanks for your reply. Here is the problem there:

Report output is a list:

Employees in CC 12345
1111 - Gary Alsop
2222 - Fred Smith

CMP111 Completions for CC 12345
2222 - Fred Smith

I can only have one or the other in the details section of my main report - ALL the employees in the CC OR Completions in the Cost Center. In addition, I seem to have omitted one of my groups from my original post, which is the course_code. Grouping actually looks like this:

{Person.Group}
{Person.Region}
{Person.Market}
{Person.cost_ctr}
{Class.course_code}

There are multiple classes. Sorry for any confusion.



-Gary

 

[glalsop]

SV -

Thank you also for your reply. Unfortunately, the Person table is populated automatically an HR update, and the fact that it has NULL values in some fields is out of my hands. Ideally I would like these people to show up in groups at the beginning of the report (this is how they are grouped when I don't use subreports - all NULL groups show up first).

I am using OR in my selection formula when I get the correct results, the problem is that when I use

(
isnull({Person.region}) or
{Person.region} = {?Pm-Person.region}
)

my report runs WAY slower than when I use

{Person.region} = {?Pm-Person.region}

without the NULL check.

Thank you both for your responses.

-Gary

 

[synapsevampire]

Yeah, it will definitely run more slowly because you have to check for 2 things.

To get an idea of what is going on, check the Database->Show SQL Query

Ultimately you would do this on the database side using a View or SP and forego subreports and null checks.

-k