create database usermaster;
use usermaster;
create table sampledata.user(userid int primary key,username varchar(45));
create table sampledata.products(productid int primary key,productname varchar(45));
create table usermaster.user_product_consumtion(id int primary key, userid int, productid int, foreign key (userid) references user(userid),foreign key (productid) references products(productid));
-- Insert user value
insert into usermaster.user values(1,'joe');
insert into usermaster.user values(2,'suzy');
--insert products value
insert into products values(1,'Milk');
insert into products values(2,'Coffee');
insert into products values(3,'Sugar');
insert user_product_consumption value
INSERT INTO `usermaster`.`user_product_consumption` (`id`,userid, `productid`) VALUES (1, (select userid from user where username='joe'), (select productid from products where productname='milk'));
INSERT INTO `usermaster`.`user_product_consumption` (`id`, `userid`, `productid`) VALUES (2, (select userid from user where username='joe'), (select productid from products where productname='coffee'));
INSERT INTO `usermaster`.`user_product_consumption` (`id`, `userid`, `productid`) VALUES (3, (select userid from user where username='joe'), (select productid from products where productname='sugar'));
INSERT INTO `usermaster`.`user_product_consumption` (`id`, `userid`, `productid`) VALUES (4, (select userid from user where username='suzy'), (select productid from products where productname='coffee'));
INSERT INTO `usermaster`.`user_product_consumption` (`id`, `userid`, `productid`) VALUES (5, (select userid from user where username='suzy'), (select productid from products where productname='sugar'));
Please note that for using env::username and to show the report according to the user logged in you should maintain the username same as available in Pentaho i.e. 'joe', 'suzy' etc. you can add your own username and give them different roles from Pentaho Administration Console as required. for details you can visit to the following link
Now back to the business, we will design report by 3 tables I have described above. First of all we will define the report query which is as follows :-
SELECT
`user`.`userid`,
`user`.`username`,
`products`.`productid`,
`products`.`productname`,
`user_product_consumption`.`id`,
`user_product_consumption`.`userid`,
`user_product_consumption`.`productid`
FROM
`user` INNER JOIN `user_product_consumption` ON `user`.`userid` = `user_product_consumption`.`userid`
INNER JOIN `products` ON `user_product_consumption`.`productid` = `products`.`productid`
After Defining the query add the parameter and give the values to the windows that appeared like parameter name, type, Default value formula, check the Hidden value and define the display type of parameter as Text box.
Default value formula should be like this ENV("username"). after doing this press ok, now you are done with defining parameter now rectify the query to use the parameter value. Add where clause to the report query and your final query should be like this:-
SELECT
`user`.`userid`,
`user`.`username`,
`user_product_consumption`.`id`,
`user_product_consumption`.`userid`,
`user_product_consumption`.`productid`,
`products`.`productid`,
`products`.`productname`
FROM
`user` INNER JOIN `user_product_consumption` ON `user`.`userid` = `user_product_consumption`.`userid`
INNER JOIN `products` ON `user_product_consumption`.`productid` = `products`.`productid`
WHERE user.username= ${username}
After defining the query design the layout as required and save the report as "envdemo1.prpt". Now publish the report to the BI server by giving it the admin credential and launch the report from PRD and logged in as joe and then you will be able to view the joe's data only because you have logged in as joe.
Now notice that if you will logged in as suzy you won't be able to view any data because suzy has no permission to access it. So log in as admin joe in PAC or PUC and right click on share and give all the rights to that user suzy by check the box all permission, now log in again as suzy and you will see the data of suzy.
For your better understanding follow the below video. :)
