1. 

   jw1n5, Member

   I am having an issue with a report that has two 'Select Lists' populating via parameter queries. The queries follow the standard documentation for a basic and connected parameter. The first parameter performs a company lookup:

   select 0, 'All' union select companyid, name from company

   The second is a stored procedure that pulls location based on company id:

   CREATE DEFINER=Reports@% PROCEDURE sp_ADBR_GetCompanyLocations( company_id INT )

   BEGIN

   SELECT 0, 'All' UNION SELECT locationid, name FROM locations WHERE companyid = ccompany_id;

   END$$

   Generally speaking, the menus/parameters work without issue. However, there are some companies that when I choose them, I get a spinning circle at the top of the page and the location never populates. It seems that the issue occurs on companies that have more than 4 or 5 locations. If I select a different company (one with fewer locations) while the wheel is still spinning, it will populate the location menu. If I switch back to the company that won't load locations, it will retain the location list from the previously loaded company. I tested the procedure in the SQL editor, adding in the company ID as the company_id parameter, and all the locations load (7 rows, including the select 0, 'All')

   My workaround:

   However, if I just run the report with the non-working company and the location set to 'All', I can view and change the location from within the report settings menu

   That all being said, has anyone else had this issue? Is it a known issue? Is there a better workaround? Some sort of timeout that I am hitting? The query finishes in 0.05 seconds when I run it from SQL editor.

   Thanks in advance.
2. 

   myDBR Team, Key Master

   Hi,
   assume the row

   WHERE companyid = ccompany_id;

   should be:

   WHERE companyid = company_id;

   to match the parameter company_id (you should name the parameters with a prefix so no confilct with column names could occur).

   As for the problem itself, there are no known problems with the connected parameters. It is unlikely that the number of locations would be the issue (unless the query is incorrect and there is lot of data in the locations table and the query would return all of them).

   Coudl you check if there are any JavaScript errors in debugger. The spinning wheel indicates an ongoing Ajax request and if the query terminates unexpectedly (or does not terminate at all), wheel keeps spinning.

   --
   myDBR Team
3. 

   jw1n5, Member

   Sorry, yes, that was a typo. The queries/procedures themselves all work and are syntactically correct. I will take the prefix change under advisement.

   Debugging in Chrome shows the following error: Uncaught SyntaxError: Unexpected token ILLEGAL (Line 4, column 1)

   

   It looks like it may just be one company in particular that this is happening on. I haven't checked all of them but I spot checked another 6 or 7 and haven't seen this same error trigger. I removed the name column from the stored procedure that pulls the location ID and names and the error stopped. I checked the names for the locations and there isn't anything out of the ordinary. Also, as mentioned, if I proceed with the report, I am able to see all of the locations on the report page settings and I am also able to see all of the locations if I leave the report parameters page with that company selected, then load it again.

   Thanks for the quick response!
4. 

   myDBR Team, Key Master

   Most likely you have linefeeds in the locations.name data causing the "Uncaught SyntaxError: Unexpected token ILLEGAL" error. You could try to modify the parameter query to:

   CREATE PROCEDURE sp_ADBR_GetCompanyLocations(
   
   in_company_id INT
   
   )
   
   BEGIN
   
   SELECT 0, 'All'
   
   UNION
   
   SELECT locationid, replace(replace(name, char(10), ''), char(13), '')
   
   FROM locations
   
   WHERE companyid = in_company_id;
   
   END
   
   $$
   
   
5. 

   jw1n5, Member

   That did it. Thank you!

Reply

You must log in to post.