Print Page | Close Window

Background Color -Conditions

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Tips and Tricks
Forum Discription: Have you learned some great tricks to share with the group? Post them here!
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=21358
Printed Date: 28 Apr 2024 at 2:59pm


Topic: Background Color -Conditions
Posted By: Stircrazy08
Subject: Background Color -Conditions
Date Posted: 02 Mar 2015 at 6:34am
i have a report that i am creating, i have an item table with many items. i created another table with two fields, one the item code that i am linking to the item table and the other field is called Color.

in my crystal xi report, i have a group footer that displays all my data. so i go into section expert and select the color tab and click formula button.

i enter the formula:
if {IM_UDT_ITEM_CODE_BLUE.UDF_COLOR} = "YELLOW" THEN CRYELLOW
ELSE crNOCOLOR

the results i get are only the items that are in my color table display in all yellow.

what i want is to display all the items in my item table and only highlight the ones that are in the color table. when i remove that formula all the items show up in the report. , with the formula only the items in my color table show up.


what the color signifies is i am trying to color code my items for different years that we put them into production.. 2014 yellow 2015 blue..etc.

any ideas???



-------------
Peter F


Replies:
Posted By: DBlank
Date Posted: 02 Mar 2015 at 8:08am
likley you are enforcing the join when you use that formula.
as a test
in preview mode where you see all of the records
set a summary as a count of any non null field into your report header to show the total record count
drop a any field from the IM_UDT_ITEM_CODE_BLUE table onto the report canvas
does your record set change?

Posted By: adavis
Date Posted: 02 Mar 2015 at 8:09am
Most likely you have an inner join between your database table and your color table, so when the join is enforced it is eliminating data that doesn't appear in both tables.

First thing to try is to change your join type to full outer join.

Second thing to try is eliminating your color table all together and base your CR color for the field off a year value from a datepart formula.


Edit: DBlank beat me to it

Posted By: Stircrazy08
Date Posted: 02 Mar 2015 at 9:38am
DB - my record counts do change so does my report when i throw a field from my color table onto the report canvas.

apparently my tables have an inner join like adavis said. but when i go into link options to change, i see inner join, left outer join and right outer join, but the full outer join is greyed out and wont let me select it.

here is my sql query.

SELECT "AR_InvoiceHistoryHeader"."InvoiceDate", "AR_InvoiceHistoryHeader"."BillToName", "AR_InvoiceHistoryDetail"."ItemCode", "AR_InvoiceHistoryDetail"."QuantityShipped", "AR_InvoiceHistoryDetail"."ExtensionAmt", "AR_InvoiceHistoryDetail"."ItemCodeDesc", "CI_Item"."ProductType", "CI_Item"."ItemCode", "IM_UDT_ITEM_CODE_BLUE"."UDF_COLOR"
FROM   "AR_InvoiceHistoryHeader" "AR_InvoiceHistoryHeader", "AR_InvoiceHistoryDetail" "AR_InvoiceHistoryDetail", "CI_Item" "CI_Item", "IM_UDT_ITEM_CODE_BLUE" "IM_UDT_ITEM_CODE_BLUE"
WHERE (("AR_InvoiceHistoryHeader"."InvoiceNo"="AR_InvoiceHistoryDetail"."InvoiceNo") AND ("AR_InvoiceHistoryHeader"."HeaderSeqNo"="AR_InvoiceHistoryDetail"."HeaderSeqNo")) AND ("AR_InvoiceHistoryDetail"."ItemCode"="CI_Item"."ItemCode") AND ("IM_UDT_ITEM_CODE_BLUE"."UDF_ITEM_CODE"="CI_Item"."ItemCode") AND "CI_Item"."ProductType"='F' AND ("AR_InvoiceHistoryHeader"."InvoiceDate">={d '2014-01-01'} AND "AR_InvoiceHistoryHeader"."InvoiceDate"<={d '2015-02-28'})
ORDER BY "AR_InvoiceHistoryDetail"."ItemCode", "AR_InvoiceHistoryHeader"."BillToName", "AR_InvoiceHistoryHeader"."InvoiceDate"




not sure if that helps. i cant base my color on any date because we dont have a date of when items went into production, thus the table i created, where i entered all items for different years, making each year a different color code.



