Filter chart

[Guest]

I have a form that serves as a printing and filtering menu. There are
numerous reports and charts that all use different filters. For a chart, I
base the chart data rowsource on a query. (e.g., Select x, y from
qryDataSource). I can edit the underlying query to get the results I want,
but I haven't been able to figure out how to apply the filter in code using

DoCmd.OpenReport .Column(0, varitem), strReportFilter

with .column () being the report name in a list box and strReportFilter
being the WHERE clause.

However, the filter does not limit the records in the underlying query.

Thanks for any suggestions.

Paco

 

[Duane Hookom]

You can "hard-code" parameters from your form controls into your row
sources. Otherwise, you might be able to dynamically build and apply a SQL
statement to the Row Source property of charts in the On Open event of your
report.

 

[Guest]

I'm using some parameters from the form (e.g., from date and to date). These
parameters are applied to the underlying query for the report (not the
chart). I'm dynamically building additional criteria for the report, but I
can't find a way to apply them. I can't apply them to the chart rowsource
because the chart rowsource uses aggregate functions.

 

[Duane Hookom]

Your chart row source is a query. You can apply your parameters in the
query. You can also modify the SQL of the query the row source is based on
prior to opening the report.

 

[Guest]

Here's an example:

My table includes 4 columns:

Date
Shop
Activity
Building

The record source for the report is a query that includes all the columns
with a parameter set on the date column. The chart row source SQL is based on
the report row source query and uses aggregate functions to return the Count
of Activities [Count(ReportRecordSourceQuery.Activities) AS
CountOfActivities] for each Date [GroupBy ReportRecordSourceQuery.Date]. If I
add Shop and/or Building to the chart row source SQL in order to use them in
the WHERE clause, multiple rows will be returned for each Date (e.g.,
activities per shop and/or building per date).

What I need to do is apply the shop and/or building criteria dynamically to
the Report record source query so that it filters out the appropriate rows
before the chart row source SQL generates the data for the chart.

Evidently when I dynamically apply the filter to the report record source
query using the DoCmd.OpenReport method, it does not take effect before the
chart row source SQL runs.

 

[Duane Hookom]

Using a Where condition in DoCmd.OpenReport method will create and apply a
filter property on your report.

You haven't ever stated specfically where the shop and/or building and/or
other filter values are entered by the user.

--
Duane Hookom
MS Access MVP

Here's an example:

My table includes 4 columns:

Date
Shop
Activity
Building

The record source for the report is a query that includes all the columns
with a parameter set on the date column. The chart row source SQL is based
on
the report row source query and uses aggregate functions to return the
Count
of Activities [Count(ReportRecordSourceQuery.Activities) AS
CountOfActivities] for each Date [GroupBy ReportRecordSourceQuery.Date].
If I
add Shop and/or Building to the chart row source SQL in order to use them
in
the WHERE clause, multiple rows will be returned for each Date (e.g.,
activities per shop and/or building per date).

What I need to do is apply the shop and/or building criteria dynamically
to
the Report record source query so that it filters out the appropriate rows
before the chart row source SQL generates the data for the chart.

Evidently when I dynamically apply the filter to the report record source
query using the DoCmd.OpenReport method, it does not take effect before
the
chart row source SQL runs.

Your chart row source is a query. You can apply your parameters in the
query. You can also modify the SQL of the query the row source is based
on
prior to opening the report.

 

[Guest]

True, it does apply the filters to the record source for the report, but the
chart row source, which uses the report record source, is not affected.

The shop and building filters are selected on the report "menu" form. But I
can't just reference the controls on the form because there are too many
possibilities. The form is designed to provide filtering for several hundred
reports and each report can have multiple criteria. That's why I build the
WHERE clause in code.

I understand that the OpenReport method only applies to the report and not
the chart, but I was hoping that since the chart uses the report record
source to build its row source, I could apply a filter ar runtime that would
limit the number of rows that the chart SQL sees.

I think I will have to take a different approach.

Using a Where condition in DoCmd.OpenReport method will create and apply a
filter property on your report.

You haven't ever stated specfically where the shop and/or building and/or
other filter values are entered by the user.

--
Duane Hookom
MS Access MVP

Here's an example:

My table includes 4 columns:

Date
Shop
Activity
Building

The record source for the report is a query that includes all the columns
with a parameter set on the date column. The chart row source SQL is based
on
the report row source query and uses aggregate functions to return the
Count
of Activities [Count(ReportRecordSourceQuery.Activities) AS
CountOfActivities] for each Date [GroupBy ReportRecordSourceQuery.Date].
If I
add Shop and/or Building to the chart row source SQL in order to use them
in
the WHERE clause, multiple rows will be returned for each Date (e.g.,
activities per shop and/or building per date).

