Show No record if Formula is False

[fkavanagh]

Hello,

I am trying to write a formula that returns a value from a table if a code is 340 for an employee which it is doing but it is also displaying the other information for the employee to the count of time they are in this table.

The formula is

if {CT_ABSENCE_DETAIL.PAY_CODE} = 340 then
{CT_ABSENCE_DETAIL.START_DATE}

what I think I need is some kind of show no record as an else if the formula is false. Am I on the right track?

Thanks again everyone.

Fergal

 

[lbass]

Can you use a record selection formula like the following?

{CT_ABSENCE_DETAIL.PAY_CODE} = 340

Otherwise, you could group on employee,right click on your formula and insert a maximum on it, drag the group name (employee) to the group footer, and suppress the details section.

-LB

 

[fkavanagh]

Thanks Ibass

I'm not sure but will try.

I will also kick myself for not describing the issue correctly. The reason for using a formula for this field is that some employees may not have this value but the other data in the report is needed for them.

What I was thinking about I guess is there a dont print function or "whileprintingrecords" ifnull etc.

Thanks for the reply

Fergal

 

[lbass]

Maybe you should provide a sample of the report as it looks now and another of how you would like it to look. If you need the other records, but don't want to display them, you could go to the section expert->details->suppress->x+2 and enter:

{CT_ABSENCE_DETAIL.PAY_CODE} <> 340

-LB

 

[fkavanagh]

No Prob

The report I have is like:

Name, Ill_St_Dt, Ill_E_Dt, Mat_St_Dt, Mat_E_Dt

If the

the mat_st_dt and mat_e_dt is only relevant if there are start and end dates in a table for pay_code 340. There may be other dates in this table and these are the ones are coming up when I dont need them.

If I put the 340 code in the selection formula it blanks out every record if this it not the case and if I select distinct records it makes no difference Because (I Think) I am using a formula to populate the field.

These 2 fields are really only for information purposes but are required if they fit the bill. As soon as I take out the 2 fields the report looks OK (except it dosn't have this field.

Rgds

Fergal

 

[lbass]

You still did not provide the sample data for the current report and for what you would like it to look like. Try showing results for a couple of employees in both the current and desired looks. You also seem to be using different fields than in your earlier posts.

I guess the issue is that by adding the formula you are accessing a second table which is inflating the record count, but still I am unclear about what you want in the end.

It would also help if you provide feedback on various suggestions. Did you try the suppression method I suggested? If so, how were the results different than what you hoped for?

-LB

 

[fkavanagh]

Hi Ibass and Thanks for your patience.

Sorry Sorry Sorry.
OK Let me start again.

I am using CR 9 on Windows connecting to an Orcale 8 database using an ODBC connection.

My report is using 2 tables, one holds medical absence data and th other holds maternity start and end dates. The two table are only linked once using the employee number.

What I am getting is a repition of data for the first 3 fields displayed for an employee and then null in the mt_st_dt and mt_e_dt fields when the 340 code is not met. I have turned on the distinct records but this does not work I presuume because the formula is restricting the printing of the record as opposed to a record selection).

What I want to happen is one record for each employee that has an illness end date greater current date and less than currentdate-7 (handled in a record selection) and if they have a maternity start and end date from the meternity table to include this also, but only this!!.

I hae tried different links (left outerm right outer) but no difference but could not get the group selection to work (error because it needs a boolean value?).

I have a work around in that I created a new database view for only paycode 340 records from the maternity table but would prefer to handle this within the report is at all possible.

Again thanks for your patience and look forward to hearing from you.

Yes it is funny how easier it is to describe a problem when you actually thaink about it. Public floggings at noon!!!

Rgds

Fergal

 

[lbass]

Fergal, by sample data I meant:

empl illstdate illenddate matstdate matenddate
1 9/16 9/17 6/20 8/20
1 9/16 9/17
1 9/16 9/17

So the code 340 is from the maternity table? You can drag your fields into an employee group footer and use a maximum on your maternity date formulas, as I suggested earlier, although this assumes that there is only one instance of an absence per the medical absence date range.

The other approach would be to use a subreport for the maternity fields. You would insert a group on employee, and place the subreport with the maternity dates in an employee group header section, linking the subreport to the main report on employee ID. Then go to the section expert and format the group header to "Underlay following sections". This should give you only one line per employee or at least not cause duplicate records in the first table.

I would think you would want a left join from the medical absence to the maternity table, unless you only want to look at those employees who took maternity leave.

-LB

 

[synapsevampire]

As you've discovered, You're better served to provide technical information than text descriptions:

Crystal version
Database/connectivity used
Example data
Expected output

First, I would change the connectivity from ODBC to native Oracle, it's much faster.

The reason for repitition is that the rows are not exact duplicates (row inflation), if you placed all of the field in the details you'd discover that there's some difference in the rows of data. You need to do some discovery to understand your data source, which is what example data (not just a few fields because it's too much work), but what is actually occuring in the data returned. Note that lbass has pleaded for example data, which you promptly ignore and instead try to describe your data with text, it's going to be less work overall to learn about your data and share the findings.

It's a bit unclear because I don't really want to duplicate lbass's typically excellent efforts and closely read all of your posts again, but if you want just one row per group, and the date is unique for that group, try a Report->Selection Formulas->Group

{table.date} = maximum({table.date}, {table.group))

If there are numerous dates for that group, drop the fields into the group footer sorted by the date descending.

I would try to eliminate the rows at the data source. Oracle supports the following to get the top 1:

select * from (
select date
from table
order by date desc
)
where rownum < 2

I would check with your dba for assistance with creating a View or a Stored Procedure (SP), keeping in mind that Crystal has specific requirements for Oracle SP development.

Here's the whitepaper:

http://support.businessobjects.com/communityCS/TechnicalPapers/cr_oracle_stored_procedures.pdf.asp

You can also use the Add Command to paste in specific SQL if that's a viable option for you. Another advanced topic is the use of SQL Expressions, which allows for pasting in SQL and in this exaqmple might be used as a subquery in lie of a subreport.

You can also use subreports, or the group footer idea floated by LB, but you will recognize better performance eliminating the subreports, and you can remove the pseuod-dupes by using better SQL.

-k

 

[fkavanagh]

Hi Guys and again thanks for the help.

I really do appreciate your efforts here and know how frustrating when somebody is looking for help won't help you to help them.

synapsevampire I am reading the white paper on Stored procedures now as I did have trouble in this field before with date formats etc.

To fix the problem I am going to leave the new view I created with just maternity dates in and take it from here.

Thnaks again for all the help and it was a learning experience in more than one way. My next wuetion will be fully thought out before I hit that submit button.

Rgds

Fergal