Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1868
  • Last Modified:

Conditionally format a crosstab in Crystal Reports

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
pai_01
Asked:
pai_01
  • 14
  • 12
  • 2
  • +1
2 Solutions
 
mlmccCommented:
I don't know if you can do that in the cross tab.

Are the min and max in the cross tab?

mlmcc
0
 
pai_01Author Commented:
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
 
mlmccCommented:
What version of Crystal do you have?

Is each row essentially a single record in the database?

mlmcc
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
pai_01Author Commented:
Version 11.5 R2
0
 
pai_01Author Commented:
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
 
mlmccCommented:
How are you building the cross tab?

mlmcc
0
 
pai_01Author Commented:
I'm not sure what you mean.  I will attach an immage of the crosstab expert. Image of the cross tab expert.
0
 
mlmccCommented:
What is OpInfo?

mlmcc
0
 
pai_01Author Commented:
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
 
pai_01Author Commented:
I can send you the report with data saved if your would like.
0
 
James0628Commented:
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
 
mlmccCommented:
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
 
mlmccCommented:
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
 
mlmccCommented:
Forgot to save with data

mlmcc
CrossTab.rpt
0
 
James0628Commented:
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
 
mlmccCommented:
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
 
pai_01Author Commented:
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
 
mlmccCommented:
Not as far as I was able to determine.

mlmcc
0
 
mlmccCommented:
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
 
pai_01Author Commented:
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
 
mlmccCommented:
This may be helpful in that endeavour

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

mlmcc
0
 
pai_01Author Commented:
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
 
mlmccCommented:
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
 
pai_01Author Commented:
That works! You are the man!

Thank you, thank you, thank you!

Jerod
0
 
pai_01Author Commented:
Is there some way I can change the solution to your last comment?
0
 
mlmccCommented:
No need.

mlmcc
0
 
vastoCommented:
I am just curious: what is the database type behind this report ?
0
 
mlmccCommented:
MS Access

mlmcc
0
 
pai_01Author Commented:
I use SQL server 2008 behind mine.
0
 
vastoCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 14
  • 12
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now