ISSUE :
 
Profile photo
Submitted by Nilesh Nirala
on December 21st, 2011 at 04:01:20 EST

pentaho design report error

we are taking employees,departments ,designations,but in every table "name" field type is mentioned there, while we join the tables three tables name field types is showing in sql but while applying these query in pentaho report design it's taking as distinct columns , but in sql it's working fine.please give me ideas.

Query is:-

select employees.employeeid, employees.name,designations.name,d.name from employees INNER JOIN designations ON employees.designation_id=designations.id INNER JOIN departments as d ON employees.department_id=d.id


Thanks,
Nilesh


Profile photo
Replied by Yogaraj Khanal
on December 21st, 2011 at 10:07:49 EST
@Nilesh,
What version of Pentaho are you using?Also how are you designing your report?Are you using Report designer that comes with out of the box?What data source you are using?Is it existing data source in Pentaho like SampleData or SteelWheels?And what kind of error you are getting?
You can quickly debug your results using CDE,CDF,CDA using CTOOLS installer too just using table component.I can try your use case and reproduce error if you provide me those information.
Accepted Answer
Accepted
 Answer

Profile photo
Replied by Nilesh Nirala
on December 22nd, 2011 at 00:00:00 EST
Thanks Yogaraj.
I am using Pentaho Report Design 3.8,using JDBC datasource(Postgres).I have written above query in query box and clicked the preview button then coming output column name wise employeeid,name,name,name. When i select ok button inside JDBC Data Source form coming only columns empoyeeid and name. it's eliminated two columns name.Please tell me,how i will take all columns inside Data Sets(Query name, inside all columns) then i can create reports throuhg drag and drop.please give me ideas.

Thanks,
Nilesh

Profile photo
Replied by Yogaraj Khanal
on December 22nd, 2011 at 17:03:54 EST
why don't you create your DDL providing distinct name to isolate the problem if it has to do with three names in the three tables sth like this:

CREATE TABLE employees( emp_id int, emp_name name, emp_salary int );
CREATE TABLE departments( dept_id int, dept_name name);
CREATE TABLE designations( desg_id int, desg_name name);


I am not sure if its your dev test or prod environment and if you have access to change that?Just idea to debug the issue?

Profile photo
Replied by Yogaraj Khanal
on December 22nd, 2011 at 17:06:08 EST
also i assume that one emp has many dept and one emp can have many designation is that the only relationship u have?do u mind sharing ur DDL script i am kind of interested to replicate this issue?

Profile photo
Replied by Yogaraj Khanal
on December 23rd, 2011 at 10:15:53 EST
Nilesh,
I am able to reproduce your problem seems its bug and need to alter your column name
but i was able to make it work by altering the column name like this:
ALTER TABLE employees RENAME COLUMN name TO emp_name;
ALTER TABLE departments RENAME COLUMN name TO dept_name;
ALTER TABLE designations RENAME COLUMN name TO desg_name;
In this case it shows all different column name and was able to pull those three name from three different tables.
So this is one solution to this problem I am not sure if its applicable to your env.

Profile photo
Replied by Yogaraj Khanal
on January 5th, 2012 at 09:55:20 EST
Nilesh,
If you don't have flexibility to alter column name you can just use aliases(employees.name as emp_name, designations.name as desg_name ,d.name as dept_name)
in your query as follow

select employees.employeeid, employees.name as emp_name,designations.name as desg_name ,d.name as dept_name from employees INNER JOIN designations ON employees.designation_id=designations.id INNER JOIN departments as d ON employees.department_id=d.id
Hope this work around resolves your issue.

Profile photo
Replied by Nilesh Nirala
on January 5th, 2012 at 22:54:39 EST
Thanks Yogaraj.Sorry for late reply.
This query is working fine in PgAdmin also working in Pentaho tools but we are fetching three column with same name so pentaho eliminating the two column name(name,name) fields and showing employeid,name columns.I think, this issue is coming from pentaho tools.

Profile photo
Replied by Yogaraj Khanal
on January 6th, 2012 at 23:06:44 EST
I know the problem with PRD 3.8.But the latest workaround I provided should solve your issue since it makes those three name as distinct without changing your data model.
I am able to replicate your problem and solve: one by altering column name and other by using aliases.Please use one of this as workaround.Hope that helps.