Solved

Web Report Builder

Posted on 2016-07-28
3
32 Views
Last Modified: 2016-08-02
Experts -

I am trying to create a web based report builder using html5/classic asp as front end and MS SQL Server as backend.

Why am I doing this?
My company gets lots of structured data in ETL format. We need to convert them in a friendly format & use it for reporting needs. I can use SQL Server Reporting services to build my report but template will change for every report.

Challenges I can see:
1. There are lot of custom aggregates that are involved. Ex: in a report, I need to display a row called "Product A" which will be a direct sum value from input file and next row will be "Product B" which will be sum of Product A and Product C. So On the fly aggregate builder is required.
2. Columns may change for every input file. First few may be labels and last few may be metrics / numbers.

What am i trying to do?

1. Create a html 5 page & connect it to a SQL db.
2. Post all input files (excel/csv) in a folder.
3. Use SQL openrowset from html5/classic asp to import "Required" files and create on the fly temp table(inputfiles_temp).
4. Just bring the headers of the temp table & assign corresponding definition (Ex: Col1 = Label, Col10 = Number) etc
5. Since the data is in ETL format, harmonization is also required.
    Ex:
    CustomerLevel1    CustomerLevel2   OrganizationLevel1 OrganizationLevel2  ProductLevel1  Shipments

   The above should be harmonized into
    CustomerLevel1   OrganizationLevel1 ProductLevel1  Shipments
   CustomerLevel1   OrganizationLevel2  ProductLevel1  Shipments
   CustomerLevel2  Organizationlevel1   ProductLevel1 Shipments
   CustomerLevel2  Organizationlevel2   ProductLevel1 Shipments

so, once the temp table is created, the above cross-tab should also be created and the output should be saved in another temp table(processing_temp).

Now, we need to look at aggregates, metrics etc (outside of the report builder) and just call the required fields & create more or less like a pivot table output from the above temp table & store the final SQL again as a temp table(output_Temp).

Can someone guide me by giving me a logic to get this done from html5/ classic asp please?
0
Comment
Question by:Manju
  • 2
3 Comments
 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
Comment Utility
Actually, your back end is sql server AND classic asp.   Your front end is html and if you choose, javascript/jquery (which you should do).

I think your question needs to be re-phrased.  Instead of asking about the middle (openrowset), stick with your end goal (Creating a report writer).

First, take a look at http://www.tableau.com/.  For the price, you couldn't do all of this and justify your own time to recreate.  There are other options like this, but I found this to be a great value and gives your users a lot of power.

If you want to stick to doing this on your own, then you want to use your back end sql server and asp to process a request and output raw data.  From there, use front end javascript to allow things like sorting and cross tables.

Keep in mind, depending on your data structure, you will probably want to create special views in sql server for these reports because the average person will not understand relating tables let alone which tables to use.

https://datatables.net/
https://handsontable.com/
http://community.jaspersoft.com/project/visualizejs
http://www.telerik.com/kendo-ui
http://www.chartjs.org/

There are a lot of parts to your question and if you can keep it to one item at a time, we can help you better.   In other words, do you need help with connecting your asp page to a database? bulk uploading data?  creating the queries to display output data? Creating tables, charts, graphs etc.

If it is all, then let's start at the beginning and only focus on that in one question, then a new thread for the next. I hope that makes sense.
0
 
LVL 6

Author Comment

by:Manju
Comment Utility
Scott - I agree with you. There's quite alot of ask from me.., apologies.

Since my post, I've been thinking and sort of came to a flow. Here's the help i need..,

from Html5 , i need to import csv / txt / excel and push them to sql in temp table and display the data as table in html5. can you suggest any tools / methods for the same please?
0
 
LVL 52

Accepted Solution

by:
Scott Fell,  EE MVE earned 500 total points
Comment Utility
There are two parts to this.  1) Uploading the file and 2) Importing to sql server.

To upload the file, you will use an html form with enctype="multipart/form-data".  Using classic asp, you will need a helper.  If you are on a shared server, many come with aspUpload installed.  http://www.aspupload.com/manual_simple.html.  I have an example in another question thread https://www.experts-exchange.com/questions/28677890/Image-Upload-Save-to-MS-SQL-Web-Form.html#a40790013

Other options include:
https://support.microsoft.com/en-us/kb/299692 where you can do it yourself from scratch.

http://www.codeguru.com/csharp/.net/net_asp/article.php/c19297/Pure-ASP-File-Upload.htm  is a free option

For now, assume aspUpload and your page that handles the upload is called process_upload.asp.  You will have a form:
<form NAME="MyForm" METHOD="POST" ENCTYPE="multipart/form-data" ACTION="process_upload.asp"> 

               <INPUT TYPE=FILE SIZE=40 NAME="FILE1">
               <INPUT TYPE=SUBMIT VALUE="Upload">

</form>

Open in new window


process_upload.asp
	Set Upload = Server.CreateObject("Persits.Upload.1")

	Upload.OverwriteFiles = False
	On Error Resume Next

	Upload.SetMaxSize 1048576	' Limit files to 1MB
	Count = Upload.Save("c:\mywebsite\htdocs\uploads")  ' this file should be in your domain and have write permissiosn

For Each File in Upload.Files
     'Response.Write File.Name & "= " & File.Path & " (" & File.Size &" bytes)<BR>"
     theFileName = File.Name
    AddFileNameToDatbase(theFileName) ' run function to add data.
Next

function AddFileNameToDatbase(data)
    ' place your sql to insert data here using theFileName for the name of the file
end function

Open in new window


Now you have a file on your server and in this example is lives in the folder c:\mywebsite\htdocs\uploads.  My example above lets you rename the file, but you can also hard code the file name.  Because the file is on the server, you can use sql server to import.  It looks like you have found a solution for this part and I suggest making that a stored procedure. Then in my code above where I have the function AddFileNameToDatbase you can simply use classic asp to call the SP.

An alternative option is to use classic asp to read the file one by one and use the split function on the selected delimiter, then loop through each row and run insert statement.

With either  method, make sure to run error checking on the data.

I saw you had a question thread for inserting already https://www.experts-exchange.com/questions/28952268/Unable-to-import-csv-using-Bulk-Insert.html.  You can add that portion to a SP and use asp to call the SP as I mentioned.
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
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…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

762 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now