cross tab rows supression.

[njahnavi]

I have a cross tab in crystal reports version XI release 2.
The cross tab has three rows and two columns.

Row #3
Row #2
Row #1

I want the cross tab to show only the subtotal of Row #3 i.e. next to Row #2 and all the others to be supressed.

please help as i have to submit the report by today.

any help will be greatly appreciated.

 

[lbass]

In the customize style tab you can select row and column subtotals for suppression.

-LB

 

[njahnavi]

Yes I can however for the first row we can't do this.
I want all the rows to be supressed except the subtotal of the first row set.

 

[lbass]

Don't know what you mean by "first row set". If you have three rows (not three instances of one row), then you should remove the rows you don't want to display. If you only want subtotals, that implies that you should remove that row as well. It is possible to just have columns and a summary field.

If this doesn't resolve it, you should show a sample of the data you are getting (labeled clearly) and then show a sample of what you would like to see.

-LB

 

[njahnavi]

Firstly I would like to apolozise for my late reply.
As i said i had to submit this report as of friday however this problem was recurring so I discussed with my PL and he said forget this part and complete the other parts and give it to me as early as possible...so i have been given time to complete this in the next release..this release it is going with out the feature...


I have a cross tab report which shows the no.of trailers required for that 6 months.

The data in the database is in the following way...
JObNO
JObLocation
TrailerName
TQuantityRequired
TQuantityAvaliable
TDates

so the sample data will be like this...

jobno joblocation trailtername tqtyrq tqtavail Tdates
0 locA T1 10 11 1/1
0 locB T2 22 20 1/21
1 locA T2 11 22 2/21
2 locA T1 33 12 2/11
3 locB T2 44 20 2/1

i have first created a cross tab like
rows : trailername
Columns : calendardate (Month only)
calendardate (Day only)

and so the cross tab was displyaing data like this...

Jan Feb
1 2 3..21.31 1 2 .11....21..28
T1 10 33
T2 22 44 11

and then a new requirement came like that the user would like to see all the trailers from the master list event though they are not used in those months and also they would like to restrict the data by location and also job too...

The master trailer cna have data like

Trailername
T1
T2
T3

To incorporate all the truck from the master trailer list i have made a left outer join so that it will have all the fields...

And then i have got the result like

Jan Feb
1 2 3..21.31 1 2 .11....21..28
T1 10 0 0 0 0 0 0 33 0 0
T2 0 0 0 22 0 44 0 0 11 0
T3 0 0 0 0 0 0 0 0 0 0.


To incorporate the other ones like to restrict by loation and job I have included both in to the crosstab...
so now my cross tab looks like

Rows : TrailerName
joblocation
jobno.

Columns : Calendardate (which shows once for month)
calendardate (which shows only date).
Sum : Sum of @totalTrailerCount.

@totaltrailercount formula has this value

if {@JObformula} and
{Trailer.Tdates} in {?Pm-?JobStartRange} to {?Pm-?JobEndRange} and
{Trailer.JObLocation} = {?Pm-?Plantparam} AND {Trailer.tdates} = {Calender.CalendarDates} then
{Trailer.TQuantityRequired}

And the formula {@jobformula} has this value

if {?Pm-?Jobparam} = -1 then true
else
{Trailer.JObNO} = {?Pm-?Jobparam}

so now i get the cross tab result as

Calendardates...
jobno
Location
Trailername

Now i want to supress the jobno rows and cross tab is not allowing this...and i want a work around...

let me know if you need any other info...

 

[lbass]

Not following this as you did not actually show the crosstab results you got after adding the other two rows. I don't think anything you did should change the order of the rows though.

Please explain what you have tried for suppression, and what results you get. I'm not understanding the problem.

-LB

 

[njahnavi]

Thanks for following up the post LB.

