cancel
Showing results for 
Search instead for 
Did you mean: 

How to look up values in a table/array in Crystal 2013?

Former Member
0 Kudos
706

I'm a newbie with Crystal Reports, learning as I go along.  I need help from the experts on how to look up values in a table and use them in Crystal Reports 2013.  Here's what I'm trying to do.

We have a salary scale for employees, which scale is in a table.  An employee's salary depends on the Step and Lane the employee is in.  Across the top of the table (in columns) are Lanes I through V.  Down the left side (in rows) are Steps 1 through 10.  There is a salary amount in each cell.  For example, if an employee is in Step 1, Lane I, the salary in the table for that is $34,679.  The next year, the employee automatically moves 1 Step, or to Step 2, Lane I (assumes there are no changes in lanes; just in steps) and gets the salary in the cell for Step 2, Lane I, or $36,001.  The table looks like this:

                                                            LANES

                    I               II               III               IV               V

STEPS

1               34,679     43,432     52,184         60,937         69,689

2               36,001     44,752     53,505         62,257         71,010

3

4

5

6

7

8

9

10

And so on, with salaries in each of the above cells.

I have a Crystal Report that pulls from our database the Employee Name, Current Step, Current Lane and Current salary, as follows:

Employee Name     Step     Lane     Current Salary     Next Year's Salary

John Smith               1              I          $34,679               $36,001

What I'm trying to do is have Crystal see that John Smith is in Step 1, Lane 1, add 1 to the Step (so it's Step 2), and then go to the table and get the salary for Step 2, Lane 1 and put that salary in the column Next Year's Salary (in this case, $36,001).  Since the table is not in the data base, I know some how I need to get it into Crystal.

Can this be done in Crystal Reports 2013?  If so, how do I do it?

Many thanks.

Mike

Accepted Solutions (1)

Accepted Solutions (1)

JWiseman
Active Contributor
0 Kudos

hi Mike,

there's probably a bunch of different ways of doing this but the following method may be the easiest...

first extract the contents of the attachment and change the .txt extension to .rpt and then open the report.

how this works:

- the scale table is used in a subreport

- if you right click on the subreport and choose Format Subreport Links you'll see that it's linked by the step & the lane

- in the subreport record selection, step is used in the filter

- in the subreport there is a formula that is used (via a case statement) to grab the next lane value based on the link coming from the main report for the current lane

- also take note of the layout of the report...the subreport is above the current section that has the employees

- note that if you right click on the subreport and choose Format Subreport > Subreport Tab, Suppress Blank Subreport is checked

- in the details A section, suppress blank section is checked in the section expert

- a shared variable is used in a formula on the main report to show the value

i hope this helps,

jamie

Former Member
0 Kudos

Jamie,

Thank you so much!  I'll fool around with this trying to use the data (employee name, step, etc.) from our data base.  I have a couple of initial questions (please excuse my ignorance here).

1.  I take it that in the Design Tab I can just replace the last name and first name with those same items from our data base?  But can I just pull in each employee's step and lane from our data base by replacing your step and lane formulas?  For example, in the table in our database there is an item step_x that I would use to pull in steps and range_x for lanes.

2.  How do I complete the rest of the table in the subreport, that is, populate it with the salaries for remaining steps 3 through 10 for the respective lanes I through V?  I see the scale_csv.STEPS and scale_csv.LaneI, LaneII, etc., but I don't know how to put the rest of the salaries in for the remaining steps/lanes.

Many thanks again!

Mike

JWiseman
Active Contributor
0 Kudos

hi Mike,

i would recommend that you open another instance of crystal reports so you have the sample that i posted and your existing report in another instance of crystal...(the one with Employee Name, Current Step, Current Lane and Current salary)

insert a new details section into this report and move it above the existing one.

for your existing report  go to the Insert menu > choose Subreport > type in a New Report Name and press the Report Wizard button.

now for the tables just add the table that contains the scale information and then press Next, add a bunch of your fields just so that you can use them to verify your data later, and then press Finish. add your subreport to your details A section.

you need to link the subeport so right click on the subeport choose Edit Subreport Links and step is linked to the step field in your subreport scale table (again see the links in the sample i gave you) and Lane is not linked to another field. the step field will automatically filter your subreport.

if you now go and preview the report, you will see your subreport is being filtered based on the step.

now you need to create the two formulae (one in your subreport) and one in your main report similar to what you see in the sample.

-jw

Former Member
0 Kudos

Thank you, Jamie.  I'll give that a try.

Mike

JWiseman
Active Contributor
0 Kudos

sigh...i should have looked closer at this Mike before i gave you the above answer...which will work...but the answer below is way easier than what i gave you.

this method will not involve a subreport.

go back to your first / existing report, go to the Database Expert and add in your scale table and then link the scale table between your employee table using the step field in the employee table and the step field in your scale table..

now in your main report create a formula similar to below, replacing the fake field names with your actual field names.

select {youremployeetable.lane field}

case 1 : {yourscaletable.LaneII field}

case 2: {yourscaletable.LaneIII field}

case 3: {yourscaletable.LaneIV field}

case 4: {yourscaletable.LaneV field};

place the new formula on your details section and you're done.

again, apologies and i hope that you didn't spend a whole lot of time on the above solution. and if you did, hopefully it got you a bit more familiar with subreports and linking.

-jamie

Former Member
0 Kudos

Thank you, Jamie.

I'll try what you indicate in your last reply, but let me tell you what I ran into using what you provided previousl

I did try what you suggested before.  The lanes I'm pulling from the database are strings (text) not numbers (unlike the steps from the database which are numbers).  So, in the GetNextSalary formula (using yours and just changing the names to match what I have) I get everything after shared numbervar gns:= is highlighted and an error message that "A number is required here".  I tried to convert everything to numbers with ToNumber, but the next year salary was not correct (so where 0), although the Display from the subreport shows the table and salaries in it correctly.

Is there something else I should do to correct the above?

Also, the way I set up the table was to type it into Excel and then saved it as a .csv file.  Maybe I didn't do it right.  Could you send me the .csv file you did for the table so I make sure I did it correctly.

Then I'll try your later suggestion.....and yes, I did learn a bit about subreports, so I appreciate your help on that.  I had not used them before.

Thanks again.

Mike

Former Member
0 Kudos

Jamie,

It's adding 1 to the lane and keeping the step the same for the next year's salary.  I need to add 1 to the step and keep the lane the same instead.  For example, if an employee currently is in step 5, lane 3, I need it to pick the salary that's in step 6, Lane 3.

How do I do that?

Thanks again.

Mike

JWiseman
Active Contributor
0 Kudos

gotcha...to add 1 to the step and keep the lane the same...

1) on your main report create a formula named Step. that formula should have syntax like:

{your employee table.step field} + 1

2) go to edit your subreport link and remove the current link for step

3) link the {@Step} formula from the main report to the step field in the subreport

4) in the subreport, change your case statement formula to syntax like:

select {?PM the subeport link for LANE}

case 1 : {yourscaletable.Lane1 field}

case 2: {yourscaletable.LaneII field}

case 3: {yourscaletable.LaneIII field}

case 4: {yourscaletable.LaneIV field}

case 5: {yourscaletable.LaneI field}

;

not sure if the your Lane field from your main report is a number or text as well...if you get an error regarding that, then change the first line to something like

select totext({?PM the subeport link for LANE},0)

Former Member
0 Kudos

Thank you so much, Jamie.  The step is a number and the lane is text in our database.

Many thanks again.  I've learned a lot from and I appreciate your help.

Mike

Answers (0)