Dynamic Panels/Books based on SQL Queries at Runtime

[ljwilson]

I am currently working on a C# in.de app for the web which is based on an Excel Spreadsheet that used macros to make calls to a MS SQL Server. It made extensive use of the pivot function in MS SQL. The issue with using pivot is it can return a variable number of columns.

I looked at the pivot component demo, but from what I could tell it was more of an ad-hoc way for a user to create their own pivots at runtime. What I want is a panel where the number and type/layout of columns is determined at runtime by the data set returned--much like using FileMangler with the csv option can give you the columns headers and column field values for whatever the data set returns.

To get the variable number of columns in a panel that a pivot MS SQL query can give you, I've been creating all the possible columns in a panel, hiding them unless they have data. This works since the variable number of columns given by the pivot command are all the same type of columns (same data type, same size, etc), and we have a known maximum number of columns. For example we might get Year_1, Year_2 in one query, and Year_1, Year_2, Year_3 in another query. I change the panel captions to the actual years returned, hiding/showing columns as needed.

And of course if we could do this with a panel, I would want to have the same functionality for a book so they can print to html/pdf in a similar way (variable number of columns).

Thanks...jack

[g.lanzi]

In order to do that, you could use a panel based on an IMDB table with a lot of fields.

Then you can implement a procedure which uses any recordset:
- copyng the data from the recordset to the IMDB table
- hiding all the useless field in the panel
- changing the captions of the fields and tyiping inside the names of the recordset's columns

[ljwilson]

Giuseppe,

Thanks--I'll try using an imdb table and a matching panel to handle more generic record sets.

But what I would really like is to be able to create controls (like fields in an imdb table, and corresponding panel fields, or even an entire book) at runtime, and then use reflection to process them.

...jack

[g.lanzi]

Understood. I can tell you that we are working on the personalization abilities of InDe. We will se good things in the future :)

[ljwilson]

Good news!

Thanks...jack