We help IT Professionals succeed at work.

How to omitted fields shown in Crystal Report 7

sunyuchen
sunyuchen asked
on
Hi, i encounter problem in crystal report
For example, i have a report of 5 fields

 ------------------------------------------------
UserName  | UserID | Age | Sex | Address
-------------------------------------------------

initially when i preview the report, i will show all fields, but user can choose which field to omitted. So if user choose Sex to omitted, i will only show
--------------------------------------
UserName  | UserID | Age | Address
--------------------------------------

so, that means the report is dynamic. if i omitted Sex, address should move forward, and there shouldn't have a gap in between Age and Address.
How can i achieve this, pls help me. thanks
Comment
Watch Question

Commented:
You can approach this in one of two ways:

1.  Master the Crystal Reports engine for modifying report definitions on the fly. (hard)

2.  Using a fixed spacing font on the report, pass the column headers as a parameter to the report and include a column in your recordset which contains the concatenated values you wish to display for each record including necessary padding between columns. (easy)

DRRYAN3

Author

Commented:
Thanks Drryan3
sorry to say i am new in using crystal report. I don't quite understand your suggestion. Especially for second. ( first one skipped, totally dont understand :)
Just to explain more detail on my problem:
Now i made the report using a store procedure, and create a form in VB as well. In the VB form, user can choose which field he wants to shown in report, such as "UserID", or all of them. Then when i call report in VB, i will pass the user choice and display what they want. So, firstly the report should be dynamic according to fields. Sorry for poor description, hope u get more understanding of my question, :)
so, do you mind to give me some examples about ur 2nd suggestion, thanks a lot.
regards

Commented:
Hi Sunyuchen,
There are three ways to achive the objective.
firstly,based on the columns of the table or procedure you get the combination  & number. Suppose you have three columns in a report so the no. of combinations are six.
Insert all the six combination as six subreports in the detail sections (detail a to detail f) and write suppress conditions on right clicking on the section area(grey one)
for the combinations.
Secondly,Make a standard report and place all the fields in three columns and place one above another and right suppressing formulae. Need to have parameters from user and you will prompt as "Want to supress" the name of the field and give the default value as yes or no (for char, string) 1 or 0 for int and use those values in suppress field formulae for every field.
Thirdly the complicated one is go for a cross tab.
Uncheck the show grid.
Suppress the totals and summarized fields.
Resize and according to passed parameter right the selection criteria. In cross tab you don't have to worry for the white space as it would not let any whitespace between two columns.
Try and let me know.
I have done this.
If you want give me your e-mail id so that I can send you the screen shots of the report as well as the design and formulae, parameter values in a word doc which will help you.

Author

Commented:
hi, hzbera, thanks a lot
i think if u send me screen shots and design, etc will help me to get more understand ur suggestion.
here is my email address:
webdev01@webdb.chassasia.com
Thanks for ur help.
Hi sunyuchen,

One more option would be to create a crosstab report, but that will need data to be sent to the report in a way to suit the requirement of crosstab. A crosstab can dynamically display columns.

--PKG
Hi sunyuchen,

Let me explain the solution of kzbera by taking your example:

In order to make this soloution work you have to use variables that will store the information of the order of display. Let assume we assign a variable named Case, There will be two values (1 & 2) of that variable, like following:

Case=1 means:
------------------------------------------------
UserName  | UserID | Age | Sex | Address
-------------------------------------------------

Case=2 means:
--------------------------------------
UserName  | UserID | Age | Address
--------------------------------------

Now if you see above display options, the first three columns remain unchanged, Of SEX if to be displayed then Address will fifth column other wise it will be fourth column.
So what you will need to do is to make a copy of address field and paste it over the SEX field.
Now if case = 1, you will suppress the Address field pasted over the sex filed and make the sex filed and the address field in fifth column visible.
And if case = 2 , suppress the sex filed and the the address field in fifth column visible, Only the address field pasted over the SEX field will be visible.
Similar thing can be done for column headings.

I hope now it is clear.

--PKG

Author

Commented:
Hi,PKG
thanks for explaining, i think i am more clear about it.
but, another problem here is for those reports i need to do, every one has a large number of columns. (20 ~ 24)
so, if i use this method, will be very troublsome, do u have any suggestion according to this?
thanks a lot
Hi sunyuchen,

Since your requirement is very complex, it needs a fair amount of effort to handle it.

There is a compartively easier method.
Fist of all you must know the total number of possibilities of combination of columns that a user may request. Then make as many reports as there are possiblites.

Now place all these n number of reports as subreports in n report headers sections of another report (this will the main or the container report)

The main report will accept the parameters from users that will determine the number of columns to be displayed. Based on these parameters you will write suppress formula for each  headers sections in which the subreports are placed.

--PKG

Author

Commented:
Hi,PKG
For example, a report has 20 fields, user can choose any no of fields to show, means user can choose 1 or 2 or ...
all fields to show, doest that mean, my combination is
20*20=400? then i need to make 400 subreport?
thanks
Hi sunyuchen

Which database / source are you using.
can you use stored procedures as a source for your report.

--PKG

Author

Commented:
Hi PKG
yes,i am using store procedure to build my report
then, i build a VB form, in VB form, i show the columns in report as check box, user can choose which colunm they want to show, then, in vb, i call report, pass storeparam to repot, so the problem is how to achieve dynamic showing columns to user.
btw , i am using SQL Server 7, CR7, and VB6
Thanks a lot
Hi sunyuchen,

I think you should use a crosstab report.
For that you have to create a stored procedure that should accepts all the parameter and generate a table having followinig columns:

UserName  
ColumnName
Datafield

(All of varchar datatype)

some sample rows in that table will be

UserName | ColumnName | Datafield
-----------------------------
Jhon    | UserID | 203944
Jhon    | Age    | 24
Jhon    | Sex    | M
Jhon    | Address| 12, street X, City Y
Marry    | UserID | 203944
Marry    | Age    | 64
Marry    | Sex    | F
Marry    | Address| A-1, street 123, City N


You will fill this table according your requirements of the columns. You will select all the rows from this table at the end of the stored procedure and these rows will be the source for the report.

Now you create the crosstab report:
Three are three components of crosstab they are Rows, Columns and summerized field
Assign UserName  as rows
ColumnName as column and
Datafield as summerized field.
Change the summary option of the summerized field to MAX.

This will generate a cross tab. Suppress all "TOTALS" fields that crystal automatically calculates.

For the columns you will want the fileds to appear in a particular order , for that go to change summary and in the sort order used "specified order" and there you specify the required order.

Try this an let me know if that helps

--PKG

Author

Commented:
Hi PKG
Thanks very much
i will try this and tell u can work or not later.
really thanks a lot, :)

Author

Commented:
Hi,PKG, my boss changed this requiement of CR7, so, currently i dont need to do it. So thanks a lot.

Author

Commented:
sorry to accept ur answer so late, coz finaly my boss change the requirment

Explore More ContentExplore courses, solutions, and other research materials related to this topic.