Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Create a report with data from SQL server and SharePoint List

Posted on 2013-06-05
5
Medium Priority
?
369 Views
Last Modified: 2013-07-29
I've been given the task of recreating a word document into a report.  The document is a closeout report that is made at the end of a contract/project.  Much of the information is stored in our database but some of it would be entered manually from Project Managers or other staff near/at the end of the contract.  

We are using SharePoint 2010 Foundation, MS SQL 2008 R2 and Office 2010.

Here is example of first page:
Example of closure reportIt has a mix of data, some are currently not being housed in our database.  We can create custom tables/fields to store this information in our current system or we can create a new database tailored for us specifically (where our old one was developed for our time entry/project management software) or we can save the information in lists or Access (though I'm not keen on the last one).  We want to encourage our staff to utilize SharePoint so I think a list would be best.  

What would be the best way to set this up?  I can call and display reports I've developed in SSRS but there is no way for those reports to produce textboxes or input controls for users to input information and the report to store it.  I thought about making parts of the document in smaller reports and having them display in web parts that way I can put input controls in web parts between, making it look like one report when its a collection of smaller parts.  Not sure how efficient that would be.  We have InfoPath but SharePoint Foundation doesn't let us use InfoPath for online forms, which sucks.  I'd prefer to have the report displayed within SharePoint and have parts of it editable rather than have a link go to another page entirely to update information.  Actually I'd love to create the page in Dreamweaver and have it pull data and populate fields, as that is what I'm used to, but sadly that isn't an option.

Can anyone please offer up some ideas of how to approach this?  
It would be greatly appreciated!!!
0
Comment
Question by:HSI_guelph
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 28

Accepted Solution

by:
clayfox earned 2000 total points
ID: 39222587
You could create an InfoPath form which could consolidate the data sources and save the info to SP, but not sure you have that.

otherwise I would create a list for the data not in SQL and then have a separate report that consolidates it all into the final report.

not sure with foundation if you have InfoPath to edit the list entry form but if so you could also have it query and display the SQL data too and maybe get what you want.
0
 

Author Comment

by:HSI_guelph
ID: 39222601
We have InfoPath but integration with SP is not available for Foundation :(
0
 
LVL 28

Expert Comment

by:clayfox
ID: 39222628
You can still use InfoPath in several ways.

create a form which integrates with SQL and collects the rest. Then save the form to a library and promote the fields.

end result is about the same.
0
 

Author Comment

by:HSI_guelph
ID: 39222655
We have a Safari account and access to this book http://my.safaribooksonline.com/book/operating-systems-and-server-administration/microsoft-sharepoint/9780735647862 so maybe that will help but we don't have a sandbox environment so I'm hesitant to experiment too much.  It has a chapter on InfoPath.  I've thought about installing SharePoint on a float laptop we have at work but it won't access our server so I'm not sure if it will be an effective learning tool.
0
 

Author Closing Comment

by:HSI_guelph
ID: 39364625
Implementing InfoPath for another project so I'll try it for this one as well.  Thanks for the assist!  Didn't think InfoPath would work but all our Office versions include InfoPath so I can use it with Foundation.
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question