1. 

   spyhunter88, Member

   Hi,

   I use Linked Report with some fixed visible value in Connected parameter but the link report error. The 2nd parameter (the one has mydbr_param_prev1 in query) read the 1st param without single quote so it recognize previous parameter value like a column, not the value.

   My 2nd parameter:
   selectBrandas ID,Brandfrom Index.Index_Category where Category = mydbr_param_prev1;

   My Linked report works:
   select 'dbr.report', 'new_popup', 'ID', 'inCat<=Cat', inBrand<="";

   But this does not work:
   select 'dbr.report', 'new_popup', 'ID', 'inCat*=Cat', inBrand*="";

   The error:
   

   Unknown column 'Mobile' in 'where clause'
   SQL: select <code>Brand</code> AS ID, <code>Brand</code> from Index.Index_Category where Category=Mobile;

   Thanks,
2. 

   myDBR Team, Key Master

   What are the parameters for the actual report? What is the parameter query for the first parameter?

   --
   myDBR Team
3. 

   spyhunter88, Member

   Sorry for the typo mistake, my actual report has 4 connected parameter, but it fail on the second due to not recognize the first value.

   My first parameter is a select :
   select Category as ID, Category from Index.Index_Category group by Category;

   In my case, I try with direct value passing for all parameter.

   select 'dbr.report', 'new_popup', 'ID', 'inCat<="Mobile"', 'inBrand<=""';

   Thanks & Best Regards,
4. 

   myDBR Team, Key Master

   What are the parameter definitions for the actual report?

   --
   myDBR Team
5. 

   spyhunter88, Member

   I don't set any options for parameters, mean they're all needed.
6. 

   myDBR Team, Key Master

   Hi,
   In order for us to recreate the report we need to have the same definitions. So your first parameter query is defined as:

   select Category as ID, Category from Index.Index_Category group by Category

   What is the parameter query type (a popup, radio button, checkbox)? What is the datatype for Index.Index_Category.Category?

   As a second parameter's parameter query you have:

   select Brand as ID, Brand from Index.Index_Category where Category = mydbr_param_prev1;

   What is the parameter query type (a popup, radio button, checkbox)? What is the datatype for Index.Index_Category.Brand?

   What are the parameter definitions for the actual report? Can you show the CREATE PROCEDURE start from start to the BEGIN so we see the parameters and the datatypes for the parameter.

   --
   myDBR Team
7. 

   spyhunter88, Member

   Hi,

   2 params are popup, all are pair ID and a column name. Also the Category and Brand columns are varchar(50);
   And my parameters store definition:
   CREATE PROCEDURE sp_DBR_add_Adjustment(inID int, inCat varchar(20), inBrand varchar(20) inAmount bigint, inNote varchar(255), inAction varchar(30), inLogin varchar(50))

   with inCat and inBrand are Connected and inCat, inBrand, inAmount are require. Others is optional.

   Thanks,
8. 

   myDBR Team, Key Master

   OK,
   that was quite clever way of going around the fact that connected parameters are only supported when using popups/radiobuttons.

   Anyways, the problem comes from the fact that you pass a fixed variable to parameter query. As myDBR has no way of knowing the datatype in question, it has no means of putting the parameter inside quotation marks.

   We've made a small change to latest build in order to support this functionality. The only catch is to use a stored procedure as a parameter query so myDBR is able to parse the datatype in question. To make it work you update to the latest build and define the parameter query for the second connected parameter as:

   create procedure sp_param_brands_for_category( inCat varchar(50) )
   
   begin
   
   select Brand, Brand
   
   from Index.Index_Category
   
   where Category = inCat;
   
   end
   
   

   
   Now that the parameter query parameter has same name (inCat) as the parameter in your original report, myDBR knows to use that parameter as connected parameter. This is better way of using the conected parameter that the older "mydbr_param_prev1"-method.

   --
   myDBR Team
9. 

   spyhunter88, Member

   Nice, I plan to use Store Procedure for parameter instead of normal queries with inLogin to provide Data permission.

   Many thanks,
10. 

    myDBR Team, Key Master

    If you are concerned about the security and you move the permissions to the user side, please note if you pass stored procedures as a parameter, you have to check the parameters within the report as user can falsify the parameters.

    The safe way of limiting data is to use report permssions and inLogin-parameter which are not user accessible.

    --
    myDBR Team
11. 

    spyhunter88, Member

    Yep, I know that, but I must use both Parameter Store Procedure and inLogin parameter. And I can only choose the first choice because the hash can make more secure and reduce re-type store.

    But in some case, the new build make the connected parameter does not work with 'new_popup', both with simple query with mydbr_param_prev{n}, store procedure with parameter mydbr_param_prev. I'll find exactly the case and report later.

    Thanks,

Reply

You must log in to post.