Tips and Tricks
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Tips and Tricks
Message Icon Topic: Background Color -Conditions Post Reply Post New Topic
Page  of 2 Next >>
Author Message
Stircrazy08
Newbie
Newbie
Avatar

Joined: 12 Nov 2014
Location: United States
Online Status: Offline
Posts: 34
Quote Stircrazy08 Replybullet Topic: Background Color -Conditions
    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
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet 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?
IP IP Logged
adavis
Senior Member
Senior Member


Joined: 30 Oct 2012
Online Status: Offline
Posts: 104
Quote adavis Replybullet 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

Edited by adavis - 02 Mar 2015 at 8:10am
IP IP Logged
Stircrazy08
Newbie
Newbie
Avatar

Joined: 12 Nov 2014
Location: United States
Online Status: Offline
Posts: 34
Quote Stircrazy08 Replybullet 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
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 02 Mar 2015 at 10:21am
did you join any of your tables?
IP IP Logged
Stircrazy08
Newbie
Newbie
Avatar

Joined: 12 Nov 2014
Location: United States
Online Status: Offline
Posts: 34
Quote Stircrazy08 Replybullet 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
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet 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.


Edited by DBlank - 02 Mar 2015 at 11:50am
IP IP Logged
Stircrazy08
Newbie
Newbie
Avatar

Joined: 12 Nov 2014
Location: United States
Online Status: Offline
Posts: 34
Quote Stircrazy08 Replybullet 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
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 03 Mar 2015 at 4:54am
what are your select conditions
IP IP Logged
Stircrazy08
Newbie
Newbie
Avatar

Joined: 12 Nov 2014
Location: United States
Online Status: Offline
Posts: 34
Quote Stircrazy08 Replybullet 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
IP IP Logged
Page  of 2 Next >>
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.014 seconds.