Advice on reporting via ASP

[Jeff Boyer]

Hello everyone,

I have recently developed a web application across my clients intranet. We
used ASP with Interdev 6.0 and SQL server as the backend.

They have now come to me asking me to develop some reports that can be
accessed via a drill down within the app. They don't have Crystal Reports so
this is not an option. Can anyone suggest an alternative to using crystal
reports to publish dynamic reports via the web and ASP?

Thanks in advance,
Jeff

[Michael D. Kersey]

Jeff Boyer wrote:

> They have now come to me asking me to develop some reports that can be
> accessed via a drill down within the app. They don't have Crystal Reports so
> this is not an option. Can anyone suggest an alternative to using crystal
> reports to publish dynamic reports via the web and ASP?

You don't need a special report writer or reporting component. Merely
generate all reports using ASP. The output is HTML, which they can print
if they wish.

Good Luck,
Michael D. Kersey

[Jeff Boyer]

Ok, I hear what your saying but for large reports that total columns isn't
that allot of code to write? I was hoping I didn't have to re-invent the
wheel. I thought maybe there might be some sort of plugin to use with
Interdev or maybe a third party application I could call from my page.

Jeff
"Michael D. Kersey" <mdkersey@hal-pc.org> wrote in message
news:uvNqA9rxDHA.3416@tk2msftngp13.phx.gbl...

> Jeff Boyer wrote:

> > They have now come to me asking me to develop some reports that can be
> > accessed via a drill down within the app. They don't have Crystal

Reports so

> > this is not an option. Can anyone suggest an alternative to using

crystal

> > reports to publish dynamic reports via the web and ASP?

>
> You don't need a special report writer or reporting component. Merely
> generate all reports using ASP. The output is HTML, which they can print
> if they wish.
>
> Good Luck,
> Michael D. Kersey
>

[Roland Hall]

"Jeff Boyer" wrote:

> Ok, I hear what your saying but for large reports that total columns isn't
> that allot of code to write?

So, your application only displays data and they don't want to have to whip
our their trusty calculators to add up the columns?

for i = 0 to ubound(arr)
sometotal = sometotal + arr(i)
next
reponse.write sometotal

Doesn't seem like a lot.

> I was hoping I didn't have to re-invent the
> wheel.
> I thought maybe there might be some sort of plugin to use with
> Interdev or maybe a third party application I could call from my page.

You wrote a custom app. I doubt you'll find a plugin that will not have to
be customized, although Crystal Reports does have an ASP plugin.

--
Roland

This information is distributed in the hope that it will be useful, but
without any warranty; without even the implied warranty of merchantability
or fitness for a particular purpose.

[Michael D. Kersey]

Jeff Boyer wrote:

> Ok, I hear what your saying but for large reports that total columns isn't
> that allot of code to write? I was hoping I didn't have to re-invent the
> wheel. I thought maybe there might be some sort of plugin to use with
> Interdev or maybe a third party application I could call from my page.
> Jeff

You can
1. fetch the SQL result set into a recordset and then accumulate totals
on the ASP page by looping through the result set, or
2. issue a separate SQL query for the required summary columns.

FWIW you can fetch as many result sets (issue as many SQL statements) as
you wish on a single ASP page.

Good Luck,
Michael D. Kersey

[Jeff Boyer]

So your both saying my only option is to run some queries and dynamically
build the tables to display the results?

Jeff
"Michael D. Kersey" <mdkersey@hal-pc.org> wrote in message
news:eP0HS0zxDHA.2076@TK2MSFTNGP10.phx.gbl...

> Jeff Boyer wrote:

> > Ok, I hear what your saying but for large reports that total columns

isn't

> > that allot of code to write? I was hoping I didn't have to re-invent the
> > wheel. I thought maybe there might be some sort of plugin to use with
> > Interdev or maybe a third party application I could call from my page.
> > Jeff

