re: Reporting off your database
-
Hi all,
once again, I'm into a new project - we've worked out a new solution to capture the data and have a prototype, now comes the part where everyone get fussy about what they want - reporting!
Everybody wants a different report, and although someone says its just one report, in truth its 50 or 60 when you slice and dice it.
I want to go towards a web based reporting system thats very user friendly, but how do you accomodate the adhoc stuff or the drill downs that people want. I've been down the hardcoded report path before - its easy to get your data out of the db with some nifty SQL, but what if the user wants it in more detail, or a more global view.
What is people's experience with this? Whats worked best in the environment? I understand you can publish straight to web from SQL, but the format is not crash hot (neither is the maintenance). Are Data cubes a good way to go (can basic users interact with it still?)
-
You have a number of possibilities, based on the $$$ you want to spend.
First off, you could buy a decent reporting package like Cognos or Business Objects. They both perform well at giving the user control over the reports. (Point-and-drag kind of stuff...).
Another possibility is using the PivotTable / PivotChart component from MS. This is an ActiveX, so you can include it on your web. Also has a point-and-click interface, but it's much less slick compared to that of BO or Cognos. This is probably the cheapest solution.
Depending on your choice, you can base the reports either on plain tables or on a(Analysis Services) cube.
Finally, you could write a report for each request you get. This is obviously best if you are a consultant. You can charge for each report
, but as a solution, won't bring you very far. -
Brendon,
If you're thinking of using asp/aspx you might just provide Excel downloads of the detailed data related to each report. You can create a fairly simple asp/aspx page that accepts query/db parameters, parses them for possible security breaches, and then loads an .xls file in the browser. Here's a short version of the code you might use to format the column headers and the rows:
set objrs = objconn.execute(strCommand)
if not objrs.EOF then
%><table border=1><tr><%
for each thing in objrs.fields
response.write "<td><b>" & thing.name & "</b></td>"
next
%></tr><%
end if
do until objrs.EOF
response.write "<tr>"
for each thing in objrs.fields
response.write "<td>" & thing.value & "</td>"
next
response.write "</tr>"
objrs.MoveNext
loop
%></table><%
objrs.close
objconn.close
set objrs = nothing
set objconn = nothing
%> -
Oops, I left off the crucial setting at the top of such pages that tells the browser to expect an Excel file:
response.contenttype = "application/vnd.ms-excel"
This really is a good solution if you can count on all users having MS Excel. Then they can create their own filters, pivot tables, charts... saving you time.
-
It depends on your user's abilities. If they are surviving brain donors then you will need something expensive to allow them to write their own reports.
For the reasonably literate users I tend to work with them to define a set or sets of base data and export it in a format suitable for their work away from my server.
When I was working for McCann-Erikson the Dataset department were fans of a package called Brio. See the attached link www.sphinxcst.co.uk. It has stand-alone and web based components but it is in the Cognos type price range rather than the MS Access price range.
I have encountered a similar problem to yours in a different company. Everyone wanted something slightly different. What we did was establish what the common ground was then got the users to fight over the differences. The reality was that there wasn't really a case for 50 or 60 differing versions of the same thing.
The users wanted to
- Filter the data going into the report (easy).
- Sort and aggregate on various levels (within limitations easy)
- Alter column orders (No, do it yourself).
- Alter calculations (politics, fight amongst yourself)
The particular organisation had umpteen calculations for profitability so depending on who you spoke to the other umpteen minus one were all wrong. Try getting this one through user acceptance testing!
To get around this I got them to agree to accept the fields necessary for their calculations for them to produce their figures.
-
hmmm...
I understand that users need to create their own adhoc rpeorts, however, our current issues have stemmed from the fact that everyone has become an Excel programmer, with their own macros etc etc, and the business says "why are people spending so much time converting reports - can't they simply pull this information as is right now?"
I guess there has to be a medium somewhere in there...
-
We are using Business Objects and their web component, Web Intelligence for ad hoc reporting, Board Reports and government reporting. It allows the smarter users to create their own reports easily and for their less cluey collegues they can just refresh data in predefined reports and distribute them over the web. Of course it all comes at a price, doesn't everything?
For all other reporting needs we use Pivot Tables in Excel XP connecting to an OLAP cube in Analysis Services. I personally like the cubes better, and of course they are a lot less costly.
We find that a combination of these two systems has worked the best for us. It's hard to find one product that will 'do it all'.
Cheers,
Angela
Edited by - AngelaBut on 07/09/2003 12:16:41 AM
-
test
, but as a solution, won't bring you very far.Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply