Using Parameters in Graph

  • Thread starter Thread starter Alaska1
  • Start date Start date
A

Alaska1

Is there a way to use Parameter in the Graphs in Access Reporting? I have a
straight graph and then graphs with cross tab queries?
 
Your 'straight graph' pulls data via some SQL statement as does the crosstab
query. These can be edited to include parameters or better yet would be to
use a form with unbound text boxes for entering the parameters and refer to
the text boxes in the criteria of the SQL.
 
Thank you,

I have listed the sql query below.

TRANSFORM Sum([Time Sheet Errors by PS 2].CountOfTimeSheetErrors) AS
SumOfCountOfTimeSheetErrors
SELECT [Time Sheet Errors by PS 2].PerformanceSupervisor
FROM [Time Sheet Errors by PS 2]
GROUP BY [Time Sheet Errors by PS 2].PerformanceSupervisor
PIVOT [Time Sheet Errors by PS 2].TimeSheetErrors;

I need to add the parameter [Which Division] and [Which Department]. I
added it intot the sql query but it gave me any error. Not sure where to add
the clause
 
Try this --
TRANSFORM Sum([Time Sheet Errors by PS 2].CountOfTimeSheetErrors) AS
SumOfCountOfTimeSheetErrors
SELECT [Time Sheet Errors by PS 2].PerformanceSupervisor
FROM [Time Sheet Errors by PS 2]
WHERE [Division] = [Which Division] AND [Department] = [Which Department]
GROUP BY [Time Sheet Errors by PS 2].PerformanceSupervisor
PIVOT [Time Sheet Errors by PS 2].TimeSheetErrors;

--
Build a little, test a little.


Alaska1 said:
Thank you,

I have listed the sql query below.

TRANSFORM Sum([Time Sheet Errors by PS 2].CountOfTimeSheetErrors) AS
SumOfCountOfTimeSheetErrors
SELECT [Time Sheet Errors by PS 2].PerformanceSupervisor
FROM [Time Sheet Errors by PS 2]
GROUP BY [Time Sheet Errors by PS 2].PerformanceSupervisor
PIVOT [Time Sheet Errors by PS 2].TimeSheetErrors;

I need to add the parameter [Which Division] and [Which Department]. I
added it intot the sql query but it gave me any error. Not sure where to add
the clause

KARL DEWEY said:
Your 'straight graph' pulls data via some SQL statement as does the crosstab
query. These can be edited to include parameters or better yet would be to
use a form with unbound text boxes for entering the parameters and refer to
the text boxes in the criteria of the SQL.
Click to expand...
Click to expand...
 
Thank you!. Appreciate your help.

Here is the query I used. I used a wildcard, so they do not have to type
exact. i keep getting Microsoft Jet Engine does not recognize [Which
Department] as a valid field name or expresssion.

TRANSFORM Sum([Time Sheet Errors by PS 2].CountOfTimeSheetErrors) AS
SumOfCountOfTimeSheetErrors
SELECT [Time Sheet Errors by PS 2].PerformanceSupervisor
FROM [Time Sheet Errors by PS 2]
WHERE ((([Time Sheet Errors by PS 2].Division) Like "*" & [Which Division] &
"*") AND (([Time Sheet Errors by PS 2].Department) Like "*" & [Which
Department] & "*"))
GROUP BY [Time Sheet Errors by PS 2].PerformanceSupervisor
PIVOT [Time Sheet Errors by PS 2].TimeSheetErrors;


KARL DEWEY said:
Try this --
TRANSFORM Sum([Time Sheet Errors by PS 2].CountOfTimeSheetErrors) AS
SumOfCountOfTimeSheetErrors
SELECT [Time Sheet Errors by PS 2].PerformanceSupervisor
FROM [Time Sheet Errors by PS 2]
WHERE [Division] = [Which Division] AND [Department] = [Which Department]
GROUP BY [Time Sheet Errors by PS 2].PerformanceSupervisor
PIVOT [Time Sheet Errors by PS 2].TimeSheetErrors;

--
Build a little, test a little.


