hello everyone,
Had some problem with the query while using the view in excell with the attach error, though the view do work with other version of database.
I’ve checked the view and it contain data into to it, but just cant pull that data to excell.
cheers
It appears that the name of the database in the dhis2_zmmay is showing
up in the Excel query. This should not be necessary. You will need to
edit the query source directly in Excel, and remove that.
It looks like you are using Office 2007? You can do this directly I
think, although I have not used Office2007 that much so I do not know
the exact procedure. Otherwise, on Office 2003, you can download
PivotPlayPlus add-in Excel Pivot Play PLUS Add-In
Regards,
jason
Hello Jason,
I did remove in the query but I am getting the same error.
in short I am using the query in two connection, one connection to dhis2 database and the next to dhis2_znzMay, the dhis2 works though all the properties are the same as those in the znzMay. and still getting the error.
and yes I am using 2007
On Tue, Sep 29, 2009 at 9:30 AM, Jason Pickering jason.p.pickering@gmail.com wrote:
It appears that the name of the database in the dhis2_zmmay is showing
up in the Excel query. This should not be necessary. You will need to
edit the query source directly in Excel, and remove that.
It looks like you are using Office 2007? You can do this directly I
think, although I have not used Office2007 that much so I do not know
the exact procedure. Otherwise, on Office 2003, you can download
PivotPlayPlus add-in http://www.contextures.com/xlPivotPlayPLUS01.html
Regards,
jason
–
Yusuph Kassim Kulindwa,
University of Dar Es Salaam,
Department of Computer Science,
System Developer/ Research Assistant.
Cell: +255 713 535648
this is the query that doesnt work
SELECT pivotsource_indicator_ou4.indicator, pivotsource_indicator_ou4.orgunit1, pivotsource_indicator_ou4.ou1, pivotsource_indicator_ou4.orgunit2, pivotsource_indicator_ou4.ou2, pivotsource_indicator_ou4.orgunit3, pivotsource_indicator_ou4.ou3, pivotsource_indicator_ou4.orgunit4, pivotsource_indicator_ou4.ou4, pivotsource_indicator_ou4.periodid, pivotsource_indicator_ou4.periodtype, pivotsource_indicator_ou4.year, pivotsource_indicator_ou4.month, pivotsource_indicator_ou4.period, pivotsource_indicator_ou4.numxfactor, pivotsource_indicator_ou4.factor, pivotsource_indicator_ou4.numeratorvalue, pivotsource_indicator_ou4.denominatorvalue, pivotsource_indicator_ou4.annualized, pivotsource_indicator_ou4.level
FROM dhis2_znzmay.public.pivotsource_indicator_ou4 pivotsource_indicator_ou4
and this is the query that works
SELECT pivotsource_indicator_ou4.indicator, pivotsource_indicator_ou4.orgunit1, pivotsource_indicator_ou4.ou1, pivotsource_indicator_ou4.orgunit2, pivotsource_indicator_ou4.ou2, pivotsource_indicator_ou4.orgunit3, pivotsource_indicator_ou4.ou3, pivotsource_indicator_ou4.orgunit4, pivotsource_indicator_ou4.ou4, pivotsource_indicator_ou4.periodid, pivotsource_indicator_ou4.periodtype, pivotsource_indicator_ou4.year, pivotsource_indicator_ou4.month, pivotsource_indicator_ou4.period, pivotsource_indicator_ou4.numxfactor, pivotsource_indicator_ou4.factor, pivotsource_indicator_ou4.numeratorvalue, pivotsource_indicator_ou4.denominatorvalue, pivotsource_indicator_ou4.annualized, pivotsource_indicator_ou4.level
FROM dhis2.public.pivotsource_indicator_ou4 pivotsource_indicator_ou4
On Tue, Sep 29, 2009 at 9:51 AM, Yusuph Kassim yusuphk@gmail.com wrote:
Hello Jason,
I did remove in the query but I am getting the same error.
in short I am using the query in two connection, one connection to dhis2 database and the next to dhis2_znzMay, the dhis2 works though all the properties are the same as those in the znzMay. and still getting the error.
and yes I am using 2007
On Tue, Sep 29, 2009 at 9:30 AM, Jason Pickering jason.p.pickering@gmail.com wrote:
It appears that the name of the database in the dhis2_zmmay is showing
up in the Excel query. This should not be necessary. You will need to
edit the query source directly in Excel, and remove that.
It looks like you are using Office 2007? You can do this directly I
think, although I have not used Office2007 that much so I do not know
the exact procedure. Otherwise, on Office 2003, you can download
PivotPlayPlus add-in http://www.contextures.com/xlPivotPlayPLUS01.html
Regards,
jason
–
Yusuph Kassim Kulindwa,
University of Dar Es Salaam,
Department of Computer Science,
System Developer/ Research Assistant.Cell: +255 713 535648
–
Yusuph Kassim Kulindwa,
University of Dar Es Salaam,
Department of Computer Science,
System Developer/ Research Assistant.
Cell: +255 713 535648
Hi Yusuph
I haven’t got excel to cross-check but I wonder if you are still “connected” to dhis2 database when you try to make the query to the other one. Is that how it works? You first setup a datasource then execute a query on it. If you just change the query you would then get this error. You also need to set up the connection again.
Sorry if I’m way off the mark.
Regards
Bob
2009/9/29 Yusuph Kassim yusuphk@gmail.com
this is the query that doesnt work
SELECT pivotsource_indicator_ou4.indicator, pivotsource_indicator_ou4.orgunit1, pivotsource_indicator_ou4.ou1, pivotsource_indicator_ou4.orgunit2, pivotsource_indicator_ou4.ou2, pivotsource_indicator_ou4.orgunit3, pivotsource_indicator_ou4.ou3, pivotsource_indicator_ou4.orgunit4, pivotsource_indicator_ou4.ou4, pivotsource_indicator_ou4.periodid, pivotsource_indicator_ou4.periodtype, pivotsource_indicator_ou4.year, pivotsource_indicator_ou4.month, pivotsource_indicator_ou4.period, pivotsource_indicator_ou4.numxfactor, pivotsource_indicator_ou4.factor, pivotsource_indicator_ou4.numeratorvalue, pivotsource_indicator_ou4.denominatorvalue, pivotsource_indicator_ou4.annualized, pivotsource_indicator_ou4.level
FROM dhis2_znzmay.public.pivotsource_indicator_ou4 pivotsource_indicator_ou4
and this is the query that works
SELECT pivotsource_indicator_ou4.indicator, pivotsource_indicator_ou4.orgunit1, pivotsource_indicator_ou4.ou1, pivotsource_indicator_ou4.orgunit2, pivotsource_indicator_ou4.ou2, pivotsource_indicator_ou4.orgunit3, pivotsource_indicator_ou4.ou3, pivotsource_indicator_ou4.orgunit4, pivotsource_indicator_ou4.ou4, pivotsource_indicator_ou4.periodid, pivotsource_indicator_ou4.periodtype, pivotsource_indicator_ou4.year, pivotsource_indicator_ou4.month, pivotsource_indicator_ou4.period, pivotsource_indicator_ou4.numxfactor, pivotsource_indicator_ou4.factor, pivotsource_indicator_ou4.numeratorvalue, pivotsource_indicator_ou4.denominatorvalue, pivotsource_indicator_ou4.annualized, pivotsource_indicator_ou4.level
FROM dhis2.public.pivotsource_indicator_ou4 pivotsource_indicator_ou4
On Tue, Sep 29, 2009 at 9:51 AM, Yusuph Kassim yusuphk@gmail.com wrote:
Hello Jason,
I did remove in the query but I am getting the same error.
in short I am using the query in two connection, one connection to dhis2 database and the next to dhis2_znzMay, the dhis2 works though all the properties are the same as those in the znzMay. and still getting the error.
and yes I am using 2007
On Tue, Sep 29, 2009 at 9:30 AM, Jason Pickering jason.p.pickering@gmail.com wrote:
It appears that the name of the database in the dhis2_zmmay is showing
up in the Excel query. This should not be necessary. You will need to
edit the query source directly in Excel, and remove that.
It looks like you are using Office 2007? You can do this directly I
think, although I have not used Office2007 that much so I do not know
the exact procedure. Otherwise, on Office 2003, you can download
PivotPlayPlus add-in http://www.contextures.com/xlPivotPlayPLUS01.html
Regards,
jason
–
Yusuph Kassim Kulindwa,
University of Dar Es Salaam,
Department of Computer Science,
System Developer/ Research Assistant.Cell: +255 713 535648
–
Yusuph Kassim Kulindwa,
University of Dar Es Salaam,
Department of Computer Science,
System Developer/ Research Assistant.Cell: +255 713 535648
Mailing list: https://launchpad.net/~dhis2-devs
Post to : dhis2-devs@lists.launchpad.net
Unsubscribe : https://launchpad.net/~dhis2-devs
More help : https://help.launchpad.net/ListHelp
And you are executing this query from which database? dhis2 or
dhis2_znmay? If you are executing it from dhis2 this query will not
work.
Postgresql does not allow cross-databaes queries by default (unlike
MS-SQL), thus the error message. You will need to either 1) create a
separate ODBC connection to the dhis2_znmay data base, remove the
database reference and link the query through that second ODBC
connection or 2) implement cross-database query support
Regards,
Jason
On Tue, Sep 29, 2009 at 8:53 AM, Yusuph Kassim <yusuphk@gmail.com> wrote:
this is the query that doesnt work
SELECT pivotsource_indicator_ou4.indicator,
pivotsource_indicator_ou4.orgunit1, pivotsource_indicator_ou4.ou1,
pivotsource_indicator_ou4.orgunit2, pivotsource_indicator_ou4.ou2,
pivotsource_indicator_ou4.orgunit3, pivotsource_indicator_ou4.ou3,
pivotsource_indicator_ou4.orgunit4, pivotsource_indicator_ou4.ou4,
pivotsource_indicator_ou4.periodid, pivotsource_indicator_ou4.periodtype,
pivotsource_indicator_ou4.year, pivotsource_indicator_ou4.month,
pivotsource_indicator_ou4.period, pivotsource_indicator_ou4.numxfactor,
pivotsource_indicator_ou4.factor, pivotsource_indicator_ou4.numeratorvalue,
pivotsource_indicator_ou4.denominatorvalue,
pivotsource_indicator_ou4.annualized, pivotsource_indicator_ou4.level
FROM dhis2_znzmay.public.pivotsource_indicator_ou4 pivotsource_indicator_ou4
Hi bob,
thanks for the response, I am using two different odbc datasource. one for the dhis2 and the other the znzMay database.
On Tue, Sep 29, 2009 at 9:59 AM, Bob Jolliffe bobjolliffe@gmail.com wrote:
Hi Yusuph
I haven’t got excel to cross-check but I wonder if you are still “connected” to dhis2 database when you try to make the query to the other one. Is that how it works? You first setup a datasource then execute a query on it. If you just change the query you would then get this error. You also need to set up the connection again.
Sorry if I’m way off the mark.
Regards
Bob2009/9/29 Yusuph Kassim yusuphk@gmail.com
this is the query that doesnt work
SELECT pivotsource_indicator_ou4.indicator, pivotsource_indicator_ou4.orgunit1, pivotsource_indicator_ou4.ou1, pivotsource_indicator_ou4.orgunit2, pivotsource_indicator_ou4.ou2, pivotsource_indicator_ou4.orgunit3, pivotsource_indicator_ou4.ou3, pivotsource_indicator_ou4.orgunit4, pivotsource_indicator_ou4.ou4, pivotsource_indicator_ou4.periodid, pivotsource_indicator_ou4.periodtype, pivotsource_indicator_ou4.year, pivotsource_indicator_ou4.month, pivotsource_indicator_ou4.period, pivotsource_indicator_ou4.numxfactor, pivotsource_indicator_ou4.factor, pivotsource_indicator_ou4.numeratorvalue, pivotsource_indicator_ou4.denominatorvalue, pivotsource_indicator_ou4.annualized, pivotsource_indicator_ou4.level
FROM dhis2_znzmay.public.pivotsource_indicator_ou4 pivotsource_indicator_ou4
and this is the query that works
SELECT pivotsource_indicator_ou4.indicator, pivotsource_indicator_ou4.orgunit1, pivotsource_indicator_ou4.ou1, pivotsource_indicator_ou4.orgunit2, pivotsource_indicator_ou4.ou2, pivotsource_indicator_ou4.orgunit3, pivotsource_indicator_ou4.ou3, pivotsource_indicator_ou4.orgunit4, pivotsource_indicator_ou4.ou4, pivotsource_indicator_ou4.periodid, pivotsource_indicator_ou4.periodtype, pivotsource_indicator_ou4.year, pivotsource_indicator_ou4.month, pivotsource_indicator_ou4.period, pivotsource_indicator_ou4.numxfactor, pivotsource_indicator_ou4.factor, pivotsource_indicator_ou4.numeratorvalue, pivotsource_indicator_ou4.denominatorvalue, pivotsource_indicator_ou4.annualized, pivotsource_indicator_ou4.level
FROM dhis2.public.pivotsource_indicator_ou4 pivotsource_indicator_ou4
On Tue, Sep 29, 2009 at 9:51 AM, Yusuph Kassim yusuphk@gmail.com wrote:
Hello Jason,
I did remove in the query but I am getting the same error.
in short I am using the query in two connection, one connection to dhis2 database and the next to dhis2_znzMay, the dhis2 works though all the properties are the same as those in the znzMay. and still getting the error.
and yes I am using 2007
On Tue, Sep 29, 2009 at 9:30 AM, Jason Pickering jason.p.pickering@gmail.com wrote:
It appears that the name of the database in the dhis2_zmmay is showing
up in the Excel query. This should not be necessary. You will need to
edit the query source directly in Excel, and remove that.
It looks like you are using Office 2007? You can do this directly I
think, although I have not used Office2007 that much so I do not know
the exact procedure. Otherwise, on Office 2003, you can download
PivotPlayPlus add-in http://www.contextures.com/xlPivotPlayPLUS01.html
Regards,
jason
–
Yusuph Kassim Kulindwa,
University of Dar Es Salaam,
Department of Computer Science,
System Developer/ Research Assistant.Cell: +255 713 535648
–
Yusuph Kassim Kulindwa,
University of Dar Es Salaam,
Department of Computer Science,
System Developer/ Research Assistant.Cell: +255 713 535648
Mailing list: https://launchpad.net/~dhis2-devs
Post to : dhis2-devs@lists.launchpad.net
Unsubscribe : https://launchpad.net/~dhis2-devs
More help : https://help.launchpad.net/ListHelp
–
Yusuph Kassim Kulindwa,
University of Dar Es Salaam,
Department of Computer Science,
System Developer/ Research Assistant.
Cell: +255 713 535648
I am having each database with its own connection. I actually tried to create direct from the ODBC connection and also tried to create the connection as I was creating the query, but both ways gives me the same error.
On Tue, Sep 29, 2009 at 10:00 AM, Jason Pickering jason.p.pickering@gmail.com wrote:
On Tue, Sep 29, 2009 at 8:53 AM, Yusuph Kassim yusuphk@gmail.com wrote:
this is the query that doesnt work
SELECT pivotsource_indicator_ou4.indicator,
pivotsource_indicator_ou4.orgunit1, pivotsource_indicator_ou4.ou1,
pivotsource_indicator_ou4.orgunit2, pivotsource_indicator_ou4.ou2,
pivotsource_indicator_ou4.orgunit3, pivotsource_indicator_ou4.ou3,
pivotsource_indicator_ou4.orgunit4, pivotsource_indicator_ou4.ou4,
pivotsource_indicator_ou4.periodid, pivotsource_indicator_ou4.periodtype,
pivotsource_indicator_ou4.year, pivotsource_indicator_ou4.month,
pivotsource_indicator_ou4.period, pivotsource_indicator_ou4.numxfactor,
pivotsource_indicator_ou4.factor, pivotsource_indicator_ou4.numeratorvalue,
pivotsource_indicator_ou4.denominatorvalue,
pivotsource_indicator_ou4.annualized, pivotsource_indicator_ou4.level
FROM dhis2_znzmay.public.pivotsource_indicator_ou4 pivotsource_indicator_ou4
And you are executing this query from which database? dhis2 or
dhis2_znmay? If you are executing it from dhis2 this query will not
work.
Postgresql does not allow cross-databaes queries by default (unlike
MS-SQL), thus the error message. You will need to either 1) create a
separate ODBC connection to the dhis2_znmay data base, remove the
database reference and link the query through that second ODBC
connection or 2) implement cross-database query support
http://www.postgresql.org/docs/8.3/static/dblink.html
Regards,
Jason
–
Yusuph Kassim Kulindwa,
University of Dar Es Salaam,
Department of Computer Science,
System Developer/ Research Assistant.
Cell: +255 713 535648
OK, so does this work on both databases (when connected to each on
seperately?) There is no need to reference the database itself (dhis2
or dhis2_znmay) when you are connected to the database itself.
SELECT pivotsource_indicator_ou4.indicator,
pivotsource_indicator_ou4.orgunit1, pivotsource_indicator_ou4.ou1,
pivotsource_indicator_ou4.orgunit2, pivotsource_indicator_ou4.ou2,
pivotsource_indicator_ou4.orgunit3, pivotsource_indicator_ou4.ou3,
pivotsource_indicator_ou4.orgunit4, pivotsource_indicator_ou4.ou4,
pivotsource_indicator_ou4.periodid,
pivotsource_indicator_ou4.periodtype,
pivotsource_indicator_ou4.year, pivotsource_indicator_ou4.month,
pivotsource_indicator_ou4.period, pivotsource_indicator_ou4.numxfactor,
pivotsource_indicator_ou4.factor,
pivotsource_indicator_ou4.numeratorvalue,
pivotsource_indicator_ou4.denominatorvalue,
pivotsource_indicator_ou4.annualized, pivotsource_indicator_ou4.level
FROM public.pivotsource_indicator_ou4
If not, then it must be an issue with the DB. Otherwise, maybe it is
something with Excel. I do not have enough experience with 2007 to be
able to provide any intelligent response really, but I know with 2003
the ODBC connection had to be explicitly defined as part of the query.
Double check that.
Regards,
Jason