>
> You can
> 1. fetch the SQL result set into a recordset and then accumulate totals
> on the ASP page by looping through the result set, or
> 2. issue a separate SQL query for the required summary columns.
>
> FWIW you can fetch as many result sets (issue as many SQL statements) as
> you wish on a single ASP page.
>
> Good Luck,
> Michael D. Kersey
>

[Michael D. Kersey]

Jeff Boyer wrote:

> So your both saying my only option is to run some queries and dynamically
> build the tables to display the results?
> Jeff

Your original post stated

> They have now come to me asking me to develop some reports that can be
> accessed via a drill down within the app.

which can be done straightforwardly with ASP by displaying each item to
be "drilled down" with an anchor, e.g.,
....
<td><a href="drilldown.asp?emp_id=03423">03423</a></td>
....
which, when clicked, takes the user to a more detailed level.

There _are_ other ways to build HTML tables, e.g., the RecordSet
object's GetString method which can build most of an HTML table from a
recordset for you. GetString has a small bug in it that can be worked
around; see
[url]http://www.google.com/groups?q=+%22Getstring%22+group:microsoft.public.i netserver.asp.*+author:mdkersey%40hal-pc.org&hl=en&lr=&ie=UTF-8&oe=UTF-8&scoring=r&selm=3AFDB1C5.44C89983%40hal-pc.org&rnum=2[/url]
for an example of GetString usage including the workaround.

Also look at either of the previous posts
[url]http://www.google.com/groups?q=+%22Getstring%22+group:microsoft.public.i netserver.asp.*+author:mdkersey%40hal-pc.org&hl=en&lr=&ie=UTF-8&oe=UTF-8&scoring=r&selm=3DCAD964.6AFE70F3%40hal-pc.org&rnum=10[/url]
or
[url]http://www.google.com/groups?q=+%22Getstring%22+group:microsoft.public.i netserver.asp.*+author:mdkersey%40hal-pc.org&hl=en&lr=&ie=UTF-8&oe=UTF-8&scoring=r&selm=3E513802.CABD7227%40hal-pc.org&rnum=9[/url]
which describe several techniques for fetching recordsets and building
HTML tables.

Perhaps there was some other requirement?

Good Luck,
Michael D. Kersey

===== Twenty-first-century taxes: ======================
lottery - tax for sleeping in math class
XML - tax for sleeping in compiler class
Web Services - tax for sleeping in data structures class
================================================== ======
That which is excellent is new forever.

[Mark Andrews]

You could use our product which uses Microsoft Access Reports on the server.
The clients only need Acrobat Reader to read the dynamic PDF files which are
created.

Use pass-thru queries (in Access) to run stored procs or SQL Server SQL to
drive the report.
At runtime you pass in the SQL for the pass-thru query. For example: "Exec
mystoredproc @CustID=34"

HTH,
Mark
RPT Software
[url]http://www.rptsoftware.com[/url]


"Jeff Boyer" <jeff@jdboyer.com> wrote in message
news:v%OEb.761092$9l5.641061@pd7tw2no...

> Hello everyone,
>
> I have recently developed a web application across my clients intranet. We
> used ASP with Interdev 6.0 and SQL server as the backend.
>
> They have now come to me asking me to develop some reports that can be
> accessed via a drill down within the app. They don't have Crystal Reports

so

> this is not an option. Can anyone suggest an alternative to using crystal
> reports to publish dynamic reports via the web and ASP?
>
> Thanks in advance,
> Jeff
>
>

[Chris Barber]

I'd recommend having a look at XML and XSLT (if you are at all familiar).
You can generate a recordset as XML and then use the XSLT (with a little
more added info) to do practically anything (also allows for totalling
columns etc.).

Chris Barber.

"Jeff Boyer" <jeff@jdboyer.com> wrote in message
news:v%OEb.761092$9l5.641061@pd7tw2no...
Hello everyone,

I have recently developed a web application across my clients intranet. We
used ASP with Interdev 6.0 and SQL server as the backend.

They have now come to me asking me to develop some reports that can be
accessed via a drill down within the app. They don't have Crystal Reports so
this is not an option. Can anyone suggest an alternative to using crystal
reports to publish dynamic reports via the web and ASP?

