April 12, 2010 at 11:26 am
Hi everyone
Let me start by saying that I have absolutely no budget at this time to purchase additional software. Let me also list the current software I have available to use:
SQL Server 2000 Standard
SQL Server 2008 Standard
Operations Manager 2005
Biz Talk Server 2004 Enterprise & Developer
Sharepoint Portal Server 2001
Speech Server 2004
Exchange Server 2003
Systems Management Server 2003
Visual Studio 2002, 2003, 2005 & 2008
Commerce Server 2002 Enterprise
Live Communications Server 2005 Enterprise
Identity Integration Server 2003
Content Management Server 2002 Enterprise
Host Integration Server 2004 Enterprise
Application Centre 2000
Class Server 3.0
I realize many of these may not apply to what I'm about to ask, but I figured I'd flip through my disks and just list them all.
So, we have a customer database of about 80,000 Representatives, of which, about 40,000 are active in any given year. At this time, we do not have a portal for them to be able to run reports, or get invoice / credit copies themselves. They do have a backoffice for e-commerce and order history, but reporting is lacking. Our software provider wants far too much to build the reporting option into the existing backoffice.
At this time, I do the reporting. I have all of the reports setup in sql server and on a quarterly basis I run the reports, and use a mail merge plugin and custom PDF form to email them each report through Adobe Acrobat Pro. Because of the number of Reps, I had to restrict this to anyone who had sold over a specific $ per quarter, which was approximately 15,000 Reps in 2009. You can imagine how many days this took.
So, what would you do? How would you go about building a portal where Reps could login and run reports pertinent to them only? I have a decent understanding of SQL, ASP, XML, but I'm just looking for opinions on what you think the best approach is, given the software I have available. Hardware is not an problem, I bought a brand new server before the spending freeze was initiated by senior management.
Thanks for any input!
Tammy 😀
April 12, 2010 at 11:41 am
What version(s) of Windows Server do you have available?
April 12, 2010 at 11:45 am
Windows Server 2003
I have another comp with Windows Server 2000, but it's old - and I do not have the disk.
April 12, 2010 at 11:51 am
This would be an externally facing site, correct?
If yes, is there any way to get some funding and if so how much. How is SQL Server 2008 licensed, Server + CAL or per processor?
April 12, 2010 at 12:03 pm
Yes, externally facing.
I can get zero funding, spending freeze (but tell me what you're thinking, you never know, maybe I'll catch them on a good day).
SQL Server 2008, I believe its processor based licensing (but its new, not installed, and locked up at the moment so I'd have to get back to you on that one.)
April 12, 2010 at 1:33 pm
Do some research on WSS (Windows SharePoint Services). It is free with Windows Server 2003, but it will cost to have it configured to be internet facing. I believe, but can't remember where I read it, that there is an internet connector for it to allow unlimited access. This is where it will cost.
We started using WSS internally and it formed the starting point for our move to MOSS 2007.
April 12, 2010 at 1:40 pm
There are some good reporting services articles here on the Site that implement some of the features that you get with Enterprise with Standard. They might help you with the distribution of reports to outsiders.
http://www.sqlservercentral.com/Tags/Reporting+Services+%28SSRS%29/
April 12, 2010 at 1:55 pm
Reporting Services can do what you need. If you have a web portal already, it's pretty straightforward to plug in a report viewer and build some reports. You can also set up Reporting Services to send out reports automatically by e-mail, at fixed intervals (quarterly, monthly, etc.) or other threshholds.
You'd need to learn how to do these things, since you won't have a budget to hire someone for it, but MSDN has some good tutorials for that kind of thing, there are books, and you can ask questions on this site and others.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 12, 2010 at 2:36 pm
Thank. I 'll check out WSS.
I have already built an internal reporting portal for management using SQL Server and SQL Server Reporting Services. They were all impressed and thankful for it. I learn new things each day that I can do with it.
I suppose I never thought that the same program could be used for external reporting.
My concerns would be this: how do I restrict the invoices, credits and reports to show just the Rep (Customer) who has logged in? I thought you had to pass the parameters at run-time. I wouldn't want a Rep to be able to run a report for another Rep. Or to see another customer's invoices, sales etc. This is essentially a customer portal with their order history, so it needs to only show their info. Make sense?
Also, SSRS is not the nicest interface, and there's little you can do about it. Unless my reading has not gone far enough, that seems to be a common complaint with SSRS.
Your thoughts?
Tammy
April 12, 2010 at 5:11 pm
SSRS is a ton easier to manage if all your users are already being authenticated by Windows. If they are logging onto your corporate network already, I'm fairly sure you can automatically populate the value of a userID parameter to pass to a proc with their logon credentials. Otherwise, in order to restrict access to their own data, you would need to set up individual accounts for each one of the 40,000 people you're trying to support. Plus, you would need to continue to maintain those accounts, do password resets, etc.
So the big question is whether or not you have to handle user authentication yourself.
April 12, 2010 at 5:33 pm
Okay, well here's what is happening now.
Our existing system is a VPN based system, and I have access to the SQL table of user names and passwords. So, they are not really logging in to our windows network when they ente orders etc.
The portal i'm hoping to achieve would be strictly reporting (no transactions) and I was thinking of simply replicating the user table from the linked server and importing into whatever database I create for reporting. This would obviously not be live, but since the existing system is where they setup the user account and reset passwords, the reporting database would refresh that user table at a timed interval, maybe using a DTS package.
Maybe that's not even possible? The gist is that this sql database for reporting will not be linked live to our current system, and would be updated through DTS every night. Its what I did for our internal reporting portal. So Reps would know that the system is always one day behind (if i were to schedule the info nightly).
Is it even possible? To use a database table of users for authentication?
April 12, 2010 at 5:46 pm
You can build an app for this, but I don't think Reporting Services will work with a table of users.
April 12, 2010 at 5:59 pm
Steve Jones - Editor (4/12/2010)
You can build an app for this, but I don't think Reporting Services will work with a table of users.
Well yes and no.
You lose windows authentication but since that part is already done over the login part of the website it's not a problem.
Of course all authentication work must be done manually vs using AD.
However assuming those reports are mostly for downloading sales, invoices, and other reports like that I don't see any problem intergrating the two.
April 12, 2010 at 6:04 pm
Obviously a lot of choices are made on the basis of personal experience and strengths.
That being said, personally I would use IIS and set up an https site and build a simple asp.net web application to do this. Ideally, the IIS would be on a box separate from your SQL Server and the IIS box doesn't need to be anything special - it won't be doing much processing.
My understanding of the issue is that the challenge is more in regards of the number of users to distribute reports to than it is the complexity of the data being presented to them. Therefore, your data presentation will be pretty simple ... the key is controlling who can see what. They can pass in their credentials on the page, asp can take those and authenticate (or not) against the table and you can return the data associated with that ID. All of that can be one proc. (I haven't used SSRS in a long time so I'm not sure if it could be wrapped within this solution to maintain security. Worst case scenario you could just display what you want with traditional asp.net controls.)
As with any external app, there are a lot of security considerations that you will need to weigh. If the data is sensitive then you would need to take additional steps to control access. Possibly (in that case) you would be better off automating a process to send regular email updates to pre-defined email addresses. The downside to that would be that the data wouldn't be available to them on demand.
Just my two cents.
April 12, 2010 at 6:09 pm
Yes, this would be simply for downloading reports, invoices etc.
But integrating the two? The two what? Sorry, I'm a little lost.
Given that this isnt a transactional or ecommerce project, I just want an online "client portal", where users can securely login to access a variety of their "client only" reports, would it be simpler to use a ASP.NET app to SQL Server?
I could call in some help if thats the best approach. I have a spending freeze on, when it comes to capital expenditures and such, but I have permission to bring in a temp programmer or devloper on a short term basis. This might be far out of my scope.
But if SSRS is still on the table, I'd liek to understand more.
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply