When I say reports I imagine something like the linked images show, a separate menu item for reports and also easy customizable reports. Reports should be easily exportable to pdf, csv, Excel, Word right from the report and not go through tools-> export like in Dolibarr or through various addons which do not fully work ( i have tried). See the attached file inventory details it’s nicely formatted with options to save as pdf, excel etc. Something like this would be very beneficial.
A full reporting app would be nice. Ability to customize filters, columns, etc. The Statistics feature on each menu is a start, but full reporting would be ideal. I did look at Dolireport, but it seems limited in what you can search for, and creating a query does not look very end-user friendly unless you are a developer (which, unfortunately, I am not).
Hi kslom,
I feel what you are saying the modules like Dolireport require developer knowledge like SQL and Dolibarr database structure. I have purchased a module called myList from Dolistore which works and has filters for each field but requires SQL and database knowledge. The only other downside of myList is the pdf export has formatting issues when you have multiple pages. I have been talking to the developers patas-monkey but they have not been able to solve the issue. I have SQL knowledge and I have thoroughly studied the Dolibarr MySQL database. Dolireport is also a bit expensive at 150 Euros.
I was wondering whether we can make a deal: you buy the Dolireport module then share it with me as in send me the files in return I will develop reports that you want. I already have a number of SQL queries already that can retrieve data from the database. What do you say a deal?
ariffidaali, that is an interesting proposition but at this time I will have to pass. I am still just evaluating Dolibarr to see if it will even work for my company, so investing in modules is not in my immediate plans. And after looking at Dolireport, I’m not sure it would do all I wanted, anyway.
Hi Aljawaid,
Since this is community where people contribute I will help you with your SQL queries. Hoping to make this beneficial for both of us, we mutually share stuff with each other. See below SQL queries. Hope it’s useful
LIST OF PRODUCTS WITHout TAGS AND CATEGORIES
SELECT DISTINCT
u.rowid AS Category_ID,
u.label AS Category_Label,
u.description AS Category_description,
p.rowid AS Product_ID,
p.ref AS Product_Code,
p.label as Product_Label
FROM
llx_categorie AS u
JOIN llx_categorie_product AS cp
ON
cp.fk_categorie = u.rowid
RIGHT JOIN llx_product AS p
ON
p.rowid = cp.fk_product
WHERE u.label is null
GROUP BY
p.rowid
ORDER BY
p.ref
ITEMS THAT HAVE BEEN IN STOCK FOR THE LAST 3 MONTHS
--------------------------------------------
SELECT DISTINCT rowid, ref, label, MONTHNAME(datec) as Month_Created, Monthname(tms) As Month_Modified, MONTHNAME(CONVERT(import_key, DATETIME)) as Month_Imported
FROM llx_product
WHERE tms >= (NOW()-INTERVAL 3 MONTH)
ORDER BY tms desc
Hubz,
I have seen that module called Query by ATM on the Dolistore but as I have stated earlier in the post reports should be built in and also the cost 216 Euros is on the higher side. There are also other modules such as Dolireport which is similar to Query. This just means that now Dolibarr doesn’t become free of cost there are costs attached to it. Another problem is that the average user cannot use these modules because it requires SQL and database knowledge.
Yes these queries can work with almost any Dolibarr version as they pull data from standard Dolibarr tables.
Products with Country of Origin (Manufacturing Country) (highest first, ignoring blanks)
SELECT
llx_product.fk_country AS 'Country Code',
llx_c_country.label AS 'Country',
COUNT(*) AS 'Number of Products'
FROM
llx_product
INNER JOIN
llx_c_country ON llx_product.fk_country = llx_c_country.rowid
GROUP BY fk_country
ORDER BY COUNT(*) DESC;