Thanks in advance,
Jeff

[Turkbear]

Rather than buying stuff, look into generating HTML tables with ASP code..Should give you all the basic reporting you
need..
hth

On Mon, 22 Dec 2003 10:49:46 -0500, "Mark Andrews" <NOSPAMmandrews@rptsoftware.com> wrote:

>You could use our product which uses Microsoft Access Reports on the server.
>The clients only need Acrobat Reader to read the dynamic PDF files which are
>created.
>
>Use pass-thru queries (in Access) to run stored procs or SQL Server SQL to
>drive the report.
>At runtime you pass in the SQL for the pass-thru query. For example: "Exec
>mystoredproc @CustID=34"
>
>HTH,
>Mark
>RPT Software
>[url]http://www.rptsoftware.com[/url]
>
>
>"Jeff Boyer" <jeff@jdboyer.com> wrote in message
>news:v%OEb.761092$9l5.641061@pd7tw2no...

>> Hello everyone,
>>
>> I have recently developed a web application across my clients intranet. We
>> used ASP with Interdev 6.0 and SQL server as the backend.
>>
>> They have now come to me asking me to develop some reports that can be
>> accessed via a drill down within the app. They don't have Crystal Reports

>so

>> this is not an option. Can anyone suggest an alternative to using crystal
>> reports to publish dynamic reports via the web and ASP?
>>
>> Thanks in advance,
>> Jeff
>>
>>

>

[John Q. Sample]

take a look at ActivePDF. I use it to create monthly management reports
on our intranet on the fly and in memory... Actually I wrote the pages
and let management "go get" the reports needed...


*** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
Don't just participate in USENET...get rewarded for it!

[Mark Andrews]

I agree that if your reporting needs are basic enough that you can do it by
creating HTML you should just code a solution.
It's more scalable, faster etc...

However I also think that sometimes a good report designer is needed for
example our company has
a intranet site in which they can generate about 150 different web based
reports from data in our data
warehouse. All of these reports go to Excel or PDF and print out very
nicely in PDF format.
You can buy a high-end package like Business Objects or MicroStrategy or
Actuate or a low-end package
like Crystal, Active Reports or our software which uses Microsoft Access as
the report designer.
High end costing 10K to 100K, low-end costing a few hundred bucks.

Another approach is to build OLAP cubes off your SQL data and provide a web
interface to the cube data.
Good for analytical reporting in which "user interaction" is needed
(drilling down to find the answer or
multi step questions etc...).

HTH,
Mark
RPT Software
[url]http://www.rptsoftware.com[/url]

PS: Feel free to send me an email directly I have looked at almost every
reporting package in the market.

"Turkbear" <noone@nowhere.com> wrote in message
news:34aeuvc2th6paat7vndihrdv5103les12p@4ax.com...

>
> Rather than buying stuff, look into generating HTML tables with ASP

code..Should give you all the basic reporting you

> need..
> hth
>
> On Mon, 22 Dec 2003 10:49:46 -0500, "Mark Andrews"

<NOSPAMmandrews@rptsoftware.com> wrote:

>

> >You could use our product which uses Microsoft Access Reports on the

server.

> >The clients only need Acrobat Reader to read the dynamic PDF files which

are

> >created.
> >
> >Use pass-thru queries (in Access) to run stored procs or SQL Server SQL

to

> >drive the report.
> >At runtime you pass in the SQL for the pass-thru query. For example:

"Exec

> >mystoredproc @CustID=34"
> >
> >HTH,
> >Mark
> >RPT Software
> >[url]http://www.rptsoftware.com[/url]
> >
> >
> >"Jeff Boyer" <jeff@jdboyer.com> wrote in message
> >news:v%OEb.761092$9l5.641061@pd7tw2no...

> >> Hello everyone,
> >>
> >> I have recently developed a web application across my clients intranet.

We

