Chart not working in report - Help requested

  • Thread starter Thread starter Tokyo Alex
  • Start date Start date
T

Tokyo Alex

Dear all,

I am trying to prepare a report with a pie chart. I want to (among other
things) show what proportion of quotes are flagged as "Premier" priority,
grouped per week.

Here are the details:
Report -
RecordSource: saved query (qdfQuoteOrderConversion)
Grouping: Week

Graph -
Section: Week Footer (which also contains a textbox bound to Week)
RowSource: SELECT [IsPrem],Count([IsPrem]) AS [CountOfIsPrem] FROM
[qdfQuoteOrderConversion] GROUP BY [IsPrem];

Link Master Fields: Week
Link Child Fields: Week
OLE Class: Microsoft Graph Chart
Class: MSGraph.Chart.8

Errors:
If I go to Report View, the report runs but the graphs display sample data
only, my data is not loaded from the RowSource.
If I go to Print Preview I get the following errors:
"This expression is typed incorrectly, or it is too complex to be evaluated.
For example, a numeric expression may contain too many complicated elements.
Try simplifying the expression by assigning parts of the expression to
variables."
"An error occurred while sending data to the OLE Server..."

If I click on the elipsis in the Link Master/Child Fields properties:
"Can't build a link between unbound forms."

If I remove the Links, I get identical graphs showing the report (not
weekly) totals in both Report View, and print preview. (This, at least, is
expected behaviour).

