It is currently 25 May 2020, 7:51 Advanced search

Dynamic Panels/Books based on SQL Queries at Runtime

In this forum you can post your ideas on how Instant Developer could be enhanced, so we can discuss them together.

Dynamic Panels/Books based on SQL Queries at Runtime

Postby ljwilson » 28 November 2014, 17:43

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
ljwilson
 
Posts: 523
Joined: 26 November 2013, 14:15

Re: Dynamic Panels/Books based on SQL Queries at Runtime

Postby g.lanzi » 3 December 2014, 15:19

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
Giuseppe Lanzi
Director of Support services
Pro Gamma S.p.A.
User avatar
g.lanzi
Pro Gamma
Pro Gamma
 
Posts: 3339
Joined: 29 September 2010, 10:24
Location: Bologna

Re: Dynamic Panels/Books based on SQL Queries at Runtime

Postby ljwilson » 3 December 2014, 15:39

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
ljwilson
 
Posts: 523
Joined: 26 November 2013, 14:15

Re: Dynamic Panels/Books based on SQL Queries at Runtime

Postby g.lanzi » 4 December 2014, 12:50

Understood. I can tell you that we are working on the personalization abilities of InDe. We will se good things in the future :)
Giuseppe Lanzi
Director of Support services
Pro Gamma S.p.A.
User avatar
g.lanzi
Pro Gamma
Pro Gamma
 
Posts: 3339
Joined: 29 September 2010, 10:24
Location: Bologna

Re: Dynamic Panels/Books based on SQL Queries at Runtime

Postby ljwilson » 4 December 2014, 13:04

Good news!

Thanks...jack
ljwilson
 
Posts: 523
Joined: 26 November 2013, 14:15


Return to Improvement Suggestions

Who is online

Users browsing this forum: No registered users and 1 guest