custom selection fileds in areport 2

[PeriyurParthi]

hi,
i have a more than 15 fields in a report.The report is not sufficient to have all the fileds. i need to select the fields when i wish view the report, so that user can have their custom selection to print or view fields. replies will be more helpful.
thanks
parthi

 

[sewilj]

Parthi

Can the fields be grouped together logically for each user, or group of users. If so you could set up a parameter with the group names such as TEAM 1, TEAM 2 etc... Once you have done this you can suppress the fields you do not require for each of the reports. If you have additional detail sections you can have the group of fields in different detail sections and then supress each section that is not required.

hope this helps

Lewis Johnson
Midlands, United Kingdom

 

[PeriyurParthi]

Dear Lewis Johnson
thanks for your reply. can you just explain me more in detail please.
thanks
parthi

 

[sewilj]

Parthi

If you create a string parameter and set the default values as your separate teams, i.e. sales, accounts, admin, etc...

If specific fields are relevant to a particular group then these can be placed into separate detail sections within your report (right click the detail section and 'Insert Section Below'). This means that you could have a detail section for each team/group.

To suppress the groups that are not required right click on each details section and 'Format Section'. In the selection expert click the 'add formula' next to 'Suppress (No Drill-Down) and add the following

{?TEAM PARAMETER} <> &quot;TEAM NAME&quot;

this will ensure that this group is only included in the report when {?TEAM PARAMETER} the parameter name is equal to &quot;TEAM NAME&quot;. All you need to do is to place this line into each details section, replacing the &quot;TEAM NAME&quot; each time with the team name specific to those fields included.


Lewis Johnson
Midlands, United Kingdom

 

[lbass]

In thread149-655589 I outlined a method for dynamically adding desired columns to a report that I think would address your needs. This method allows the user to select any columns they wish, and the columns will appear in the report in consecutive order from left to right based on the order of selection.

-LB

 

[sewilj]

lbass

Am I right in assuming that the case operator in the formula you have added to thread149-655589 is used in place of an IF ELSE statement?

Lewis
United Kingdom

 

[lbass]

sewilj--

Yes, it's just a little simpler to write than an if-then-else statement. I'm not sure whether select/case can be used in CR 7.0 and below, so then you would need to use the if-then-else method.

-LB

 

[sewilj]

lbass

When I can find the time i'll experiment with this as I use CR v8.5.

It's probably rude to ask but I have posted thread767-660478 about the spead it takes to compile a report. Any possibility you could have a quick look to offer any useful advice as it's driving me crazy having to wait for the report to generate.

Thanks

Lewis
United Kingdom

 

[PeriyurParthi]

dear sewilj ,
thanks for your help. its working fine. but even then i have created like what lbass have told

