Link chart to report

[Mishanya]

I've created bar-chart showing payments in different currencies (variable
quantity) grouped by year and destined to be filtered by ClientID value. The
query-code under the chart is:

TRANSFORM Sum([qryPayments].PaymentValue) AS SumOfPaymentValue
SELECT (Format([PaymentDate],"YYYY")) AS Year
FROM [qryPayments]
GROUP BY (Format([PaymentDate],"YYYY")), [qryPayments].ClientID
PIVOT [qryPayments].CurrencySymbol;

Putting ClientID number as Criteria directly in the query-grid for a test
works OK.

Now I want to nest the chart into MyReport and link it by ClientID (MyReport
source is tblClientDetails, has ClientID control and is supposed to show data
only for one chosen client).

The initial nesting works OK - I get an aggregate chart for all the clients
in MyReport.

When I put [Reports]![MyReport]![ClientID] as criteria in the charts
query-grid and try to switch to the Preview mode, I get error msg: "The
Microsoft Jet database engine does not recognize
[Reports]![MyReport]![ClientID] as a valid field name or expression".

Alternatively, linking the chart to the report by chart' Master/Child
linking Property does let to switch to the Preview mode once, but when trying
to reopen the saved and closed report, I get error msg: "Cannot use the
crosstab of a non-fixed column as a subquery" (although hosting the same
chart only without linking to the ClientID control was not a problem).

How can I solve this?

 

[Duane Hookom]

With any dynamic criteria in a crosstab, you should and often are required to
enter the data types of the criterian in the Query->Parameters dialog.

 

[Mishanya]

Hi Duane!
I'm glad to come in touch with You - I've learned a lot from Your posts in
the forum - thank You very much indeed.
As to the case, I'm still a rookie, so I don't quite understand what You mean?
If You can just lead me - why the chart based ob crosstab query works in
general, but don't let to link it to the report field for filtering?
Happy Thanksgiving!

With any dynamic criteria in a crosstab, you should and often are required to
enter the data types of the criterian in the Query->Parameters dialog.
--
Duane Hookom
Microsoft Access MVP

I've created bar-chart showing payments in different currencies (variable
quantity) grouped by year and destined to be filtered by ClientID value. The
query-code under the chart is:

TRANSFORM Sum([qryPayments].PaymentValue) AS SumOfPaymentValue
SELECT (Format([PaymentDate],"YYYY")) AS Year
FROM [qryPayments]
GROUP BY (Format([PaymentDate],"YYYY")), [qryPayments].ClientID
PIVOT [qryPayments].CurrencySymbol;

Putting ClientID number as Criteria directly in the query-grid for a test
works OK.

Now I want to nest the chart into MyReport and link it by ClientID (MyReport
source is tblClientDetails, has ClientID control and is supposed to show data
only for one chosen client).

The initial nesting works OK - I get an aggregate chart for all the clients
in MyReport.

When I put [Reports]![MyReport]![ClientID] as criteria in the charts
query-grid and try to switch to the Preview mode, I get error msg: "The
Microsoft Jet database engine does not recognize
[Reports]![MyReport]![ClientID] as a valid field name or expression".

Alternatively, linking the chart to the report by chart' Master/Child
linking Property does let to switch to the Preview mode once, but when trying
to reopen the saved and closed report, I get error msg: "Cannot use the
crosstab of a non-fixed column as a subquery" (although hosting the same
chart only without linking to the ClientID control was not a problem).

How can I solve this?

 

[Duane Hookom]

While in the design view of the crosstab query, menu select Query->Parameters
and enter:
[Reports]![MyReport]![ClientID] Long
This assumes ClientID is long numeric.

--
Duane Hookom
Microsoft Access MVP

Hi Duane!
I'm glad to come in touch with You - I've learned a lot from Your posts in
the forum - thank You very much indeed.
As to the case, I'm still a rookie, so I don't quite understand what You mean?
If You can just lead me - why the chart based ob crosstab query works in
general, but don't let to link it to the report field for filtering?
Happy Thanksgiving!

With any dynamic criteria in a crosstab, you should and often are required to
enter the data types of the criterian in the Query->Parameters dialog.
--
Duane Hookom
Microsoft Access MVP

I've created bar-chart showing payments in different currencies (variable
quantity) grouped by year and destined to be filtered by ClientID value. The
query-code under the chart is:

TRANSFORM Sum([qryPayments].PaymentValue) AS SumOfPaymentValue
SELECT (Format([PaymentDate],"YYYY")) AS Year
FROM [qryPayments]
GROUP BY (Format([PaymentDate],"YYYY")), [qryPayments].ClientID
PIVOT [qryPayments].CurrencySymbol;

Putting ClientID number as Criteria directly in the query-grid for a test
works OK.

Now I want to nest the chart into MyReport and link it by ClientID (MyReport
source is tblClientDetails, has ClientID control and is supposed to show data
only for one chosen client).

The initial nesting works OK - I get an aggregate chart for all the clients
in MyReport.

When I put [Reports]![MyReport]![ClientID] as criteria in the charts
query-grid and try to switch to the Preview mode, I get error msg: "The
Microsoft Jet database engine does not recognize
[Reports]![MyReport]![ClientID] as a valid field name or expression".

Alternatively, linking the chart to the report by chart' Master/Child
linking Property does let to switch to the Preview mode once, but when trying
to reopen the saved and closed report, I get error msg: "Cannot use the
crosstab of a non-fixed column as a subquery" (although hosting the same
chart only without linking to the ClientID control was not a problem).

How can I solve this?

 

[Mishanya]

Great!
Thank You very much.

While in the design view of the crosstab query, menu select Query->Parameters
and enter:
[Reports]![MyReport]![ClientID] Long
This assumes ClientID is long numeric.

--
Duane Hookom
Microsoft Access MVP

Hi Duane!
I'm glad to come in touch with You - I've learned a lot from Your posts in
the forum - thank You very much indeed.
As to the case, I'm still a rookie, so I don't quite understand what You mean?
If You can just lead me - why the chart based ob crosstab query works in
general, but don't let to link it to the report field for filtering?
Happy Thanksgiving!

With any dynamic criteria in a crosstab, you should and often are required to
enter the data types of the criterian in the Query->Parameters dialog.
--
Duane Hookom
Microsoft Access MVP


:

I've created bar-chart showing payments in different currencies (variable
quantity) grouped by year and destined to be filtered by ClientID value. The
query-code under the chart is:

TRANSFORM Sum([qryPayments].PaymentValue) AS SumOfPaymentValue
SELECT (Format([PaymentDate],"YYYY")) AS Year
FROM [qryPayments]
GROUP BY (Format([PaymentDate],"YYYY")), [qryPayments].ClientID
PIVOT [qryPayments].CurrencySymbol;

Putting ClientID number as Criteria directly in the query-grid for a test
works OK.

Now I want to nest the chart into MyReport and link it by ClientID (MyReport
source is tblClientDetails, has ClientID control and is supposed to show data
only for one chosen client).

The initial nesting works OK - I get an aggregate chart for all the clients
in MyReport.

When I put [Reports]![MyReport]![ClientID] as criteria in the charts
query-grid and try to switch to the Preview mode, I get error msg: "The
Microsoft Jet database engine does not recognize
[Reports]![MyReport]![ClientID] as a valid field name or expression".

Alternatively, linking the chart to the report by chart' Master/Child
linking Property does let to switch to the Preview mode once, but when trying
to reopen the saved and closed report, I get error msg: "Cannot use the
crosstab of a non-fixed column as a subquery" (although hosting the same
chart only without linking to the ClientID control was not a problem).

How can I solve this?