I have looked at Duane Hookom's "Report with chart using 2 link fields"
(http://www.access.hookom.net/Samples.htm) and it works on my machine.

System:
Windows XP Pro SP3
Access 2007 SP2
Microsoft Graph (12. ...) SP2

I am at my wits' end, so any ideas I could try would be very much appreciated.

Thanks,
Alex.
 
I don't see anything wrong with a quick glance. Can you provide the SQL from
qdfQuoteOrderConversion?
 
Hi Duane,

Thanks for the response.

Here goes.
Query - qdfQuoteOrderConversion:
SELECT Int(Val(Right([tblQuoteData]![qdDate],2))/7)+1 AS Week,
[tblQuoteData].[qdVendorCode], [tblOrderData].[odOrderNum],
Sum(CLng(Mid([tblQuoteData]![qdNotes],InStr(InStr(1,[tblQuoteData]![qdNotes],"=")+1,[tblQuoteData]![qdNotes],"=")+1,InStr(1,[tblQuoteData]![qdNotes],"Y")-InStr(InStr(1,[tblQuoteData]![qdNotes],"=")+1,[tblQuoteData]![qdNotes],"=")-1)))
AS QuoteValue, Sum([tblOrderData].[odPriceExTax]) AS OrderValue,
First(IIf(Left([tblQuoteData]![qdVendorCode],1)="1","Premier","NotPrem")) AS
IsPrem
FROM [tblQuoteData] LEFT JOIN [tblOrderData] ON
[tblQuoteData].[qdVendorCode] = [tblOrderData]. [odVendorCode]
WHERE (((Left([tblQuoteData]![qdDate],6))="201001") AND
(([tblQuoteData].[qdProdName]) Not Like "SHIP*"))
GROUP BY Int(Val(Right([tblQuoteData]![qdDate],2))/7)+1, [tblQuoteData].[
qdVendorCode], [tblOrderData].[odOrderNum];

Doe this offer any insight?

Cheers,
Alex.


Duane Hookom said:
I don't see anything wrong with a quick glance. Can you provide the SQL from
qdfQuoteOrderConversion?

--
Duane Hookom
Microsoft Access MVP


Tokyo Alex said:
Dear all,

I am trying to prepare a report with a pie chart. I want to (among other
things) show what proportion of quotes are flagged as "Premier" priority,
grouped per week.

Here are the details:
Report -
RecordSource: saved query (qdfQuoteOrderConversion)
Grouping: Week

Graph -
Section: Week Footer (which also contains a textbox bound to Week)
RowSource: SELECT [IsPrem],Count([IsPrem]) AS [CountOfIsPrem] FROM
[qdfQuoteOrderConversion] GROUP BY [IsPrem];

Link Master Fields: Week
Link Child Fields: Week
OLE Class: Microsoft Graph Chart
Class: MSGraph.Chart.8

Errors:
If I go to Report View, the report runs but the graphs display sample data
only, my data is not loaded from the RowSource.
If I go to Print Preview I get the following errors:
"This expression is typed incorrectly, or it is too complex to be evaluated.
For example, a numeric expression may contain too many complicated elements.
Try simplifying the expression by assigning parts of the expression to
variables."
"An error occurred while sending data to the OLE Server..."

If I click on the elipsis in the Link Master/Child Fields properties:
"Can't build a link between unbound forms."

If I remove the Links, I get identical graphs showing the report (not
weekly) totals in both Report View, and print preview. (This, at least, is
expected behaviour).

I have looked at Duane Hookom's "Report with chart using 2 link fields"
(http://www.access.hookom.net/Samples.htm) and it works on my machine.

System:
Windows XP Pro SP3
Access 2007 SP2
Microsoft Graph (12. ...) SP2

I am at my wits' end, so any ideas I could try would be very much appreciated.

Thanks,
Alex.
Click to expand...
Click to expand...
 
I would replace that big Sum(Clng(......)) as QuoteValue with a user-defined
function that returns a numeric.
Also, if qdDate is a text value then I would probably use a real date.

--
Duane Hookom
MS Access MVP

Tokyo Alex said:
Hi Duane,

Thanks for the response.

Here goes.
Query - qdfQuoteOrderConversion:
SELECT Int(Val(Right([tblQuoteData]![qdDate],2))/7)+1 AS Week,
[tblQuoteData].[qdVendorCode], [tblOrderData].[odOrderNum],
Sum(CLng(Mid([tblQuoteData]![qdNotes],InStr(InStr(1,[tblQuoteData]![qdNotes],"=")+1,[tblQuoteData]![qdNotes],"=")+1,InStr(1,[tblQuoteData]![qdNotes],"Y")-InStr(InStr(1,[tblQuoteData]![qdNotes],"=")+1,[tblQuoteData]![qdNotes],"=")-1)))
AS QuoteValue, Sum([tblOrderData].[odPriceExTax]) AS OrderValue,
First(IIf(Left([tblQuoteData]![qdVendorCode],1)="1","Premier","NotPrem"))
AS
IsPrem
FROM [tblQuoteData] LEFT JOIN [tblOrderData] ON
[tblQuoteData].[qdVendorCode] = [tblOrderData]. [odVendorCode]
WHERE (((Left([tblQuoteData]![qdDate],6))="201001") AND
(([tblQuoteData].[qdProdName]) Not Like "SHIP*"))
GROUP BY Int(Val(Right([tblQuoteData]![qdDate],2))/7)+1, [tblQuoteData].[
qdVendorCode], [tblOrderData].[odOrderNum];

Doe this offer any insight?

Cheers,
Alex.


Duane Hookom said:
I don't see anything wrong with a quick glance. Can you provide the SQL
from
qdfQuoteOrderConversion?

--
Duane Hookom
Microsoft Access MVP


Tokyo Alex said:
Dear all,

I am trying to prepare a report with a pie chart. I want to (among
other
things) show what proportion of quotes are flagged as "Premier"
priority,
grouped per week.

Here are the details:
Report -
RecordSource: saved query (qdfQuoteOrderConversion)
Grouping: Week

Graph -
Section: Week Footer (which also contains a textbox bound to Week)
RowSource: SELECT [IsPrem],Count([IsPrem]) AS [CountOfIsPrem] FROM
[qdfQuoteOrderConversion] GROUP BY [IsPrem];

Link Master Fields: Week
Link Child Fields: Week
OLE Class: Microsoft Graph Chart
Class: MSGraph.Chart.8

Errors:
If I go to Report View, the report runs but the graphs display sample
data
only, my data is not loaded from the RowSource.
If I go to Print Preview I get the following errors:
"This expression is typed incorrectly, or it is too complex to be
evaluated.
For example, a numeric expression may contain too many complicated
elements.
Try simplifying the expression by assigning parts of the expression to
variables."
"An error occurred while sending data to the OLE Server..."

If I click on the elipsis in the Link Master/Child Fields properties:
"Can't build a link between unbound forms."

If I remove the Links, I get identical graphs showing the report (not
weekly) totals in both Report View, and print preview. (This, at least,
is
expected behaviour).

I have looked at Duane Hookom's "Report with chart using 2 link fields"
(http://www.access.hookom.net/Samples.htm) and it works on my machine.

System:
Windows XP Pro SP3
Access 2007 SP2
Microsoft Graph (12. ...) SP2

I am at my wits' end, so any ideas I could try would be very much
appreciated.

Thanks,
Alex.
Click to expand...
Click to expand...
Click to expand...
 
Thanks for the suggestions.

I moved the CLng(...) into a UDF; no difference.
Unfortunately, the qdDate being a text value is something I have no control
over. I guess I could CDate it before the calculations in this query, but
adding another layer of complexity doesn't seem to be the best idea here.

To test, I changed the query into a maketable and pointed the report and
chart at the new table for recordsource and rowsource.

Now, when I go to Report View it puts itself into a seemingly endless loop
and just sits there. In Print Preview the report runs instantly but the
charts are blank.

I'm guessing I might have to put the chart into a subreport and link the
subreport. Hmm, there's a project for tomorrow.

I get the feeling this would be so much easier if you could just tell the
chart to follow the report recordsource.

Is there anything else you can think of that may help?

Once again, thanks very much for the input.

Cheers,
Alex.


Duane Hookom said:
I would replace that big Sum(Clng(......)) as QuoteValue with a user-defined
function that returns a numeric.
Also, if qdDate is a text value then I would probably use a real date.

--
Duane Hookom
MS Access MVP

Tokyo Alex said:
Hi Duane,

Thanks for the response.

Here goes.
Query - qdfQuoteOrderConversion:
SELECT Int(Val(Right([tblQuoteData]![qdDate],2))/7)+1 AS Week,
[tblQuoteData].[qdVendorCode], [tblOrderData].[odOrderNum],
Sum(CLng(Mid([tblQuoteData]![qdNotes],InStr(InStr(1,[tblQuoteData]![qdNotes],"=")+1,[tblQuoteData]![qdNotes],"=")+1,InStr(1,[tblQuoteData]![qdNotes],"Y")-InStr(InStr(1,[tblQuoteData]![qdNotes],"=")+1,[tblQuoteData]![qdNotes],"=")-1)))
AS QuoteValue, Sum([tblOrderData].[odPriceExTax]) AS OrderValue,
First(IIf(Left([tblQuoteData]![qdVendorCode],1)="1","Premier","NotPrem"))
AS
IsPrem
FROM [tblQuoteData] LEFT JOIN [tblOrderData] ON
[tblQuoteData].[qdVendorCode] = [tblOrderData]. [odVendorCode]
WHERE (((Left([tblQuoteData]![qdDate],6))="201001") AND
(([tblQuoteData].[qdProdName]) Not Like "SHIP*"))
GROUP BY Int(Val(Right([tblQuoteData]![qdDate],2))/7)+1, [tblQuoteData].[
qdVendorCode], [tblOrderData].[odOrderNum];

Doe this offer any insight?

Cheers,
Alex.


Duane Hookom said:
I don't see anything wrong with a quick glance. Can you provide the SQL
from
qdfQuoteOrderConversion?

--
Duane Hookom
Microsoft Access MVP


:

Dear all,

I am trying to prepare a report with a pie chart. I want to (among
other
things) show what proportion of quotes are flagged as "Premier"
priority,
grouped per week.

Here are the details:
Report -
RecordSource: saved query (qdfQuoteOrderConversion)
Grouping: Week

Graph -
Section: Week Footer (which also contains a textbox bound to Week)
RowSource: SELECT [IsPrem],Count([IsPrem]) AS [CountOfIsPrem] FROM
[qdfQuoteOrderConversion] GROUP BY [IsPrem];

Link Master Fields: Week
Link Child Fields: Week
OLE Class: Microsoft Graph Chart
Class: MSGraph.Chart.8

Errors:
If I go to Report View, the report runs but the graphs display sample
data
only, my data is not loaded from the RowSource.
If I go to Print Preview I get the following errors:
"This expression is typed incorrectly, or it is too complex to be
evaluated.
For example, a numeric expression may contain too many complicated
elements.
Try simplifying the expression by assigning parts of the expression to
variables."
"An error occurred while sending data to the OLE Server..."

If I click on the elipsis in the Link Master/Child Fields properties:
"Can't build a link between unbound forms."

If I remove the Links, I get identical graphs showing the report (not
weekly) totals in both Report View, and print preview. (This, at least,
is
expected behaviour).

I have looked at Duane Hookom's "Report with chart using 2 link fields"
(http://www.access.hookom.net/Samples.htm) and it works on my machine.

System:
Windows XP Pro SP3
Access 2007 SP2
Microsoft Graph (12. ...) SP2

I am at my wits' end, so any ideas I could try would be very much
appreciated.

Thanks,
Alex.
Click to expand...
Click to expand...
Click to expand...
 
I would look at the raw data feeding these in datasheet view to make sure the
link columns are both left or both right aligned.

I would also limit the results to a smaller group of records to see if there
might be a value in a record that is causing this issue.

--
Duane Hookom
Microsoft Access MVP


Tokyo Alex said:
Thanks for the suggestions.

I moved the CLng(...) into a UDF; no difference.
Unfortunately, the qdDate being a text value is something I have no control
over. I guess I could CDate it before the calculations in this query, but
adding another layer of complexity doesn't seem to be the best idea here.

To test, I changed the query into a maketable and pointed the report and
chart at the new table for recordsource and rowsource.

Now, when I go to Report View it puts itself into a seemingly endless loop
and just sits there. In Print Preview the report runs instantly but the
charts are blank.

I'm guessing I might have to put the chart into a subreport and link the
subreport. Hmm, there's a project for tomorrow.

I get the feeling this would be so much easier if you could just tell the
chart to follow the report recordsource.

Is there anything else you can think of that may help?

Once again, thanks very much for the input.

Cheers,
Alex.


Duane Hookom said:
I would replace that big Sum(Clng(......)) as QuoteValue with a user-defined
function that returns a numeric.
Also, if qdDate is a text value then I would probably use a real date.

--
Duane Hookom
MS Access MVP

Tokyo Alex said:
Hi Duane,

Thanks for the response.

Here goes.
Query - qdfQuoteOrderConversion:
SELECT Int(Val(Right([tblQuoteData]![qdDate],2))/7)+1 AS Week,
[tblQuoteData].[qdVendorCode], [tblOrderData].[odOrderNum],
Sum(CLng(Mid([tblQuoteData]![qdNotes],InStr(InStr(1,[tblQuoteData]![qdNotes],"=")+1,[tblQuoteData]![qdNotes],"=")+1,InStr(1,[tblQuoteData]![qdNotes],"Y")-InStr(InStr(1,[tblQuoteData]![qdNotes],"=")+1,[tblQuoteData]![qdNotes],"=")-1)))
AS QuoteValue, Sum([tblOrderData].[odPriceExTax]) AS OrderValue,
First(IIf(Left([tblQuoteData]![qdVendorCode],1)="1","Premier","NotPrem"))
AS
IsPrem
FROM [tblQuoteData] LEFT JOIN [tblOrderData] ON
[tblQuoteData].[qdVendorCode] = [tblOrderData]. [odVendorCode]
WHERE (((Left([tblQuoteData]![qdDate],6))="201001") AND
(([tblQuoteData].[qdProdName]) Not Like "SHIP*"))
GROUP BY Int(Val(Right([tblQuoteData]![qdDate],2))/7)+1, [tblQuoteData].[
qdVendorCode], [tblOrderData].[odOrderNum];

Doe this offer any insight?

Cheers,
Alex.


:

I don't see anything wrong with a quick glance. Can you provide the SQL
from
qdfQuoteOrderConversion?

--
Duane Hookom
Microsoft Access MVP


:

Dear all,

I am trying to prepare a report with a pie chart. I want to (among
other
things) show what proportion of quotes are flagged as "Premier"
priority,
grouped per week.

Here are the details:
Report -
RecordSource: saved query (qdfQuoteOrderConversion)
Grouping: Week

Graph -
Section: Week Footer (which also contains a textbox bound to Week)
RowSource: SELECT [IsPrem],Count([IsPrem]) AS [CountOfIsPrem] FROM
[qdfQuoteOrderConversion] GROUP BY [IsPrem];

Link Master Fields: Week
Link Child Fields: Week
OLE Class: Microsoft Graph Chart
Class: MSGraph.Chart.8

Errors:
If I go to Report View, the report runs but the graphs display sample
data
only, my data is not loaded from the RowSource.
If I go to Print Preview I get the following errors:
"This expression is typed incorrectly, or it is too complex to be
evaluated.
For example, a numeric expression may contain too many complicated
elements.
Try simplifying the expression by assigning parts of the expression to
variables."
"An error occurred while sending data to the OLE Server..."

If I click on the elipsis in the Link Master/Child Fields properties:
"Can't build a link between unbound forms."

If I remove the Links, I get identical graphs showing the report (not
weekly) totals in both Report View, and print preview. (This, at least,
is
expected behaviour).

I have looked at Duane Hookom's "Report with chart using 2 link fields"
(http://www.access.hookom.net/Samples.htm) and it works on my machine.

System:
Windows XP Pro SP3
Access 2007 SP2
Microsoft Graph (12. ...) SP2

I am at my wits' end, so any ideas I could try would be very much
appreciated.

Thanks,
Alex.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top