Dynamic SQL / Pivot
Hi there,
I have a source database that manages flexible forms. What I mean by this is that there are some hard-coded fields required for all forms, (Work Group and Form Name for example) but every other field is customized by users. The fields are saved vertically in rows and since each form can have wildly different numbers of columns with different column names, I created a stored procedure that will iterate through the combinations of Work Group and Form Name to yield the right column list for each form. Essentially, when it runs, the idea is that it'll dynamically give the output from all forms. I've thought about enhancing the procedure to SELECT INTO tables for every combination of workgroup/form name and then insert into thereafter, but there are some inherent problems with that (namely if users change form fields). What I was hoping to do instead was use the dynamic SQL that pivots the data, giving the (currently) 50+ combinations of forms to put it into a view in YF. I tested it in a FH SQL report and it fails for executequery method since it's updating data. My question is there anything that could support the variable nature of the results in a view? As noted, every combination of forms would yield a different result set (some forms may have 4 columns whilst others may have 50). I know this is a long shot, but wanted to see if you might have a way to address this so I don't have to dynamically maintain dozens of automatically created tables when new forms are created.
Thanks!
Craig
The same question
Hi Craig,
that's certainly an interesting setup you've got! Unfortunately though, given that the metadata for Yellowfin views is static I can't think of a way for Yellowfin to support dynamic views. If you look at the ReportView and ReportFieldTemplate tables you'll see what I mean, in the ReportView table has a unique ID for the view itself, the records that represent the view itself are the ones where ViewTypeCode is either DRAGANDDROP or SQLREPORT or CUBE, and then under those records you'll a set of records where ViewTypeCode is CHILDELEMENT - these are the tables within the view uses. And then in the ReportFieldTemplate table you'll find all of the view's columns that are linked back to the view via the ViewID.
However, thinking along totally different lines, what about the following:
What if you could programmatically create a brand new Yellowfin view every time a user created a new form? Would that sort of solution work for you? Obviously you would end up with dozens of Yellowfin views, however you wouldn't have had to create any of them manually.
Please let me know what you think, and if you like it then I can give you more details on how this could be achieved.
regards,
David
Hi Craig,
that's certainly an interesting setup you've got! Unfortunately though, given that the metadata for Yellowfin views is static I can't think of a way for Yellowfin to support dynamic views. If you look at the ReportView and ReportFieldTemplate tables you'll see what I mean, in the ReportView table has a unique ID for the view itself, the records that represent the view itself are the ones where ViewTypeCode is either DRAGANDDROP or SQLREPORT or CUBE, and then under those records you'll a set of records where ViewTypeCode is CHILDELEMENT - these are the tables within the view uses. And then in the ReportFieldTemplate table you'll find all of the view's columns that are linked back to the view via the ViewID.
However, thinking along totally different lines, what about the following:
What if you could programmatically create a brand new Yellowfin view every time a user created a new form? Would that sort of solution work for you? Obviously you would end up with dozens of Yellowfin views, however you wouldn't have had to create any of them manually.
Please let me know what you think, and if you like it then I can give you more details on how this could be achieved.
regards,
David
Replies have been locked on this page!