Alaska1 said:
Thank you,

I have listed the sql query below.

TRANSFORM Sum([Time Sheet Errors by PS 2].CountOfTimeSheetErrors) AS
SumOfCountOfTimeSheetErrors
SELECT [Time Sheet Errors by PS 2].PerformanceSupervisor
FROM [Time Sheet Errors by PS 2]
GROUP BY [Time Sheet Errors by PS 2].PerformanceSupervisor
PIVOT [Time Sheet Errors by PS 2].TimeSheetErrors;

I need to add the parameter [Which Division] and [Which Department]. I
added it intot the sql query but it gave me any error. Not sure where to add
the clause

KARL DEWEY said:
Your 'straight graph' pulls data via some SQL statement as does the crosstab
query. These can be edited to include parameters or better yet would be to
use a form with unbound text boxes for entering the parameters and refer to
the text boxes in the criteria of the SQL.

--
Build a little, test a little.


:

Is there a way to use Parameter in the Graphs in Access Reporting? I have a
straight graph and then graphs with cross tab queries?
Click to expand...
Click to expand...
Click to expand...
 
I forgot, crosstab queries must have parameters defined. Below is if they
are integers. This is for text --
PARAMETERS [Which Department] TEXT (255), [Which Division] TEXT (255);

PARAMETERS [Which Department] Short, [Which Division] Short;
TRANSFORM Sum([Time Sheet Errors by PS 2].CountOfTimeSheetErrors) AS
SumOfCountOfTimeSheetErrors
SELECT [Time Sheet Errors by PS 2].PerformanceSupervisor
FROM [Time Sheet Errors by PS 2]
WHERE ((([Time Sheet Errors by PS 2].Division) Like "*" & [Which Division] &
"*") AND (([Time Sheet Errors by PS 2].Department) Like "*" & [Which
Department] & "*"))
GROUP BY [Time Sheet Errors by PS 2].PerformanceSupervisor
PIVOT [Time Sheet Errors by PS 2].TimeSheetErrors;

--
Build a little, test a little.


Alaska1 said:
Thank you!. Appreciate your help.

Here is the query I used. I used a wildcard, so they do not have to type
exact. i keep getting Microsoft Jet Engine does not recognize [Which
Department] as a valid field name or expresssion.

TRANSFORM Sum([Time Sheet Errors by PS 2].CountOfTimeSheetErrors) AS
SumOfCountOfTimeSheetErrors
SELECT [Time Sheet Errors by PS 2].PerformanceSupervisor
FROM [Time Sheet Errors by PS 2]
WHERE ((([Time Sheet Errors by PS 2].Division) Like "*" & [Which Division] &
"*") AND (([Time Sheet Errors by PS 2].Department) Like "*" & [Which
Department] & "*"))
GROUP BY [Time Sheet Errors by PS 2].PerformanceSupervisor
PIVOT [Time Sheet Errors by PS 2].TimeSheetErrors;


KARL DEWEY said:
Try this --
TRANSFORM Sum([Time Sheet Errors by PS 2].CountOfTimeSheetErrors) AS
SumOfCountOfTimeSheetErrors
SELECT [Time Sheet Errors by PS 2].PerformanceSupervisor
FROM [Time Sheet Errors by PS 2]
WHERE [Division] = [Which Division] AND [Department] = [Which Department]
GROUP BY [Time Sheet Errors by PS 2].PerformanceSupervisor
PIVOT [Time Sheet Errors by PS 2].TimeSheetErrors;

--
Build a little, test a little.


Alaska1 said:
Thank you,

I have listed the sql query below.

TRANSFORM Sum([Time Sheet Errors by PS 2].CountOfTimeSheetErrors) AS
SumOfCountOfTimeSheetErrors
SELECT [Time Sheet Errors by PS 2].PerformanceSupervisor
FROM [Time Sheet Errors by PS 2]
GROUP BY [Time Sheet Errors by PS 2].PerformanceSupervisor
PIVOT [Time Sheet Errors by PS 2].TimeSheetErrors;

