I am trying to create a Financial Report that shows sales figures by product and am unable to do so.
Example
Account:
Sales
Other revenue
Product:
A
B
Report should show:
Sales A
Sales B
Sales no product
Other revenue A
Other revenue B
Other revenue no product
How do I achieve this? I have tried creating a line for each report line with two reports sources (one for the account and the other for the product) but that doesn't return the correct figures.
Any suggestions would be appreciated.
Regards
Cameron
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
The report-source seems to be an OR-relation. Therefore, it will also take into account all other acount-elements where the dimension of product is value A. Your numbers will therefore not only be sales :-(
Don't have a solution yet for your problem, but you've checked the sales-invoice reports? Maybe they will serve your purpose as well.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
this data is also accessible in the accounting fact details report. select account range 4 to 4z (for GWorld), select the date range you need. order and group by account and product and sum the accounted amount. It will display every row of data. If this is too much detail either:
..export the data to a csv file and summarise and report with your tool of choice, or
..create your own database view that sums to the level you want then add the table/view in Compiere and create a report and process to report on it.
regards..
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I would like to pursue the Financial Report tool option since it seems so close. To do so it looks like I need to modify Compiere:
- update the report sources form so that the user can specify "and" instead of the default "or"
- update the code that does the selection so that this option is used
Could anyone provide any help on doing that? Is there some general documentation on customising Compiere?
Regards
Cameron
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
re Reinier's observation:
"The report-source seems to be an OR-relation. Therefore, it will also take into account all other acount-elements where the dimension of product is value A. Your numbers will therefore not only be sales :-( "
You can set a filter in the column definition to say the column type is a segment value and the Type is an account. Unfortunately you have to select a real account not a summary account but at least it will give you a column with only sales. perhaps you can get it to work with multiple accounts by having an unprinted column for each and then a sum of the columns.
good luck. let us know if you succeed.
...as dumped from gardenworld for month Aug 2002
Name Description sales
Azalea Bush Azalea Bush
Elm Elm Tree
Fertilizer Fertilizer #50
Grass Grass Seed Container
Hoe Hoe 4 ft
Holly Bush Holly Bush
Mulch Mulch 10#
Oak Oak Tree 61.75
PChair Patio Chair
PScreen Patio Sun Screen
PTable Patio Table
Planting Planting Service 42.75
Plum Tree Plum Tree 47.5
Rake-Bamboo Rake Bamboo
Rake-Metal Rake Metal
Rose Bush Rose Bush
Seeder Grass Seeder
Tiller Lawn Tiller
Transplanter Transplanter
Weeder Weeder
Sales By Product 152
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Thanks for the advice. Unfortunately I want revenue and expenses by product line so not being able to use parent-level values makes using columns impractical.
Does the fact that no-one's given any pointers on modifying report sources mean it's not practical to do that either? I would have thought that this was such a basic need: for end-users to write reports on profitability by product group, or division etc. Of course anything's possible with views but that's not the point of the Financial Report tool (FSG in E-Business Suite terminology).
Regards
Cameron
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
One way you might consider is to create separate gl accounts for product group A and product group B.
So you will have a summary account Sales with two child account Sales A and Sales B. You can direct all the product in group A to use Sales A and same for B (see the accounting tab in the product group). Then when you look at the financial reports with list source you should see what you want. If you don't have too many groups this should be practical.
Stephan Keller,
Sodexis, Inc.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Anonymous
-
2007-11-02
It's fairly simple to use the Financial Reports capability to create a Sales by Product report. In fact the new version of Compiere, will have one pre-seeded. It assumes the Sales are all reported in one account (41000_Trade Revenue in GW). This report will list the products in the rows (current limitation of <1000 products) and have two columns for Current Period sales and YTD Sales. Using the Dimension capability, you can create Product lines and group products under the product lines, and the create one report line for each product line. We're working on supporting summary level columns as well.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Thanks. Do you mean it *will be* fairly simple to create such a report using the new version of Compiere? Or do you mean it *is* easy using the version I run (2.6.0a). If the latter, how?
Regards
Cameron
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Anonymous
-
2007-11-02
I'm not sure about the version you're running, but it works in 2.6.3. You can easily try for yourself:
1. Create a Report Line Set called All Products.
2. Create a Report Line under it called Product with a Line Type of Segment Value (leave Posting and Amount Types blank)
3. Create a Report Source under it with Type=Product and leave the Product field blank
4. Create a Report Column Set called Current Period and YTD
5. Create 2 report columns under it, one for Current Period and YTD. The first one should have Posting Type=Actual, Amount Type=Period Balance, Column Type=Relative Period, Relative Period=0. The second one should be the same except Amount Type=Year Balance.
6. Then create a Financial report using these Report Line and Columns Sets. Make sure to check List Sources when you run to see all the products. Warning, If there are more than 1000 products it won't work (we're obviously aware of this limitation and working on removing it).
You can easily produce a similar report for Sales by Business Partner by just substituting Type=Business Parter in Step 3.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
We could debate the idea of using the account element to record additional information such as product, division, project (and other things that can be recorded in independent elements/segments). Suffice it to say I personally don't agree with this approach. In my opinion this should be handled by reporting.
Thanks for your thoughts anyway.
Cameron
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
..Cameron..Compiere be a SMB software is Cameron keep Compiere in a Compiere
..Albert..Compiere be a MBS software is Albert want Compiere be a Compiere
>>We could debate the idea of using the account element
>>to record additional information such as product, division, project
>>(and other things that can be recorded in independent elements/segments).
>>Suffice it to say I personally don't agree with this approach.
>>In my opinion this should be handled by reporting.
>>Thanks for your thoughts anyway.
thank Cameron, Let Compiere forum still alive....
same SAP SD-Configuration document for someone reference only...
IMPORTANT POINTS IN SD – FI INTEGRATIONS
1. Customer Account Assignment Groups:
Domestic Customers, Foreign Customers and Affiliated Companies. I think they are finalized.
2. Material Account Assignment Groups :
3. Trading Goods and performances.
They are not exhaustive and they need revision.
Finished Goods & Semi Finished Goods are covered under Trading Goods. Performances are said to be Nil. Shall we have Material Groups as Trading, Finished, Packaging and Services.
(5)
50001 Domestic Direct Sales
50002 Domestic Branch Sales
50003 Domestic Direct Sales 80 1A
50004 Domestic Branch Sales 80 1A
50005 Export Sales
50006 Inter Unit Transfer– Finished Goods
52116 Sale of Free Sample (Domestic)
52117 Sale of Free Sample (Export)
52118 Sale of Captive Consumption
a) What is branch Sales? There are no sales from branches and there are no depots at branches. Invoices are being sent only through branches. Second Sale concept is also not there.
b) Sales of Dadra & Barotiwala are to same account?
50001 Domestic Direct Sales
50002 Domestic Branch Sales
50003 Domestic Direct Sales 80 1A
50004 Domestic Branch Sales 80 1A
50005 Export Sales
50006 Inter Unit Transfer– Finished Goods
52116 Sale of Free Sample (Domestic)
52117 Sale of Free Sample (Export)
52118 Sale of Captive Consumption
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I am trying to create a Financial Report that shows sales figures by product and am unable to do so.
Example
Account:
Sales
Other revenue
Product:
A
B
Report should show:
Sales A
Sales B
Sales no product
Other revenue A
Other revenue B
Other revenue no product
How do I achieve this? I have tried creating a line for each report line with two reports sources (one for the account and the other for the product) but that doesn't return the correct figures.
Any suggestions would be appreciated.
Regards
Cameron
Hello,
I believe the problem is with creating tow report-sources. I assume that (for example sales A) you've got the line defined as:
REPORT-LINE
Name: Sales A
Postingtype: Actual
Amount type: ...
For reportline Sales A, the
REPORT-SOURCE
type: Account
Account element: 4-sales
Type: Product
Product: A
The report-source seems to be an OR-relation. Therefore, it will also take into account all other acount-elements where the dimension of product is value A. Your numbers will therefore not only be sales :-(
Don't have a solution yet for your problem, but you've checked the sales-invoice reports? Maybe they will serve your purpose as well.
this data is also accessible in the accounting fact details report. select account range 4 to 4z (for GWorld), select the date range you need. order and group by account and product and sum the accounted amount. It will display every row of data. If this is too much detail either:
..export the data to a csv file and summarise and report with your tool of choice, or
..create your own database view that sums to the level you want then add the table/view in Compiere and create a report and process to report on it.
regards..
Thanks for the suggestions.
I would like to pursue the Financial Report tool option since it seems so close. To do so it looks like I need to modify Compiere:
- update the report sources form so that the user can specify "and" instead of the default "or"
- update the code that does the selection so that this option is used
Could anyone provide any help on doing that? Is there some general documentation on customising Compiere?
Regards
Cameron
re Reinier's observation:
"The report-source seems to be an OR-relation. Therefore, it will also take into account all other acount-elements where the dimension of product is value A. Your numbers will therefore not only be sales :-( "
You can set a filter in the column definition to say the column type is a segment value and the Type is an account. Unfortunately you have to select a real account not a summary account but at least it will give you a column with only sales. perhaps you can get it to work with multiple accounts by having an unprinted column for each and then a sum of the columns.
good luck. let us know if you succeed.
...as dumped from gardenworld for month Aug 2002
Name Description sales
Azalea Bush Azalea Bush
Elm Elm Tree
Fertilizer Fertilizer #50
Grass Grass Seed Container
Hoe Hoe 4 ft
Holly Bush Holly Bush
Mulch Mulch 10#
Oak Oak Tree 61.75
PChair Patio Chair
PScreen Patio Sun Screen
PTable Patio Table
Planting Planting Service 42.75
Plum Tree Plum Tree 47.5
Rake-Bamboo Rake Bamboo
Rake-Metal Rake Metal
Rose Bush Rose Bush
Seeder Grass Seeder
Tiller Lawn Tiller
Transplanter Transplanter
Weeder Weeder
Sales By Product 152
Thanks for the advice. Unfortunately I want revenue and expenses by product line so not being able to use parent-level values makes using columns impractical.
Does the fact that no-one's given any pointers on modifying report sources mean it's not practical to do that either? I would have thought that this was such a basic need: for end-users to write reports on profitability by product group, or division etc. Of course anything's possible with views but that's not the point of the Financial Report tool (FSG in E-Business Suite terminology).
Regards
Cameron
Cameron,
One way you might consider is to create separate gl accounts for product group A and product group B.
So you will have a summary account Sales with two child account Sales A and Sales B. You can direct all the product in group A to use Sales A and same for B (see the accounting tab in the product group). Then when you look at the financial reports with list source you should see what you want. If you don't have too many groups this should be practical.
Stephan Keller,
Sodexis, Inc.
It's fairly simple to use the Financial Reports capability to create a Sales by Product report. In fact the new version of Compiere, will have one pre-seeded. It assumes the Sales are all reported in one account (41000_Trade Revenue in GW). This report will list the products in the rows (current limitation of <1000 products) and have two columns for Current Period sales and YTD Sales. Using the Dimension capability, you can create Product lines and group products under the product lines, and the create one report line for each product line. We're working on supporting summary level columns as well.
Thanks. Do you mean it *will be* fairly simple to create such a report using the new version of Compiere? Or do you mean it *is* easy using the version I run (2.6.0a). If the latter, how?
Regards
Cameron
I'm not sure about the version you're running, but it works in 2.6.3. You can easily try for yourself:
1. Create a Report Line Set called All Products.
2. Create a Report Line under it called Product with a Line Type of Segment Value (leave Posting and Amount Types blank)
3. Create a Report Source under it with Type=Product and leave the Product field blank
4. Create a Report Column Set called Current Period and YTD
5. Create 2 report columns under it, one for Current Period and YTD. The first one should have Posting Type=Actual, Amount Type=Period Balance, Column Type=Relative Period, Relative Period=0. The second one should be the same except Amount Type=Year Balance.
6. Then create a Financial report using these Report Line and Columns Sets. Make sure to check List Sources when you run to see all the products. Warning, If there are more than 1000 products it won't work (we're obviously aware of this limitation and working on removing it).
You can easily produce a similar report for Sales by Business Partner by just substituting Type=Business Parter in Step 3.
Dear all:
Management issue must use mangment tool, not reporting tool,
Why not add Accounting Element like this
Account:
4000 Sales
4100 Sales A
4200 Sales B
4300 Other revenue
Some Big Sales Product or
Some main Product or Product Category
Use different Account Element.....
We have experience for NYSE / TSEC listed compnay use Compiere
Do the right thing ?
Do the thing right ?
WorldWide fee supoort
From a Compiere senior Developer
With Finacial Consultant/NYSE Listed Compnay Control Experience
You can trust Albert
The only way to take-over and pass thur Compiere ERP + CRM Business Solution......
We could debate the idea of using the account element to record additional information such as product, division, project (and other things that can be recorded in independent elements/segments). Suffice it to say I personally don't agree with this approach. In my opinion this should be handled by reporting.
Thanks for your thoughts anyway.
Cameron
..Cameron..Compiere be a SMB software is Cameron keep Compiere in a Compiere
..Albert..Compiere be a MBS software is Albert want Compiere be a Compiere
>>We could debate the idea of using the account element
>>to record additional information such as product, division, project
>>(and other things that can be recorded in independent elements/segments).
>>Suffice it to say I personally don't agree with this approach.
>>In my opinion this should be handled by reporting.
>>Thanks for your thoughts anyway.
thank Cameron, Let Compiere forum still alive....
same SAP SD-Configuration document for someone reference only...
IMPORTANT POINTS IN SD – FI INTEGRATIONS
1. Customer Account Assignment Groups:
Domestic Customers, Foreign Customers and Affiliated Companies. I think they are finalized.
2. Material Account Assignment Groups :
3. Trading Goods and performances.
They are not exhaustive and they need revision.
Finished Goods & Semi Finished Goods are covered under Trading Goods. Performances are said to be Nil. Shall we have Material Groups as Trading, Finished, Packaging and Services.
3 Condition Types : KOFI & KOFK ( Account Assignment for CO)
4 Account Keys
ERL Sales
RDM Rounding
ERS Sales Deductions
ERF Freight Collected Domestic & Foreign
EIN Transit Insurance Collected
EXD Excise Duty
ERB Rebate Sales Deductions
ERV Rebate Accruals
EVV Cash Clearing
MW3 Sales Tax
MWS Tax on sales & purchases
(5)
50001 Domestic Direct Sales
50002 Domestic Branch Sales
50003 Domestic Direct Sales 80 1A
50004 Domestic Branch Sales 80 1A
50005 Export Sales
50006 Inter Unit Transfer– Finished Goods
52116 Sale of Free Sample (Domestic)
52117 Sale of Free Sample (Export)
52118 Sale of Captive Consumption
a) What is branch Sales? There are no sales from branches and there are no depots at branches. Invoices are being sent only through branches. Second Sale concept is also not there.
b) Sales of Dadra & Barotiwala are to same account?
6 ERS Sales Deductions
51001 Trade Discount
51002 Cash Discount
51003 Commission
7 Following Accounts to be identified and attached in SD_Fi integration
52001 Job Plating Service Charges
52001 Labor Charges
52001 Testing Analysis Charges Received
52004 Erection & Commissioning Charges
52005 Technical Fees Recd
52006 License Fees Recd
52101 Scrap sales
52102 Sundry Receipts
52103 Commission Recd (Local)
52104 Commission Recd (Foreign)
52105 Insurance Claims Recd
52106 Sundry Balances W/o
52110 Refund of Sales Tax
52111 Refund of Octroi
52112 Rounding Off
55104 Export Benefits & entitlements-Chemicals
69001 Freight & Clearing Imported--Inward
69002 Freight & Clearing Indigenous - Inward
69003 Octroi Charges - Inward
73701 Freight & Clearing Imported--Outward
73702 Carriage & Cartage (Outward)
73703 Octroi Charges (Outward)
8 For Acc. Key
EVV (Cash Clearing) 11602 (Petty Cash) is found attached
ERU (Rebate Accruals) 39024(Out Standing Expenses) is attached
SALES RELATED ACCOUNTS
1. ERL Sales
50001 Domestic Direct Sales
50005 Export Sales
52001 Job Plating Service Charges
52002 Labor Charges
52003 Testing Analysis Charges Received
52004 Erection & Commissioning Charges
52101 Scrap sales
50006 Inter Unit Transfer– Finished Goods
52116 Sale of Free Sample (Domestic)
52117 Sale of Free Sample (Export)
52118 Sale of Captive Consumption
2. RDM Rounding
52112 Rounded off Differences - Invoices
3. ERS Sales Deductions
74951 Trade Discount
74952 Additional Discount
74953 Cash Discounts
74954 Loyalty Discounts
74955 Overriding Commissions
4. ERF Freight Collected Domestic & Foreign
73701 Forwarding Charges (Outward) (Expense Account)
73702 Carriage & Cartage (Outward) (Expense Account)
73703 Octroi Charges (Outward) (Expense Account)
52111 Octroi Refund Account (Income A/c)
5. EIN Transit Insurance Collected
52105 Insurance Claim Received (Income A/c)
6. ERB Rebate Sales Deductions
7. ERV Rebate Accruals
8. EVV Cash Clearing
9. Tax Accounts
EXD Excise Duty
19001 Central excise – Current A/c Bal (PLA)
MW3 Sales Tax
39033 LST Maharastra Sales tax
MWS Tax on sales & purchases
39034 CST Central sales Tax
use different account element
50001 Domestic Direct Sales
50002 Domestic Branch Sales
50003 Domestic Direct Sales 80 1A
50004 Domestic Branch Sales 80 1A
50005 Export Sales
50006 Inter Unit Transfer– Finished Goods
52116 Sale of Free Sample (Domestic)
52117 Sale of Free Sample (Export)
52118 Sale of Captive Consumption