Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
PETE314
Starting Member
37 Posts |
 Posted - 2005-08-08 : 14:42:51
|
| Access 200 Project (.adp)SQL Server 2000 backendI am trying to make a report that in this case shows the amount of orders entered for each state for each day within a specific month... so the return should be similar to this.....Date:_________St#1________St#2________St#3________01/01/2005____35__________73__________22__________01/02/2005____65__________64__________31__________I have used the Stored Procedure from this site that deals with creating dynamic pivot tables to generate the data. Using SQL Query Analyzer I perfected my Select statement for variable @select and perfected the inputs for all of the variables needed for the stored procedure. I am able to get the results I am looking for (great work guys).So now that I know how to generate my results I now need to get those results into a report. Being a dynamic pivot table, you never know how many colums you can get....so I built a form that allows the user to select a year and month.....And then they are able to choose up to 6 parameters....In this case states. So I created code in the OnOpen event of the report that changes the control source and labels of the fields in order to pick up the data once it comes in.BUT in using the Stored Procedure as a record source......my input parameters are variable themselves. So although it should work, creating the Input parameters in the OnOpen Event for the report does not work.....neither does trying the EXEC commands. However I feel I have found a work around. I create the InputParameters and place them into invisible text boxes(and in the case of the @select variable, a memo box)and then I hardwire the Input parameters to the text boxes on the form. So now the my parameters are created and placed into these text boxes before I call the event to open the form.This too should work....but alas I am getting a runtime error....Runtime error 2757 'There was a problem accessing a property or method of the OLE object'Now in my debugging I have found that my text boxes do contain the correct information. That is if I enter the exact same thing using SQL Query Analyzer I can get a return......So I do not know what is holding this up......Any ideas???? |
|
|
PETE314
Starting Member
37 Posts |
Posted - 2005-08-08 : 14:46:27
|
| Just for reference this is the Stored Procedure that I am speaking of.....[url]http://www.sqlteam.com/item.asp?ItemID=2955[/url] |
 |
|
|
Sitka
Aged Yak Warrior
571 Posts |
Posted - 2005-08-08 : 15:13:41
|
| I can't quite remember if this works in ADP's but Access has a native TRANSFORM function in it's query language.You may be able to regress from using the crosstab proc and just use the TRANSFORM and a view.Creates a crosstab query.SyntaxTRANSFORM aggfunctionselectstatementPIVOT pivotfield [IN (value1[, value2[, ...]])]The TRANSFORM statement has these parts:Part Descriptionaggfunction An SQL aggregate function that operates on the selected data.selectstatement A SELECT statement.pivotfield The field or expression you want to use to create column headings in the query's result set.value1, value2 Fixed values used to create column headings.RemarksWhen you summarize data using a crosstab query, you select values from specified fields or expressions as column headings so you can view data in a more compact format than with a select query.TRANSFORM is optional but when included is the first statement in an SQL string. It precedes a SELECT statement that specifies the fields used as row headings and a GROUP BY clause that specifies row grouping. Optionally, you can include other clauses, such as WHERE, that specify additional selection or sorting criteria. You can also use subqueries as predicates — specifically, those in the WHERE clause — in a crosstab query.The values returned in pivotfield are used as column headings in the query's result set. For example, pivoting the sales figures on the month of the sale in a crosstab query would create 12 columns. You can restrict pivotfield to create headings from fixed values (value1, value2 ) listed in the optional IN clause. You can also include fixed values for which no data exists to create additional columns.The following example creates a crosstab query that shows product sales by month for a user-specified year. The months are returned from left to right (pivoted) as columns, and the product names are returned from top to bottom as rows.PARAMETERS [Sales for which year?] LONG;TRANSFORM Sum([Order Details].Quantity * ([Order Details].UnitPrice - ([Order Details].Discount / 100) * [Order Details].UnitPrice)) AS SalesSELECT ProductName FROM OrdersINNER JOIN (Products INNER JOIN [Order Details] ON Products.ProductID = [Order Details].ProductID)ON Orders.OrderID = [Order Details].OrderIDWHERE DatePart("yyyy", OrderDate) = [Sales for which year?]GROUP BY ProductNameORDER BY ProductNamePIVOT DatePart("m", OrderDate);I wish someone would start an Official XML Rant Thread. |
|
|
|
PETE314
Starting Member
37 Posts |
Posted - 2005-08-08 : 15:22:52
|
| TRANSFORM will not work in adp's because it does not use the Jet Engine. TRANSFORM is an awesome command. It is the one thing I really like about the Jet Engine tho....but in this case I am using an .adp and therefore using Transact SQL. |
|
|
|
Sitka
Aged Yak Warrior
571 Posts |
Posted - 2005-08-08 : 15:54:25
|
| Sorry, couldn't remember if it was supported.I wish someone would start an Official XML Rant Thread. |
|
|
|
PETE314
Starting Member
37 Posts |
Posted - 2005-08-08 : 16:14:53
|
| Hey, I appreciate any and all help.....This one report has been an absolute nightmare...lol :P |
|
|
|
PETE314
Starting Member
37 Posts |
Posted - 2005-08-15 : 14:37:08
|
| ^bump^Really needing a few outside ideas from anyone who has used this process of creating crosstab data and displayed it in a report in Access..... |
|
|
|
|
|
|
|
|
Crosstab/Pivot Table Report............