Crystal is not showing Null values

[mravimtnl]

Dear all

I have various formulas based on two fileds udfnum.un_udfgen_id and udfnum. The table of the fields are like this.



Udf gen ID UN Nmber

1
2
...
...

53 contains 1, 0, or null
54 contains 1, 0, or null
55 contains 1, 0, or null

I have limited my record selction on 53 and 54. However crystal is showing only where there is data entered i.e either 1 or 0 fileds and blak fields where ther is no data is not being displyed. But i want all the data irrecspective of wheher data is entered or not.

I have already tried he following, made null values to default values in report options, made left out joins and all combinations of joins etc.


SQL is given below for your perusal:

SELECT
xaction."x_hist_type", xaction."x_term_name", xaction."x_timestamp",
badge."b_number_str",
cardholder."c_fname",
company."company_name",
udfboolean."ub_udfgen_id", udfboolean."ub_boolean",
udfnum."un_udfgen_id", udfnum."un_number"
FROM
{ oj (((("Pegasys"."dbo"."xaction" xaction INNER JOIN "Pegasys"."dbo"."badge" badge ON
xaction."x_badge_number" = badge."b_number_str")
INNER JOIN "Pegasys"."dbo"."cardholder" cardholder ON
badge."b_cardholder_id" = cardholder."c_id")
INNER JOIN "Pegasys"."dbo"."udfboolean" udfboolean ON
cardholder."c_id" = udfboolean."ub_cardholder_id")
INNER JOIN "Pegasys"."dbo"."udfnum" udfnum ON
cardholder."c_id" = udfnum."un_cardholder_id")
INNER JOIN "Pegasys"."dbo"."company" company ON
cardholder."c_company_id" = company."company_id"}
WHERE
xaction."x_timestamp" >= {ts '2011-06-01 00:00:00.00'} AND
xaction."x_timestamp" < {ts '2011-07-01 00:00:00.00'} AND
(xaction."x_hist_type" <> 33 AND
xaction."x_hist_type" <> 35 AND
xaction."x_hist_type" <> 37) AND
udfboolean."ub_boolean" = 1 AND
(udfboolean."ub_udfgen_id" = 17 OR
udfboolean."ub_udfgen_id" = 18 OR
udfboolean."ub_udfgen_id" = 19 OR
udfboolean."ub_udfgen_id" = 20 OR
udfboolean."ub_udfgen_id" = 55) AND
(udfnum."un_udfgen_id" = 53 OR
udfnum."un_udfgen_id" = 54)
ORDER BY
company."company_name" ASC,
badge."b_number_str" ASC

 

[lbass]

You need to use a left outer join FROM cardholder TO udfnum, and then change your formula to:

(
isnull({udfnum.un_udfgen_id}) or
{udfnum.un_udfgen_id} = 53 OR
{udfnum.un_udfgen_id = 54}
)

However, using this means that records from other tables that have other values of un_udfgen_id will be excluded. Not sure if that is what you want.

-LB

 

[mravimtnl]

I have done like that but still i am not getting the required.I am attaching herewith the crystal report also:



Selection:
{xaction.x_timestamp} >={?fromdate} and
{xaction.x_timestamp} <= {?todate} and
{xaction.x_hist_type}<>33 and
{xaction.x_hist_type}<>35 and
{xaction.x_hist_type}<>37 and
({udfboolean.ub_udfgen_id} = 17 or
{udfboolean.ub_udfgen_id} = 18 or
{udfboolean.ub_udfgen_id} = 19 or
{udfboolean.ub_udfgen_id} = 20 or {udfboolean.ub_udfgen_id}=55)
and ({udfboolean.ub_boolean}=1) and
(isnull({udfnum.un_udfgen_id}) or {udfnum.un_udfgen_id}= 53 OR
{udfnum.un_udfgen_id} = 54) AND
IF {?company}='ALLCOMPANIES'
Then {company.company_name} like '*'
Else {company.company_name} = {?company} ;




SQL
SELECT
xaction."x_hist_type", xaction."x_term_name", xaction."x_timestamp",
badge."b_number_str",
cardholder."c_fname",
company."company_name",
udfboolean."ub_udfgen_id", udfboolean."ub_boolean",
udfnum."un_udfgen_id", udfnum."un_number"
FROM
{ oj (((("Pegasys"."dbo"."xaction" xaction INNER JOIN "Pegasys"."dbo"."badge" badge ON
xaction."x_badge_number" = badge."b_number_str")
INNER JOIN "Pegasys"."dbo"."cardholder" cardholder ON
badge."b_cardholder_id" = cardholder."c_id")
INNER JOIN "Pegasys"."dbo"."udfboolean" udfboolean ON
cardholder."c_id" = udfboolean."ub_cardholder_id")
INNER JOIN "Pegasys"."dbo"."company" company ON
cardholder."c_company_id" = company."company_id")
LEFT OUTER JOIN "Pegasys"."dbo"."udfnum" udfnum ON
cardholder."c_id" = udfnum."un_cardholder_id"}
WHERE
xaction."x_timestamp" >= {ts '2011-08-01 00:00:00.00'} AND
xaction."x_timestamp" < {ts '2011-08-02 00:00:00.00'} AND
(xaction."x_hist_type" <> 33 AND
xaction."x_hist_type" <> 35 AND
xaction."x_hist_type" <> 37) AND
udfboolean."ub_boolean" = 1 AND
(udfnum."un_udfgen_id" IS NULL OR
(udfnum."un_udfgen_id" = 53 OR
udfnum."un_udfgen_id" = 54)) AND
(udfboolean."ub_udfgen_id" = 17 OR
udfboolean."ub_udfgen_id" = 18 OR
udfboolean."ub_udfgen_id" = 19 OR
udfboolean."ub_udfgen_id" = 20 OR
udfboolean."ub_udfgen_id" = 55)
ORDER BY
company."company_name" ASC,
badge."b_number_str" ASC

 

[lbass]

There is nothing wrong with your formula per se, but you have to realize that your equal joins always require a value in both tables. Since cardholder is joined with equal joins to company and udfboolean, they will limit the records returned also. I can't really help you without understanding what you expect to be returned, and how that is different from what you are seeing. The example you provided doesn't do that. Are you expecting to see all results from a particular table?

-LB

 

[mravimtnl]

If I am not using Udf gen Id and Un Number I am getting all the records in desired format. However, if i include these two fields, I am getting a blank report.

After running the report I need UDF gen ID and corresponding UDF Numer. IF no data is entered in udfnumber , I wan to create a formula that data needs to be entered. I am unable to get the same.

 

[lbass]

Test a few things. Try changing the joins for the company and the udfboolean table to left joins and see what happens.

Also try removing the selection criteria on the {udfnum.un_udfgen_id} and observe the results.

-LB