Search the Community

Hi, I would like to built custom reports related to invoices. I do have multi company version so the problem is how to define that the query would return only current company entries? Is where predefined fields for that? Kind Regards, Nerijus Barauskas

Using Google, I have pulled a few SQL queries from this forum to start building a deck of sample custom reports. I have six or so such reports. Is there a resource for finding more? I understand the Blesta ER diagram is proprietary, but would be nice if there was a bit more information regarding the table structure somewhere. With a bit more information, Blesta could be used for many businesses - gyms, prechools, HVAC firms -- anyone wanting to build a membership or subscription type revenue stream for their business.

When I run an Invoice Report from Billing->Reports, I'd love to be able to include the packages associated with each invoice, so I can see revenue from each package independantly. Is there a way to do this? Thanks!

Hello, I'm trying to create a custom report, but I receive error: "Only one query allowed, and it must be a SELECT query." Problem occurs when I trying to add next string in my query: GROUP_CONCAT(CONCAT(`client_notes`.`title`, `client_notes`.`description`) SEPARATOR '; ') AS 'Notes' Full text of my query: SELECT `users`.`date_added` AS 'Memeber Since', `clients`.`status` AS 'Client Status', `clients`.`id_value` AS 'CRM #', `contacts`.`first_name` AS 'First Name', `contacts`.`last_name` AS 'Last Name', `contacts`.`company` AS 'Company/Org.', `contacts`.`address1` AS 'Address', `contacts`.`city` AS 'City', `contacts`.`country` AS 'Country', `contacts`.`state` AS 'State/Province', `contacts`.`zip` AS 'Zip/Postal Code', `contacts`.`email` AS 'Email', GROUP_CONCAT(CONCAT(`client_notes`.`title`, `client_notes`.`description`) SEPARATOR '; ') AS 'Notes' FROM `clients` JOIN `users` ON (`users`.`id`=`clients`.`user_id`) LEFT JOIN `contacts` ON (`contacts`.`client_id`=`clients`.`id` AND `contacts`.`contact_type`='primary') LEFT JOIN `contact_numbers` ON (`contact_numbers`.`contact_id`=`contacts`.`id`) LEFT JOIN `client_notes` ON (`client_notes`.`client_id`=`clients`.`id`) GROUP BY `clients`.`id`