Creating a Chart From a Crosstab Query

[JimB]

I'm trying to create a chart from a crosstab query that also uses the Master
and child link properties. Whenever I run the report, I get the error
message "Cannot use the crosstab of a non-fixed column as a sub-query".

If I run the report without the Master/Child link fields, it will display.

If I leave the links in, and open the report in design view, select the row
source of the chart object, view the underlying query and then do a print
preview the report displays perfectly with the correct chart displaying for
each record.

If, using code, I open the report in design view, and then modify the SQL
statement for the row source property of the chart, and then display the
report in preview mode, it runs fine. I can't get the report to run
correctly by just opening the report, or sending it to the printer.

The SQL statement used as the rowsource property is:

"TRANSFORM Sum(tblCrossVariance.[Total Of Hours]) AS [SumOfTotal Of Hours]
SELECT tblCrossVariance.Category, tblCrossVariance.LogicalGroupOfSitesID FROM
tblCrossVariance GROUP BY tblCrossVariance.Category,
tblCrossVariance.LogicalGroupOfSitesID PIVOT tblCrossVariance.ShortName;"

The Link Master Field is [ID]
The link child field is [LogicalGroupOfSitesID]

Does anyone have any idea what's going on here?

Thanks for you time.

Jim B.

 

[Duane Hookom]

The obvious answer is to change the "non-fixed columns" to "fixed". Can you
enter the possible values of ShortName into the Column Headings property?

 

[JimB]

Thanks for responding. The number of columns can change if additional Sites
(Shortname) are entered into the database. Is the Column Headings property
one that I can change in code based on the Sites (Shortnames) in the Sites
table?

Jim B.

The obvious answer is to change the "non-fixed columns" to "fixed". Can you
enter the possible values of ShortName into the Column Headings property?

--
Duane Hookom
MS Access MVP
--

I'm trying to create a chart from a crosstab query that also uses the
Master
and child link properties. Whenever I run the report, I get the error
message "Cannot use the crosstab of a non-fixed column as a sub-query".

If I run the report without the Master/Child link fields, it will display.

If I leave the links in, and open the report in design view, select the
row
source of the chart object, view the underlying query and then do a print
preview the report displays perfectly with the correct chart displaying
for
each record.

If, using code, I open the report in design view, and then modify the SQL
statement for the row source property of the chart, and then display the
report in preview mode, it runs fine. I can't get the report to run
correctly by just opening the report, or sending it to the printer.

The SQL statement used as the rowsource property is:

"TRANSFORM Sum(tblCrossVariance.[Total Of Hours]) AS [SumOfTotal Of Hours]
SELECT tblCrossVariance.Category, tblCrossVariance.LogicalGroupOfSitesID
FROM
tblCrossVariance GROUP BY tblCrossVariance.Category,
tblCrossVariance.LogicalGroupOfSitesID PIVOT tblCrossVariance.ShortName;"

The Link Master Field is [ID]
The link child field is [LogicalGroupOfSitesID]

Does anyone have any idea what's going on here?

Thanks for you time.

Jim B.

 

[Duane Hookom]

You can use DAO code to change the SQL property of a saved query. There are
other crosstab report solutions at
http://www.invisibleinc.com/divFiles.cfm?divDivID=4. The Crosstab.mdb has a
solution for dynamic columns that might work.

--
Duane Hookom
MS Access MVP
--

Thanks for responding. The number of columns can change if additional
Sites
(Shortname) are entered into the database. Is the Column Headings
property
one that I can change in code based on the Sites (Shortnames) in the Sites
table?

Jim B.

The obvious answer is to change the "non-fixed columns" to "fixed". Can
you
enter the possible values of ShortName into the Column Headings property?

--
Duane Hookom
MS Access MVP
--

I'm trying to create a chart from a crosstab query that also uses the
Master
and child link properties. Whenever I run the report, I get the error
message "Cannot use the crosstab of a non-fixed column as a sub-query".

If I run the report without the Master/Child link fields, it will
display.

If I leave the links in, and open the report in design view, select the
row
source of the chart object, view the underlying query and then do a
print
preview the report displays perfectly with the correct chart displaying
for
each record.

If, using code, I open the report in design view, and then modify the
SQL
statement for the row source property of the chart, and then display
the
report in preview mode, it runs fine. I can't get the report to run
correctly by just opening the report, or sending it to the printer.

The SQL statement used as the rowsource property is:

"TRANSFORM Sum(tblCrossVariance.[Total Of Hours]) AS [SumOfTotal Of
Hours]
SELECT tblCrossVariance.Category,
tblCrossVariance.LogicalGroupOfSitesID
FROM
tblCrossVariance GROUP BY tblCrossVariance.Category,
tblCrossVariance.LogicalGroupOfSitesID PIVOT
tblCrossVariance.ShortName;"

