May 11, 2012 at 9:50 am
polkadot (5/11/2012)
Time is the issue. I am on this forum to ask someone who is experienced with SSRS if they know the answer. I am not asking for them to write the solution for me but whether that is the tool I can go to and KNOW that it will support the functionality I described.
Tried getting someone here who does know more.
Even if this person were here and said it would work, you would still need to do the research to understand and develop a solution. I have made a suggestion based on research I had done years ago at another comapny as we looked at both RS and SharePoint. I don't have access to that research beyond my vague memories.
From what I recall, this is an alternative you should pursue, and the research necessary still needs to be done by you.
May 11, 2012 at 1:03 pm
polkadot (5/11/2012)
Users will open a Word template which will allow for free text summary of project status. It should also contain two fields each of which accepts a parameter which after entered should return to the word document a report which summarizes the status of that project as of that moment, along with a chart.
This is twitch #1. I hope you are handling proc access via windows group security. Anything else is going to be painful.
I... yikes. There is no 'elegant' solution to this without being a .NET, VBA, and SQL developer all at the same time. I certainly couldn't code this up inside of a week anymore, my skills in VBA have gone rusty.
As you've noticed, that 'freeform' component really hoses you up, otherwise generating the SSRS report and letting them fire it out as a word doc would basically do what you need. The fact that you're trying to pass in parameters FROM Word, through SQL, into Excel and then posting the add-in back to word means that I'm basically out of my depth here without research, sorry Polkadot.
I know you can access the add-in controls from the outer document and VBA code the inner component to accept different datapoints/information, which is where it sounds like you'll need to go with this. I however would have fought this tooth and nail for their timeline so I could have gotten in the research. The business is daydreaming about 'how easy it is' because they can easily copy/paste a chart into a word document and move on with their lives. Via code, that's not so simple. Windows did a lot of work to MAKE it simple to a user.
This is going to require some user intervention in the solution if you want it done in a week, and I can't see a single 'elegant' solution that I personally know how to code.
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
May 11, 2012 at 2:03 pm
I've been thinking this issue over and to be honest, I think you're asking the right question in the wrong place.
We're SQL guys/Database Dudes. If you wanted to discuss the connection to the database and how to get the data back to where you wanted to use it, you're in the right place. If you wanted to discuss how to use presented tools from the database (like SSRS) and what they can/can't do and how to get close to what you want, again, right place.
For full on VBA/VB.NET code customization... this ain't the right place.
I'd recommend you start on one of the coding sites that can assist you with this, in particular probably Experts Exchange, though they can be a bit of a PITA. But really, you're asking a chef how to build a cabinet. He may have done it once or twice but you're inquiring of the wrong expert.
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
May 11, 2012 at 2:14 pm
I would urge you to look around the SSRS forum here as well. I have not used Word in any BI project thus far.
I don't understand why project status has to be in this Word template. If it lived in a database, it would be a lot easier to access from many end user tools.
Sorry you've been shafted on timeframe but you've been handed some very difficult requirements and I would suggest presenting them with a reasonable design that would also be maintainable.
May 11, 2012 at 3:55 pm
Here the summary of my own research on this topic so far:
For EXCEL MACRO BASED SOLUTION I need
(1)Capability of VBA to connect to SQL Server (script)
(2)VBA script to pass parameters to and trigger a SP
(3)VBA script to take result and pass it to Excel and give instructions for formatting report and chart
(4)VBA script to relay pivot chart and table to Word
Cons:
I don't know VBA. Macros create vulnerabilities, anyone who can open the document containing the macro could alter it and have it do malicious things.
ALTERNATIVES:
(1)SSRS
I would like to learn it. To be examined this weekend
(2)Add-In
Cons: requires lots of development. Requires installer packages (create classes, libraries to connect to SQL). This object becomes a permanent part of Excel for any user that installs it. Performance is impacted during loading, closing, etc. of document. Installer has to be hosted somewhere (some SP website where there’s a link) and has to been
I am going the SSRS route. If anyone still wants to pipe up about SSRS (specifically) I would appreciate. SSRS will take me a dedicated week to learn in a general way. I would like to hone in on the piece of it that contains the solution for this problem.
Thank you so much Evil Kraig and to all advice so far.
--Quote me
May 11, 2012 at 4:27 pm
polkadot (5/11/2012)
I am going the SSRS route. If anyone still wants to pipe up about SSRS (specifically) I would appreciate. SSRS will take me a dedicated week to learn in a general way. I would like to hone in on the piece of it that contains the solution for this problem.Thank you so much Evil Kraig and to all advice so far.
I'm afraid there might be a confusion here, so I just want to make sure this is clarified:
SSRS is a hosted solution. It's not something you shove into a word document, it's something you run on a server (and need IIS as well if you're on 2k5), and connect to via a web-browser, even interally at the company. However, from the server your users can create their own LOCAL Word documents/Excel Spreadsheets/etc. You're going to approach this completely differently than you'd expect.
If you're going to do a little weekend work, you'll need a development copy of SQL Server to bring up an SSRS instance on your box. You'll really want to experiment with it.
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
May 11, 2012 at 4:37 pm
And don't forget that Reporting Services can also be integrated with your SharePoint site.
May 12, 2012 at 6:42 pm
So, it's SSRS + weekend (and believe me it's a really uncharacteristically beautifully sunny one).
Still the key question is....can parameters be passed to SSRS (from Word or Excel) for a semi customized report....without using VBA?
--Quote me
May 12, 2012 at 7:41 pm
polkadot (5/12/2012)
So, it's SSRS + weekend (and believe me it's a really uncharacteristically beautifully sunny one).Still the key question is....can parameters be passed to SSRS (from Word or Excel) for a semi customized report....without using VBA?
Forget it. You are obsessed with using Word for this project, use Word.
We are saying to use SSRS instead of Word.
May 14, 2012 at 6:09 am
Lynn Pettis (5/12/2012)
polkadot (5/12/2012)
So, it's SSRS + weekend (and believe me it's a really uncharacteristically beautifully sunny one).Still the key question is....can parameters be passed to SSRS (from Word or Excel) for a semi customized report....without using VBA?
Forget it. You are obsessed with using Word for this project, use Word.
We are saying to use SSRS instead of Word.
....And also obsessed with users entering freeform text BEFORE the report is actually rendered.
RS can take the parameters, and after they export to the desired format (a click away), they can enter all the freeform text they wish.
Do a sample and see if it is acceptable to the users.
Then justify the cost for the other way if it is not.
To do as you suggest, without a lot of experience in coding in VBA, in a short time frame, will be very difficult.
May 14, 2012 at 6:01 pm
I presented status update to manager.
Since currently reporting is via PowerPivot on Sharepoint it is just as easy to supply link to that as to SSRS. Filter boxes permit 'passing parameters' and report can be opened in Excel from where it can be cut/pasted.
Unless someone shares with me some very similar VBA code (on experts exchange) that I can tweak, there will be no passing params to SQL Server and back to Word, at this point.
Certainly learned a lot about the landscape. Thanks for all the feedback.
--Quote me
May 15, 2012 at 2:37 pm
Oh good, it sounds like you get to travel a better path!! Please post back on how it goes.
May 16, 2012 at 4:40 am
Hey Polkadot,
I have an excel VBA based reporting tool that does what you need - accepts user parameters, fires off a query to a sql database, gets a recordset back, then pipes the data into another excel template (that offers additional functionality, pivottables graphs etc).
The main bit you'll need is a "GetRecordset" function that you'll feed a sql statement to to get a recordset - which you can then easily dump into a worksheet, or pipe it into a pivot table/graph etc.
Not sure how familiar you are with VBA so I'll try to keep it simple, sorry if I'm pitching at the wrong level...
1. Open Excel, then press alt-F11 to open the integrated VBA editor.
2. From the Tools menu (in the vba editor), choose References, then scroll down and tick "Microsoft ActiveX Data Objects 2.x Library" (i usually use 2.1 - it's old, but so are the machine builds where I work!)
3. From the Insert menu, choose Module, then add the following:
'Change these to your server details...
Global Const strDefaultServer = "YourServer\Instance,Port"
Global Const strDefaultDatabase = "YourDatabase"
Function GetRecordSet(strSQL As String, Optional strDB As String, Optional strServer 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
'assumes authenticated security...
strConn = "Driver={SQL Server};Server=" & iif(strServer="",strDefaultServer,strServer) & ";Database=" & iif(strDB="",strDefaultDatabase,strDB) & ";"
' ... or you could specify a userid and password:
' strConn = "Driver={SQL Server};Server=" & iif(strServer="",strDefaultServer,strServer) & ";Database=" & iif(strDB="",strDefaultDatabase,strDB) & ";Uid=YourUserID;Pwd=YourPassword;"
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
Sub TestIt()
Dim myRecordset As New ADODB.Recordset
Dim sUserParameter As String
' this is where you would use a form, input box, or just grab some data from a cell...
sUserParameter = "Hello World..."
'you usually need to clean up a bit...
sUserParameter = Replace(sUserParameter, "'", "''")
'then you can easily call a procedure...
Set myRecordset = GetRecordSet("exec uspYourProc @Parameter = '" & sUserParameter & "'")
'or just fire off a select statement...
Set myRecordset = GetRecordSet("select * from SomeTable where SomeField = '" & sUserParameter & "'")
'... and once you've got your recordset you can pipe it into a QueryTable or a PivotTable... something like this...
Dim aPivot As PivotTable, aQueryTable As QueryTable
Set aPivot = ThisWorkbook.ActiveSheet.PivotTables(0)
Set aPivot.PivotCache.Recordset = myRecordset
aPivot.RefreshTable
Set aQueryTable = ThisWorkbook.ActiveSheet.QueryTables(0)
Set aQueryTable.Recordset = myRecordset
aQueryTable.Refresh
End Sub
Hope this helps...
Cheers
May 16, 2012 at 12:22 pm
Wow Wow Wow. Standby.
:kiss::kiss::kiss::kiss::kiss::kiss::kiss::kiss::kiss::kiss:
--Quote me
May 17, 2012 at 8:48 am
Hi,
Polkadot, I think we're both at risk of being lambasted by the "sql-only gurus" :unsure: - if so, before that starts 😀 let me say that the vba code I provided, and the requirements of the solutions where it might be used, in my experience, can be better met using sql-only tools - SSRS and SSAS. 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! )
The problem with a "sql-only" approach, is that many "sql-only gurus" understand the ideal/optimal "sql-only" solution well, but seem to have little understanding of any alternatives- i.e. what to do where austerity rules; where there is little or no investment available for better infrastructure (hardware/software)? - so, you're stuck with your old environment and tools, but "management" still needs better ways to understand their business in real-time.
Perhaps it's time for me to look for alternative employment?... possibly - any offers?? 😀
Or perhaps it's time to focus on what is apparently a niche-market!!? ... in experienced hands, VBA can achieve most if not all that SSRS and SSAS can - and in fact it can be (...or rather, has been for me...) a good way of splitting work up between database guys and business users - get the business users to build their own report templates (in excel, where many business users have a certain level of "expertise"), and the database guys to sort out the stuff they're good at - managing the data that is ultimately piped into the templates that the business users set up.
I'd love to have the most up-to-date tools/versions etc to build with... however not having them doesn't mean I'll stop what I'm doing until they turn up!
Polkadot - good luck with your endeavours... "sql-only gurus" with a gripe at this advice, bring it on 😎 !!!
Viewing 15 posts - 31 through 45 (of 59 total)
You must be logged in to reply to this topic. Login to reply