I'm creating a list of customers by Outside Salesperson, and grouping by State, and then by City. This functionality works perfectly.
My problem comes in when I try to create a list of products purchased by each customer and group that list with the customer. I'm clearly not setting this part up correctly.
Using this function, I can get a list of Product Group Codes purchased by a given customer for a given date range: =NL("Rows","Sales Invoice Line","Product Group Code","Sell-to Customer No.",F10,"Posting Date",NP("datefilter",Support!$H$3,Support!$H$4))
My problem is that the product list isn't grouped correctly. If there's only one customer in the group for a city, then all of the product will be listed correctly under that customer. If multiple customers are listed, I'll only get the product list for the first customer in the list, AND the product list begins under the last customer.
Here's the layout of my report:
Date
Votes
10 comments
-
Jet Reports Historic Posts Hello -
When you state that "the product list begins under the last customer", this would indicate that your "Rows=" numbers don't quite match up with what you are trying to do.
Let's consider an example. I want to list out my sales - grouped by Salesperson, then by State, and then by customer.
Here is what that might look like (Sorry it's so small… I wanted to fit in as much as possible. Click on the image to display it in a larger form):
(I've shaded my replicated regions so that it's easier to see what's going on).
With results that look like this:
If my NL("Rows") function in cell G8 did *not* have the "=3" added, I wouldn't get the sales for that particular customer… rather only some sales would simply show up at the end of my report.
Take a look at your report to make sure that your NL("Rows") functions include enough lines to get all the data in them. -
Jet Reports Historic Posts Hi, HP.
Coming to my rescue again!
Okay, I figured out that indeed I needed a fresh NL function to extract those Product Group Codes from my Sales Invoice Lines. That has been part of my problem. However, I might have multiple instances of a given Product Group Code across several Sales Invoice Lines. How do I group those together?
In your example, it looks like you only get one instance of each sale type in your groups. Is that because that's how it is in your data, or are you somehow grouping sales of like items to a single customer together? -
Jet Reports Historic Posts Hi -
As you can see by the function in cell I9, I am simply listing all records from the Sales Invoice Line table (S-I-L) for the customer number listed in G8.
If I wanted to, I could use filtering to limit cell I9 to a specific "Transaction Type", or I could expand all my replicated regions and add new NL("Rows") functions in cells I10, I11, etc. to filter based on any field in the S-I-L…
Is that what you mean? -
Jet Reports Historic Posts Not exactly.
I'd like to list every unique instance of a product sale, and then total the sales for each product type. Finding the totals isn't a problem. Getting a list of unique sale types is my current issue.
For instance, I have a customer who has purchased CT 8 times this month, and JT 6 times. I want the output to be 2 rows, one with CT and it's totals, and one with JT and it's totals. I have something like 30 different product types and a range of customers who might purchase any amount from the mix, or none. -
Jet Reports Historic Posts Ah-ha!
Now I understand.
Let's fall back on my habit of looking at as simple an example as I can construct…
I can create a report to list out my sales (the sort in cell C4 is just added to help illustrate the result once we run the report):
Obviously, this will list out each individual transaction:
I have multiple lines for "AMSTERDAM Lamp", "ANTWERP Conference Table", etc.
Jet Reports training materials refer to this technique as a "data dump" - as all of the data is dumped into the report.
What you want is what is referred to as a "summary list". Here I will create a a list of unique values from the table and them summarize other information related to those values:
Now, when I run the report, this is what I get:
Obviously, if these was part of a grouping report, *all* of my NL() functions would need to include further filtering (salesperson, state, customer, etc.) in order to return the results I want. -
Jet Reports Historic Posts Okay. I'm with you there. That works okay for me, but now I'm back to my other issue with the grouping.
I need this list of products to be grouped by customer. How do you setup your Excel so it displays the formulae in your cells, instead of the results? I'd like to post mine without having to obscure sensitive customer data. Thanks!
EDIT: Figured out my grouping issue. My rows were not setup properly. I'd still love to hear the answer to my question above, but thanks so much for all your help, HP! -
Jet Reports Historic Posts Glad you got it worked out!
That Excel setting can be toggled using the CTRL-grave (or backtick) key combination:
-
Jet Reports Historic Posts Great to know, HP.
Okay, I've still got 2 issues I'm struggling against.
First, I'm getting an extra row below my customers who have zero sales. Here's my functions:
What's odd is I've got a hidden row option on the first row of the product list, but the test is =IF(L10=""). I'm guessing there's still some kind of residual function in that cell that's causing the test to fail. I'm guessing this also explains my problem with SUMIF on the columns based on a condition that checks the same cell for text.
Any thoughts on what condition I could actually check for to hide that row? -
Jet Reports Historic Posts Found it. IF(LEN(L10)>0,"Show","Hide")
That was a tough one. An NL function that returns a blank cell is always text, nontext, not blank, not zero, and a reference.
Now I need a way to check LEN on such a cell as a condition in a COUNTIF statement. -
Jet Reports Historic Posts After much testing and struggle, I've learned you cannot use a LEN function in an array that will work with a Jet report, for whatever reason.
Originally, I was trying to sum a column based on whether an NL("Rows") function returned a result. The L row in the spreadsheet contained my function. The M row contained the numbers I wanted to SUM. I tried a =COUNTIFS(L9:L11,"<>", M9:M11, ">0"). This would fail with #VALUE! whenever the NL function returned nothing.
Per my post above, a "blank" NL function return shows up as practically everything, so it's very difficult to test for it. Since I found I could check the LEN of the information in the cell, I tried ={COUNTIFS(L9:L11, LEN(L9:L11)>0,M9:M11, ">0")}. Since you can only test LEN against a range in an array function, I thought this might work. The result was always 0, though. When I tried to evaluate the formula, the LEN function would yield a set of actual results that matched all the cells, but on the next evaluation the entire function would evaluate to 0.
Ultimately, I had to create a hidden row where I evaluated =LEN(L9) on down the column, and then tested for that column >0 in my summing function for a subtotal. I hope this is helpful to others.
Please sign in to leave a comment.