0

GL entry report by Company

Jet Reports Historic Posts

February 19, 2013 20:32

None

Follow

Our database has 53 company's. I have to make an report with the sum of some GL account (only VAT accounts). I want to display the report like this:

Company GL account Amout
WSALK
"LIVE","WSAW","15","1","1501" 1501 € 100,00
"LIVE","WSAW","15","1","1502" 1502 € 200,00
"LIVE","WSAW","15","1","1503" 1503 € -
"LIVE","WSAW","15","1","1504" 1504 € -
"LIVE","WSAW","15","1","1511" 1511 € -
"LIVE","WSAW","15","1","1512" 1512 € -
"LIVE","WSAW","15","1","1513" 1513 € -
"LIVE","WSAW","15","1","1514" 1514 € -
"LIVE","WSAW","15","1","1517" 1517 € -
"LIVE","WSAW","15","1","1520" 1520 € 150,00

WSW
"LIVE","WSAW","15","1","1501" 1501 € 120,00
"LIVE","WSAW","15","1","1502" 1502 € 50,00
"LIVE","WSAW","15","1","1503" 1503 € 200,00
"LIVE","WSAW","15","1","1504" 1504 € -
"LIVE","WSAW","15","1","1511" 1511 € -
"LIVE","WSAW","15","1","1512" 1512 € -
"LIVE","WSAW","15","1","1513" 1513 € -
"LIVE","WSAW","15","1","1514" 1514 € -
"LIVE","WSAW","15","1","1517" 1517 € -
"LIVE","WSAW","15","1","1520" 1520 € -

The problem is: The filter for Company is not going correct with the company rows. i used the query's as below:

Company:NL("Rijen=3";"2000000006 Bedrijf";"1 Naam";"1 Naam";"WSALK|WSW")
GL: NL("Rijen";"15 Grootboekrekening";;"1 Nr.";"1501..1520")
Sum: NL("Som";"17 Grootboekpost";"17 Bedrag";"6 Documentnr.";"<>BTW*";"Bedrijf=";$C7;"3 Grootboekrekeningnr.";$E8;"4 Boekingsdatum";$D$3)
postingdate is in D3

How can i setup an report like this?

• Facebook
• Twitter
• LinkedIn

2 comments

• 0
  
  Jet Reports Historic Posts February 19, 2013 22:29

  Hi!
  
  It looks like you left out the Company= filter from your GL and Sum formulas. You need something like this:
  
  GL: NL("Rijen";"15 Grootboekrekening";;"1 Nr.";"1501..1520";"Company=";[Cell Reference to the formula replicating companies]) Sum: NL("Som";"17 Grootboekpost";"17 Bedrag";"6 Documentnr.";"<>BTW*";"Bedrijf=";$C7;"3 Grootboekrekeningnr.";$E8;"4 Boekingsdatum";$D$3;"Company=";[Cell Reference to the formula replicating companies])
  Does that help?
  
  Regards,
  Hughes

• 0
  
  Jet Reports Historic Posts February 20, 2013 20:16

  Hi Stef,
  
  Oh I see, you already had a company= filter in the sum formula. I think your problem is actually just that your cell reference for the Company= won't work. Since this is a grouping report, you'll need to copy the company down the column in order for this to work. I will attach an example, since it's much easier than typing all the formulas out here.
  
  Does that help?
  
  Regards,
  Hughes

  • GL Grouping by Company.xlsx