Solved

Dashboard design--Branch comparision

  • 22 April 2021
  • 9 replies
  • 239 views

Userlevel 6
Badge +5

Hello,

    We are having 4 branches. And the management would like to see data like below.

I previously made the report in excel, and calculate each value speparately and fill in the cell.

Now, they’d like to see it on dashboard. 

Can anyone give me some ideas, just ideas.

Like which widget is the best way to select, I am thinking of score KPI

and How can I set the data source, in my opinion , I have to write at least 2 GI, 1 is to count the Inventory value, 2 is to count the sales total and margin.

  Inventory Total Value Last Month Sales Total Margin%
Branch1      
Branch2      
Branch3      
Branch4      
icon

Best answer by ray20 22 April 2021, 13:39

View original

9 replies

Userlevel 7
Badge +17

Hi @ray20 I’m NOT that expert in creating of dashboards but I can suggest based on my knowledge.

If you choose Scorecard KPI, then you may need to create several dashboards with 2 GIs. Instead of Scorecard KPI, if you create the “Chart” dashboard then you need to create only 2 dashboards with 2 GIs  just like below.

 

I have created sample GI for Sales Total and created a Chart dashboard, this way you can see the Sales Total based on the Branches. 

Example:

Sales Total GI, with 2 branches


Created a Chart Dashboard for this GI.



Hope that helps!!

Userlevel 6
Badge +5

@Naveen B 
Thank you for your always warm hands.
Do you konw where to get the monthly aggreation sales figure?
Although I can get the total figure by counting the ARtran, but is there a place I can get the figure directly?

Like the Inventory total, I do not need to count all items
I can get the total value in  GLHistory with AccountiD= “Inventory”,

Userlevel 7
Badge +17

@ray20  We worked on the same requirement to get the Total Sold for each item but NOT on the monthly basis. For that, we brought the values from the ARTran table 

Userlevel 6
Badge +5

@Naveen B 
Hello, I’ve found a table INItemSalesHist, it is supposing to have the sales data by period aggregated.

But, unfortunately, the system has some bugs that the data in the table INItemSalesHist is not accurate.

For example, the below should have sales value, but unfortunately the system failed to record.

 

 

 

So, we can not use this table to do the calculation. even just for a whole period. Unfortunately, NO.

We can only get data from ARtran, I assume.

Userlevel 6
Badge +5

@Naveen B 
To my friend and anyone who are interesting in dashboard. 
here is what I have done.

 

 

I’ve tried to use 1 widget to display the data, but I failed.I can hardly link the data in 1 GI. SO I used 2 widget in a row.
1, I am using donut chart to display the inventory total value.
The data source is from GLHistory, it would be quick to get the data.

2, I am using a Table to display the sales and profit margin.
The data source is from ARTran.
Note:

Fields “NetsalesAmount” and “Cost” are quite helpful, because they are postive when typed Inivoice and Negative when typed CRM. 

Userlevel 3
Badge

Hi Naveen

Great info for me! this is what i am looking for,  to add in the sales and net profit or gross profit margin in the dashboard where the net profit and gross profit margin can only get from financial statement report!

May i know the data source : ARTran is created for new GI or is existing data source in accumatica system?

refer to inquiry screen, related with ARXXXX is only as below? 

Not sure what is the information in “ ARTran” ?

 

 

Userlevel 7
Badge +9

Hi @erin ARTran is a table that you have to add it to a generic inquiry. The table contains all the AR Transactions mostly entered in the Invoices and Invoice and Memos screen → Details.

 

Screenshot for reference:

Thanks

Hi @erin ARTran is a table that you have to add it to a generic inquiry. The table contains all the AR Transactions mostly entered in the Invoices and Invoice and Memos screen → Details.

 

Screenshot for reference:

Thanks

The problem with reading transactions is that when you get large datasets it takes an age to run and when you start to consider Isolation Levels in SQL Server then you find that long running queries can do things like put locks on rows, table and also on the index (depending what it thinks it needs to do for the given isolation level being used). It would be better to get Acumatica to fix the history table or if you have access to the SQL engine and it’s SQL Server then potentially creating an indexed view of what the summary table should look like.

@Naveen B 
Hello, I’ve found a table INItemSalesHist, it is supposing to have the sales data by period aggregated.

But, unfortunately, the system has some bugs that the data in the table INItemSalesHist is not accurate.

For example, the below should have sales value, but unfortunately the system failed to record.

 

 

 

So, we can not use this table to do the calculation. even just for a whole period. Unfortunately, NO.

We can only get data from ARtran, I assume.

 

I think it’s when you Update IN from the Shipment it forgets to update the sales when Prepare Invoice is done.

Reply


About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2024  Acumatica, Inc. All rights reserved