I need to add the parameter [Which Division] and [Which Department]. I
added it intot the sql query but it gave me any error. Not sure where to add
the clause

:

Your 'straight graph' pulls data via some SQL statement as does the crosstab
query. These can be edited to include parameters or better yet would be to
use a form with unbound text boxes for entering the parameters and refer to
the text boxes in the criteria of the SQL.

--
Build a little, test a little.


:

Is there a way to use Parameter in the Graphs in Access Reporting? I have a
straight graph and then graphs with cross tab queries?
Click to expand...
Click to expand...
Click to expand...
 
Thank you! Thank you!

It works. Thanks so much.

I added a text box
=" " & [Department] & "/ " & [Division] over the graph to list the
department and division but the parameters are not showing up.

KARL DEWEY said:
I forgot, crosstab queries must have parameters defined. Below is if they
are integers. This is for text --
PARAMETERS [Which Department] TEXT (255), [Which Division] TEXT (255);

PARAMETERS [Which Department] Short, [Which Division] Short;
TRANSFORM Sum([Time Sheet Errors by PS 2].CountOfTimeSheetErrors) AS
SumOfCountOfTimeSheetErrors
SELECT [Time Sheet Errors by PS 2].PerformanceSupervisor
FROM [Time Sheet Errors by PS 2]
WHERE ((([Time Sheet Errors by PS 2].Division) Like "*" & [Which Division] &
"*") AND (([Time Sheet Errors by PS 2].Department) Like "*" & [Which
Department] & "*"))
GROUP BY [Time Sheet Errors by PS 2].PerformanceSupervisor
PIVOT [Time Sheet Errors by PS 2].TimeSheetErrors;

--
Build a little, test a little.


Alaska1 said:
Thank you!. Appreciate your help.

Here is the query I used. I used a wildcard, so they do not have to type
exact. i keep getting Microsoft Jet Engine does not recognize [Which
Department] as a valid field name or expresssion.

TRANSFORM Sum([Time Sheet Errors by PS 2].CountOfTimeSheetErrors) AS
SumOfCountOfTimeSheetErrors
SELECT [Time Sheet Errors by PS 2].PerformanceSupervisor
FROM [Time Sheet Errors by PS 2]
WHERE ((([Time Sheet Errors by PS 2].Division) Like "*" & [Which Division] &
"*") AND (([Time Sheet Errors by PS 2].Department) Like "*" & [Which
Department] & "*"))
GROUP BY [Time Sheet Errors by PS 2].PerformanceSupervisor
PIVOT [Time Sheet Errors by PS 2].TimeSheetErrors;


KARL DEWEY said:
Try this --
TRANSFORM Sum([Time Sheet Errors by PS 2].CountOfTimeSheetErrors) AS
SumOfCountOfTimeSheetErrors
SELECT [Time Sheet Errors by PS 2].PerformanceSupervisor
FROM [Time Sheet Errors by PS 2]
WHERE [Division] = [Which Division] AND [Department] = [Which Department]
GROUP BY [Time Sheet Errors by PS 2].PerformanceSupervisor
PIVOT [Time Sheet Errors by PS 2].TimeSheetErrors;

--
Build a little, test a little.


:

Thank you,

I have listed the sql query below.

TRANSFORM Sum([Time Sheet Errors by PS 2].CountOfTimeSheetErrors) AS
SumOfCountOfTimeSheetErrors
SELECT [Time Sheet Errors by PS 2].PerformanceSupervisor
FROM [Time Sheet Errors by PS 2]
GROUP BY [Time Sheet Errors by PS 2].PerformanceSupervisor
PIVOT [Time Sheet Errors by PS 2].TimeSheetErrors;

I need to add the parameter [Which Division] and [Which Department]. I
added it intot the sql query but it gave me any error. Not sure where to add
the clause

:

Your 'straight graph' pulls data via some SQL statement as does the crosstab
query. These can be edited to include parameters or better yet would be to
use a form with unbound text boxes for entering the parameters and refer to
the text boxes in the criteria of the SQL.

--
Build a little, test a little.


