Hi,
I would like to create a phone list in Jet Reports. However I do not want all data in 1 column, but I'd like to spread the data in 2 colums. In the first column(s) I would like to report the phone numbers (and names) of persons of which the name starts with an A to K. In the second column(s) I would like to see the phone numbers (and names) of persons with name starting with L to Z.
I have tried to use the function NL("Rows";"Employee"…;"Name";"A..L") in cel C2 and the same function NL("Rows";"Employee"…;"Name";"L..Z") in cel E2, but I do not get unique records, but several copies of one record.
Is it possible to use the row function in differtent cels but in the same row?
I also want to make a "birthday calendar" separated in 2 columns (in stead of one long row)…
Can anyone help me?
Saskia Kuijer
Date
Votes
11 comments
-
Jet Reports Historic Posts Saskia,
Not the way that you are thinking. You cannot have (or should not have) multiple NL("Rows") on the same Row. To get the two columns you want, you need to know how many employees to list in column 1 and how many employees in total you have. This is done by using the NL("Count") function.$D$3 =NL("Count","Employee",,"Last Name","A..M") $D$4 =NL("Count","Employee",,"Last Name","*")
Then you can use the NL("Rows") function with the Navision Integer table to produce a list of numbers from 1 to the number of employees you have.$D$8 =NL("Rows","Integer","Number","Number","1.."&$D$4)
then use in cell E8 the following$E$8 =NL($D8,"Employee",,"+Last Name","A..M")
This will give you a list in Column 1 of the employees with the "Last Name" of A..L (I know the code says A..M, but trust me, because of the way that Navision filters, this will work). When you use a number as the "What" parameter in an NL function, you get a specific record. For example, if I say =NL(3,"Employee"), then I will get the third employee in my table.
Now, you need to do some math for the second column.$H$8 =ROW()-ROW($D$8)+1+$D$3
This give me the current row number minus the row number of cell D8 then add 1 and then add whatever is in cell D3 (the count of employees in column 1).
then in cell I8 I have=NL($H8,"Employee",,"+Last Name","*")which gives me the employees in the second column.
Hopefully this help. I've attached the complete workbook to show this example. -
-
Jet Reports Historic Posts Thnx Sherman!
Does this also work for a birthday calender? -
Jet Reports Historic Posts Here's another example of splitting data in 2 columns: http://community.jetreports.com/viewtopic.php?f=13&t=63
-
Jet Reports Historic Posts Hi Sherman,
Thnx again! But this report is not a solution of my 2nd "wish". I would like to create a birthday calender. To sort the birthdays I'd like to sort without year of birth. For example:
Person A: January 15, 1970, Person B: March 10, 1965, Person C: Januari 20, 1960.
The sorting of a birthday calender should be: 1, Person A January 15, 1970; 2, Person C January 20, 1960; Person B, March 10, 1965.
But the sorting in Jet Reports has the following result: 1, Person B March 10, 1965; 2 Person C, January 20, 1960; 3 Person A, Januari 15, 1970.
My question is: how can I sort the birthday calender on birthday date, without birthday year?
Saskia -
Jet Reports Historic Posts It's not easy, but you can use a calculated field, something like
=NL("Rows","Employee",,"+=TEXT(NF(,""Birth Date""),""MM-DD"")","*")
I modified shermang's original report to sort by "Birth Date" -
Jet Reports Historic Posts Hi Emis,
I understand what the result of the formula should be: ascending (or descending) on birthday MM-DD. But it doesn't work. The sorting of my file is still on employee number. Any other suggestions?
Saskia -
Jet Reports Historic Posts Saskia,
I just tried the workbook Emis provided on my system and it works correctly. I wonder if you might have an older version of Jet Reports, or if this might be a problem with the Windows Regional language settings on your computer. Mine are set to English (US). -
Jet Reports Historic Posts Hi Chuck,
The version of Jet Reports I work with is: 7.1.2 Build 1820
My regional settings are Dutch (Dutch) -
Jet Reports Historic Posts I think you have to use the Local function of Jet Reports. I've seen this before, but I'm not sure if it will work in your case and you may need to ask support.
=NL($D8,"Employee",,"+=Local(TEXT(NF(,""Birth Date""),""MM-DD""))","*")I think this is correct, but I can't test it. Basically it changes everything within the quotes to the correct language.
here is the workbook -
Jet Reports Historic Posts Thnx!!!!
Please sign in to leave a comment.