Crystal Reports XI - Formula fields/Sorting/Grouping

Probably the easiest method would be to add tables that translate the code to the word equivalent.

SO a table like
ROOM INFO
Room ID      Room Text
   BN                 Bedroom 2

You could then use the table for the filters.

mlmcc

I'll give this a go and report back. My first concern is how to establish a relationship for this table...

I would LEFT JOIN it to the table with the room abbreviation

mlmcc

I don't have a room or flooring table. Those will be new tables.  I've got to join this/thes to the option table

What database are you using?

Are you using a command as the datasource or just the tables?

I assume all you have is the field value 20918BNC1 and you need to use the BN and C1 to link?

mlmcc

It's a  SQL2012 database and I'm just using the tables in a Crystal Report. Yes all I have is the option ID's like 20918BNC1 and all I need is the BN and C1

Do you want the user to be able to select the code and see the description then show just the records for the code selected?

Do the codes change much?  Are there so many it would be a problem typing them in?

mlmcc

My report uses a hundreds of unique option codes.

 When you say that the "report uses", do you mean that you have hundreds of codes defined in the report somehow, in parameters or formulas?  Or just that the _data_ that's read by the report includes hundreds of codes?  I'm guessing the latter.

 As for the codes to identify the room and type of flooring (eg. "BN" and "C1"), is your concern how the user will enter/select those codes, or is it simply how to match the entered codes with the option codes (eg. "20918BNC1") in your data?

 If it's the latter, then the simplest answer may be to use Like.  For example:
{option code field} like "*" + {?Room} + "*" and
{option code field} like "*" + {?Flooring} + "*"

 That assumes that all of the room and flooring codes are different (eg. "BN" isn't both a room code and a flooring code), and that those same strings won't show up anywhere else in the option code (eg. you won't have an option code like "BN918BNC1").

 If the room and flooring codes will always be together, as in your example, you could use:
{option code field} like "*" + {?Room} + {?Flooring} + "*"

 If they're always at the end of the option code, you don't need the final "*".

 James

James,
I like you idea. For clarification all my option codes are unique and stored in a SQL table (tbAvailablePlanOption). All option codes contain both the Room ID (BN in my example) and the Flooring Type (C1 in my example).  The format of the option ID will always remain constant with the Room ID and Flooring type in the same positions.  With this information can you clarify/elaborate on  your example above?

Option code ex. 20918BNC1

If I understand the data, your option table has values like BNC1

If so you could use that table for the list of values for the parameter then the selection from James above becomes

{option code field} like "*" + {?OptionCode} +  "*"

mlmcc

If the room and flooring codes are combined, then you could use a formula like the one that mlmcc posted, although if they're always at the end of the option ID, then you don't need the "*" at the end.  You can go to Report > Selection Formulas > Record and enter something like:

{option ID field} like "*" + {?Option Code}

 James

Thanks guys but I'm still not where I need to be with that. What I need to (if its possible) is to create Forumla Fields for each Room ID (ex. BN = Bed 2, BQ = Bed 3) and do the same for Flooring type (ex. C1= Carpet Level 1, C2 Carpet Level 2) I then want to run the report by selecting a room(s) and flooring type(s).

Ex.  Selection Criteria = BN & C2 would return all the flooring for Bed2 and Carpet Level 2.
I have it working now using this Formula Field in Select Expert,  mid({vw_SHEA_Flooring.OptionCode},6,2)="BN" , however doing it this way doesn't let me allow the user to choose, it forces them to ue what I built in the report.

Why do you need formulas?

 It sounds like you just want two parameters, one for Room and one for Flooring Type.  You could have the user type in the values, or you could set the default values and they'd select them from the list.  If you set the default values, you could also enter a description for each value (eg. the parameter value is "BN" and the description is "Bed 2").  If you set the default values, and then the values change (eg. you add a new Room ID), then you'd have to edit the report and update the default values.

 If the Room ID or Flooring Type values change (new values are added or old ones are deleted), you could use a dynamic parameter (I think CR XI supports those).  You'd need some kind of table(s) that contained lists of all valid Room ID and Flooring Type values, possibly with descriptions.  Basically, for each parameter, you'd create a query that would get the list of values from a table, and the user would be able to select from those values.

 James

I started working in this direction but got side tracked trying to link this new table containing the Room ID (BN) and Room Descriptions (Bed 2) back to the rest of the database or more specifically the Option table.

To do that you will need the list of the codes.

If you have a table with the codes and descriptions it can be used as the source for the parameter values.
If no table exists you could create one or just add the values to the list for static parameters.

To create a static parameter (no table with the values)
Right click PARAMETERS in the field explorer
Click NEW
Name - RoomID
Type - String
List of Values - Static
In the VALUE column click the CLICK HERE TO ADD ITEM
Add the first value
Click in the description column of that row
Enter the description
Repeat until you have all the values added
To sort them click the VALUE header
Click OK

Repeat the above for the FloorType

Use the formula above
{option code field} like "*" + {?RoomID} + {?FlooringType}

mlmcc

If you have a table, you don't need to link it to the rest of the data unless you are trying to also display the description from the table.  Just add the tables then use them as the source for the parameter values.

So long as the fields from the tables aren't used in the report the tables won't affect the query

mlmcc

The static value solution  should be fine as my rooms and flooring types don't change. That being said I built this as you suggested however the report is bringing in all my options and not looking for the RoomID (BN) or Flooring type (C1) that is inte option code.   (ex 20918BNC1 ) I'm not great with code but I don't see where this formula would do that.

{option code field} like "*" + {?RoomID} + {?FlooringType}

Am I missing something?  And by the thank you.

Assuming that you actually tried that test, and still got all records, I suspect that you just created a new formula and entered the test there.  You need to enter it in the record selection formula.  As I mentioned in an earlier post, you can go to Report > Selection Formulas > Record, and you'd enter the test there.  If you're not sure exactly what to enter, post the exact name of your Option Code field, and your parameter names.

 If you already entered the test in the record selection formula and you're still getting all records, can you post your exact formula?

 James

Yes. In the Formula Workshop - Record Selection Formula Editor I have the following formula under the Record Selection group;  {vw_SHEA_Flooring.OptionCode} like "*" + {?RoomID} + {?FlooringType}  

The report then prompts me to enter a RoomID and a FlooringType as planned but for some reason it bring in all my option and does not filter down on the RoomID or FlooringType.

Going back to you instructions on the Parameter for RoomID. For testing purposes do I only have "BN" and "BQ" listed under the Value column and then my corresponding description to the right of that. Nothing else is required is it?  Seems very straight forward but the report doesn’t seem to acknowledge the values.

Also thought it might be worth mentioning that even thought I enter the formula as described above I can see and edit it from Select Expert. Didn't know if that was normal behavior.

Is the formula in the RECORD selection or GROUP selection?

If you have it in the group selection, that simply suppresses some groups but doesn't filter the records at all.

mlmcc

Record

Can you upload the report file?

mlmcc

Yes.  Be happy to. Any particular location?

Sorry. I didn't realize I could upload to this site.
FlooringReport.rpt

You have your floor and room parameters set to accept multiple values.  I think you need to set them so they don't accept multiple values.

I changed the selection formula to
{vw_SHEA_Flooring.OptionCode} like "*" + {?RoomID}[1] + {?FlooringType}[1]    

The report updated (using saved data) to show only the ones with BNC2 as part of the option.

Also don't add the * around the ROOM parameter

Do you need/want the user to be able to enter multiple values?
If so the selection formula may need to be rewritten to handle that.

mlmcc

I removed the multiple values from the Room and Flooring parameter and it works but I do need to be able to specify multiple values.

I don't understand the [1] after {?RoomID}

Multiple value parameters are treated as an array.  the  [1] says to use the first element of the array.  I suspect the system may have a blank/null value that is being used or that the concatenation of the array doesn't work so it is ignored and the selection is empty.

mlmcc

If you need the users to be able to enter BN and BH for the room and C1 and C2 for the floor do you want to see

BNC1 and BHC2 or
BNC1, BNC2, BHC1, BHC2

mlmcc

BNC1, BNC2, BHC1, BHC2

That is what I expected.

Is it always the right 4 characters in the option that need to match?

mlmcc

Yes

This formula seems to work.  I suspect it may be slow

Local StringVar RoomFloorList;
Local NumberVar RoomIndex;
Local NumberVar FloorIndex;

For RoomIndex := 1 to UBound({?Room}) do
    For FloorIndex := 1 to UBound({?Floor}) do
        RoomFloorList := RoomFloorList & ' ' & {?Room}[RoomIndex] & {?Floor}[FloorIndex];

Right({Sheet1_.Option Code},4) IN Split(Trim(RoomFloorList),' ')

Select allOpen in new window

mlmcc

mlmcc,

 FWIW, I think it'd be much better to just extract the two separate strings from the field and compare the parameters separately (as in my post), rather than try to create all possible combinations from the two parameters and compare those to the last 4 characters in the field.  The separate tests might even be passed to the server, if CR knows the command to use to extract the characters from the field in the db that's being used.

 Combining the two parameters would be the simpler solution if each one only had a single value.

 James

This seems to be the best solution.

mid({vw_SHEA_Flooring.OptionCode},6,2)={?RoomID} and
mid({vw_SHEA_Flooring.OptionCode},8,2)={?FlooringType}

Can I then group based on a RoomID?  ex. if RoomID = EB then KITCHEN

I'm afraid I'm having issue with this formula not working. Should this formula work in a group selection?

No, the formula should be a regular formula then used for grouping the report.

mlmcc

Sorry. Where is the formula entered then?

Thank you both!

You're welcome.  Glad I could help.

 James