Crosstab suppress some rows

[orion127]

In a cross tab I have this as a result

blue red white green Total

John 1 2 8 4 15
Mary 3 0 0 5 8
Tod 0 1 1 1 3

IF the sum of column "red" = 0 AND column "white" = 0, I don't want the row to appear. The result should then exclude the row "Mary"

How do I do that?

 

[lbass]

Create these formulas:

//{@red}:
if {table.col} = "Red" then
{table.amt}

//{@white}:
if {table.col} = "White" then
{table.amt}

//{@amt}:
if not(
sum({@white},{table.name}) = 0 and
sum({@red},{table.name}) = 0
) then
{table.amt}

//{@name}:
if not(
sum({@white},{table.name}) = 0 and
sum({@red},{table.name}) = 0
) then
{table.name}

Use sum of {@amt} as your summary and {@name} as your row field in the crosstab. If you don't have one already, insert a group on {table.name} in the main report.

-LB

 

[orion127]

Thanks a lot, it's exactly what I need!

 

[Gino2020]

Hi I have a similar problem,
I have an clock in solution, where i have some dates with data such as, various entries for a date eg, 01/11/2010 1hr, 01/11/2010 3 hrs etc, 03/11/2010 2hrs , 05/11/2010 4.5hrs, 05/11/2010 4 hrs so i need total for each day and highlight only those days, where total is less than 4.5, including days which don’t have records eg 02/11/2010 & 04/11/2010, I summarise in totals using a cross tab, to get summarised output for each day as,
Totals
01/11/2010 4
03/11/2010 2
05/11/2010 8.5
in order to get the dates which didn’t have records, i added a dataset from Excel spreadsheet , which LB suggested in her solutions, also the Cross tabs, where i just have a sequential dates for the year , and use record selection to select only those dates in range which i need to display, so the result i get
01/11/2010 4
02/11/2010 0
03/11/2010 2
04/11/2010 0
05/11/2010 8.5
so far so good, all using cross tab, now i want to suppress rows which have total > 4.5 so the result should be
01/11/2010 4
02/11/2010 0
03/11/2010 2
04/11/2010 0
How can i do that?, thanks LB for giving valuable suggestions i have managed to get certain reports ready but now stuck in the suppression of rows.

 

[lbass]

You can't really suppress the label based on that condition, although you could suppress the summary by referencing: currentfieldvalue >= 4.5 . Instead, in the main group, insert a group on {table.date} and then create a formula:

if sum({table.hours},{table.date}) < 4.5 then
{table.hours}

Use this formula instead of your hours field as the summary field. On the customize style tab, check "suppress blank rows" and "Suppress blank columns".

-LB

 

[Gino2020]

hi I can suppress summary, but how do i suppress the whole row is there a work around, also in terms of the performance what do u think of cross tabs are they slow ?
thanks in advance, you have been a freat help LB.

 

[Gino2020]

hi I can suppress summary, but how do i suppress the whole row is there a work around, also in terms of the performance what do u think of cross tabs are they slow ?
thanks in advance, you have been a great help LB.

 

[lbass]

Crosstabs are actually very efficient. I showed in my earlier post how to eliminate the unwanted rows.

-LB

 

[Gino2020]

Hi LB , i am able to capture the summary , assign value 0 if > 4.5 , and with suppress rows in Customise Style of Cross Tabs, am able to suppress rows, but it suppresses the dates which have 0 value as well ,(to give a more clear picture, those are the rows which have no records, coming from db, i want to display all days when the user booked hours less than 4.5, and also those days when he didnt book anything, i.e no records existing)

 

[lbass]

I don't actually know what you did. You should just use a formula like this:

Replace {table.hours} with a formula {@hours}:

if isnull({table.hours}) then
0 else
{table.hours}

Then use a formula like this as your summary field in the crosstab:

if sum({@hours},{table.date}) < 4.5 then
{@hours}

On the customize style tab, check "suppress blank rows" and "Suppress blank columns".

-LB

 

[Gino2020]

Hi LB thanks, i got it working Finally , thanx a million, can you suggest a book for Crystal Reports (with advanced features explained).

 

[lbass]

You could try the George Peck books and/or Ken Hamady's manuals at

http://www.kenhamady.com

or follow the threads on Tek-Tips where I've learned most of what I know.

-LB

 

[Gino2020]

Hi LB, The solution works fine, but is very slow , I think it is due to the excel sheet with dates I am linking, can I replace that with a formula to create dates.

 

[lbass]

There is nothing in my suggestion that would cause a report to be particularly slow, so I think you should look for other reasons. Check to see whether the entire record selection formula is passing to the SQL query (database->show SQL query). If it isn't, you have not optimized your report for performance. Also make sure you don't have two separate SQL queries. Is the Excel sheet linked to another table?

-LB

 

[Gino2020]

Hi LB, The SQL query is passing
< SELECT `Sheet1_`.`F1`, `Sheet1_`.`Fri`
FROM `Sheet1$` `Sheet1_` >
the entire calendar I have filled the excel with consecutive dates for 10 years.
The Excel sheet is not linked to other table, however if I link it then it only shows those dates which have data against them, and not the dates which have no data ex;
date selected for reporting is 14/11/10 till 20/11/10, & data in the table is :
16/11/10 00:00:00 4.00

17/11/10 00:00:00 4.00
17/11/10 00:00:00 5.00

18/11/10 00:00:00 8.25

data i want to display on the report,
14/11/10 Sun 0.00
15/11/10 Mon 0.00
16/11/10 Tue 4.00
17/11/10 Wed 9.00
18/11/10 Thu 8.25
19/11/10 Fri 0.00
20/11/10 Sat 0.00
Total 21.25

If I don't link the excel sheet to the database then in the record selection use the formula < {Sheet1_.F1} >= {@Merged_dt1} and
{Sheet1_.F1} <= {@Merged_dt2} > it works fine but is very slow.

where @Merged_dt1 & @Merged_dt2 is 14/11/10 and 20/11/10 respectively , which I get in another table options set for the report.

Please advise, Many thanks.

 

[Gino2020]

if i link the Excel sheet with the data table , the SQL query looks like
< SELECT `Sheet1_`.`F1`, `Sheet1_`.`Fri`
FROM `Sheet1$` `Sheet1_`
EXTERNAL JOIN Sheet1_.F1={?ReportDataSet: LaborHed.ClockInDate}

>
used external join so it should include the dates which don't have a record in the LaborHed.ClockInDate, but doesn't seem to make a difference.

 

[Gino2020]

Also another thought, what do you think about it. Can i use arrays to fill it with dates in the range ex fill the array with dates starting from 14/11/10 till 20/11/10.

 

[lbass]

Are you able to use a left join from the Excel sheet? You should then be selecting on the excel sheet field by adding something like:

{Sheet1_.F1} >= {?StartDate} and
{Sheet1_.F1} < {?EndDate}

What is the content of your merged date formulas? Formulas used in the selection formula often (but not always) slow selection.

I wouldn't try to use an array in an inserted crosstab.

-LB

 

[Gino2020]

i am able to join (left outer join) but does not give me the desired output.
the formulas are as follows;
Select Expert Record :
{Sheet1_.F1} <= {@Merged_dt2} and
{Sheet1_.F1} >= {@Merged_dt1}

the merged date formula is nothing but the start date and end date in uk format rather than usa i.e dd/mm/yy

 

[lbass]

No, you can't use the records from the other table in the selection formula and still maintain the left outer join. I was asking about the content of the nested formulas, e.g., {@Merged_dt1}.

-LB