• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 353
  • Last Modified:

Crosstab Query Based on a Parameter Query

I have a parameter query that I am using as the source of a crosstab query. The parameter is based on the date field and the date field drives the columns in the crosstab query. I know the way to do this is to define the columns in the properties for the crosstab query. My question is how to do define the columns when it could be any date range the user selects? Is there a way to assign a variable to the column fields?
0
Ennistymon
Asked:
Ennistymon
  • 4
  • 4
1 Solution
 
GRayLCommented:
Can you post the SQL's for both the Parameter Query and the Cross Tab query?
0
 
GRayLCommented:
I've just run a simple testing using a Parameter query as the basis for a Cross tab - no problem.
0
 
EnnistymonAuthor Commented:
The problem comes whn you try to pass the crosstab query to the report wizard. It comes back with no records and will not allow a report. As I understand it you then need to define the columns in the crosstab query . When you do that, the detail does not come back only the totals. SQL to follow:

Parameter Query

SELECT tblExecptions.Date, tblExecptions.Comments, tblExecptions.Type, tblExecptions.[Name / SCAC]
FROM tblExecptions
WHERE (((tblExecptions.Date) Between [Start date] And [End date]) AND ((tblExecptions.Type)="Misplacement"));

Crosstab Query

PARAMETERS [Start date] DateTime, [End date] DateTime;
TRANSFORM Count([tblExecptions Query].Comments) AS CountOfComments
SELECT [tblExecptions Query].[Name / SCAC], Count([tblExecptions Query].Comments) AS [Total Of Comments]
FROM [tblExecptions Query]
GROUP BY [tblExecptions Query].[Name / SCAC]
PIVOT Format([Date],"Short Date") In ("1","2","3","4","5","6","7","8","9","10","11","12","13","14","15","16","17","18","19","20","21","22","23","24","25","26","27","28","29","30","31");
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
GRayLCommented:
Now I see where you are coming from.  Here we see the ugly side of the cross tab beast.  It cannot be used in a report unless you tailor each report to the vagaries of the included dates.  As a workaround, I try to put things that vary - like dates, in the SELECT clause, and keep the things which do not vary in the TRANSFORM clause.

BTW, as you have written your Crosstab, if [Date] were any date, the PIVOT clause would never be true.

The ShortDate format (default is mm/dd/yy) will never appear in the list of 1-31.

Maybe you meant:  PIVOT Day(Format([Date],"ShortDate"))  ??

In which case none of the IN() values should be quoted.
0
 
EnnistymonAuthor Commented:
In your reference to the SELECT and TRANSFORM clauses, would putting the date in the SELECT and the SCAC in the TRANSFORM cause Date to show as a row and SCAC to show as a column?
0
 
EnnistymonAuthor Commented:
Thank you VERY much! You have solved my problem. Your instructions to change the SQL to the following did it, "PIVOT Day(Format([Date],"ShortDate"))." Thanks again.
0
 
EnnistymonAuthor Commented:
Thanks!
0
 
GRayLCommented:
Thanks, glad to help
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now