Solved

Crystal Reports XI - Formula fields/Sorting/Grouping

Posted on 2014-12-09
43
533 Views
Last Modified: 2014-12-15
I'm working on a custom report and have gotten hung up trying to filter my data down the way my user would like to see the information presented.  My report uses a hundreds of unique option codes. The letters and numbers  in the option code are used to identify the location the option is used and what type of option it is. For example option 20918BNC1 is carpeting level 1 in Bed room 2. We use 'BN' to identify the room and 'C1' to identify the flooring type. Fortunately there are only about 15 rooms and 8 flooring levels so I was hoping I could just build in Formula Fields to accomplish my objective. That being said my goal is to allow my user to run the report from my enterprise server and be able to specify the room or rooms they wish to see and also the type of flooring from a selection window.
0
Comment
Question by:srilee
  • 19
  • 16
  • 8
43 Comments
 
LVL 100

Expert Comment

by:mlmcc
ID: 40489842
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
0
 

Author Comment

by:srilee
ID: 40489913
I'll give this a go and report back. My first concern is how to establish a relationship for this table...
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 40490034
I would LEFT JOIN it to the table with the room abbreviation

mlmcc
0
 

Author Comment

by:srilee
ID: 40490047
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
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 40490287
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
0
 

Author Comment

by:srilee
ID: 40490321
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
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 40490367
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
0
 
LVL 34

Expert Comment

by:James0628
ID: 40491064
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
0
 

Author Comment

by:srilee
ID: 40491358
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
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 40491411
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
0
 
LVL 34

Expert Comment

by:James0628
ID: 40492264
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
0
 

Author Comment

by:srilee
ID: 40492384
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.
0
 
LVL 34

Expert Comment

by:James0628
ID: 40492438
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
0
 

Author Comment

by:srilee
ID: 40492444
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.
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 40492453
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
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 40492458
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
0
 

Author Comment

by:srilee
ID: 40492858
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.
0
 
LVL 34

Expert Comment

by:James0628
ID: 40492921
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
0
 

Author Comment

by:srilee
ID: 40493695
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.
0
 

Author Comment

by:srilee
ID: 40493736
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.
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 40493821
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
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:srilee
ID: 40493837
Record
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 40493854
Can you upload the report file?

mlmcc
0
 

Author Comment

by:srilee
ID: 40493877
Yes.  Be happy to. Any particular location?
0
 

Author Comment

by:srilee
ID: 40493922
Sorry. I didn't realize I could upload to this site.
FlooringReport.rpt
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 40494243
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
0
 

Author Comment

by:srilee
ID: 40494300
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}
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 40494309
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
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 40494313
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
0
 

Author Comment

by:srilee
ID: 40494331
BNC1, BNC2, BHC1, BHC2
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 40494341
That is what I expected.

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

mlmcc
0
 

Author Comment

by:srilee
ID: 40494343
Yes
0
 
LVL 34

Accepted Solution

by:
James0628 earned 350 total points
ID: 40494712
Yeah, the multiple values complicate things.  As mlmcc said, CR treats multi-value parameters like arrays, and it turns out that a formula like "*" + [ array ] + [ array ]  just combines all of the values into one array.  I didn't know that you could even do that.  So if you enter "BN" and "BH" for the first parameter and "C1" and "C2" for the second, your formula ends up with a single array that contains 5 strings -- "*", "BN", "BH", "C1" and "C2".  So, the formula ends up trying to match OptionCode with each of those individual strings, and since you used Like, you get Like "*" from the first string, which matches everything.   That's why the report includes everything.  If you'd used = instead of Like, it wouldn't have included anything.

 Anyway, assuming that OptionCode will _always_ be in this form, XXXXXRRFF, where RR is the Room ID and FF is the Flooring Type, then you can use the same kind of test that you were using earlier.  You were using Mid to extract the required characters from OptionCode and compare those to literal values (eg. "BN").  You just need to compare the extracted characters to your parameters, instead of literal values.
 
mid({vw_SHEA_Flooring.OptionCode},6,2)={?RoomID} and
mid({vw_SHEA_Flooring.OptionCode},8,2)={?FlooringType}


 Also, I noticed that in the report that you u/l'ed, you had entered "*BN*" for {?RoomID}.  You need to leave off the "*"s and just enter the Room ID (eg. "BN").

 James
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 40494777
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),' ')

Open in new window


mlmcc
0
 
LVL 34

Expert Comment

by:James0628
ID: 40494840
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
0
 

Author Comment

by:srilee
ID: 40496129
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
0
 
LVL 100

Assisted Solution

by:mlmcc
mlmcc earned 150 total points
ID: 40496180
You would need to group on a formula since the group has to be based on a database field

    mid({vw_SHEA_Flooring.OptionCode},6,2)

You could write a formula like

Local StringVar RoomId;

RoomdID := mid({vw_SHEA_Flooring.OptionCode},6,2);
If RoomID = 'EB' then
   'Kitchen'
Else if  RoomID = 'BN' then
   'Bedroom 2'
etc

Open in new window


mlmcc
0
 

Author Comment

by:srilee
ID: 40497033
I'm afraid I'm having issue with this formula not working. Should this formula work in a group selection?
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 40497061
No, the formula should be a regular formula then used for grouping the report.

mlmcc
0
 

Author Comment

by:srilee
ID: 40497077
Sorry. Where is the formula entered then?
0
 
LVL 34

Assisted Solution

by:James0628
James0628 earned 350 total points
ID: 40497503
Create a new formula in the Field Explorer.  You'll need to give it a name (eg. RoomName or RoomGroup).  Then you'd group the report by that formula.

 If there are more than a few different RoomID's, I'd use Select-Case, instead of a series of If-Else's.  I just think Select-Case is simpler.  Something like:

Select Mid ({vw_SHEA_Flooring.OptionCode},6,2)
 Case "EB" :
  "Kitchen"
 Case "BN" :
  "Bedroom 2"

etc., etc.

 Default :
  "Other"


 James
0
 

Author Closing Comment

by:srilee
ID: 40500204
Thank you both!
0
 
LVL 34

Expert Comment

by:James0628
ID: 40500904
You're welcome.  Glad I could help.

 James
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now