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?

 

[Gino2020]

I have the Excel sheet with 2 columns 1st with dates and 2nd with days of the week.
Which in the Cross Tab, I added those 2 rows ex.
Sheet_.F1 (this is first column in Excel where all dates are consecutive)
Sheet_.Fri (this is the weekday names , sun, mon..etc)
then in the summarised field have Sum of @Same dt
where @Same dt is
if {BAQReportResult.LaborHed.ClockInDate} = {Sheet1_.F1} then
{BAQReportResult.Approved}

in the Report > Selection formulas > Record I have the following formula
{Sheet1_.F1} >= {@Merged_dt1} and
{Sheet1_.F1} <= {@Merged_dt2}

{@Merged_dt2} = 20/11/10 (your ex {?StartDate})
and
{@Merged_dt1} = 14/11/10 (your ex {?EndDate})

 

[lbass]

What does your the content of your formula show for {@Merged_dt2} if you go to field explorer->@merged_dt2}->edit? If you are hard coding a date, you don't need to use a formula.

-LB

 

[Gino2020]

Hi Lb the formula for {@Merged_dt1} is

date ((Mid ({BAQReportParameter.Option01},4 ,2 )+"/"+ Left ({BAQReportParameter.Option01}, 2) +"/" +Mid ({BAQReportParameter.Option01},7 ,4 )));

I had to resort to this formula cause my db returns date in us format mm/dd/yyyy and in text format. {@Merged_dt1} is the start date and 2 is end date.

How can i join the excel and fill the dates with no records ?

 

[lbass]

I have lost track of what the issue is here. Isn't the crosstab working?

-LB

 

[Gino2020]

Hi LB, the cross tab is working, in fact the report is working but the loading is very slow, the reason I guess is cause the Excel sheet is not linked to any other table. but if i link it ..then i get only the data that is present on the db and not the whole range of dates i need. How do i speed it up ?

 

[Gino2020]

any help will be appreciated

 

[lbass]

I have no more thoughts on this. Maybe someone else does.

-LB