Hello,
I have created a report with information from 3 tables - customer, census (customized table) and detailed customer ledger entry. It should have been fairly simply (I discovered snippets yesterday and love it!) - I'm pulling customer number and name from the customer table, the sum of invoices/credit memos for each quarter from Det. Customer ledger enty and the suite number from the census table for the specified customers. I have some suite numbers that start with D (D01..D29) and the other suite number are numberic (101..351). This is a Seniors Residence.
I am trying to summarize the data for each group of suite numbers (D01..D29) and (101..351). I've tried the usual sort on the ("+audit unit code","*"") from the census table but the report is still sorted by customer number. I read another post and tried the following instead but it still does not work.
=NL(,"Census","Audit Unit code","Tenant Code",$E12,"+=NF(,""Audit Unit code"")","*")
(The tenant code in the census table is the same as the customer number)
The alternative is to create totals using a sumif or some other formula that I'm probably not familiar with. After much research I found some help on the internet and tried the following:
=SUMIF(G12:G13,"D01", H12:H13)+SUMIF(G12:G13,"D02",H12:H13)+SUMIF(G12:G13,"D03",H12:H13)…
This does give me the required results for one group (D01..D29) but there just has to be a better way. You can imagine how long the formula is for 29 suites not to mention suites 101..351. HELP!!
I'm still new to Jet and not very strong with Excel formulas so any help will be greatly appreciated.
Regards,
Michelle
Date
Votes
4 comments
-
Jet Reports Historic Posts Hi Michelle,
You can only sort within your replicator formula itself. That would be probably your NL(Rows) formula in this case. The results of the NL(Rows) formula are what creates the rows in the report, so this is where the sorting must take place. In your case, you are trying to add a sort on your NL(First) function (NL with a blank first argument is the same as NL(1) or NL(First)) which does not work. What does your NL(Rows) formula look like? That is where the sort needs to go.
Regards,
Hughes -
Jet Reports Historic Posts Hi Hughes,
Thanks for the info.
This is my NL formula:
=NL("Rows","Customer",,"No.","<>''","Name","<>''","Resident Days","<>0","Search Type","Resident|Discharged")
I have NF formulas using this key.
The field that I need to sort on is not available in the customer table and the only field that links them is customer number.
Thanks
Michelle -
Jet Reports Historic Posts Michelle,
Okay, so based on this and your previous attempt at a sort formula, I will try to create a CalcFilterField which performs the correct sort. This may not be exactly right, but hopefully it points you in the right direction. It's something like this:=NL("Rows","Customer",,"No.","<>''","Name","<>''","Resident Days","<>0","Search Type","Resident|Discharged","+=NL(,""Census"",""Audit Unit code"",""Tenant Code"",NF(,""No.""))","*")
Does that work for you?
Regards,
Hughes -
Jet Reports Historic Posts Thanks a million Hughes! It worked perfectly.
I think I need a JetReports course.
Please sign in to leave a comment.