SQL Reporting Addon: Development

[furti]

If you have new feature requests for the SQL Reporting Addon or want to discuss some development stuff, please do not post to the Announcements Topic anymore but instead reply to this topic.


Informations about new features are still posted to the announcement topic: https://forum.freecadweb.org/viewtopic.php?f=9&t=33403

[panda1967]

The problem
Hi, I have tried to realize a Bill Of Material using Reporting workbench in a mechanical context, working on an assembly managed with A2Plus, in order to finally include the BOM in a drawing using the TechDraw workbench. For each solid part I shown in the BOM the material, the volume, the density and the mass. In order to obtain the volume I used Shape.Volume directly in the query, whereas for material and density I have made up for the current lack of support to materials in some workbench of FreeCAD by adding two corresponding dinamic properties to the parts of assembly. In the Select I used a bit of arithmetic in order to accomplish the correct units. Here you can see the sql code I used:

Code: Select all

Select
    count(Label) As 'Num',
    Label As 'Parte',
    Materiale,
    Shape.Volume / 1000000 As 'Volume [dm^3]',
    Densit As 'Densità [kg/dm^3]',
    Shape.Volume * Densit As 'Massa [mg]'
From document
Where objectType = 'a2pPart' AND Visibility = 1
Group By
    Label,
    Materiale,
    Shape.Volume / 1000000,
    Densit,
    Shape.Volume * Densit

In addition to this, I wanted to report the parts which are identical, as the screws, only one time by grouping them in a unique row and showing the total number of parts. As you can see in the picture, I couldn't get the job done by a hair:

img1.png (132.7 KiB) Viewed 759 times

img2.png (187.27 KiB) Viewed 759 times

Proposal
It would be great if someone could implement these two functionalities:

• Add a python-like function round(x,y) in the Select. In so doing, it could be possible to use a Select like this:

  Code: Select all

  Select
      ...,
      round(Shape.Volume / 1000000, 7) As 'Volume [dm^3]',
      ...
  

  so that the number shall be rounded to the 7th places to the right of the decimal point.

• Permit more than one arithmetic operations in Select like in this example where I could use two operations (multiplication and division) in order to obtain the mass in kg:

  Code: Select all

  Select
      ...,
      Shape.Volume * Densit / 1000000 As 'Massa [kg]'
  
  

I attach the FreeCAD file too.
Thank you very much.

Massimo

My version:

OS: Ubuntu 18.04.4 LTS (XFCE/xfce)
Word size of OS: 64-bit
Word size of FreeCAD: 64-bit
Version: 0.19.
Build type: Release
Branch: unknown
Hash: a724961ca3c78182a80adde4663f1f74e2e07eb6
Python version: 3.6.9
Qt version: 5.9.5
Coin version: 4.0.0a
OCC version: 7.3.0
Locale: Italian/Italy (it_IT)