1. 

   Dev, Member

   Hi,

   I am looking to use the multiselect filter in one of my reports, but the issue is that my main report, where the parameter query is called, doesn't seem to be working. It shows a typable box instead of a search-and-find box.

   Below is my parameter query:

   DROP PROCEDURE IF EXISTS sp_ADBR_po_multiselect
   $$
   CREATE PROCEDURE `sp_ADBR_po_multiselect`(IN inTenantKey varchar(128))
   BEGIN
   SELECT 0 AS id, 'All' AS customerName
   UNION
   SELECT DISTINCT
   c.id,
   COALESCE(CONCAT(c.first_name, ' ', COALESCE(c.last_name, '')), 'Walk-in Customer') AS customerName
   FROM
   kennel_payment.invoice inv
   LEFT JOIN kennel_payment.payment p ON p.invoice_id = inv.id AND p.payment_status = 'APPROVED'
   LEFT JOIN kennel_payment.customer c ON c.id = inv.customer_id
   WHERE
   inv.merchant_key = inTenantKey
   AND inv.deleted = FALSE
   ORDER BY customerName;
   END
   $$

   And I have called the above in my main query as below:

   parameter as inSelectedCustomers TEXT
   & in the report query as -> (inSelectedCustomers IS NULL OR FIND_IN_SET(c.id, inSelectedCustomers)

   Please help me out here.

   Thank you.
2. 

   myDBR Team, Key Master

   A multiselect parameter shows a typable box which then is used for the search. See the demo.

   Your query seems to be ok.
   --
   myDBR Team
3. 

   Dev, Member

   Thank you, but I seem to have seen the text 'The results could not be loaded' in the search box. What is the problem I am having?
4. 

   Dev, Member

   Updated Parameter query is

   DROP PROCEDURE IF EXISTS sp_ADBR_po_multiselect
   $$
   CREATE PROCEDURE `sp_ADBR_po_multiselect`(inTenantKey varchar(128),in_search TEXT)
   BEGIN

   SELECT
   DISTINCT
   c.id,
   COALESCE(CONCAT(c.first_name, ' ', COALESCE(c.last_name, '')), 'Walk-in Customer') AS customerName

   FROM
   kennel_payment.invoice inv
   JOIN kennel_payment.payment p ON p.invoice_id = inv.id AND p.payment_status = 'APPROVED'
   JOIN kennel_payment.customer c ON c.id = inv.customer_id

   WHERE
   inv.merchant_key = inTenantKey
   AND inv.deleted = FALSE
   AND COALESCE(CONCAT(c.first_name, ' ', COALESCE(c.last_name, '')), 'Walk-in Customer') LIKE CONCAT('%', in_search, '%')

   ORDER BY customerName LIMIT 40;

   END
   $$

   The same error is appearing "The results couldn't be loaded". The search appears to be working when the parameter is defined. But not working when the parameter is not specified value. Any kind of input would be appreciated.
5. 

   myDBR Team, Key Master

   The message 'The results couldn't be loaded' means that no matching records were found based on your search criteria. This is the expected behavior. When you search using matching criteria, you will be able to select the records you want from the results.

   --
   myDBR Team

Reply

You must log in to post.