Display results of boolean formula as details in xtab

[elsenorjose]

Hello everyone,

I am creating a crosstab to display whether certain requirements are optional or required for certain job roles. My rows are the requirements, my column headers are the job roles and what I'm trying to display in the details is the result of a formula to convert boolean values to a letter.

If table.must_have = 0 Then 'O'
Else If table.must_have = 1 then 'R'
Else 'N/A'

Is this possible? I've tried using Maximum as the summary operation but all I get back are the 'R's.

I am using Crystal Reports XI.

Thank you.

 

[lbass]

The maximum should work as long as there is only one value per job role/requirement combination.

-LB

 

[elsenorjose]

Agreed. There can only be one value per role/requirement intersection; it's either required or optional. But all I'm getting back in the report are 'R's, the optional values are all blank.

 

[elsenorjose]

Ok, further testing debunks that assumption. I wrote a SQL command to convert the 1s and 0s to their respective letter value and when I do a max on it in the crosstab, I still only get Rs. It would appear that a max summary will only get you the highest value in that field, not the role/requirement intersection.

 

[lbass]

Try changing your formula to:

If isnull({table.must_have}) Then
'O' Else
If table.must_have = 1 then
'R' Else
'N/A'

-LB

 

[elsenorjose]

Thanks LB. That didn't help either. If I just do a MAX on the numeric values, that works fine; I see 0s or 1s. It's only when converting to text values that I see this behavior.

 

[lbass]

I'm not sure what's going on, but try using the original formula with zeros and ones in the crosstab and then format usin the display string formula area:

if currentfieldvalue = 0 then "O" else
if currentfieldvalue = 1 the "R" else
"N/A"

-LB

 

[elsenorjose]

Hi and thanks again. I tried that as well and still no luck. It's really a bizarre problem. I did a little testing by filtering roles and what I discovered is that if I retrieve all roles, I see some 0s for some role/requirement combinations. BUT, when I filter for one specific role which I have confirmed has 0s in some role/requirement combinations, the report throws out the 0s and only returns the 1s. Isn't that weird? There is no filtering in the report at all and the underlying view has 1s and 0s. I'm going to just try rebuilding the report from scratch and see if that makes any difference.

 

[lbass]

What appears in database->show SQL query? Are you using more than one table? How are they linked?

-LB

 

[elsenorjose]

Just one view with all the underlying data. Here's the SQL:

SELECT "V_REP_REQUIREMENT_POLICY"."REP_HIS_REQ_TITLE", 
"V_REP_REQUIREMENT_POLICY"."REP_HIS_REQ_NO", 
"V_REP_REQUIREMENT_POLICY"."REP_ROL_CODE", 
"V_REP_REQUIREMENT_POLICY"."REP_MUST_HAVE"

FROM   "SmartReport"."dbo"."V_REP_REQUIREMENT_POLICY" 
"V_REP_REQUIREMENT_POLICY"

When I run the SQL in SQL Server Management Studio, I get the 1s and 0s as expected. One thing I missed in my original description of the report design is that I have an additional row, Req. No. So the report rows are actually Req. Title > Req. No. but I don't believe that should affect the data retrieved. What really has me stumped is why does filtering on one role result in 0s being thrown out yet retrieving all roles shows the 0s.

 

[elsenorjose]

Ok, I figured out the problem but now I need to find a workaround to it. It turns out that CR is displaying 0 as the max value for 'must_have' when there is no role/requirement intersection. So, the value isn't NULL or 0, there just doesn't exist a role/requirement combo. Is there any way to address this? Basically, I would need that scenario to display 'N/A'. In the jpeg, all those 0s should be 'N/A' because the requirement doesn't exist for those roles.

http://www.prismdatasystems.com/untitled.JPG

 

[elsenorjose]

Sorry for the bump...just wondering if anyone has any ideas on this.

Thanks.

 

[elsenorjose]

Ok, I figured out how to retrieve the data correctly by LEFT OUTER JOINing the table to itself so any role/requirement relation with no 'must_have' value displays a blank space; in my SQL browser, these are NULL. Now, however, I still can't get Crystal to display the results of this formula correctly:

If isnull({table.must_have}) Then
'N/A' Else
If table.must_have = 1 then
'R' Else
'O'

Any field with a value for 'must_have' keeps getting converted to 'R' regardless of the value in the field.

 

[lbass]

If you place {table.must_have} in Crystal in the detail section (not in the crosstab), what does it display? If you browse the field, does it say the datatype is number with 0,1 options? Or a boolean? You need to test the data more thoroughly.

-LB

 

[elsenorjose]

Hi LB, thanks as always for the direction. I spoke to our DBA and suggested we just create a view for this specific report with O, R, and N/A in the view instead of trying to do it in CR. If I use the MAX value in the summary, as you mentioned earlier, I should be able to display what I need in the crosstab.

Thanks again.