Many times we need to design the reports which consist of data according to the user logged in. One can not show whole information to everyone, what we need to do is show the report accordingly. So here I am going to describe how to generate the report as per the user logged in.
For getting information of user logged in we need ENVIRONMENT variable which are available from the release PRD 3.6. with the help of Environment variable we can get many information like Role, username, pentaho base url, server base url and so on. For more details on ENV variable available in PRD you can refer to these blog http://www.on-reporting.com/blog/access-session-and-environment/ and
and you can also refer to this video to get to know other details of Latest feature of PRD 3.6
Now I will take you through the details of how to use ENV variable env::username. First of all we need a database schema to make the report. I have made the schema usermaster having 3 tables:-
- user
- products
- user_product_consumption
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. :)
