dynamic interface 1

[DotNetGnat]

Ok guys,

let me try to explain the situation.

Lets suppose i have the following table:

saleID | saleCounty | saleZip | saleNumber
__________________________________________

1 | blah1 | 25252 | 2
2 | blah2 | 25253 | 1
3 | blah3 | 25254 | 3
4 | blah4 | 25255 | 2
5 | blah1 | 25252 | 5
6 | blah2 | 25253 | 2
7 | blah5 | 25256 | 1
8 | blah5 | 25256 | 2
9 | blah6 | 25257 | 1
10 | blah6 | 25257 | 4

Now how can i provide a dynamic interface to the users
who can slice and dice the results according to the
variables they want.

what i mean is like creating pivot tables(as in MS Excel)
depending upon the variables.


one example scenario...user wants to see by salecounty and by saleNumber
then the results will look something like this:

saleCounty | SaleNUmber
blah1 | 7
blah2 | 3...and so on...yeah i can just use count and group by to get that

but the users can come up with many scenarios...and the results should be
based on that...

one more scenario...salesCounty and number of sales split by months or years...ofcourse i have a date field too...i have just simplified..

something like this

Jan 05 | Feb 05|
blah1 | 1 | 3 |
blah2 | 2 | 0 |

i am just wondering whether can something like this be done or not using ASP. or is there any other way of doing this....

Thanks for any insight...

-DNG

 

[DotNetGnat]

ok..after browsing for a while i came across this link and learned that it is possible to implement my requirement using ASP.NET and pivot chart web controls...

here is the link...may be useful for someone else in here...

http://www.dbazine.com/sql/sql-articles/charran3

please pour in any more suggestions you have...

Thanks

-DNG

 

[Sheco]

XML ?

 

[DotNetGnat]

Sheco, any more explanation??

Thanks

-DNG

 

[damber]

Hi DNG,

Some options,..

1. Dynamic SQL Code Generation from the user based on a pre-defined view / query and meta-data table (describing the allowable values, etc) - make sure that every value is validated for poisoning (sql injection) for safeties sake. (Think of the Query Builder in Access/SQL Server)

2. Generate XML Data Island and use XSLT to manipulate at the client:

http://www.topxml.com/code/default.asp?p=3&id=v20010226151246

http://www.asptoday.com/Content.aspx?id=720

3. Write an ActiveX Component if an MSIE only environment

4. Use ASP.NET's built in controls


The XML/XSLT way in conjunction with the dynamic query is probably the best classic ASP way, unless you want to buy a third party activeX component and limit your user based to MSIE.

Otherwise, ASP.NET would make it much easier, if it is available to you.

Hope that helps

A smile is worth a thousand kind words. So smile, it's easy!

 

[DotNetGnat]

thanks damber,

Looks promising to me. I like the idea of XML/XSLT/ASP way. Since ASP.NET is also available to me i will try that one too... i am sure it will be much faster and easier using ASP.NET.

-DNG

 

[vongrunt]

Personally I went with modified option #1 - SQL that gives GROUP BY aggregates and X/Y-axis categories and returns data via XMLHTTP.

Client does the rest. First it builds a HTML table (crosstab) based on X/Y-axis info then fills cell values with aggregated data. Relatively simple and fast both server and client-side. No XSLT though.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[DotNetGnat]

thanks for your input vongrunt...do you have any sample code or a link HTML table (crosstab) based on X/Y-axis which i can refer to get my hands dirty...

-DNG

 

[vongrunt]

Sorry, no sample code . I can post some over weekend, if that's OK to you.

Note that I hadn't used XSLT, so formatting of results is kinda crude.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[DotNetGnat]

Its definitely OK...I can wait for the code

i was sure to get some solid replies for this post and you guys did not disappoint me...thank you experts...

i am still in learning stages...

-DNG

 

[DotNetGnat]

vongrunt,

please reply to this thread at your convenience...

thanks

-DNG

 

[vongrunt]

Since my last reply I was in a total rush... had only time to answer on simple questions in my primary forum (M$SQL Server).

Code I currently have is part of my custom-made framework and is too heavily dependent (some 5-6 classes) to be posted as stand-alone code. Instead I'll try to write code mostly from scratch.

For starters, few words about concept in general. First thing to consider is some kind of query builder. There are three (3) things to specify:

- X-category column (horizontal axis)
- Y-category column (vertical axis)
- pivot value expression (cell values)

Suppose that
- X-category = saleCounty,
- y-category = saleZip
- pivot expression = SUM(saleNumber)

Then query composed by builder should be:

SELECT saleCounty as xCat,
	saleZip as yCat,
	SUM(saleNumber) as pivotValue
FROM myTable
GROUP by saleCounty, saleZip
ORDER by saleCounty, saleZip

Composed query does not provide any information about empty pivot rows/columns. This is often necessary (for example, if you have a county "blah7"), so it would be nice to have ability to specify "external source" for X/Y categories - both optional.

Anyway, after some thingamajig with Recordset/Dictionary objects you can easily pull out distinct X values, distinct Y values and cell (pivot) values. The rest is about presentation. One question: do you want to build pivot table server-side (Response.write stuff) or client-side (by transferring data to client and then using DOM/Javascript)?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[DotNetGnat]

vongrunt,

thanks for your insight. I am planning to build pivot table client-side by transferring data to the client.
I would want the client to select x and y items and rebuild the pivot table accordingly and dynamically...
so do i need to pass the selection values to the query dynamically to achieve this??

-DNG