Solved

Suppressing data in Crosstab reports in Crystal Reports

Posted on 2010-08-25
20
3,813 Views
Last Modified: 2012-09-14
Hi there,
Is it possible to suppress data in Crystal Reports based on certain conditions? I can't seem to make anything work unless there is blank data. Basically, I have three rows of data in my report:
CustomerN (row1)
CustomerSalesID (row2)
InvoiceSalesID (row3)
Essentially, I would like to suppress data based on certain conditions (ie CustomerSalesID=212 and InvoiceSalesID=112). Is there any possibility of doing this in a crosstab?

Thanks!
0
Comment
Question by:jmugford
[X]
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
  • 8
  • 7
  • 2
  • +2
20 Comments
 
LVL 8

Expert Comment

by:kingjely
ID: 33528114

Hi imug,

have you tried clicking in the field>

choose suppress X2 formular

if {Table.CustomerSalesID} = 212 and {table.InvoiceSalesID}=112 then true else false
0
 
LVL 8

Expert Comment

by:kingjely
ID: 33528130

Sorry in more detail

Right Click the field >Format Field> Common>'tick the suppress checkbox'

choose suppress X2 formular

if {Table.CustomerSalesID} = 212 and {table.InvoiceSalesID}=112 then true else false.

Does this help ?

Kj
0
 

Author Comment

by:jmugford
ID: 33528176
Yes but it doesn't seem to do anything. Using the same code in a standard report the suppression works perfectly but it doesn't change anything on the crosstab.
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

 
LVL 8

Expert Comment

by:kingjely
ID: 33528190


I have just tried it and it seems to work fine for me?

Are you sure its not your AND / OR Operator that is cusing the issue?

what if you just try a simple test, where you can see id 212 is being printed, use this

if {Table.CustomerSalesID} = 212 then true else false;
0
 

Author Comment

by:jmugford
ID: 33528207
Just to make sure I'm doing this correctly, where do you then select the to see only the 'true' results or suppress the false results? Using the example if {Table.CustomerSalesID} = 212 then true else false, where do I say to just show 'true'?
Thanks!
0
 
LVL 8

Expert Comment

by:kingjely
ID: 33528276
HI sorry i will be with u in a sec, I just tried to replicate exactly what you have said your columns are witn a new crosstab with a table i have, I had 5 other reports open i was working on, and when i clicked ok on the cross-tab, crystal reports Dissapeard ! not in memory just all gone!

I'm just restarting it..

0
 

Author Comment

by:jmugford
ID: 33528293
No problem! I'm signing off for tonight but will check in later tomorrow. I'm clearly missing something.....Thanks for all of your help.
0
 
LVL 8

Expert Comment

by:kingjely
ID: 33528308
Hi okay before you go,

Just to double check,You want to suppress all records  = 122.

If you have 2 secs could you post a screen shot of what is printing, and what needs to be suppressed.

If not, Ill just try work it out ;)

No worries
0
 

Author Comment

by:jmugford
ID: 33528336
No problem - screenshot attached. In this example, If Cust.SalesID=212 (row1) and Invoice.SalesID=112 (row3) then I would like row3 to be suppressed (what I've circled). Hope this helps!
commission.png
0
 
LVL 11

Expert Comment

by:tickett
ID: 33528741
I would recommend surpressing the record in your record selection criteria (or do you need to display it elsewhere on the report?

Alternatively you may be able to use a formula for row3:

If Cust.SalesID <> 212 OR Invoice.SalesID <> 112 then Invoice.SalesID

This will return a NULL value for the row you want to hide- although i can't remember of the top of my head how cross-tabs handle NULLS (and/or if you can tell it how to handle them).

L
0
 

Author Comment

by:jmugford
ID: 33533991
@tickett - thiis doesn't seem to work with Crosstabs. Crystal doesn't seem to let you exclude data at a record level the way you can with a standard report. There are several conditions that may be applied so it's not as straightforward as using nulls.
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 33535389
Do you need the data for the rest of the report?

Where is the cross tab?

Os the data grouped or sorted?

mlmcc
0
 

Author Comment

by:jmugford
ID: 33538637
@mlmcc:
I don't need the data for the rest of the report. Not sure what you mean by where is the crosstab - it is the main report (not a subreport). The data is only 'grouped' through the selections in the crosstab (ie grouped in to rows).
0
 
LVL 8

Expert Comment

by:kingjely
ID: 33538813
Hi imug

I was playing round with this yesterday for ages, Also read lots of threads about the same thing, Seems Crystal report crosstabs aren't that smart as to beable to format with multiple conditions with suppression, even Record selection to filter the records seems pretty difficult, Mimmc or Ticket may beable to work it out, the main common response to this question i found was re-think the setup of the crosstab, or adjust the record selection in the database/sql side to include the records you want to see.

Crosstabs are defiantely not as flexible as the Main reports, they were designed just for basic summarising of fields..

I'll keep hunting, hopefulyl mimmc or ticket can prove me wrong for your sake ;)

Kj  
0
 
LVL 11

Expert Comment

by:tickett
ID: 33539602
You say the data is not needed for the rest of the report? So you can surpress the rows you don't want to see in the record selection expert?

Is the criteria complicated? Maybe we can help.

L
0
 
LVL 101

Accepted Solution

by:
mlmcc earned 500 total points
ID: 33547488
WHere is the cross tab - Which section is it in?
If you can group the data into 2 groups (data for the cross tab and data not wanted) then you could put the cross tab in the group header and get what you need.

If the data you want suppressed isn't needed in the report then there may be a way using selection filters to have the report only have the data you want.

mlmcc
0
 

Author Comment

by:jmugford
ID: 33547954
mlmcc - I will try using the group idea as you suggest. Right now my crosstab is the whole report - not a section within. Good idea, I will try that and get back to you.
0
 

Author Comment

by:jmugford
ID: 33548249
mlmcc - it worked perfectly! I basically started from scratch, created a new 'standard' report, applied the selection criteria and groups as you suggested and then inserted the cross-tab at the group level and all of the data displayed properly.
Thanks so much for all of your help!
0
 
LVL 8

Expert Comment

by:kingjely
ID: 33548267
Hey !
Glad you got it sorted.
Kj
0
 

Expert Comment

by:kefan
ID: 38398399
For anyone that is interested. I had a similar issue where client wanted option to show/not show an additional row level summary in a crosstab. Easy enough to give a parameter to choose whether to suppress the extra sumary level or not but not easy to get Crystal to suppress rows based on a parameter value.

My solution was to have two Report Footers each with a copy of the same crosstab apart from the extra summary level appearing in only one footer crosstab. It was then an easy matter of suppressing the relevant Report Footer based on the user selection of the parameter.

Seems simple now!
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…

617 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