:

Is there a way to use Parameter in the Graphs in Access Reporting? I have a
straight graph and then graphs with cross tab queries?
Click to expand...
Click to expand...
Click to expand...
 
Do you have any suggestions or ideas as to why the parmenters for division
and department are not showing up.

Built it in text box with an expression and added it to the page header of
the report.

=" " & [Department] & "/ " & [Division] over the graph to list the
department and division but the parameters are not showing up.

Alaska1 said:
Thank you! Thank you!

It works. Thanks so much.

I added a text box
=" " & [Department] & "/ " & [Division] over the graph to list the
department and division but the parameters are not showing up.

KARL DEWEY said:
I forgot, crosstab queries must have parameters defined. Below is if they
are integers. This is for text --
PARAMETERS [Which Department] TEXT (255), [Which Division] TEXT (255);

PARAMETERS [Which Department] Short, [Which Division] Short;
TRANSFORM Sum([Time Sheet Errors by PS 2].CountOfTimeSheetErrors) AS
SumOfCountOfTimeSheetErrors
SELECT [Time Sheet Errors by PS 2].PerformanceSupervisor
FROM [Time Sheet Errors by PS 2]
WHERE ((([Time Sheet Errors by PS 2].Division) Like "*" & [Which Division] &
"*") AND (([Time Sheet Errors by PS 2].Department) Like "*" & [Which
Department] & "*"))
GROUP BY [Time Sheet Errors by PS 2].PerformanceSupervisor
PIVOT [Time Sheet Errors by PS 2].TimeSheetErrors;

--
Build a little, test a little.


Alaska1 said:
Thank you!. Appreciate your help.

Here is the query I used. I used a wildcard, so they do not have to type
exact. i keep getting Microsoft Jet Engine does not recognize [Which
Department] as a valid field name or expresssion.

TRANSFORM Sum([Time Sheet Errors by PS 2].CountOfTimeSheetErrors) AS
SumOfCountOfTimeSheetErrors
SELECT [Time Sheet Errors by PS 2].PerformanceSupervisor
FROM [Time Sheet Errors by PS 2]
WHERE ((([Time Sheet Errors by PS 2].Division) Like "*" & [Which Division] &
"*") AND (([Time Sheet Errors by PS 2].Department) Like "*" & [Which
Department] & "*"))
GROUP BY [Time Sheet Errors by PS 2].PerformanceSupervisor
PIVOT [Time Sheet Errors by PS 2].TimeSheetErrors;


:

Try this --
TRANSFORM Sum([Time Sheet Errors by PS 2].CountOfTimeSheetErrors) AS
SumOfCountOfTimeSheetErrors
SELECT [Time Sheet Errors by PS 2].PerformanceSupervisor
FROM [Time Sheet Errors by PS 2]
WHERE [Division] = [Which Division] AND [Department] = [Which Department]
GROUP BY [Time Sheet Errors by PS 2].PerformanceSupervisor
PIVOT [Time Sheet Errors by PS 2].TimeSheetErrors;

--
Build a little, test a little.


:

Thank you,

I have listed the sql query below.

TRANSFORM Sum([Time Sheet Errors by PS 2].CountOfTimeSheetErrors) AS
SumOfCountOfTimeSheetErrors
SELECT [Time Sheet Errors by PS 2].PerformanceSupervisor
FROM [Time Sheet Errors by PS 2]
GROUP BY [Time Sheet Errors by PS 2].PerformanceSupervisor
PIVOT [Time Sheet Errors by PS 2].TimeSheetErrors;

I need to add the parameter [Which Division] and [Which Department]. I
added it intot the sql query but it gave me any error. Not sure where to add
the clause

:

Your 'straight graph' pulls data via some SQL statement as does the crosstab
query. These can be edited to include parameters or better yet would be to
use a form with unbound text boxes for entering the parameters and refer to
the text boxes in the criteria of the SQL.

--
Build a little, test a little.


:

Is there a way to use Parameter in the Graphs in Access Reporting? I have a
straight graph and then graphs with cross tab queries?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top