Crosstab - Supress Columns

I'm using CR8.5 with RDC, VB 6, SQL Server 7, I build the recordset in VB and pass it to CR. The query can't be changed...

I need to build a crosstab...

Example Data:
Bob - Value1
Bob - Value2
Joe - Value1
Joe - Value3
John - Value2
Mary - Value2
Mary - Value3
Mary - Value3
George - Value3
George - Value3
George - Value3

The crosstab would look like this:

Name Value1 Value2 Value3 Total
Bob 1 1 2
Joe 1 1 2
John 1 1
Mary 1 2 3
George 3 3

Problem 1 - I need Value1 and Value2 displayed all the time, regardless if there is any data in the column. I tried to use 'Specified Order' in the Group Options, but it always leaves out a column if there's no data.

Problem 2 - There's a column that always returns data that I don't want displayed (In this example, it would be Value3). I was able to leave this column out by not specifying it in the Specified Order list of columns, but I was looking for an easier way.

Problem 3 - (part of Problem2) - I was able to use a conditional suppress formula that said if the columnheader was Value3 then suppress the column, but then I get a blank column even though the option for suppress empty columns is turned on. I always thought suppress meant to make it invisible in runtime, but it seems that in the crosstab is just makes the data invisible and leaves the column there.Hi
PROBLEM 2:
you can filter the record through Recod Selection Formula which u can write by Selecting Report->Record Selection->Record(CR9) menu, let say if you want to filter records where Second colum contain "Value3" record selction formula will be

left({TableName.ColumName},6)<> 'Value3'

OR

{TableName.ColumName} <> 'Value3'

Where TableName will be the name of Source Table or in your case query and colum name will be the colum on which you apply the filter

PROBLEM1:

you cannot display a value for colum source which is not in the repot data in crystal reports if you are using Crystal Reports Crosstab, however, if the colums of the crosstab reports are static, by static i mean that your are sure(in your example) about that you have to just display "Value1" "Value2","Value3" then you can create a Customise crosstab report, otherwise it is not possible, just have a look at the sample report which I have created for you based on the sample data you provided

please see the attached repot which I have created for you.
Hope this will help you(report is created in crystal reports9)

PROBLEM3:
the answer is in Report file, please view the report

(unfortunately I am not able to attache the report as its extention is .rpt which is not allowed by the forum, send me an email and I will send it to you)
MY EMEIL ADD: jadoger_1@.hotmail.com

Cheers|||jadoger,

I'm not able to send or recieve emails from this computer at this time. Can you put the rpt file into a zip file and attach it that way? If not, don't worry about it. I've already "solved" my problem with a workaround. I created my own crosstab which I have more control over.

If you can send the rpt file though, I would be curious to see what you did and see if that would have solved my problem. I appreciate you putting the time and effort into creating it for me.