> >> used ASP with Interdev 6.0 and SQL server as the backend.
> >>
> >> They have now come to me asking me to develop some reports that can be
> >> accessed via a drill down within the app. They don't have Crystal

Reports

> >so

> >> this is not an option. Can anyone suggest an alternative to using

crystal

> >> reports to publish dynamic reports via the web and ASP?
> >>
> >> Thanks in advance,
> >> Jeff
> >>
> >>

> >

>

[Roland Hall]

"Jeff Boyer" wrote:

> So your both saying my only option is to run some queries and dynamically
> build the tables to display the results?

Hi Jeff...

Sorry, I was out of town for a couple of days making XMas presents. Santa's
work is never done! {(O;|>*

I'm not saying this is your only option. It is an alternative option as you
asked. If you wrote the application you should have no problem building the
report. I was just showing an example of calculating an array variable and
that it didn't require a lot of code to do it.

You have been given several options from others so now it's decision time.
I'm not suggesting you pick a product. You have to determine how much your
time is worth, how long it will take you if you created the reporting engine
vs the cost of a commercial product, integrating it with your product and
the learning curve. If time is critical then that should also be
considered. A last option would be to contract someone to build it for you.
This option would be if you required something custom that a commercial
reporting engine could not easily offer.

Good luck with whatever you decide, and if you should choose to do it
yourself and need some help, I'm sure someone here can get you past any
rough spots.

--
Roland

This information is distributed in the hope that it will be useful, but
without any warranty; without even the implied warranty of merchantability
or fitness for a particular purpose.

[Jeff Boyer]

Thanks for all the info guys..... I'll look into it all and go from there.
I'm extreamly interested in a few of the suggestions.

Jeff
"Chris Barber" <chris@blue-canoe.co.uk.NOSPAM> wrote in message
news:ODNGojKyDHA.2464@TK2MSFTNGP12.phx.gbl...

> I'd recommend having a look at XML and XSLT (if you are at all familiar).
> You can generate a recordset as XML and then use the XSLT (with a little
> more added info) to do practically anything (also allows for totalling
> columns etc.).
>
> Chris Barber.
>
> "Jeff Boyer" <jeff@jdboyer.com> wrote in message
> news:v%OEb.761092$9l5.641061@pd7tw2no...
> Hello everyone,
>
> I have recently developed a web application across my clients intranet. We
> used ASP with Interdev 6.0 and SQL server as the backend.
>
> They have now come to me asking me to develop some reports that can be
> accessed via a drill down within the app. They don't have Crystal Reports

so

> this is not an option. Can anyone suggest an alternative to using crystal
> reports to publish dynamic reports via the web and ASP?
>
> Thanks in advance,
> Jeff
>
>
>

[Cathy Jones]

Hi,

If you are looking for an alternative third party tool I suggest that
you look at Cognos Reportnet([url]www.cognos.com[/url]) or Intelliview([url]www.intelliview.com[/url]).

Cathy

[Brynn]

I do many of the reports on the intranets I work on. Building reports
with ASP is incredibly easy. build a couple decent subroutines for the
table building, maybe go grab my DBConn.asp file from coolpier.com to
simplify the database stuff.

Data is some custom for a business, ASP reports will probably be
easier than using an program anyway.

This is a subroutine I would use ... I haven't tested yet ...



<%
'// The cp_CellWidths and cp_CellTitles need to be seperated by @@@
'// It's been easier to have a string in my code, and let sub
make the arrays for the above.
'// For a blank title box in your string put &nbsp;
'// For no titles simply put an empty string "" which will
make the array cp_CellTitlesArray = -1

'//I occasionally modify this script ... without titles, this that
whatever
'// KNOWING my table is going to be 4 columns ... I have done
the follwing
'// When I modify it, I just copy the subroutine and paste it
below named cp_BuildTable2
'// My cp_BuildTable2 here is an example of how to use CSS
classes for different columns
'// My cp_BuildTable3 is a simple example with every other row
a different CSS class
'// Where there are changes I put '// **********

'// In fact, I am going to add cp_CellClasses before I put
this script on my site

'// As always, the cp_ coolpier.com prefix is used before all variable
names as to not conflict with any you may be using.
'// As always, use this script at your own risk ... I do not guarantee
the effects of this script.

Sub cp_BuildTable(cp_DataArray, cp_CellWidths, cp_CellTitles)
Dim cp_tableHTML, cp_rowHTML
cp_tableHTML = "<table align=""center"" valign=""top""
width=""96%"">"
cp_rowHTML = "<tr>"

Dim cp_CellWidthsArray, cp_CellTitlesArray '//cell HTML left
in loop for easy modifying later
cp_CellWidthsArray = Split(cp_CellWidths, "@@@")
cp_CellTitlesArray = Split(cp_CellTitles, "@@@")

Dim cp_n, cp_i, cp_Rows, cp_Columns: cp_Rows =
ubound(cp_DataArray,2): cp_Columns = ubound(cp_DataArray,1)
With Response
.Write cp_tableHTML

'// TITLES
If Not cp_CellTitlesArray = -1 Then
.Write cp_rowHTML
For cp_n = 0 to cp_Columns
.Write "<td align=""center"" valign=""middle""
class=""yourTitleCSSclass"" width=""" & cp_CellWidthsArray(cp_i) &
""">"
.Write cp_CellTitlesArray(cp_n)
.Write "</td>"
Next
.Write "</tr>"
End If

'// DATA
For cp_n = 0 to cp_Rows
.Write cp_rowHTML
For cp_i = 0 to cp_Columns
.Write "<td align=""left"" valign=""top""
class=""yourCSSclass"" width=""" & cp_CellWidthsArray(cp_i) & """>"
.Write cp_DataArray(cp_i, cp_n)
.Write "</td>"
Next
.Write "</tr>"
Next
.Write "</table>"

End With
End Sub


Sub cp_BuildTable2(cp_DataArray, cp_CellWidths, cp_CellTitles)
Dim cp_tableHTML, cp_rowHTML, cp_ClassesArray(3)'// **********
cp_tableHTML = "<table align=""center"" valign=""top""
width=""96%"">"
cp_rowHTML = "<tr>"
cp_ClassesArray(0) = "myclass1"'// **********
cp_ClassesArray(1) = "myclass2"'// **********
cp_ClassesArray(2) = "myclass3"'// **********
cp_ClassesArray(3) = "myclass4"'// **********



Dim cp_CellWidthsArray, cp_CellTitlesArray '//cell HTML left
in loop for easy modifying later
cp_CellWidthsArray = Split(cp_CellWidths, "@@@")
cp_CellTitlesArray = Split(cp_CellTitles, "@@@")

Dim cp_n, cp_i, cp_Rows, cp_Columns: cp_Rows =
ubound(cp_DataArray,2): cp_Columns = ubound(cp_DataArray,1)
With Response
.Write cp_tableHTML

'// TITLES
If Not cp_CellTitlesArray = -1 Then
.Write cp_rowHTML
For cp_i = 0 to cp_Columns
.Write "<td align=""center"" valign=""middle""
class=""" & cp_ClassesArray(cp_i) & """ width=""" &
cp_CellWidthsArray(cp_i) & """>" '// **********
.Write cp_CellTitlesArray(cp_i)
.Write "</td>"
Next
.Write "</tr>"
End If

'// DATA
For cp_n = 0 to cp_Rows
.Write cp_rowHTML
For cp_i = 0 to cp_Columns
.Write "<td align=""left"" valign=""top""
class=""" & cp_ClassesArray(cp_i) & """ width=""" &
cp_CellWidthsArray(cp_i) & """>"
.Write cp_DataArray(cp_i, cp_n)
.Write "</td>"
Next
.Write "</tr>"
Next
.Write "</table>"

End With
End Sub




Sub cp_BuildTable3(cp_DataArray, cp_CellWidths, cp_CellTitles)
Dim cp_tableHTML, cp_rowHTML, cp_ClassesArray(1), cp_RowClass
'// **********
cp_tableHTML = "<table align=""center"" valign=""top""
width=""96%"">"
cp_rowHTML = "<tr>"
cp_ClassesArray(0) = "firstColumnsClass" '//
**********
cp_ClassesArray(1) = "secondColumsClass" '//
**********


Dim cp_CellWidthsArray, cp_CellTitlesArray '//cell HTML left
in loop for easy modifying later
cp_CellWidthsArray = Split(cp_CellWidths, "@@@")
cp_CellTitlesArray = Split(cp_CellTitles, "@@@")

Dim cp_n, cp_i, cp_Rows, cp_Columns: cp_Rows =
ubound(cp_DataArray,2): cp_Columns = ubound(cp_DataArray,1)
With Response
.Write cp_tableHTML

'// TITLES
If Not cp_CellTitlesArray = -1 Then
For cp_n = 0 to cp_Columns
.Write "<td align=""center"" valign=""middle""
class=""yourTitleCSSclass"" width=""" & cp_CellWidthsArray(cp_i) &
""">"
.Write cp_CellTitlesArray(cp_n)
.Write "</td>"
Next
.Write "</tr>"
End If

'// DATA
cp_RowClass = cp_ClassesArray(1) '//**********
For cp_n = 0 to cp_Rows
If cp_RowClass = cp_ClassesArray(1) Then: cp_RowClass
= cp_ClassesArray(0): Else: cp_RowClass = cp_ClassesArray(1): End If
'//**********
.Write "<tr class=""" & cp_RowClass & """>"
'//**********

.Write cp_rowHTML
For cp_i = 0 to cp_Columns
.Write "<td align=""left"" valign=""top""
class=""yourCSSclass"" width=""" & cp_CellWidthsArray(cp_i) & """>"
.Write cp_DataArray(cp_i, cp_n)
.Write "</td>"
Next
.Write "</tr>"
Next
.Write "</table>"

End With
End Sub
%>


Anyway, I think it is pretty easy to make nice looking reports with a
few custom subroutines. Anyway, have fun coding.

Brynn
[url]www.coolpier.com[/url]

P.S. I also sometimes modify the scripts to build the html for the
reports into an array ... then use that array with my ArrayToText.asp
script and build static reports. For reports that are going to be
generated like every morning or something ... you can also use vbs
scripts to execute your .asp files to build these reports on a timer.
I make write the html files so that they are reading a static page,
and the database isn't being hit for every report view ... AND the
time and resources to dynamically build the report isn't there ... AND
it is nice to have an archive of all the reports ever written.

You can use my little FilesInFolder.asp script to read the file names
in the folder with all the reports in it ... or what I like to do is
enter each file name into a text file as they are being written ... I
use my ArrayToText_Append.asp for that.

ANYWAY, just thought I would post :)

Brynn
[url]www.coolpier.com[/url]





On Sat, 20 Dec 2003 21:36:00 GMT, "Jeff Boyer" <jeff@jdboyer.com>
wrote:

>So your both saying my only option is to run some queries and dynamically
>build the tables to display the results?
>
>Jeff
>"Michael D. Kersey" <mdkersey@hal-pc.org> wrote in message
>news:eP0HS0zxDHA.2076@TK2MSFTNGP10.phx.gbl...

>> Jeff Boyer wrote:

>> > Ok, I hear what your saying but for large reports that total columns

>isn't

>> > that allot of code to write? I was hoping I didn't have to re-invent the
>> > wheel. I thought maybe there might be some sort of plugin to use with
>> > Interdev or maybe a third party application I could call from my page.
>> > Jeff

>>
>> You can
>> 1. fetch the SQL result set into a recordset and then accumulate totals
>> on the ASP page by looping through the result set, or
>> 2. issue a separate SQL query for the required summary columns.
>>
>> FWIW you can fetch as many result sets (issue as many SQL statements) as
>> you wish on a single ASP page.
>>
>> Good Luck,
>> Michael D. Kersey
>>

>
>