Solved

Suppressing data in Crosstab reports in Crystal Reports

Posted on 2010-08-25
20
3,357 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
  • 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
 
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

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 100

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 100

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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
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…

705 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

18 Experts available now in Live!

Get 1:1 Help Now