Zucker Report Opportunities Break down

[nvdp2002]

Sugar Version 5.0.0c (Build 3187)
Zucker Version 1.9

I am trying to create a report in Zucker reports where oporunities for the current user are displayed.

I can a list of opportunities to display but when i fillter bu parameter " CURRENT USER " it returns no result.

The current user parameter is supposed to return the ID of the current user which should match with some of the "assigned_user_id" for the opportunities.

Any help will be apreciated.

Thanks

[nvdp2002]

Hi Guys amd gals, Any help here would be appreciated.

[DragonflyMaster]

You could create a report from a custom SQL query without any parameter.
Just create the report and define in it the query to extract what you want.

[nvdp2002]

Hi Thanks

The reason for going via the listings template way was that when i ran the sequal query

SELECT opportunities.name, opportunities.amount, opportunities.date_closed
FROM opportunities, accounts_opportunities
WHERE opportunities.deleted = 0
AND opportunities.assigned_user_id='$SUGAR_USER_ID'
AND opportunities.id = accounts_opportunities.opportunity_id

Then the accounts name would be the same as the opportunities name, depending on the order of the query. Like if the accounts name was ABC and the opportunities name was DEF then the report would show DEF DEF or ABC ABC depending on which name got called second.

[DragonflyMaster]

Hi Thanks

The reason for going via the listings template way was that when i ran the sequal query

SELECT opportunities.name, opportunities.amount, opportunities.date_closed
FROM opportunities, accounts_opportunities
WHERE opportunities.deleted = 0
AND opportunities.assigned_user_id='$SUGAR_USER_ID'
AND opportunities.id = accounts_opportunities.opportunity_id

Then the accounts name would be the same as the opportunities name, depending on the order of the query. Like if the accounts name was ABC and the opportunities name was DEF then the report would show DEF DEF or ABC ABC depending on which name got called second.

Try the query in PHPMyAdmin and see if it works correctly there or not.

[nvdp2002]

Hi

I meant to include accounts.name in that query , so its suppose to look like this.


SELECT accounts.name, opportunities.name, opportunities.amount, opportunities.date_closed
FROM opportunities, accounts_opportunities
WHERE opportunities.deleted = 0
AND opportunities.assigned_user_id='$SUGAR_USER_ID'
AND opportunities.id = accounts_opportunities.opportunity_id

phpmyadmin is for MySQL rite? i have mssql running on the server. Ill use the admin tools that come with that and let you know.

[DragonflyMaster]

i have mssql running on the server. Ill use the admin tools that come with that and let you know.

Ok, then open SQL Server Management Studio Express and try there your query.

[nvdp2002]

This is what i get when i enter the query

SELECT accounts.name, opportunities.name, opportunities.amount, opportunities. date_closed
FROM accounts, accounts_opportunities, opportunities
WHERE accounts.id = accounts_opportunities.account_id
AND opportunities.id = accounts_opportunities.opportunity_id
AND opportunities.deleted = 0

in the MS SQL Server Management studio express

SSY Australia PS 85 2008-03-20 00:00:00.000
SSY Australia PS 85 2008-07-02 00:00:00.000
SSY Australia PS 85 2008-05-06 00:00:00.000

But when i enter it into Zucker Reports i get

PS PS 85 20/03/2008 00:00
PS PS 85 02/07/2008 00:00
PS PS 85 06/05/2008 00:00

[DragonflyMaster]

This is what i get when i enter the query

SELECT accounts.name, opportunities.name, opportunities.amount, opportunities. date_closed
FROM accounts, accounts_opportunities, opportunities
WHERE accounts.id = accounts_opportunities.account_id
AND opportunities.id = accounts_opportunities.opportunity_id
AND opportunities.deleted = 0

in the MS SQL Server Management studio express

SSY Australia PS 85 2008-03-20 00:00:00.000
SSY Australia PS 85 2008-07-02 00:00:00.000
SSY Australia PS 85 2008-05-06 00:00:00.000

But when i enter it into Zucker Reports i get

PS PS 85 20/03/2008 00:00
PS PS 85 02/07/2008 00:00
PS PS 85 06/05/2008 00:00

Have you tried with an explicit JOIN query?

[nvdp2002]

when i use the following query

SELECT accounts.id, accounts.name, opportunities.name, opportunities.amount, opportunities. date_closed
FROM accounts
INNER JOIN accounts_opportunities
ON accounts.id = accounts_opportunities.account_id
INNER JOIN opportunities
ON opportunities.id = accounts_opportunities.opportunity_id
WHERE opportunities.deleted = 0

I still get the same result

PS PS 85 20/03/2008 00:00
PS PS 85 02/07/2008 00:00
PS PS 85 06/05/2008 00:00

as apposed to

SSY Australia PS 85 2008-03-20 00:00:00.000
SSY Australia PS 85 2008-07-02 00:00:00.000
SSY Australia PS 85 2008-05-06 00:00:00.000

which is what im supposed to get.