Crystal Reports connection to a Bugzilla MySQL database - problem with links.

I'm trying to set up Crystal Reports with an ODBC connection to Bugzilla which I know works.

It is crashing if I add more than one table - either if I click on Links or OK.

Do I need to give the user any extra priveledges to see links between tables. Are there links between tables in the MySQL database in the same way you would see in a SQL Server database?

I've done searches such as:
bugzilla odbc links
and
mysql odbc links

but I didn't find anything...

Thanks for your help,

JamesNobody got any idea what I might be doing wrong? My MySQL server is on a Linux Fedora Core 5 machine if that helps...|||It could be smart linking.
Try 'File' menu, 'Options', 'Database' tab, uncheck 'Automatic Smart Linking' at the bottom of the 'Advanced Options' section.|||It could be smart linking.
Try 'File' menu, 'Options', 'Database' tab, uncheck 'Automatic Smart Linking' at the bottom of the 'Advanced Options' section.
I don't have an 'Automatic Smart Linking' option, I'm using Crystal Reports 9.2.0.448

Crump Links

I would like to add crump links feature to my drill down reports. Anybody has
an lead on this ?
Thanks for your time in advance.What are crump links?
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Rajan" <Rajan@.discussions.microsoft.com> wrote in message
news:A5B7EC27-2C15-4589-AA2F-E13CEC1D3BC4@.microsoft.com...
>I would like to add crump links feature to my drill down reports. Anybody
>has
> an lead on this ?
> Thanks for your time in advance.|||i have a drill down report which is going to five level. I would like to give
a link how the drill down came all the way, so that by clicking the link user
can straightaway go to the drill down level say from 5 directly to level 3.
Normally we can see this type of links in sites. somethink like the following
one
MSDN Home > MSDN Library > Development Tools and Languages > Visual
Studio .NET > Visual Basic and Visual C# > Reference > Visual Basic
Language > Visual Basic Language and Run-Time Reference > Functions > S-Z
Functions
Hope i had made u understand. If not please give me a buzz.
"Jeff A. Stucker" wrote:
> What are crump links?
> --
> Cheers,
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> "Rajan" <Rajan@.discussions.microsoft.com> wrote in message
> news:A5B7EC27-2C15-4589-AA2F-E13CEC1D3BC4@.microsoft.com...
> >I would like to add crump links feature to my drill down reports. Anybody
> >has
> > an lead on this ?
> >
> > Thanks for your time in advance.
>
>

Crosstab problem help

Hi,
I have tried with the solutions available in the links provided by Pootle,but still I think I have to go a long way.My main problem is with the second aggregate column of previous year.
Let me put it again...

I have a table policy :

CREATE TABLE Policy(
Policyno VARCHAR(20)
,Inceptiondate DATETIME
,agentid VARCHAR(20))

I want a report like this (dates in DD/MM/YYYY)

date 32001 32002 32003 32004 2006 2005
01/08/2006 3 1 11 1 16 12
02/08/2006 1 1 1 2 5 22
03/08/2006 1 1 1 1 4 3

Now the problem is I can get a report using crosstab like this in crystal report

| agentid
------
date | sum(policyno)
------

which is giving a result like this

date 32001 32002 32003 32004 2006/ToTal
01/08/2006 3 1 11 1 16
02/08/2006 1 1 1 2 5
03/08/2006 1 1 1 1 4
Total 4 3 13 4 25

But I can't bring the previous year aggregate column ,i.e 2005 in the above case.

So can you suggest me a way or direction to acheive that?