-------------
Peter F

Posted By: DBlank
Date Posted: 02 Mar 2015 at 10:21am
did you join any of your tables?

Posted By: Stircrazy08
Date Posted: 02 Mar 2015 at 10:45am
Originally posted by DBlank

did you join any of your tables?


yes, when i created the report, just linked them.

i linked ci_item to my udf table and invoice detail by item code

and then linked my invoice header to invoice detail by invoice no and header seq no.,

when i go into link options they show as all inner joins.
enforce join = Not enforced
Link Type is =



-------------
Peter F

Posted By: DBlank
Date Posted: 02 Mar 2015 at 11:37am
I think I see them in the WHERE portion of the sql now.
I expected to see them as table join in the FROM.
You should be able to do a left outer join to your 'color code' table.
You do have other WHERE conditions (select formulas) that can cause records to be lost in an outer join. I think you are safe in this case but be aware that the sequence is to create the data set via the joins first and then to select records from that full data set via the WHERE, so the join is happening before the where.
These where criteria can make an outer join stop acting as such becuase you end up filtering out some of your "expected outer join" data set.
 
Primary example of this is when someone want to see all prioducts from a product table and what was sold during a certain period.
They outer join products to sales and then add a where claseu on the sales date.
What they end up with is only items that were never sold (no match to sales table at all) and records that matched the sales dates in the WHERE criteria. They lose all of the records that matched the sales table but did not have a sale in the dates from the WHERE.

Posted By: Stircrazy08
Date Posted: 03 Mar 2015 at 4:30am
i changed the link to outer join for my 'color code' table, and results are same, when i try to do a formula on the group footer looking for 'yellow' it only brings back the items that are in the 'color code' table.

here is my where now.

FROM   "AR_InvoiceHistoryHeader" "AR_InvoiceHistoryHeader",
"AR_InvoiceHistoryDetail" "AR_InvoiceHistoryDetail",
{oj "IM_UDT_ITEM_CODE_BLUE" "IM_UDT_ITEM_CODE_BLUE" LEFT OUTER JOIN "CI_Item" "CI_Item" ON "IM_UDT_ITEM_CODE_BLUE"."UDF_ITEM_CODE"="CI_Item"."ItemCode"}




-------------
Peter F

Posted By: DBlank
Date Posted: 03 Mar 2015 at 4:54am
what are your select conditions

Posted By: Stircrazy08
Date Posted: 03 Mar 2015 at 5:00am
SELECT "AR_InvoiceHistoryHeader"."InvoiceDate", "AR_InvoiceHistoryHeader"."BillToName", "AR_InvoiceHistoryDetail"."ItemCode", "AR_InvoiceHistoryDetail"."QuantityShipped", "AR_InvoiceHistoryDetail"."ExtensionAmt", "AR_InvoiceHistoryDetail"."ItemCodeDesc", "CI_Item"."ProductType", "CI_Item"."ItemCode", "IM_UDT_ITEM_CODE_BLUE"."UDF_COLOR"



WHERE (("AR_InvoiceHistoryHeader"."InvoiceNo"="AR_InvoiceHistoryDetail"."InvoiceNo") AND ("AR_InvoiceHistoryHeader"."HeaderSeqNo"="AR_InvoiceHistoryDetail"."HeaderSeqNo")) AND ("AR_InvoiceHistoryDetail"."ItemCode"="CI_Item"."ItemCode") AND "CI_Item"."ProductType"='F' AND ("AR_InvoiceHistoryHeader"."InvoiceDate">={d '2014-01-01'} AND "AR_InvoiceHistoryHeader"."InvoiceDate"<={d '2015-02-28'})


ORDER BY "AR_InvoiceHistoryDetail"."ItemCode", "AR_InvoiceHistoryHeader"."BillToName", "AR_InvoiceHistoryHeader"."InvoiceDate"


-------------
Peter F

Posted By: DBlank
Date Posted: 03 Mar 2015 at 5:27am
are you daisy chaining the joins?
if so make sure all the joins daisychained with the color table in that chain are also outer joined.
 
if that is not a solution I would next test if the where conditions are  teh source of the issue. I would remove the where clauses, not use the color table, run the report, drop the color table and see if that again enforces the join and the record count changes.


Print Page | Close Window