I may or may not have found a bug in SAS VA.
I have columns that are coming from Oracle and colums that are calulated at the Data Query level.
Here are the calculated columns:
Column a: CASE WHEN (O_OVT_COMPTIME_FV.NC_PAYCODE IN ('Comp Time Earned', 'Comp Time Earned II')) THEN O_OVT_COMPTIME_FV.HOURS_EARNED END
Column b:CASE WHEN (O_OVT_COMPTIME_FV.NC_PAYCODE IN ('Overtime', 'Overtime SD', 'OT ADJ', 'OT SD ADJ')) THEN O_OVT_COMPTIME_FV.HOURS_EARNED END
Column c: CASE WHEN (O_OVT_COMPTIME_FV.NC_PAYCODE IN ('Regular', 'REG ADJ', 'REG SD ADJ', 'Regular SD')) THEN O_OVT_COMPTIME_FV.HOURS_EARNED END
On the Report, I have created 3 aggregate fields based on each of these columns, the aggregate fields are the following:
Sum [_ByGroup_] ('Column a'n)
Sum [_ByGroup_] ('Column b'n)
Sum [_ByGroup_] ('Column c'n)
I have a Table object(same thing happens in a crosstab) that is supposed to display these aggregate values by the group and if all 3 columns mentioned above have data, then the row is displayed with no problem.
However, I have noticed that records that have missing values in, say Column a, wont show up in the table object(or the crosstab) once I add Column a to the object. If i remove column a from the table object, the row shows up again.
Something about that SUM is causing the record to dissapear from the table object, and no, i dont have any filters on that particular object.
I tried doing an IF check in that aggregate field, but it will not let me check "Column a" against missing or zero, it forces me to use "Sum [_ByGroup_] ('Column a'n)" in the comparison.
I have also tried adding an Else 0 to all the column calculations in the data query but that doesnt help either.
See how to use one filter for multiple data sources by mapping your data from SAS’ Alexandria McCall.
Find more tutorials on the SAS Users YouTube channel.