What I need to do is apply the shop and/or building criteria dynamically
to
the Report record source query so that it filters out the appropriate rows
before the chart row source SQL generates the data for the chart.

Evidently when I dynamically apply the filter to the report record source
query using the DoCmd.OpenReport method, it does not take effect before
the
chart row source SQL runs.

Your chart row source is a query. You can apply your parameters in the
query. You can also modify the SQL of the query the row source is based
on
prior to opening the report.

--
Duane Hookom
MS Access MVP

I'm using some parameters from the form (e.g., from date and to date).
These
parameters are applied to the underlying query for the report (not the
chart). I'm dynamically building additional criteria for the report,
but I
can't find a way to apply them. I can't apply them to the chart
rowsource
because the chart rowsource uses aggregate functions.

:

You can "hard-code" parameters from your form controls into your row
sources. Otherwise, you might be able to dynamically build and apply a
SQL
statement to the Row Source property of charts in the On Open event of
your
report.

--
Duane Hookom
MS Access MVP

I have a form that serves as a printing and filtering menu. There are
numerous reports and charts that all use different filters. For a
chart, I
base the chart data rowsource on a query. (e.g., Select x, y from
qryDataSource). I can edit the underlying query to get the results I
want,
but I haven't been able to figure out how to apply the filter in
code
using

DoCmd.OpenReport .Column(0, varitem), strReportFilter

with .column () being the report name in a list box and
strReportFilter
being the WHERE clause.

However, the filter does not limit the records in the underlying
query.

Thanks for any suggestions.

Paco

 

[Duane Hookom]

I would use a saved query as the Row Source of the chart(s). Then, as you
build the where condition for your report, you can build a similar where
clause for your chart's query. Then use DAO code to replace the SQL property
of the saved query with your new SQL statement which uses your where
condition.

--
Duane Hookom
MS Access MVP

True, it does apply the filters to the record source for the report, but
the
chart row source, which uses the report record source, is not affected.

The shop and building filters are selected on the report "menu" form. But
I
can't just reference the controls on the form because there are too many
possibilities. The form is designed to provide filtering for several
hundred
reports and each report can have multiple criteria. That's why I build the
WHERE clause in code.

I understand that the OpenReport method only applies to the report and not
the chart, but I was hoping that since the chart uses the report record
source to build its row source, I could apply a filter ar runtime that
would
limit the number of rows that the chart SQL sees.

I think I will have to take a different approach.

Using a Where condition in DoCmd.OpenReport method will create and apply
a
filter property on your report.

You haven't ever stated specfically where the shop and/or building and/or
other filter values are entered by the user.

--
Duane Hookom
MS Access MVP

Here's an example:

My table includes 4 columns:

Date
Shop
Activity
Building

The record source for the report is a query that includes all the
columns
with a parameter set on the date column. The chart row source SQL is
based
on
the report row source query and uses aggregate functions to return the
Count
of Activities [Count(ReportRecordSourceQuery.Activities) AS
CountOfActivities] for each Date [GroupBy
ReportRecordSourceQuery.Date].
If I
add Shop and/or Building to the chart row source SQL in order to use
them
in
the WHERE clause, multiple rows will be returned for each Date (e.g.,
activities per shop and/or building per date).

What I need to do is apply the shop and/or building criteria
dynamically
to
the Report record source query so that it filters out the appropriate
rows
before the chart row source SQL generates the data for the chart.

Evidently when I dynamically apply the filter to the report record
source
query using the DoCmd.OpenReport method, it does not take effect before
the
chart row source SQL runs.

:

Your chart row source is a query. You can apply your parameters in the
query. You can also modify the SQL of the query the row source is
based
on
prior to opening the report.

--
Duane Hookom
MS Access MVP

I'm using some parameters from the form (e.g., from date and to
date).
These
parameters are applied to the underlying query for the report (not
the
chart). I'm dynamically building additional criteria for the report,
but I
can't find a way to apply them. I can't apply them to the chart
rowsource
because the chart rowsource uses aggregate functions.

:

You can "hard-code" parameters from your form controls into your
row
sources. Otherwise, you might be able to dynamically build and
apply a
SQL
statement to the Row Source property of charts in the On Open event
of
your
report.

--
Duane Hookom
MS Access MVP

I have a form that serves as a printing and filtering menu. There
are
numerous reports and charts that all use different filters. For a
chart, I
base the chart data rowsource on a query. (e.g., Select x, y from
qryDataSource). I can edit the underlying query to get the
results I
want,
but I haven't been able to figure out how to apply the filter in
code
using

DoCmd.OpenReport .Column(0, varitem), strReportFilter

with .column () being the report name in a list box and
strReportFilter
being the WHERE clause.

However, the filter does not limit the records in the underlying
query.

Thanks for any suggestions.

Paco