1. 

   mfiorentino, Member

   Hi,
   I can not understandout how i can populate a select list dynamically.

   I want to populate it one way or another depending on the value of a variable:
   set @admin = (select `admin` from mydbrtutto.mydbr_userlogin where user = inLogin);

   if @admin=1 ther selectList= Italy, France, Spain, Germany
   else if @admin=0 selectList= Italy (just my country of belonging)

   In others words if i'm login as administrator then I'll run a query (I show all the dealers), otherwise I'll run another query (I'll only show one dealer)

   i can not do it before during this:
   CREATE PROCEDURE `sp_DBR_TPVReport_MultiVenta`(inCheckbox varchar(20))
   because i need to populate select list depending from @admin value

   thanks
2. 

   myDBR Team, Key Master

   Hi,
   what determines into which country the user belongs to i.e which country to show to non-admin user? Are you countries in a table / do that have ID's?

   --
   myDBR Team
3. 

   mfiorentino, Member

   yes this is no problem, i have a table with all countries in db. If i'm administrator i want see all countries but if i'm not administrator i want see just one country. This is to no permit to a no administrator user to see report sales of all countries because they must see just his country report sale. Instead administrator must have possibility to switch country.

   thanks
4. 

   myDBR Team, Key Master

   So what determines into which country the non-admin user belongs to?

   --
   myDBR Team
5. 

   mfiorentino, Member

   admin see all countries; not admin just his country: i have a table user, here i set user country; then i have a table with all countries. but my problem is how i can i populate select list dynamically and not before.
   i want populate select list depending value of a variable

   thanks
6. 

   myDBR Team, Key Master

   So you have a table user which contains the default country for the user. myDBR contains an automatic variable inLogin which contains the login for the logged in user. The login has some connection to your user-table (username, email or something like that).

   So, the parameter query for the Country selection would be something like this:

   create procedure sp_param_countries( inLogin varchar(30) )
   
   begin
   
   declare v_admin int;
   
   select admin into v_admin
   
   from mydbr_userlogin u
   
   where u.user = inLogin;
   
   select c.id, c.name
   
   from countries c
   
    join users u on v_admin = 1 or u.country_id = c.id
   
   where u.login = inLogin;
   
   end
   
   

   Note that if the country parameter is sensitive information and you use an user changeable parameter (like selectlist), you cannot trust the user input (user can change it via forging the input), you need to verify the input inside the report.

   To do this use again the inLogin parameter to see if user is admin and get the verified country for the user.

   --
   myDBR Team
7. 

   mfiorentino, Member

   hi,
   how can I put these values ​​in a select list?
   What I want to do is in the parameters above, before executing the query, filling the select list, with the names of all the countries (if an administrator logs in) or with the name of only one country if a non-administrator user logs on.

   findallCountries()
   if (you_are_admin == yes)
   listbox = allcountries
   else
   listbox = onlyYourCountry

   thanks
8. 

   myDBR Team, Key Master

   Create a selectlist parameter query like the sp_param_countries example above and attach it to the report parameter. The sp_param_countries -shows example how you add the logic for country selection.

   The actual query depends on your database structure for user- and company-tables.

   --
   myDBR Team
9. 

   mfiorentino, Member

   Hi,
   ok but how do I create a query type parameter that fills with all countries if an administrator logs in, or just with a country if a normal user logs in? can icreate a query type parameter with an if inside?

   thanks
10. 

    myDBR Team, Key Master

    The "IF"-clause was in the example query present in form of an OR-condition:

    create procedure sp_param_countries( inLogin varchar(30) )
    
    begin
    
    select c.id, c.name
    
    from mydbr_userlogin m
    
      join users u on m.user = u.user
    
      join countries c on m.admin = 1 or c.id = u.country_id
    
    where m.user = inLogin;
    
    end
    
    

    The JOIN to countries table is made so that if user is admin all countries are used and if the user is not admin, only the country that matches the user is joined.

    --
    myDBR Team
11. 

    mfiorentino, Member

    ok thank you very much

Reply

You must log in to post.