The cross tab results after adding the jobno and jobloaction is as below...
Jan Feb
1 2 3..21. 31 1 2 .11....21...28
T1..locA..0 10 0 0 0 0 0 0 0 0 0
T1..locA..2 0 0 0 0 0 0 0 33 0 0
T1..LOcATotal 10 0 0 0 0 0 0 33 0 0
T1 Total 10 0 0 0 0 0 0 33 0 0
T2..locA..1 0 0 0 0 0 0 0 0 11 0
T2..LocATotal 0 0 0 0 0 0 0 0 11 0
T2..locB..0 0 0 0 22 0 0 0 0 0 0
T2..locB..3 0 0 0 0 0 44 0 0 0 0
T2 LOcB Total 0 0 0 22 0 44 0 0 0 0
T2 Total 0 0 0 22 0 44 0 0 11 0
T3 Total 0 0 0 0 0 0 0 0 0 0
GrandTotal 10 0 0 22 0 44 0 0 11 0

HOwever i just want to see the Trrailer..Total e.g. T1 Total rows and i want to supress the all others.

I am having issues when i try to supress the job subtotal since the cross tab expert has grayed it out.

Hope I made some sense..if not pelase let me know..i will try to explain further...

I once again thank you for following up the post...

 

[njahnavi]

The table was not well formatted in the above reply so ii am sending it formatted...

Jan Feb
1 2 3..21. 31 1 2 .11....21...28
T1..locA..0 10 0 0 0 0 0 0 0 0 0
T1..locA..2 0 0 0 0 0 0 0 33 0 0
T1..LocATotal 10 0 0 0 0 0 0 33 0 0
T1 Total 10 0 0 0 0 0 0 33 0 0
T2..locA..1 0 0 0 0 0 0 0 0 11 0
T2..LocATotal 0 0 0 0 0 0 0 0 11 0
T2..locB..0 0 0 0 22 0 0 0 0 0 0
T2..locB..3 0 0 0 0 0 44 0 0 0 0
T2 LOcB Total 0 0 0 22 0 44 0 0 0 0
T2 Total 0 0 0 22 0 44 0 0 11 0
T3 Total 0 0 0 0 0 0 0 0 0 0
GrandTotal 10 0 0 22 0 44 0 0 11 0

 

[lbass]

I don't see why you don't just use trailer as your row field, i.e., if you remove the other two rows (jobno and job location), you will have the correct results.

-LB

 

[njahnavi]

yes you will get the correct results however if user wants to see only for certain job and certain location then it will fail..

 

[njahnavi]

p.s i can give those conditons in the record selection however then the Trailer T3 will not be visible as it will not have null location adn null job

 

[njahnavi]

please let me know if you need any more information LB.

 

[lbass]

I'm sorry but I'm just not following this.

-LB

 

[njahnavi]

I don't see why you don't just use trailer as your row field, i.e., if you remove the other two rows (jobno and job location), you will have the correct results."

if i remove JObno and JOblocation then i can not have correct results the reason for this is..since user have selection criteria on JObno and JOblocation. These values are in two dropdown boxes.

If jobno = all then i have show all the jobs otherwise if it is equal to any jobno then i will have to show only for that particular job.

For joblocation they have to select only one location so we have to show the rows related to that joblocation = 'chicago'

The first sted i did was i have kept these in to the record selection formula like this
@jobparameterformula and joblocation = 'chicago'

@jobparamterformula :- if jobno = 'All' then true
else jobno = '019'

Then the results are fine except that T3 is not showing up since the T3 is not used in any job and in any location.
This was showing up only becuase we have made left outer join with the master trailer table.

so i cant go to the approach of the record selection.

That whay with out restricting any reocrds i have created a cross tab where with the calcuations i can make the count to 0 if it does not meet the conditions.

The results are correct however I want to supress the jobno rows.

I hope i made some sense atleast this time...
please reply..as this is my main hope..

 

[lbass]

So you want to conditionally suppress the rows depending upon the jobno parameter value?

I guess you could select the cells you want conditionally suppressed and then use a formula like this:

if {?jobno} <> "All" then
gridrowcolumnvalue("jobno") <> {?jobno} else
if {?jobno} = "All" then
false

-LB

 

[njahnavi]

I would like to THANK YOU (by caps i meant emotion not shouting)...for replying back..as i said this is my last and final hope...

I want the cross tab to
1)supress the job rows despite of the job parameter i.e. display only trailer and location in the rows of the
2)calculate the values based on the job and the location parameter.

 

[njahnavi]

and the main thing is the cross tab has to show all the Trialers from the master trailer table