May 17, 2012 at 9:26 am
Sober,
No gripe at all. I have toiled in the sparse budget arena of non-profit and without Office/VBA, would have been hard-pressed to create the user-friendly tools I needed that kept business folks from gumming up databases yet provided them with the tools and features they needed.
IMO, for any project, you take the requirements in conjunction with available tools and create a custom solution. There is never just one way to do anything.
Dot's project, however, just seems risky and hard to maintain with the Word and Excel requirements. I have, however, done more than my share of using VBA to hit SQL and throw data into Excel. Users love Excel and are comfortable with it.
The Word template with parameters is where I depart this scenario, both from lack of exposure (which I am grateful for!) and from a standpoint of "this is giving users the ability to hurt things" as users seem to be able to foul a template all too quickly.
Hope I'm wrong on that last point, and look to learn something from this thread as Dot moves along the development path.
May 17, 2012 at 9:59 am
I got my start with Excel and VBA.
At the end of the day, I see it easier to actually meet the core requirements with SSRS by pretty much just modifying when the freeform text is put in. Especially if the poster is not fluent in VBA and time is such a big factor.
In the end, the poster has to support whatever technoolgy and platform they choose.
They will make choices that will change over time, just like the technoolgy does.
When you could probably do a prototype in reporting services in 30 minutes to show the users off one of the many examples you can find on the web, it is probably time well spent.
If nothing else, both you and your user base get exposure to a new tool, for a very minimal investment. You already own it.
I'm sure at some point you will find it useful.
Also imagine the risk to the business should you take freeform user input into a dynamic select statement, and you do not have the correct edits in place.
My suggestions were based on my experience, having been there, done that, both ways.
Others are free to have their opinions too.
As well as to choose how they solve a problem.
They end up supporting what they feel comfortable with.
May 17, 2012 at 10:55 am
Sober Counsel,
Yes, this forum is EX-CEL-LENT (par excellence) for all things SQL and I bow low for all the help that I get here. From the Lynns and dwains and opc.threes and dwains and gregs and drews and ColdCoffees and more than I can list I have learned so much and what that means for me you will never fully appreciate.
But, same goes for the help with VBA.
The path (VBA) to the solution desired by boss requires much less alteration to the overall landscape of tools currently in use. Frankly, the boss didn't even know it would take VBA. He said 'here, this is what I need'. Yes, I could make an argument for SSRS (theoretically) but it would mean retrofitting all current reports which I am loathe to argue for simply because I need to get a report delivered to Word/Excel. It is like buying a new car just because it doesn't have a built in gizmo. Or, say like buying new bike because there's no pre fab way to attach a rear basket on the current model. NO LIKE!
Thanks to Experts Exchange I got as far as passing params to SQL Datawarehouse and returning a report to an Excel Template. I am now working on incorporating this portion of your code that formats the returned report and adds a chart, but am getting following error.
"Run-time error '438':
Object doesn't support this property or method.
The portion of your code I am tying into 'my' code starts on line 14: "Set aPivot..." and ofcourse, having introduced it with brute force, that is exactly the line highlighted for this error.
Sub Report_Click()
Dim con As New Connection
Dim rs As New Recordset
Dim strSQL As String
con.Open "Provider=SQLOLEDB;Data Source=V-HELKR032112;;Initial Catalog=Sandbox;Integrated Security=SSPI;"
strSQL = "select Application, Build, Severity, system_state from BugTable where Application = '" & ActiveSheet.Range("B5") & "' and Build = '" & ActiveSheet.Range("B6") & "' ;"
Set rs = con.Execute(strSQL)
ActiveSheet.Range("A8:A13").EntireRow.Delete
ActiveSheet.Range("A8").CopyFromRecordset rs
rs.Close
con.Close
Dim aPivot As PivotTable, aQueryTable As QueryTable
Set aPivot = ThisWorkbook.ActiveSheet("A15:A7000").PivotTables(0)
Set aPivot.PivotCache.Recordset = rs
aPivot.RefreshTable
Set aQueryTable = ThisWorkbook.ActiveSheet.QueryTables(0)
Set aQueryTable.Recordset = rs
aQueryTable.Refresh
End Sub
This is where I'm at with this and I am going to work on this as we speak. Boss has given me more time in light of the information I gave him. Any further injection of advice is great. I have a lot to learn about VBA syntax.
--Quote me
May 17, 2012 at 4:05 pm
As long as you are set on learning VBA, my advice is to learn to make some things much more dynamic.
In just that snippet, I see you set an arbitrary range.
You never know how many rows you are going to get back, and you likely will get blanks or miss data.
Pivot tables are something that change between the versions of Excel.
And I hope you are protecting your code from curious eyes.
That caused me nothing but trouble.
Glad you're excited and learning something.
May 17, 2012 at 4:51 pm
Greg Edwards-268690 (5/17/2012)
As long as you are set on learning VBA, my advice is to learn to make some things much more dynamic.In just that snippet, I see you set an arbitrary range.
You never know how many rows you are going to get back, and you likely will get blanks or miss data.
OK I will not define a range.
And I hope you are protecting your code from curious eyes.
That caused me nothing but trouble.
I do not know how to protect my code.
Glad you're excited and learning something.
Thank you. I am happy to learn.
--Quote me
May 17, 2012 at 5:03 pm
in case anyone can give me more input here I'm posting DDL. I can't upload macro enabled excel as it is restricted so I will show only screenshot.
DDL for the table: BugTable
use sandbox
go
create table BugTable
(application nvarchar(30)
, build nvarchar(30)
, severity int
, system_state nvarchar(50)
, bug_id int)
insert into BugTable
values
('Buckwheat', '0700.0300.0500', 1, 'active', 1001),
('Buckwheat', '0700.0300.0500', 2, 'proposed', 1002),
('Buckwheat', '0700.0300.0500', 3, 'closed', 1003),
('Buckwheat', '0700.0300.0500', 4, 'resolved', 1004),
('Meteorite', '0500.0400.0300', 1, 'active', 1005),
('Meteorite', '0500.0400.0300', 2, 'proposed', 1006),
('Meteorite', '0500.0400.0300', 3, 'closed', 1007),
('Meteorite', '0500.0400.0300', 4, 'resolved', 1008);
Select statement that macro will use to retrieve data, based on 2 parameters called Application and Build.
select
Application
, Build
, Severity
, system_state
from BugTable
where Application = 'Buckwheat'
and Build = '0700.0300.0500'
The VBA that integrates this select statement, accepts two parameters, and returns a report.
Sub Report_Click()
Dim con As New Connection
Dim rs As New Recordset
Dim strSQL As String
con.Open "Provider=SQLOLEDB;Data Source=V-HELKR032112;;Initial Catalog=Sandbox;Integrated Security=SSPI;"
strSQL = "select Application, Build, Severity, system_state from BugTable where Application = '" & ActiveSheet.Range("B5") & "' and Build = '" & ActiveSheet.Range("B6") & "' ;"
Set rs = con.Execute(strSQL)
ActiveSheet.Range("A8:A13").EntireRow.Delete
ActiveSheet.Range("A8").CopyFromRecordset rs
rs.Close
con.Close
End Sub
The VBA code after I integrated some of SoberCounsels VBA and inserted a pivot table into the active worksheet. At this point my integration has added ZERO progress. Running this gives me the following error:
'You cannot move a part of a Pivot Table report, or insert worksheet cells, rows, or columns inside a PivotTAble report. To insert worsheet cells, rows, or columns, first move the PivotTable report (with the PivotTable report selected, on the Option tab, in the Actions group, click Move PivotTable). To add, move, or remove cells within the report, do one of the following:" ????
Sub Report_Click()
Dim con As New Connection
Dim rs As New Recordset
Dim strSQL As String
con.Open "Provider=SQLOLEDB;Data Source=V-HELKR032112;;Initial Catalog=Sandbox;Integrated Security=SSPI;"
strSQL = "select Application, Build, Severity, system_state from BugTable where Application = '" & ActiveSheet.Range("B5") & "' and Build = '" & ActiveSheet.Range("B6") & "' ;"
Set rs = con.Execute(strSQL)
ActiveSheet.Range("A8:A13").EntireRow.Delete
ActiveSheet.Range("A8").CopyFromRecordset rs
rs.Close
con.Close
Dim aPivot As PivotTable, aQueryTable As QueryTable
MsgBox (ThisWorkbook.ActiveSheet.PivotTables(0))
Set aPivot = ThisWorkbook.ActiveSheet.PivotTables(0)
Set aPivot.PivotCache.Recordset = rs
aPivot.RefreshTable
Set aQueryTable = ThisWorkbook.ActiveSheet.QueryTables(0)
Set aQueryTable.Recordset = rs
aQueryTable.Refresh
End Sub
From the screenshot you will see the following problems:
(1) I do not have a label in cell B6. I would like it to be Build. Whatever I type in there is erased when I hit the Report macro. How to add label?
(2) When I run macro the data is retrieved but doesn't populate the pivot table. I have to go to field list, tick off checkboxes, and format the pivot table. This will need to be done by macro but how to do?
(3) there is no chart. How to add chart?
(4) The data itself is being utilized by pivot table for column/row labels. Just like with (1) I need some way of adding column labels that will be utilized by pivot. How to add column headings?
If anyone can help me to forge ahead a bit I'd appreciate it. What do I add to code now in light of these problems?
--Quote me
May 17, 2012 at 6:11 pm
In your project, you can password protect it to keep others from viewing.
I've had workbooks copied sommewhere else on the network, then someone 'enhances it', and then it breaks.
Then I've had to go back and fix it.
Not fun. Especially if they are gone and there is no documentation in the code.
And by dynamic, I mean you might consider naming it.
Then you can go back and resize to the current region as needed.
This avoids the issues of blanks or missing some data.
Error handling is something else to learn about.
Hoping at some point you try the RS option.
If nothing else, just to see that the bulk of what your requirements are, with just a little bending, could have been done in 30 minutes.
Heading up north for a few days. Hoping the fish are biting.
May 18, 2012 at 5:01 am
Hi,
This should work for you...
In your excel file, insert the following Names:
Range("B5") = Application
Range("B6") = Build
Then, in your file as I can see it (in the screenshot), delete rows 8 to 16 (including the pivot table, which incidentally doesn't look quite right, due I think to the fact that the CopyFromRecordset method does not copy column headers)... then add the following to your module, overwriting the existing Report_Click...
Sub Report_Click()
Dim rs As New Recordset
Dim strSQL As String
Dim aQueryTable As QueryTable, aPivotTable As PivotTable
strSQL = "select Application, Build, Severity, system_state from BugTable where Application = '" & Range("Application") & "' and Build = '" & Range("Build") & "' ;"
Set rs = GetRecordSet(strSQL, , , "Driver={SQL Server};Server=sqllpb2dbl54201.global.lloydstsb.com\inst_08,14338;Database=test;trusted_connection=yes")
'get rid of existing data...
Range("A10").CurrentRegion.EntireRow.Delete
Dim xlName As Name
For Each xlName In ThisWorkbook.Names
If xlName.Name = "Results" Then xlName.Delete
Next
'pipe in new data
Set aQueryTable = ActiveSheet.QueryTables.Add(rs, Range("A10"))
aQueryTable.Refresh
aQueryTable.EnableEditing = False
'add Results name, then create a pivot table (which is defined to look at Results)
ThisWorkbook.Names.Add "Results", "=" & Range("A10").CurrentRegion.Address
'this creates a new pivottable called PivotResults, and adds a couple of fields
Set aPivotTable = ThisWorkbook.PivotCaches.Add(xlDatabase, "Results").CreatePivotTable(Range("E2"), "PivotResults")
aPivotTable.AddFields "Severity", "system_state"
aPivotTable.AddDataField aPivotTable.PivotFields("Application"), "Bugs", xlCount
'at this point you should manually edit the pivot table that's just been created (one time only!) - when you're done comment out the lines that created it and added the fields
'this updates an existing pivottable called PivotResults
ActiveSheet.PivotTables("PivotResults").PivotCache.Refresh
End Sub
Function GetRecordSet(strSQL As String, Optional strConnection As String, Optional strServer as string, Optional strDB as String) As ADODB.Recordset
'Returns a recordset...
'Declare Variables
Dim strConn As String, adoCN As ADODB.Connection, adoRS As ADODB.Recordset
'Error handle...
On Error GoTo ErrorHandler
'Build Connection String
If strConnection = "" Then
strConn = "Driver={SQL Server};Server=" & IIf(strServer = "", strDefaultServer, strServer) & ";Database=" & IIf(strDB = "", strDefaultDatabase, strDB) & ";"
Else
strConn = strConnection
End If
OpenCN:
'Open Connection
Set adoCN = New ADODB.Connection
adoCN.CommandTimeout = 10
adoCN.Open strConn
'Open Recordset
OpenRS:
Set adoRS = New ADODB.Recordset
With adoRS
.CursorType = adOpenStatic
.CursorLocation = adUseClient
Do Until .State = 1
.Open strSQL, adoCN, adOpenForwardOnly, adLockReadOnly
Loop
End With
'Close Connection
adoRS.ActiveConnection = Nothing
adoCN.Close
Set adoCN = Nothing
'Return recordset to calling procedure
Set GetRecordSet = adoRS
'Finish up and exit
Exit Function
ErrorHandler:
'you should probably add some here!
End Function
Hope this helps - I'll leave formatting etc up to you !
...And, good luck!
May 18, 2012 at 12:53 pm
Hope the fish are biting! Thanks Greg.
--Quote me
May 18, 2012 at 6:33 pm
I have studied the example on Microsoft's BOL. http://msdn.microsoft.com/en-us/library/hh243933.aspx titled Creating PivotTable Reports and Charts with VBA in Excel 2010
I am using Excel 2010
---here's updated ddl, basically just added column called 'date_created'
use sandbox
go
create table BugTable
(application nvarchar(30)
, build nvarchar(30)
, severity int
, date_created datetime
, system_state nvarchar(50)
, bug_id int)
insert into BugTable
values
('Buckwheat', '0700.0300.0500', 1, '04/07/2012', 'active', 1001),
('Buckwheat', '0700.0300.0500', 2, '03/01/2012', 'proposed', 1002),
('Buckwheat', '0700.0300.0500', 3, '05/02/2012', 'closed', 1003),
('Buckwheat', '0700.0300.0500', 4, '05/10/2012', 'resolved', 1004),
('Meteorite', '0500.0400.0300', 1, '02/10/2012', 'active', 1005),
('Meteorite', '0500.0400.0300', 2, '01/05/2012', 'proposed', 1006),
('Meteorite', '0500.0400.0300', 3, '02/06/2012', 'closed', 1007),
('Meteorite', '0500.0400.0300', 4, '03/15/2012', 'resolved', 1008);
I have tried to run the VBA code below. The first subroutine (Sub Report_Click()) executes successfully and populates the data worksheet but second subroutine (Sub CreatePivot()) fails. It's the second one that is supposed to create the pivot table, I get error 'Method 'PivotTableWizard' of object'_Worksheet' failed....referring to this line: Set objTable = Sheet1.PivotTableWizard
Option Explicit
Sub Report_Click()
'get the data
Dim con As New Connection
Dim rs As New Recordset
Dim strSQL As String
con.Open "Provider=SQLOLEDB;Data Source=V-HELKR032112;Initial Catalog=Sandbox;Integrated Security=SSPI;"
strSQL = "select " & _
" Application " & _
" , Build " & _
" , Severity " & _
" , date_created " & _
" , system_state " & _
"from BugTable " & _
" where Application = '" & ActiveSheet.Range("B6") & "'" & _
" and Build = '" & ActiveSheet.Range("B7") & "'"
rs.Open strSQL, con, adOpenStatic, adLockReadOnly
'delete the old data
Sheets("Data").Range("A2:A7000").EntireRow.Delete
'insert the data
Sheets("Data").Range("A2").CopyFromRecordset rs
'update the named range
ActiveWorkbook.Names("SQLData").RefersToR1C1 = "=Data!R1C1:R" & rs.RecordCount + 1 & "C4"
'cleanup
rs.Close
con.Close
End Sub
Sub CreatePivot()
' Creates a PivotTable report from the table on Sheet1
' by using the PivotTableWizard method with the PivotFields
' method to specify the fields in the PivotTable.
Dim objTable As PivotTable, objField As PivotField
' Select the sheet and first cell of the table that contains the data.
ActiveWorkbook.Sheets("Data").Select
Range("A2").Select
' Create the PivotTable object based on the Employee data on Sheet1.
Set objTable = Sheet1.PivotTableWizard
' Specify row and column fields.
Set objField = objTable.PivotFields("State")
objField.Orientation = xlRowField
Set objField = objTable.PivotFields("Date")
objField.Orientation = xlColumnField
' Specify a data field with its summary
' function and format.
Set objField = objTable.PivotFields("Severity")
objField.Orientation = xlDataField
objField.Function = xlCount
' Preview the new PivotTable report.
ActiveSheet.PrintPreview
' Prompt the user whether to delete the PivotTable.
Application.DisplayAlerts = False
If MsgBox("Delete the PivotTable?", vbYesNo) = vbYes Then
ActiveSheet.Delete
End If
Application.DisplayAlerts = True
End Sub
In the way of summary, I have two worksheets:
(1) a Report worksheet which contains the two fields which accept parameters in cells B6 and B7
(2) a Data worksheet which contains the data starting in cell A2. Cell A1 and the row across contains headers: Application, Build, Severity, Date_Created, System_state. This worksheet populates when I run just the first sub routine.
My hope was to run the subroutine microsoft provided after the subroutine I got from experts exchange, one after another. But that does not work.
The only way I can get the microsoft subroutine to work is if I run it first. How to join the functionality of each into one harmoniously flowing code?
--Quote me
May 21, 2012 at 2:44 am
Hi,
I think this is because the line expects your "Data" worksheet to be Sheet1 in the excel project - changing "Sheet1.CreatePivotTable" to "ActiveWorkbook.Sheets("Data").CreatePivotTable" should resolve the issue, by making it not reliant on the order in which you named the sheets when you built your file...
Cheers
May 21, 2012 at 11:27 am
yes that did it.
I realize I have a lot I'm going to have to learn on my own, which is why I am reading VB for Dummies. I plan to change the bar chart type to piechart for example...and I want to have the report appear in the Report worksheet, not a new worksheet....
Could you help me with just one more thing. Is it possible to generate multiple reports with the click of one button, using same two parameters?
--Quote me
May 22, 2012 at 2:26 am
Hi, it's all possible...
Each time you call the PivotTableWizard it adds a new sheet (with a new pivottable on it), so if you want the pivot to appear on a specific sheet, you either need to move it after you call the pivottablewizard (good old copy-paste would work), or, you could always use the method in my previous post 😀 - use the CreatePivotTable method of the pivotcache object - one of the parameters is a destination range.
If it's possible in your environment then you should think about using a template - you'd build this manually up front with as many sheets, pivots, charts, management summary sheet, company logos, page setup so it's looks great printed, etc etc etc... all with no overhead having to learn more vba.
The only 'rule' is that all the Pivottables in the template should point to your "SQLData" named range... then your code would look something like this...
Dim myTemplate as Workbook
'open the template...
set myTemplate = Workbooks.Add("c:\temp\bugreport.xlt")
'dump the data into it...
myTemplate.Sheets("Data").Range("A1").CurrentRegion.Offset(1,0).EntireRow.Delete
myTemplate.Sheets("Data").Range("A1").offset(1,0).CopyFromRecordset rs
'update the named range...
myTemplate.Names("SQLData").RefersTo = "=Data!" & myTemplate.Sheets("Data").Range("A1").CurrentRegion.Address
'update pivots etc to look at new data...
myTemplate.RefreshAll
If you can't use a template then you'll have to learn quite a lot more vba to create and interact with charts and pivots, add formatting, logos, page setup, etc...
To create multiple reports with one click... you just need to call the right bits of the code multiple times! [- I'm not entirely sure I understand what you mean by this - multiple pivots, or sheets, or files?]
Cheers
May 28, 2012 at 4:39 am
SoberCounsel
Each time you call the PivotTableWizard it adds a new sheet (with a new pivottable on it), so if you want the pivot to appear on a specific sheet, you either need to move it after you call the pivottablewizard (good old copy-paste would work), or, you could always use the method in my previous post - use the CreatePivotTable method of the pivotcache object - one of the parameters is a destination range.
At the time you provided the code I didn't understand how your code was creating a pivot table and chart and I didn't recognize that you were using CreatePivotTable method of the pivotcache object. Specifically was thrown off by comment 'at this point you should manually edit the pivot table that's just been created (one time only!) - when you're done comment out the lines that created it and added the fields.
This is why I forged ahead with PivotTableWizard method, not knowing its limitations (secondary worksheets). I would like to recover from that fumble. I *do* want pivot to appear on a specific sheet!!
Here's what you gave me, but I didnt utilize properly:
Sub Report_Click()
Dim rs As New Recordset
Dim strSQL As String
Dim aQueryTable As QueryTable, aPivotTable As PivotTable
strSQL = "select " & _
" Application " & _
" , Build " & _
" , Severity " & _
" , date_created " & _
" , system_state " & _
"from BugTable " & _
" where Application = '" & ActiveSheet.Range("B6") & "'" & _
" and Build = '" & ActiveSheet.Range("B7") & "'"
Set rs = GetRecordSet(strSQL, , , "Driver={SQL Server};Server=sqllpb2dbl54201.global.lloydstsb.com\inst_08,14338;Database=test;trusted_connection=yes")
'get rid of existing data...
Range("A10").CurrentRegion.EntireRow.Delete
Dim xlName As Name
For Each xlName In ThisWorkbook.Names
If xlName.Name = "Results" Then xlName.Delete
Next
'pipe in new data
Set aQueryTable = ActiveSheet.QueryTables.Add(rs, Range("A10"))
aQueryTable.Refresh
aQueryTable.EnableEditing = False
'add Results name, then create a pivot table (which is defined to look at Results)
ThisWorkbook.Names.Add "Results", "=" & Range("A10").CurrentRegion.Address
'this creates a new pivottable called PivotResults, and adds a couple of fields
Set aPivotTable = ThisWorkbook.PivotCaches.Add(xlDatabase, "Results").CreatePivotTable(Range("E2"), "PivotResults")
aPivotTable.AddFields "Severity", "system_state"
aPivotTable.AddDataField aPivotTable.PivotFields("Application"), "Bugs", xlCount
'at this point you should manually edit the pivot table that's just been created (one time only!) - when you're done comment out the lines that created it and added the fields
'this updates an existing pivottable called PivotResults
ActiveSheet.PivotTables("PivotResults").PivotCache.Refresh
End Sub
Function GetRecordSet(strSQL As String, Optional strConnection As String, Optional strServer As String, Optional strDB As String) As ADODB.Recordset
'Returns a recordset...
'Declare Variables
Dim strConn As String, adoCN As ADODB.Connection, adoRS As ADODB.Recordset
'Error handle...
On Error GoTo ErrorHandler
'Build Connection String
If strConnection = "" Then
strConn = "Driver={SQL Server};Server=" & IIf(strServer = "", strDefaultServer, strServer) & ";Database=" & IIf(strDB = "", strDefaultDatabase, strDB) & ";"
Else
strConn = strConnection
End If
OpenCN:
'Open Connection
Set adoCN = New ADODB.Connection
adoCN.CommandTimeout = 10
adoCN.Open strConn
'Open Recordset
OpenRS:
Set adoRS = New ADODB.Recordset
With adoRS
.CursorType = adOpenStatic
.CursorLocation = adUseClient
Do Until .State = 1
.Open strSQL, adoCN, adOpenForwardOnly, adLockReadOnly
Loop
End With
'Close Connection
adoRS.ActiveConnection = Nothing
adoCN.Close
Set adoCN = Nothing
'Return recordset to calling procedure
Set GetRecordSet = adoRS
'Finish up and exit
Exit Function
ErrorHandler:
'you should probably add some here!
End Function
Here's where I'm at (below). If I substitute it with the above then I get an error with aQueryTable.Refresh method.
Option Explicit
Sub Report_Click()
'get the data
Dim con As New Connection
Dim rs As New Recordset
Dim strSQL As String
con.Open "Provider=SQLOLEDB;Data Source=V-HELKR032112;Initial Catalog=Sandbox;Integrated Security=SSPI;"
strSQL = "select " & _
" Application " & _
" , Build " & _
" , Severity " & _
" , date_created " & _
" , system_state " & _
"from BugTable " & _
" where Application = '" & ActiveSheet.Range("B6") & "'" & _
" and Build = '" & ActiveSheet.Range("B7") & "'"
rs.Open strSQL, con, adOpenStatic, adLockReadOnly
'delete the old data
Sheets("Data").Range("A2:A7000").EntireRow.Delete
'insert the data
Sheets("Data").Range("A2").CopyFromRecordset rs
'update the named range
ActiveWorkbook.Names("SQLData").RefersToR1C1 = "=Data!R1C1:R" & rs.RecordCount + 1 & "C4"
'cleanup
rs.Close
con.Close
End Sub
Sub CreatePivot()
' Creates a PivotTable report from the table on Sheet1
' by using the PivotTableWizard method with the PivotFields
' method to specify the fields in the PivotTable.
Dim objTable As PivotTable, objField As PivotField
' Select the sheet and first cell of the table that contains the data.
ActiveWorkbook.Sheets("Data").Select
Range("A2").Select
' Create the PivotTable object based on the Employee data on Data worksheet
Set objTable = ActiveWorkbook.Sheets("Data").PivotTableWizard
' Specify row and column fields.
Set objField = objTable.PivotFields("State")
objField.Orientation = xlRowField
Set objField = objTable.PivotFields("Date")
objField.Orientation = xlColumnField
' Specify a data field with its summary
' function and format.
Set objField = objTable.PivotFields("Severity")
objField.Orientation = xlDataField
objField.Function = xlCount
' Preview the new PivotTable report.
ActiveSheet.PrintPreview
' Prompt the user whether to delete the PivotTable.
Application.DisplayAlerts = False
If MsgBox("Delete the PivotTable?", vbYesNo) = vbYes Then
ActiveSheet.Delete
End If
Application.DisplayAlerts = True
End Sub
---the DDL
use sandbox
go
create table BugTable
(application nvarchar(30)
, build nvarchar(30)
, severity int
, date_created datetime
, system_state nvarchar(50)
, bug_id int)
insert into BugTable
values
('Buckwheat', '0700.0300.0500', 1, '04/07/2012', 'active', 1001),
('Buckwheat', '0700.0300.0500', 2, '03/01/2012', 'proposed', 1002),
('Buckwheat', '0700.0300.0500', 3, '05/02/2012', 'closed', 1003),
('Buckwheat', '0700.0300.0500', 4, '05/10/2012', 'resolved', 1004),
('Meteorite', '0500.0400.0300', 1, '02/10/2012', 'active', 1005),
('Meteorite', '0500.0400.0300', 2, '01/05/2012', 'proposed', 1006),
('Meteorite', '0500.0400.0300', 3, '02/06/2012', 'closed', 1007),
('Meteorite', '0500.0400.0300', 4, '03/15/2012', 'resolved', 1008
Will you please help me convert from my method to yours? If possible can we stay with seperate sub routines for retrieving data, creating pivottable, and creating pivotchart?
--Quote me
July 13, 2012 at 2:52 am
Was banging around on a few older topics I'd gotten involved in and ran into this one with a bit of.. emphasis.
My comments lie below:
SoberCounsel (5/17/2012)
Hi,Polkadot, I think we're both at risk of being lambasted by the "sql-only gurus" :unsure:
When all you have is a hammer: http://tvtropes.org/pmwiki/pmwiki.php/Main/WhenAllYouHaveIsAHammer
I'm as guilty as anyone else for this trope. Believe it or not, half of my discussion in scrum meetings is "Give that to George, it's better in code!" as often as "The hell?! Nononono, that's mine. Proc's name will be.... errr... "dbo.meeting_212_41_itsbetterhere".
Phew, hopefully that's staved off some abuse!! ( I got some on another thread btw, I think because the advice I offered didn't tow the sql-only line! )
Please fire me off a PM with that link. I'm curious who was involved. Just a PM, please, though. I've got enough recent political headaches.
... a lot of truth about SQL ONLY KILL ALL INVADERS ...
In case the above wasn't an obvious, that's a paraphrase. Sober makes a good point about newer SQL people thinking they're a one stop shop for all solutions. While SQL can create, roughly, almost any solution, it's not always the best choice.
Polkadot - good luck with your endeavours... "sql-only gurus" with a gripe at this advice, bring it on 😎 !!!
Sober, I'm sorry of your... opinion... of this board. I'm not going to go so far as to say it's unearned though, I'm certainly as guilty as others. However, if you come to warhammer school to learn spearcraft you're going to end up with a very confused dwarf.
However, I hope that none of our local gurus would tear into you for offering effective and intelligent advice (as discussed above) as a performant alternative. That's not saying you wouldn't see code immediately following said advice that explained how to do it in SQL (I've got my hammer!), but using the correct tool... I'd hope... wouldn't earn you a scold here.
PM me if you've found otherwise, please... and while we've got a few new folks who are exemplifying what we'd hope to see (Rock from VbCity comes to mind), in general I'd recommend taking advice from newer folks with a grain of salt... and even older ones. I'm a perfect example. Gail Shaw and Paul White still correct my bad advice from time to time, and I've theoretically been kicking around for a while!
aaaaannnnddd... Sorry for the Necro, but that was something I felt needed addressing.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 15 posts - 46 through 59 (of 59 total)
You must be logged in to reply to this topic. Login to reply