Web Report Builder
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?
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?
ASKER
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.