Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Conditionally format a crosstab in Crystal Reports

Posted on 2011-09-13
31
1,790 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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 

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
 
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Hi, In my previous Article  (http://www.experts-exchange.com/Database/Reporting/A_15199-Introduction-to-Microstrategy.html)I discussed some basic understanding of Microstrategy that how we can get in Intro of Microstrategy (MSTR). Now it's tim…
I recently went through setting up a JasperReports Server using the AWS EC2 instance, and this article will cover some basic administration tasks I had to perform.
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used.

789 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