Select {?Parm1}
Case &quot;CustID&quot; : totext({Order.customerID},0,&quot;&quot
Case &quot;OrderDate&quot; : totext({Orders.Order Date},&quot;MM/dd/yyyy&quot
Case &quot;OrderAmount&quot; : totext({Orders.Order Amount})
Case &quot;ProdName&quot; : {Product.ProductName}
|
|
Case &quot;19&quot; : {Product.Color}
default : &quot;&quot;

but giving error as &quot;DATE RANGE IS REQUIRED HERE&quot;. i can understand that i have included the date field which is date data type. even then my parameter value type is date time. i cant able find a solution for that. can you just give me a right way to solve the problem.
thanks
parthi

 

[sewilj]

parthi

I have not yet tried what lbass has suggested so I am not quite sure what is required here. Have you used the Select Expert to assign the Date selection parameter to your date field?

Lewis
United Kingdom

 

[lbass]

In my solution, the parameters are being used to choose what fields to display, not to limit them, i.e., they determine whether to include the date field or not, and all fields are converted to strings in the case statement. If you are getting a &quot;date range is required here&quot; message, then your parameter is a date range parameter--and you are attempting something different than what I suggested. My parameters were a list of different fields, all converted to strings, that could alternatively be used on a report. If you wanted, though, you could create a separate date parameter to use in the record selection statement.

-LB

 

[PeriyurParthi]

lbass
i agree with your sugesstion. but what i have done is i have created a parameter as param3 ,value type as date time, for the field data of birth(in access table - dob), in crystal formulas when i include this param3 then if i place this
Case &quot;CustID&quot; : totext({Order.customerID},0,&quot;&quot

then it is giving error as &quot;date range is required here&quot;, i need to solve this problem. replies and help with be more helpful.thanks

parthi

 

[lbass]

parthi,

I think you are mixing methods. What I was suggesting is that you create a parameter for each field {?field1}, {?field2}, etc., and that each parameter would be a string with defaults set to different fields like &quot;Name&quot;, &quot;ID&quot;, etc., so that Field1 might be ID or it could be Name, or Field2 could be ID or Name, etc., so that users were choosing each field to display in each column.

If you are now trying to do something different and trying to use the select/case operator for a date parameter, you would have to use dates for your cases. A select/case statement is just a simpler version of an if-then-else, so the logic would be the same:

if {?date} = Date(2003,09,24) then &quot;New&quot; else
if {?date} = Date(2003, 07, 31) then &quot;Old&quot;

Using a select/case, it would look like:

Select {?date}
case Date(2003,09,24) : &quot;New&quot;
case Date(2003,07,31) : &quot;Old&quot;
default : &quot;&quot;

If you are just trying to set up a parameter in the record select statement, then just try:

{?date} = {table.birthdate}

Maybe you should update us on what you are trying to do.

-LB

 

[PeriyurParthi]

thanks lbass,
what i need is exactly ill explain it.
i have a around 15 different type of fileds(includeing date data type). i need user to select the fileds to display. like i have

Select {?Param2}
Case &quot;ID&quot; : totext({EmpMaster.ID })
Case &quot;EmpName&quot; : totext({EmpMaster.empname})
Case &quot;DOB&quot; : totext({EmpMaster.DOB},&quot;dd/MM/yyyy&quot
case &quot;sex&quot; : totext({EmpMaster.Sex})
case &quot;contstart&quot; : totext({EmpMaster.CONTSTART})
case &quot;Curconstart&quot; :totext({EmpMaster.CURCONSTART})
case &quot;EmpPassExp&quot; :totext({EmpMaster.EmpassExp})
case &quot;MedDate&quot; :totext({EmpMaster.meddate})
case &quot;Nationality&quot; :totext({EmpMaster.NATIONALITY})
default : &quot;&quot;

the above is no problem for strings, when i go for date and time parameter, then it is not accepting in formula editor.
replies will be helpful
thanks
parthi

 

[lbass]

So you will have 15 parameters, and each one will be a STRING parameter, in order to determine what fields are placed on the report canvas. And they will all have the SAME 15 cases, so that the user is selecting from the same list for each parameter.

Please note that you ONLY need to use totext for fields which are not already strings, so your use of this is dependent on the datatype of the field you want as your result for each case. If {EmpMaster.ID} is a number, then you would want to use totext({EmpMaster.ID},0,&quot;&quot. Since {EmpMaster.empname} is a string, you don't need totext. For dates which are of date or datetime datatype, you would need totext({EmpMaster.DOB},&quot;dd/MM/yyyy&quot, but if it's already a string, again you don't need the totext function. Just as you would with an if-then statement, each result of the formula must be of the same datatype, and because the parameter is a string type, all result fields must be of string type.

You have only shown nine cases in your formula, but if you want to provide the options for 15 fields, you would need 15 cases, so I'm assuming you're only showing part of your formula.

Separately from the 15 parameters you would use to create up to 15 dynamic columns, if you want to establish a user-defined time period for the report, you could add a 16th parameter {?date} as a date range. This has nothing to do with the other parameters and does not require the use of the select/case operator. Simply go to the record selection formula area and enter:

{?date} = {table.date} //substitute the field that you want to use for the range

The other 15 parameters are used only in the body of the report. See the original thread for details.

If this still does not address what you are trying to do, please explain in more detail what you are trying to do with the date parameter (if you really want one).

-LB

 

[PeriyurParthi]

lbass thanks.

{?Param3}<>{EmpMaster.DOB}, i did like this in formula (col3), the reports prints as true or false for all the records. i need the value of the field in report meantime let me expalin what i am trying to do.

i have around 15 fields with all datatypes such as string,number,date. all this 15 fields is not possible to display for users in screen. so i would like to go for a custom slection of fileds. so that they can choose the fields from the vb-form then it is simply displayed in the crystal reports. there is no calculation or filter in the formulas or in parameter. (it may come on furture). this is what i require. if you have any doubts plese let me know. ill appreciate your help.
thanks again
parthi

 

[lbass]

I really know nothing about using vb-forms for passing parameters. Maybe someone else can help with that.

All of my earlier suggestions were based on using parameters within Crystal. It seems that you do not yet understand my earlier suggestions, and I'm unsure how to explain the method any more clearly. If you are still interested in trying to set up these parameters in order to create dynamic columns WITHIN Crystal, please let me know, and I'll try one more time.

If you post your e-mail, I would be willing to send you a sample report (using the Xtreme database that comes with CR) with three dynamic columns so you could read the earlier thread and then look at the report to see how to set it up.

-LB

 

[PeriyurParthi]

dear lb,
my email id is periyurparthi@hotmail.com. there is nothing related to vb forms also. simple, i cant able to display date data type in bleow code. its giveing error under parameter name as &quot;DATE RANGE IS REQUIRED HERE&quot;.

Select {?dob} // error here
Case &quot;ID&quot; : totext({EmpMaster.ID })
Case &quot;EmpName&quot; : totext({EmpMaster.empname})
Case &quot;DOB&quot; : totext({EmpMaster.DOB},&quot;dd/MM/yyyy&quot
case &quot;sex&quot; : totext({EmpMaster.Sex})
case &quot;contstart&quot; : totext({EmpMaster.CONTSTART})
case &quot;Curconstart&quot; :totext({EmpMaster.CURCONSTART})
case &quot;EmpPassExp&quot; :totext({EmpMaster.EmpassExp})
case &quot;MedDate&quot; :totext({EmpMaster.meddate})
case &quot;Nationality&quot; :totext({EmpMaster.NATIONALITY})
default : &quot;&quot;

then i tried in record selection as u said
{?date} = {table.date} //substitute the field that you want to use for the range

but it is display same records all pages.

 

[lbass]

I am copying the entire contents of the other thread here. Please follow these instructions to the letter. Your last post shows that you are still trying to make the parameter specific to a specific field--but what you should be doing is creating a string parameter that only refers to a column number. The formula that links the parameter to a specific field is the SAME for every column, except the parameter name changes. The formulas {@col2} for {?parm1}, {@col2} for {?parm2}, {@col3} for {?parm3}...{@col5) for {?parm15} are then placed across the report canvas. Here's the thread--I just changed the column numbers to correspond to your need for 15:

Here's a method which allows the user to choose which columns they wish and they automatically will be in columns positioned from left to right.

First create 15 discrete string parameters called {?parm1} to {?parm15} and set defaults for each with all possible column names. For these defaults, choose descriptive names that will work well as column headers. Also enter a blank as a default choice, and using the toggle key, make this the topmost parameter choice. This will become the default option for someone who only wants a couple of columns--the columns for which they do not actively choose a field will return as blanks. In the prompt section, you might want to enter text which tells the user to click on the dropdown for potential field names until they have the fields they wish and then close out of the parameter prompt (without making selections for the remaining parameters).

Next create 15 formulas called {@Col1} through {@Col15}. They will all be exactly the same except for the parm number, which will vary from 1 to 15, so you can copy the following formula and just change the number for each new formula, substituting your own field names. {@Col1} would look like:

Select {?Parm1}
Case &quot;CustID&quot; : totext({Order.customerID},0,&quot;&quot
Case &quot;OrderDate&quot; : totext({Orders.Order Date},&quot;MM/dd/yyyy&quot
Case &quot;OrderAmount&quot; : totext({Orders.Order Amount})
Case &quot;ProdName&quot; : {Product.ProductName}
|
|
Case &quot;15&quot; : {Product.Color}
default : &quot;&quot;

The formula should have 15 cases, followed by the default. Note that each result of the case statement is converted to a string, since they must be of the same type.

Place the 15 formulas across your details section. Next for each column heading, delete the automatic text &quot;Col1&quot; and insert the corresponding parameter, e.g., {?Parm1}. This will give you the correct (dynamic) name for the column heading. Then for each heading go to format text->common->suppress->x+2 and enter:

{?Parm1} = &quot;&quot; //substituting the correct parameter number for each column heading.

That should do it.

I am also forwarding an example that uses only three columns--you would be expanding the idea to 15 columns.

-LB

 

[lbass]

I tried e-mailing you directly through the link above and it didn't work--the e-mail was returned to me.

-LB