The Link Master Field is [ID]
The link child field is [LogicalGroupOfSitesID]

Does anyone have any idea what's going on here?

Thanks for you time.

Jim B.

 

[JimB]

Thanks for the help. I was able to get what I needed by changing the column
Headings with the In clause of the SQL statement, and then changing the SQL
property of the query as you suggested. This at least let the Link child
and master fields work. The only problem with this was that every site
listed in the column headings was shown for each Logical Group of sites even
if a site wasn't part of the group. I was able to get around this by
deleting the column in the Print Event of the report if the group did not
contain the site:

objGraph.Application.DataSheet.Columns(intX + 2).Delete

While trying to figure out how to get this to work, I was trying to find a
way to access the Series Category Text from the data Series. I finally gave
up because I couldn't find any properties that allowed me to access it
directly. It obviously exists because it is displayed on the chart, yet the
objects, methods and properties for MS Chart don't seem to let me get at it.

Just curious, since I played with this for a long time, is this just an
undocumented property?


Jim B

You can use DAO code to change the SQL property of a saved query. There are
other crosstab report solutions at
http://www.invisibleinc.com/divFiles.cfm?divDivID=4. The Crosstab.mdb has a
solution for dynamic columns that might work.

--
Duane Hookom
MS Access MVP
--

Thanks for responding. The number of columns can change if additional
Sites
(Shortname) are entered into the database. Is the Column Headings
property
one that I can change in code based on the Sites (Shortnames) in the Sites
table?

Jim B.

The obvious answer is to change the "non-fixed columns" to "fixed". Can
you
enter the possible values of ShortName into the Column Headings property?

--
Duane Hookom
MS Access MVP
--

I'm trying to create a chart from a crosstab query that also uses the
Master
and child link properties. Whenever I run the report, I get the error
message "Cannot use the crosstab of a non-fixed column as a sub-query".

If I run the report without the Master/Child link fields, it will
display.

If I leave the links in, and open the report in design view, select the
row
source of the chart object, view the underlying query and then do a
print
preview the report displays perfectly with the correct chart displaying
for
each record.

If, using code, I open the report in design view, and then modify the
SQL
statement for the row source property of the chart, and then display
the
report in preview mode, it runs fine. I can't get the report to run
correctly by just opening the report, or sending it to the printer.

The SQL statement used as the rowsource property is:

"TRANSFORM Sum(tblCrossVariance.[Total Of Hours]) AS [SumOfTotal Of
Hours]
SELECT tblCrossVariance.Category,
tblCrossVariance.LogicalGroupOfSitesID
FROM
tblCrossVariance GROUP BY tblCrossVariance.Category,
tblCrossVariance.LogicalGroupOfSitesID PIVOT
tblCrossVariance.ShortName;"

The Link Master Field is [ID]
The link child field is [LogicalGroupOfSitesID]

Does anyone have any idea what's going on here?

Thanks for you time.

Jim B.

 

[Duane Hookom]

Sorry, I don't have the knowledge or experience to answer your last
question. Have you considered making a line invisible as opposed to
attempting to delete it?

--
Duane Hookom
MS Access MVP
--

Thanks for the help. I was able to get what I needed by changing the
column
Headings with the In clause of the SQL statement, and then changing the
SQL
property of the query as you suggested. This at least let the Link child
and master fields work. The only problem with this was that every site
listed in the column headings was shown for each Logical Group of sites
even
if a site wasn't part of the group. I was able to get around this by
deleting the column in the Print Event of the report if the group did not
contain the site:

objGraph.Application.DataSheet.Columns(intX + 2).Delete

While trying to figure out how to get this to work, I was trying to find a
way to access the Series Category Text from the data Series. I finally
gave
up because I couldn't find any properties that allowed me to access it
directly. It obviously exists because it is displayed on the chart, yet
the
objects, methods and properties for MS Chart don't seem to let me get at
it.

Just curious, since I played with this for a long time, is this just an
undocumented property?


Jim B

You can use DAO code to change the SQL property of a saved query. There
are
other crosstab report solutions at
http://www.invisibleinc.com/divFiles.cfm?divDivID=4. The Crosstab.mdb has
a
solution for dynamic columns that might work.

--
Duane Hookom
MS Access MVP
--

Thanks for responding. The number of columns can change if additional
Sites
(Shortname) are entered into the database. Is the Column Headings
property
one that I can change in code based on the Sites (Shortnames) in the
Sites
table?

Jim B.

:

The obvious answer is to change the "non-fixed columns" to "fixed".
Can
you
enter the possible values of ShortName into the Column Headings
property?

