Solved

Conditionally format a crosstab in Crystal Reports

Posted on 2011-09-13
31
1,778 Views
Last Modified: 2012-05-12
I am having issues conditionally formating a crostab in Crystal.

In this example, I would like to be able to change the BG color of the on hand based on the value of min or max.
for example: belom min: crred ... close to min: cryellow etc.etc.

Min | On Hand | Max
25       98           100
25       201         100
25       18           100
25       51           100

This is too complex for the highlight expert.  So the only place I know of to do this is conditionally format the background in the boder tab under format field.  I cant seem to get the syntax right though.  

Any help would be appreciated.

Thanks,

Jerod
0
Comment
Question by:pai_01
  • 14
  • 12
  • 2
  • +1
31 Comments
 
LVL 100

Expert Comment

by:mlmcc
ID: 36531724
I don't know if you can do that in the cross tab.

Are the min and max in the cross tab?

mlmcc
0
 

Author Comment

by:pai_01
ID: 36531775
The min and max are just colum values.  Sorry for the confusion.  They could be anything

column1 | column2 | column3
25               98            100
etc.

I would like to make the BG color of column2 red if its value is below the value of column1.

Does that make better sense?
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 36544333
What version of Crystal do you have?

Is each row essentially a single record in the database?

mlmcc
0
 

Author Comment

by:pai_01
ID: 36544448
Version 11.5 R2
0
 

Author Comment

by:pai_01
ID: 36544518
I have two tables linked. The rows come from the min/max table, which houses the "min" and "max" values of course.  The "on hand" comes from a different talbe that is linked by partID.  

In the Summerized fields I have Min and max from one table and on hand from another.

so it acutally looks like:

             Min | On Hand | Max
PartID |  25  |  98          |  100
PartID |  25  |  201        | 100
etc.
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 36544979
How are you building the cross tab?

mlmcc
0
 

Author Comment

by:pai_01
ID: 36545080
I'm not sure what you mean.  I will attach an immage of the crosstab expert. Image of the cross tab expert.
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 36546823
What is OpInfo?

mlmcc
0
 

Author Comment

by:pai_01
ID: 36550925
A formula for operation titles.
Here is what is inside:

if {PCC_VENDOR_MIN_MAX.Op} = 10 then "10 - Reliable/Pre-Roughing"
else
if {PCC_VENDOR_MIN_MAX.Op} = 20 then "20 - Bodycote/Pre-Heat Treat"
else
if {PCC_VENDOR_MIN_MAX.Op} = 30 then "30 - Chromium/Pre-Copper Plating"
else
if {PCC_VENDOR_MIN_MAX.Op} = 40 then "40 - Reliable/Pre-Bore Op."
else
if {PCC_VENDOR_MIN_MAX.Op} = 50 then "50 - Bodycote/Pre-Blast/Nitride"
else
if {PCC_VENDOR_MIN_MAX.Op} = 60 then "60 - Precision Components/Pre-Machining"
else
if {PCC_VENDOR_MIN_MAX.Op} = 80 then "80 - Chromium/Pre-Black Oxide"
else
if {PCC_VENDOR_MIN_MAX.Op} = 90 then "90 - Precision Components/Kitting"
0
 

Author Comment

by:pai_01
ID: 36551489
I can send you the report with data saved if your would like.
0
 
LVL 34

Assisted Solution

by:James0628
James0628 earned 250 total points
ID: 36553785
I may be missing something, but I'm confused.  In your posts, you show what is presumably a sample cross-tab, which appears to just have a row for each part, with columns for "Min", "On Hand" and "Max".  But then in the cross-tab expert screenshot, you have the columns based on OpInfo, which produces values like "10 - Reliable/Pre-Roughing" and "20 - Bodycote/Pre-Heat Treat", which is obviously much more complex.

 You want to change the color in one of those columns based on a comparison with the value in another column?  If so, which columns do you compare?  Is it the previous column, or is there some pre-defined relationship (eg. compare "50 - Bodycote/Pre-Blast/Nitride" with "20 - Bodycote/Pre-Heat Treat")?

 The "Min"/"On Hand"/"Max" scenario would be one thing.  I'm not really sure if you could do what you want in a cross-tab like that, but it might be possible.  CR 10 is pretty limited in what it will let you do in cross-tabs, but it might be doable there.  And later CR versions may be more flexible.  If nothing else, I was thinking of suggesting that you just forget the CR cross-tab and create a "manual cross-tab" instead.  With something that simple, it should be easy.

 However, with the OpInfo thing, I really don't know.

 Yes, it might be a big help if you could post a report with saved data.  It certainly couldn't hurt.  :-)

 James
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 36554571
Will you only have 1 record for each {PCC_VENDOR_MIN_MAX.Op}/{PCC_VENDOR_MIN_MAX.PartId}  pair?
ie
You have data like this
Op     PartId   QTY
10      ABC       5  
20      ABC        8

