Hi
I would appreciate anybody's help on this.
I have created a report based on Sales Header, Sales Line and Customer tables and linked the tables accordingly. This is laid out in 3 groups. The first group shows Sell to customer No., sell to customer name, balance, credit limit. The second group shows Sales document no, Status,and the third group shows Line No., No., Qty to Ship, Line Amount and Planned shippment date. The report is to show the status of all sales orders that have a remaining qty to ship of >0 and to show the order status. Therefore I have then inserted subtotals based on document no. and sell to customer no. so this gives a total for each order and an overall total for the customer. I have then inserted extra subtotals in the subtotal area to total "Pending Approval Orders", "Open" orders and "Released" Orders which all work fine.
I now only want to show records where the total orders for that customer exceed the customers available credit. This is why I have inserted the balance and credit limit into the report. A formula has then been inserted into the totals area again based on Credit limt Less Balance to give an avaibale credit figure. If the total of the orders for that customer irrelevant of their status is greater than the available credit I want the report to show the data. If not then hide this.
I hope this makes sense. I have tried the show and hide rows functionalty but still can't get this to work. I would appreciate anybodys help on this.
Thanks
Date
Votes
11 comments
-
Jet Reports Historic Posts Hi Alex,
Would you be able to post your report? I'm trying to recreate your report based on your description but it was a little difficult to do.
Regards, -
Jet Reports Historic Posts Hi
I have not uploaded one before but i think I have done this correctly.
Thanks -
Jet Reports Historic Posts Alex,
Let me see if I understand what you are trying to accomplish. It seems like your report works the way you want it to right now BUT you also want to add functionality that will only show the report information for customers where their balance is greater than their credit limit. Is this correct?
If so, you could add =IF(J11>K11,"show","hide") to cell B11 which says, if Balance is greater than the Credit Limit, show that row.
You will also need to add additional logic from B12-B22 that says, if the cell above is "show" also show this row. For example, I put =IF(B11="show","show","hide") and then copied that down to be B22 so it just kept looking at the cell above it to determine whether to show or hide.
Is that what you were trying to accomplish? -
Jet Reports Historic Posts Hi
Thanks for your speedy reply
Nearly right - I want the report to display records where if the total of all sales orders for that customer is greater that their current credit limit less their current balance. All other records to be hidden
Cheers -
Jet Reports Historic Posts So basically, it seems like you are saying if the Total for COMPANY in cell V20 (design mode) exceed their "Available Credit" value in P20 (design mode) you want to show all the records above.
If the Total for CUSTOMER is less than Available Credit, then you just want to see the summary box area? (Rows 17-22)
You could so something like an IF statement comparing those two values to return "show" or "hide" in column C so the actual hidden and not used (see attached). -
Jet Reports Historic Posts Hi
Sorry for late reply but the weekend jumped in the way :-(
Yes this is what I am wanting. I didn't realise that I could use the show/hide functionality in more than one column, however when I insert what you have suggested and the condition is met, for some reason not all lines for the orders appear, for some reason it hides some and I am not sure why. :-( -
Jet Reports Historic Posts Hi
Managed to suss this out - I had to point cells C11-C19 and C21-C22 to show =$C$20 rather than pointed it to the cell directly below.
Many Thanks for your help. -
Jet Reports Historic Posts Hi
I revert back to my first message today - this still doesn't work as when I anchor the cells obviously it doesn't carry the formula down correctly - having a blond moment!.
Consequntely going back to as why this does not show all the lines for the orders, if I insert your formula as you have suggested and show colmn C11 to C22 and at this point don't insert your formula in cells B12 to B15 and run the report, you can see where this starts to go wrong with what is displayed. In cell C15 for some reason it jumps to =C83 and then from cell C21 down it all goes wrong and I am not sure why ?
I would be grateful if you could look into this further and advise.
Cheers -
Jet Reports Historic Posts Hi
Sussed it as attached.
I had to move your formula that was in C20 to C11 and then point the other cells in column C to this cell and bingo it works. It must be to do with the grouping.
Many Thanks -
Jet Reports Historic Posts Sorry - forgot to attach!
-
Jet Reports Historic Posts Seems like you are good to go? It was just difficult to fully understand what you were trying to hide and what you weren't.
If you were able to take my example and tweak it to fit your needs, that's great!
Please sign in to leave a comment.