--
Duane Hookom
MS Access MVP
--

I'm trying to create a chart from a crosstab query that also uses
the
Master
and child link properties. Whenever I run the report, I get the
error
message "Cannot use the crosstab of a non-fixed column as a
sub-query".

If I run the report without the Master/Child link fields, it will
display.

If I leave the links in, and open the report in design view, select
the
row
source of the chart object, view the underlying query and then do a
print
preview the report displays perfectly with the correct chart
displaying
for
each record.

If, using code, I open the report in design view, and then modify
the
SQL
statement for the row source property of the chart, and then display
the
report in preview mode, it runs fine. I can't get the report to run
correctly by just opening the report, or sending it to the printer.

The SQL statement used as the rowsource property is:

"TRANSFORM Sum(tblCrossVariance.[Total Of Hours]) AS [SumOfTotal Of
Hours]
SELECT tblCrossVariance.Category,
tblCrossVariance.LogicalGroupOfSitesID
FROM
tblCrossVariance GROUP BY tblCrossVariance.Category,
tblCrossVariance.LogicalGroupOfSitesID PIVOT
tblCrossVariance.ShortName;"

The Link Master Field is [ID]
The link child field is [LogicalGroupOfSitesID]

Does anyone have any idea what's going on here?

Thanks for you time.

Jim B.

 

[JimB]

The only property I can find to access is the column.delete property. (This
is a 3D column graph). There isn't a column.Hidden or a column.Visible
property. I was able to do the same thing by using:

objchart.SeriesCollection(intX).delete

The problem with this was that I couldn't run any kind of test to determine
which series I should delete or which I should keep (That's why I needed to
access the Series Category Text property).

Deleting the column on the fly seems to work fine, since I refresh the data
in the object each time and the graphs are displaying correctly.

Jim B

Sorry, I don't have the knowledge or experience to answer your last
question. Have you considered making a line invisible as opposed to
attempting to delete it?

--
Duane Hookom
MS Access MVP
--

Thanks for the help. I was able to get what I needed by changing the
column
Headings with the In clause of the SQL statement, and then changing the
SQL
property of the query as you suggested. This at least let the Link child
and master fields work. The only problem with this was that every site
listed in the column headings was shown for each Logical Group of sites
even
if a site wasn't part of the group. I was able to get around this by
deleting the column in the Print Event of the report if the group did not
contain the site:

objGraph.Application.DataSheet.Columns(intX + 2).Delete

While trying to figure out how to get this to work, I was trying to find a
way to access the Series Category Text from the data Series. I finally
gave
up because I couldn't find any properties that allowed me to access it
directly. It obviously exists because it is displayed on the chart, yet
the
objects, methods and properties for MS Chart don't seem to let me get at
it.

Just curious, since I played with this for a long time, is this just an
undocumented property?


Jim B

You can use DAO code to change the SQL property of a saved query. There
are
other crosstab report solutions at
http://www.invisibleinc.com/divFiles.cfm?divDivID=4. The Crosstab.mdb has
a
solution for dynamic columns that might work.

--
Duane Hookom
MS Access MVP
--

Thanks for responding. The number of columns can change if additional
Sites
(Shortname) are entered into the database. Is the Column Headings
property
one that I can change in code based on the Sites (Shortnames) in the
Sites
table?

Jim B.

:

The obvious answer is to change the "non-fixed columns" to "fixed".
Can
you
enter the possible values of ShortName into the Column Headings
property?

--
Duane Hookom
MS Access MVP
--

I'm trying to create a chart from a crosstab query that also uses
the
Master
and child link properties. Whenever I run the report, I get the
error
message "Cannot use the crosstab of a non-fixed column as a
sub-query".

If I run the report without the Master/Child link fields, it will
display.

If I leave the links in, and open the report in design view, select
the
row
source of the chart object, view the underlying query and then do a
print
preview the report displays perfectly with the correct chart
displaying
for
each record.

If, using code, I open the report in design view, and then modify
the
SQL
statement for the row source property of the chart, and then display
the
report in preview mode, it runs fine. I can't get the report to run
correctly by just opening the report, or sending it to the printer.

The SQL statement used as the rowsource property is:

"TRANSFORM Sum(tblCrossVariance.[Total Of Hours]) AS [SumOfTotal Of
Hours]
SELECT tblCrossVariance.Category,
tblCrossVariance.LogicalGroupOfSitesID
FROM
tblCrossVariance GROUP BY tblCrossVariance.Category,
tblCrossVariance.LogicalGroupOfSitesID PIVOT
tblCrossVariance.ShortName;"

The Link Master Field is [ID]
The link child field is [LogicalGroupOfSitesID]

Does anyone have any idea what's going on here?

Thanks for you time.

Jim B.