Dynamic SQL / Pivot

Craig Dubin shared this question 7 months ago
Answered

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

Comments (6)

photo
1

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

photo
1

Hi David,

I figured as much, was just grasping at straws. The forms engine is a great tool, but the nature of how the data is saved doesn't make it very good for reporting; at least not when custom fields are introduced. In terms of programmatically creating new views in Yellowfin, that sounds awesome! I know how to do it in SQL server...it's just more complicated block code because you have to start with a SELECT INTO statement if the combination didn't exist prior, and switch to INSERT INTO once the data exists. All perfectly doable, but ripe for errors.

I'd imagine the control flow has to be similar for creating views this way in Yellowfin, but if there's a tool or method to do this that's got less overhead and is better for error handling, I'm all in! We're migrating from 6.3 to 7.2, but I think I can just plan to work in 7.2 if it's supported in that version.


Thanks for the quick response!

Craig

photo
1

Hi Craig,

OK, well firstly I must confess that I've never done it myself but we've definitely got clients who programmatically create new content.

What they do is to write their own program that can create a Yellowfin .xml export file. I don't have any documentation on how the .xml file must be structured, but I gather it's not that hard to work out, they just got Yellowfin to create a few export files and then they studied them.

And then once your program has created the .xml export file containing the new view, then you can get your program to call the web service function that imports content into Yellowfin.

So that's a basic overview of the 2 main steps involved, and I guess you could use some sort of database trigger to call your program when a user has created one of those new forms that you were talking about.


I hope this info will help you get going, and of course, if you've got any further questions on this subject matter please don't hesitate to ask.


regards,

David

photo
1

Hi Craig,

just wondering if you've got any more questions on this topic, or whether you'd like this ticket closed?

regards,

David

photo
1

Thanks Dave, no, I think that answers the question so you can close. Thanks!

photo
1

OK, no worries Craig.

I'd be interested to hear how this project eventually goes, so, one day if you ever get a minute to share it with us that would be much appreciated.

Good luck!


David

photo