You don't have
Op     PartId   QTY
10      ABC       5  
10      ABC       8  

mlmcc
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 36555043
I was not able to get at the min and max inside a given cell.  However by adding a status column to the cross tab I was able to get something that might work


CrossTab.rpt
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 36555048
Forgot to save with data

mlmcc
CrossTab.rpt
0
 
LVL 34

Expert Comment

by:James0628
ID: 36555243
FWIW, the two reports show the same size in your posts (98 KB), so I checked them and I didn't see data with either one.

 James
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 100

Expert Comment

by:mlmcc
ID: 36555269
Try again.

I opened the one I uploaded and saved with data which put it in the temp directory rather than my EE directory so I just uploaded the same file.

Thanks for catching that.

mlmcc


CrossTab.rpt
0
 

Author Comment

by:pai_01
ID: 36559595
Wow you replicated it pretty much to a tee.  

This is very creative, and might be acceptable.  I would much rather have the "On Hand" field have the color,  do you think this is possible?  If not, I might have to move to a manual cross-tab as James suggested.

Jerod
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 36560075
Not as far as I was able to determine.

mlmcc
0
 
LVL 100

Accepted Solution

by:
mlmcc earned 250 total points
ID: 36560088
The problem is when in a cross tab cell you don't have (at least in CR XI and before) access to any values other than the current field value.

mlmcc
0
 

Author Comment

by:pai_01
ID: 36560151
I accept that it cannot be done in a "canned" cross-tab as of my version of Crystal.  I will re-build the report as a manual crosstab.

Thanks for the help mlmcc, and James0628.  Your posts were very helpful.
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 36560237
This may be helpful in that endeavour

Manual Cross-Tabs (cross-tabs that allow formulas):
http://www.kenhamady.com/form12.shtml

mlmcc
0
 

Author Comment

by:pai_01
ID: 36560309
Very helpful, as this will be my first manual crosstab.  I hope to not have to open up any new question on EE.  At least if I do, you will know exacltly where I am coming from. :)

Jerod
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 36560347
Before working that out check this cross tab.  I had an inspiration to use the status idea on the field itself.

mlmcc
CrossTabRev1.rpt
0
 

Author Comment

by:pai_01
ID: 36560451
That works! You are the man!

Thank you, thank you, thank you!

Jerod
0
 

Author Comment

by:pai_01
ID: 36560461
Is there some way I can change the solution to your last comment?
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 36560609
No need.

mlmcc
0
 
LVL 18

Expert Comment

by:vasto
ID: 37247278
I am just curious: what is the database type behind this report ?
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 37247522
MS Access

mlmcc
0
 

Author Comment

by:pai_01
ID: 37255689
I use SQL server 2008 behind mine.
0
 
LVL 18

Expert Comment

by:vasto
ID: 37256351
Well , if there is already a working solution it doesn't make sense to change it , but for future cases it may be helpful to prepare the crosstab outside crystal reports.
Both MSAccess and SQLServer support similar functionality (Access supports TRANSFORM - PIVOT operator, SQLServer supports PIVOT).
Creating the crosstab in the database will simplify the report (no manual crosstabs, no formulas, just one normal table ) and will allow you to create much more complex crosstabs.

You can see an example for SQLServer 2005/2008 here: http://r-tag.com/PivotSample.aspx

This example shows a dynamic crosstab and text in the value section. Crystal reports will be not able to prepare crosstab like that at all. Dynamic columns may look like overkill , but in fact may allow you to use one stored procedure for different reports and to simplify your future maintenance. Crystal reports also doesn't support text in the value section.
 
However for your scenario will be enough to use just the pivot part:
SELECT * FROM <TABLE>
                             PIVOT (
                                          <aggregation function>(<column being aggregated>)
                                          FOR [<column that contains the values >]
                                          IN (<list of columns>)
                                         )

Obviously you need to use a stored procedure or command.
Consider this as another option.  
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

1. Set up your parameter at the report level as usual, check the box Multi-value, and set the Data Type to String 2. Set the Stored Procedure Parameter to varchar(max)  --<---- This part here is the key to it's success Example:    @cst_key var…
Problem Statement In an SAP BI BO Integration project when a BO universe is built on a BEx query, there can be an issue of unit & formatted value objects not getting generated in a BO universe for some key figures. This results in an issue whereb…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now