Any help would be really appreciated.
BTW,Pootle gave these links in my previous post...
link1 (http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21820764.html#16500817)
Link2 (http://www.sqlteam.com/item.asp?ItemID=2955)I'd pre-process the pig into a scratch table, then crosstab that, something like:-- ptp 20060920 Sample for crosstab using prior year data

DROP TABLE s1
DROP TABLE s2
GO

CREATE TABLE s1 (
agent_id INT
, asof DATETIME
, policy INT IDENTITY (1001, 1)
)

INSERT INTO s1 (
agent_id, asof
) SELECT 32001, '2005-08-01'
FROM (SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS m
WHERE m.n < 10 * Rand()
UNION ALL SELECT 32002, '2005-08-01'
FROM (SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS m
WHERE m.n < 10 * Rand()
UNION ALL SELECT 32003, '2005-08-01'
FROM (SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS m
WHERE m.n < 10 * Rand()
UNION ALL SELECT 32001, '2005-08-02'
FROM (SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS m
WHERE m.n < 10 * Rand()
UNION ALL SELECT 32002, '2005-08-02'
FROM (SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS m
WHERE m.n < 10 * Rand()
UNION ALL SELECT 32003, '2005-08-02'
FROM (SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS m
WHERE m.n < 10 * Rand()
UNION ALL SELECT 32001, '2005-08-03'
FROM (SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS m
WHERE m.n < 10 * Rand()
UNION ALL SELECT 32002, '2005-08-03'
FROM (SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS m
WHERE m.n < 10 * Rand()
UNION ALL SELECT 32003, '2005-08-03'
FROM (SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS m
WHERE m.n < 10 * Rand()
UNION ALL SELECT 32001, '2006-08-01'
FROM (SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS m
WHERE m.n < 10 * Rand()
UNION ALL SELECT 32002, '2006-08-01'
FROM (SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS m
WHERE m.n < 10 * Rand()
UNION ALL SELECT 32003, '2006-08-01'
FROM (SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS m
WHERE m.n < 10 * Rand()
UNION ALL SELECT 32001, '2006-08-02'
FROM (SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS m
WHERE m.n < 10 * Rand()
UNION ALL SELECT 32002, '2006-08-02'
FROM (SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS m
WHERE m.n < 10 * Rand()
UNION ALL SELECT 32003, '2006-08-02'
FROM (SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS m
WHERE m.n < 10 * Rand()
UNION ALL SELECT 32001, '2006-08-03'
FROM (SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS m
WHERE m.n < 10 * Rand()
UNION ALL SELECT 32002, '2006-08-03'
FROM (SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS m
WHERE m.n < 10 * Rand()
UNION ALL SELECT 32003, '2006-08-03'
FROM (SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS m
WHERE m.n < 10 * Rand()

CREATE TABLE s2 (
agent_id INT
, asof DATETIME
, count_this INT
, count_prior INT
)

INSERT INTO s2 (
agent_id, asof, count_this, count_prior
) SELECT agent_id, asof, Count(*)
, (SELECT Count(*)
FROM s1 AS z
WHERE z.agent_id = a.agent_id
AND z.asof = DateAdd(year, -1, a.asof))
FROM s1 AS a
GROUP BY a.agent_id, a.asof

SELECT *
FROM s2
ORDER BY agent_id, asof-patP|||Note that the bulk of the code that I posted creates the test data, and that the test data is different almost every time the snippet is run... The real "solution" is actually the next to the last INSERT / SELECT statement that "pre-digests" the raw data into a summarized form that is easy to report.

An interesting side note is that this solution is tailored for the simplest case, as simple 1d to 2d crosstab, with a simple variable... The results are directly comparable for agent to themselves, year over year. They are NOT comparable for the agency year over year, and they may or may not be comparable agent to agent. Other crosstabs are possible, but they get wickedly difficult to manage in reporting tools, especially Crystal Reports.

-PatP|||INSERT INTO s2 (
agent_id, asof, count_this, count_prior
) SELECT agent_id, asof, Count(*)
, (SELECT Count(*)
FROM s1 AS z
WHERE z.agent_id = a.agent_id
AND z.asof = DateAdd(year, -1, a.asof))
FROM s1 AS a
GROUP BY a.agent_id, a.asof
--PatP

So short and concise, I can't believe it...:shocked:
Pat you are great and I am grateful..:rolleyes: