June 12, 2015 at 12:51 pm
Sean Lange (6/12/2015)
No you don't. Create a view in sql server and have excel select from your view and add a where clause with whatever dates you want at run time.
I tried that, by creating an identical view (without date ranges) to return ALL the initial data.
However, when I try to do a parameter query in excel, it doesn't "group" and "sum" correctly, and I end up with thousands of records, instead of the 291 that my original view returns.
June 12, 2015 at 12:54 pm
I appreciate the helpful replies.
Stored procedures aren't much help, because I can't directly access them through Excel (only tables and views).
So, I would still have to take the results of the stored procedure and copy/paste/format them into Excel.
June 12, 2015 at 12:56 pm
Lynn Pettis (6/12/2015)
Are the dates user selectable or are they determined by when the query is run?
user selectable
June 12, 2015 at 1:04 pm
tsobiech (6/12/2015)
I appreciate the helpful replies.Stored procedures aren't much help, because I can't directly access them through Excel (only tables and views).
So, I would still have to take the results of the stored procedure and copy/paste/format them into Excel.
Why do you think you can't access a stored procedure from Excel? Since you say the dates are user selectable a stored procedure is EXACTLY what you want.
create procedure MyProcedure
(
@StartDate date,
@EndDate date
) as
SELECT dbo.[!Act32CountyInfo].SubmittersEIN
,dbo.[!Act32CountyInfo].LocalAccountNumber
,dbo.[!Act32CountyInfo].WorkPSD
,dbo.[!Act32CountyInfo].TaxYear
,dbo.[!Act32CountyInfo].Period
,HR.EmployeeDemographics.EmployeeSSN AS [Social Security Number]
,dbo.[!Act32EmpName].LastName AS [Employee Last Name]
,dbo.[!Act32EmpName].FirstName AS [Employee First Name]
,dbo.[!Act32EmpName].MiddleName AS [Employee Middle Initial]
,dbo.[!Act32EmpAddInfo].AddressLine1 AS [Location Address]
,dbo.[!Act32EmpAddInfo].AddressLine2 AS [Delivery Address]
,dbo.[!Act32EmpAddInfo].City
,dbo.[!Act32EmpAddInfo].EntryValue AS [State Abbreviation]
,dbo.[!Act32EmpAddInfo].ZipCode AS [Zip Code]
,SUM(dbo.[!Act32EITPayrollTaxALL].GrossAmount) AS [Taxable Wages]
,SUM(dbo.[!Act32EITPayrollTaxALL].TaxAmount) AS [Tax Withheld]
,dbo.[!Act32TaxHeader].ACT32number AS [Residence PSD]
FROM dbo.[!Act32TaxHeader]
INNER JOIN dbo.[!Act32CountyInfo]
ON dbo.[!Act32TaxHeader].CompanyID = dbo.[!Act32CountyInfo].CompanyID
RIGHT OUTER JOIN dbo.[!Act32EITPayrollTaxALL]
ON dbo.[!Act32TaxHeader].TaxHeaderID = dbo.[!Act32EITPayrollTaxALL].TaxHeaderID
LEFT OUTER JOIN HR.EmployeeDemographics
ON dbo.[!Act32EITPayrollTaxALL].EmployeeID = HR.EmployeeDemographics.EmployeeId
LEFT OUTER JOIN dbo.[!Act32EmpName]
ON dbo.[!Act32EITPayrollTaxALL].EmployeeID = dbo.[!Act32EmpName].EmployeeId
LEFT OUTER JOIN dbo.[!Act32EmpAddInfo]
ON dbo.[!Act32EITPayrollTaxALL].EmployeeID = dbo.[!Act32EmpAddInfo].EmployeeId
WHERE(dbo.[!Act32EITPayrollTaxALL].ChangedDate >= @startDate)
AND (dbo.[!Act32EITPayrollTaxALL].ChangedDate <= @endDate)
GROUP BY HR.EmployeeDemographics.EmployeeSSN
,dbo.[!Act32EmpName].LastName
,dbo.[!Act32EmpName].FirstName
,dbo.[!Act32EmpName].MiddleName
,dbo.[!Act32EmpAddInfo].AddressLine1
,dbo.[!Act32EmpAddInfo].AddressLine2
,dbo.[!Act32EmpAddInfo].City
,dbo.[!Act32EmpAddInfo].EntryValue
,dbo.[!Act32EmpAddInfo].ZipCode
,dbo.[!Act32TaxHeader].ACT32number
,dbo.[!Act32CountyInfo].SubmittersEIN
,dbo.[!Act32CountyInfo].LocalAccountNumber
,dbo.[!Act32CountyInfo].WorkPSD
,dbo.[!Act32CountyInfo].TaxYear
,dbo.[!Act32CountyInfo].Period
Then all you need to do in Excel is figure out where the values for the parameter come from, add a button and a little bit of VBA to call your procedure.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 12, 2015 at 1:10 pm
Sean Lange (6/12/2015)
Why do you think you can't access a stored procedure from Excel?
Because when I connect to my SQL database through excel, I only see tables and views.
June 12, 2015 at 1:18 pm
tsobiech (6/12/2015)
Sean Lange (6/12/2015)
Why do you think you can't access a stored procedure from Excel?Because when I connect to my SQL database through excel, I only see tables and views.
Ahh gotcha. You aren't using VBA code, you are just using the visual tool. You absolutely can access a stored procedure from Excel. Are you familiar with VBA?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 12, 2015 at 1:19 pm
Sean Lange (6/12/2015)
Are you familiar with VBA?
Just enough to be dangerous, because I don't use it, everyday. 🙂
June 12, 2015 at 1:21 pm
Or you could use SSRS to create a nice report with parameters that can be exported to Excel, Word, PDF & others.
The Stairway to Reporting Services will guide you step by step. http://www.sqlservercentral.com/stairway/72382/
By the way, try to alias your tables on your query to avoid having lengthy code and prevent problems in a future version of SQL Server because using three-part and four-part column references is deprecated.
June 12, 2015 at 1:37 pm
Take a look at the link below. It should provide most of the information you need to do this from Excel.
http://realworldsql.com/2012/06/07/stored-procedures-with-parameters-in-excel/
Edit: oops. I didn't see the Luis' response before posting mine.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
June 12, 2015 at 1:49 pm
Luis Cazares (6/12/2015)
Or you could use SSRS to create a nice report with parameters that can be exported to Excel, Word, PDF & others.The Stairway to Reporting Services will guide you step by step. http://www.sqlservercentral.com/stairway/72382/
Thanks! But, Report Builder and BIDS were not installed with my version of SQL Studio. (I'm working on that with the IT guy right now) 🙂
June 12, 2015 at 1:53 pm
tsobiech (6/12/2015)
Luis Cazares (6/12/2015)
Or you could use SSRS to create a nice report with parameters that can be exported to Excel, Word, PDF & others.The Stairway to Reporting Services will guide you step by step. http://www.sqlservercentral.com/stairway/72382/
Thanks! But, Report Builder and BIDS were not installed with my version of SQL Studio. (I'm working on that with the IT guy right now) 🙂
I hope that you get it, it's a good tool that shouldn't require an additional license.
June 12, 2015 at 2:16 pm
Alvin Ramard (6/12/2015)
Take a look at the link below. It should provide most of the information you need to do this from Excel.http://realworldsql.com/2012/06/07/stored-procedures-with-parameters-in-excel/
Thanks for the link!
However, when I follow those instructions - it never prompts me for parameters.
I get an error message saying "No value given for one or more required parameters"
Also, I can't get the "SQL" setting to "stick" - it keeps defaulting back to command type "table."
This is very frustrating, because this FEELS like the solution I need, I just can't get it to work!
June 12, 2015 at 2:27 pm
tsobiech (6/12/2015)
Alvin Ramard (6/12/2015)
Take a look at the link below. It should provide most of the information you need to do this from Excel.http://realworldsql.com/2012/06/07/stored-procedures-with-parameters-in-excel/
Thanks for the link!
However, when I follow those instructions - it never prompts me for parameters.
I get an error message saying "No value given for one or more required parameters"
Also, I can't get the "SQL" setting to "stick" - it keeps defaulting back to command type "table."
This is very frustrating, because this FEELS like the solution I need, I just can't get it to work!
Can you show me the SQL statement you're trying to execute?
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
June 12, 2015 at 2:31 pm
Alvin Ramard (6/12/2015)
Can you show me the SQL statement you're trying to execute?
exec Act32EITReport ?,?
June 12, 2015 at 3:13 pm
Time for Excel to get an upgrade!
Trying to do this without VBA is getting to be a pain in the ...
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
Viewing 15 posts - 16 through 30 (of 30 total)
You must be logged in to reply to this topic. Login to reply