| Author |
Message |
Stircrazy08
Newbie
Joined: 12 Nov 2014
Location: United States
Online Status: Offline
Posts: 34
|
   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 Logged |
|
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
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 Logged |
|
adavis
Senior Member
Joined: 30 Oct 2012
Online Status: Offline
Posts: 104
|
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 Logged |
|
Stircrazy08
Newbie
Joined: 12 Nov 2014
Location: United States
Online Status: Offline
Posts: 34
|
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 Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 02 Mar 2015 at 10:21am |
|
did you join any of your tables?
|
|
IP Logged |
|
Stircrazy08
Newbie
Joined: 12 Nov 2014
Location: United States
Online Status: Offline
Posts: 34
|
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 Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
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 Logged |
|
Stircrazy08
Newbie
Joined: 12 Nov 2014
Location: United States
Online Status: Offline
Posts: 34
|
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 Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 03 Mar 2015 at 4:54am |
|
what are your select conditions
|
|
IP Logged |
|
Stircrazy08
Newbie
Joined: 12 Nov 2014
Location: United States
Online Status: Offline
Posts: 34
|
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 Logged |
|
|
|
