Create SQL reports in Zoho Analytics
Budget: €100 – €120 EUR
MAKE 5 REPORTS SQL IN ZOHO ANALYTICS, ONE DASHBOARD AND 5 WIDGETS
Tables to use:
Invoices
Invoice Items
Customers
Credit notes
DONNEES MANDATAIRE
ACHAT ANNONCES OK
Columns in the 5 reports:
Invoices.Invoice Date
Invoices.Invoice number
Invoices.Purchase order
Invoices.INSTANCE D'ORIGINE
Customers.Customer name
Invoice items.Sub total (BCY)
Invoices.NOM DU JOURNAL
ACHAT ANNONCES OK.Montant du lignage
ACHAT ANNONCES OK.Taux de rétrocession du journal extérieur
DONNEES MANDATAIRE.Nom du mandataire
DONNEES MANDATAIRE.Taux de rétrocession
Credit notes.Credit Note Number
Credit notes.Sub Total (BCY)
Invoices.DEPARTEMENT
Make 5 reports different with these columns according to some filters in order to calculate 5 aggregate formulas (the result must be used in other reports):
I-Rapport d'export (non Malcom):
User Filters:
”Invoices.INSTANCE D'ORIGINE” (multisélection)
”Invoices.NOM DU JOURNAL” (multiselection)
Filters
“Invoices.Status”: closed
“Invoices.Commission des instances”: non payé
"ACHAT ANNONCES OK"."Montant du lignage": not empty
Invoice items.Name of the article: contains “LIGNAGE”
Invoices.TYPE DE FACTURE: is Annonce légale
Agregate formula: "Produit net d'exploitation journaux extérieurs" (The data "Produit net d'exploitation journaux extérieurs" calculated from this formula must be used in other reports).:
"Invoice items.Sub total (BCY)" - "Credit notes.Sub Total (BCY)" - "ACHAT ANNONCES OK.Montant du lignage" + ("ACHAT ANNONCES OK.Montant du lignage" * "DONNEES MANDATAIRE.Taux de rétrocession" / 100)
II-Rapport d'export (MALCOM):
User Filters:
Invoices.INSTANCE D'ORIGINE (multisélection)
Invoices.NOM DU JOURNAL (multiselection)
Filters
Invoices.Status: closed
“Invoices.Commission des instances”: non payé
Invoice items.Name of the article: contains “LIGNAGE”
Invoices.TYPE DE FACTURE: is “Annonce légale”
Agregate formula: "Produit net d'exploitation export MALCOM" (The data "Produit net d'exploitation export MALCOM" calculated from this formula must be used in other reports).:
(("Invoice items.Sub total (BCY)"- Credit notes.Sub Total (BCY)) *30/100
III-Rapport journal de l’instance
User Filters:
Invoices.INSTANCE D'ORIGINE (multisélection)
Invoices.NOM DU JOURNAL (multiselection)
Filters
Invoices.Status: closed
“Invoices.Commission des instances”: non payé
Invoice items.Name of the article: contains “LIGNAGE”
Invoices.TYPE DE FACTURE: is Annonce légale
Agregate formula: "Produit net d'exploitation journal de l’instance" (The data "Produit net d'exploitation journal de l’instance" calculated from this formula must be used in other reports).:
"Invoice items.Sub total (BCY)" - Credit notes.Sub Total (BCY)
IV-Rapport d’import depuis instances non MALCOM:
User Filters:
Invoices.INSTANCE D'ORIGINE (multisélection)
Invoices.NOM DU JOURNAL (multiselection)
Filters
Invoices.Status: closed
“Invoices.Commission des instances”: non payé
Invoice items.Name of the article: contains “LIGNAGE”
Invoices.TYPE DE FACTURE: is Annonce légale
Agregate formula: "Produit net d'exploitation importé depuis instances non MALCOM" (The data "Produit net d'exploitation inter instance" calculated from this formula must be used in other reports).:
(Invoice items.Sub total (BCY) - Credit notes.Sub Total (BCY)) * 60/100
V-Rapport d’import depuis instance MALCOM:
User Filters:
Invoices.INSTANCE D'ORIGINE (multisélection)
Invoices.NOM DU JOURNAL (multiselection)
Filters
Invoices.Status: closed
“Invoices.Commission des instances”: non payé
Invoice items.Name of the article: contains “LIGNAGE”
Invoices.TYPE DE FACTURE: is Annonce légale
Agregate formula: "Produit net d'exploitation importé depuis MALCOM" (The data "Produit net d'exploitation envoyé par MALCOM" calculated from this formula must be used in other reports).:
(Invoice items.Sub total (BCY) - Credit notes.Sub Total (BCY))
VI-Create one Dashboards with these 5 reports.
User filters:
“Invoices.INSTANCE D'ORIGINE” (multiselection)
“Invoices.Date of invoice” (multiselection =>quarters and years)
In this dashboard, create five widgets for the five reports with one figure in each: calculate SUM for the quarter of all Produit net d’exploitation XXXXX you calculate before. (see picture and PDF in attached file)
Tables to use:
Invoices
Invoice Items
Customers
Credit notes
DONNEES MANDATAIRE
ACHAT ANNONCES OK
Columns in the 5 reports:
Invoices.Invoice Date
Invoices.Invoice number
Invoices.Purchase order
Invoices.INSTANCE D'ORIGINE
Customers.Customer name
Invoice items.Sub total (BCY)
Invoices.NOM DU JOURNAL
ACHAT ANNONCES OK.Montant du lignage
ACHAT ANNONCES OK.Taux de rétrocession du journal extérieur
DONNEES MANDATAIRE.Nom du mandataire
DONNEES MANDATAIRE.Taux de rétrocession
Credit notes.Credit Note Number
Credit notes.Sub Total (BCY)
Invoices.DEPARTEMENT
Make 5 reports different with these columns according to some filters in order to calculate 5 aggregate formulas (the result must be used in other reports):
I-Rapport d'export (non Malcom):
User Filters:
”Invoices.INSTANCE D'ORIGINE” (multisélection)
”Invoices.NOM DU JOURNAL” (multiselection)
Filters
“Invoices.Status”: closed
“Invoices.Commission des instances”: non payé
"ACHAT ANNONCES OK"."Montant du lignage": not empty
Invoice items.Name of the article: contains “LIGNAGE”
Invoices.TYPE DE FACTURE: is Annonce légale
Agregate formula: "Produit net d'exploitation journaux extérieurs" (The data "Produit net d'exploitation journaux extérieurs" calculated from this formula must be used in other reports).:
"Invoice items.Sub total (BCY)" - "Credit notes.Sub Total (BCY)" - "ACHAT ANNONCES OK.Montant du lignage" + ("ACHAT ANNONCES OK.Montant du lignage" * "DONNEES MANDATAIRE.Taux de rétrocession" / 100)
II-Rapport d'export (MALCOM):
User Filters:
Invoices.INSTANCE D'ORIGINE (multisélection)
Invoices.NOM DU JOURNAL (multiselection)
Filters
Invoices.Status: closed
“Invoices.Commission des instances”: non payé
Invoice items.Name of the article: contains “LIGNAGE”
Invoices.TYPE DE FACTURE: is “Annonce légale”
Agregate formula: "Produit net d'exploitation export MALCOM" (The data "Produit net d'exploitation export MALCOM" calculated from this formula must be used in other reports).:
(("Invoice items.Sub total (BCY)"- Credit notes.Sub Total (BCY)) *30/100
III-Rapport journal de l’instance
User Filters:
Invoices.INSTANCE D'ORIGINE (multisélection)
Invoices.NOM DU JOURNAL (multiselection)
Filters
Invoices.Status: closed
“Invoices.Commission des instances”: non payé
Invoice items.Name of the article: contains “LIGNAGE”
Invoices.TYPE DE FACTURE: is Annonce légale
Agregate formula: "Produit net d'exploitation journal de l’instance" (The data "Produit net d'exploitation journal de l’instance" calculated from this formula must be used in other reports).:
"Invoice items.Sub total (BCY)" - Credit notes.Sub Total (BCY)
IV-Rapport d’import depuis instances non MALCOM:
User Filters:
Invoices.INSTANCE D'ORIGINE (multisélection)
Invoices.NOM DU JOURNAL (multiselection)
Filters
Invoices.Status: closed
“Invoices.Commission des instances”: non payé
Invoice items.Name of the article: contains “LIGNAGE”
Invoices.TYPE DE FACTURE: is Annonce légale
Agregate formula: "Produit net d'exploitation importé depuis instances non MALCOM" (The data "Produit net d'exploitation inter instance" calculated from this formula must be used in other reports).:
(Invoice items.Sub total (BCY) - Credit notes.Sub Total (BCY)) * 60/100
V-Rapport d’import depuis instance MALCOM:
User Filters:
Invoices.INSTANCE D'ORIGINE (multisélection)
Invoices.NOM DU JOURNAL (multiselection)
Filters
Invoices.Status: closed
“Invoices.Commission des instances”: non payé
Invoice items.Name of the article: contains “LIGNAGE”
Invoices.TYPE DE FACTURE: is Annonce légale
Agregate formula: "Produit net d'exploitation importé depuis MALCOM" (The data "Produit net d'exploitation envoyé par MALCOM" calculated from this formula must be used in other reports).:
(Invoice items.Sub total (BCY) - Credit notes.Sub Total (BCY))
VI-Create one Dashboards with these 5 reports.
User filters:
“Invoices.INSTANCE D'ORIGINE” (multiselection)
“Invoices.Date of invoice” (multiselection =>quarters and years)
In this dashboard, create five widgets for the five reports with one figure in each: calculate SUM for the quarter of all Produit net d’exploitation XXXXX you calculate before. (see picture and PDF in attached file)