I have a stored procedure that receives an input parameter of ReporID and then sends back the appropriate dataset to the data-driven subscription. In the stored procedure, there is some concatenated sql in producting the final select statement to send to the subscription, but it is generating the following error:
Because there are some optional columns that need to conditionally appear based on the report, I can't avoid concatenation. My only other option is to have hundreds of individual tables to source all our reports, which I don't have time to manage.
Please let me know how I can allow the data-driven subscription setup process to discover the columns for my concatenated SQL so I can seutp my schedules based on the single stored procedure with all my logic nested in my table structure.
Thanks.
If anyone has an answer, even that I can't accomplish what I want to, I would love to hear it. Otherwise, I will have to march down a very high-maintenance road that will probalby encompass more objects than I can maintain along with my other responsibilities.
I appreciate any help you can offer.
|||>>Because there are some optional columns that need to conditionally appear based on the report, I can't avoid concatenation.Does the result of the concatenation always result in the same number of columns according to your report definition, or could it possibly be creating the wrong result sometimes?
I do understand that your report has optional columns. However the dataset as defined in the report does have a set number of columns, some of which you presumably suppress for some runs. I'm saying that the stored proc may have to include placeholders for those columns in cases where they are not needed, to match up with what the RDL has defined.
Does that make sense? I'm not 100% sure that this is your problem, from your description, but it seems like a likely cause.
>L<
|||Lisa, thanks for the response. I understand what you are saying but my solution is much more complex... but designed for very simple administration once the backend work is done.
I have (for starters) 76 reports x 50 client databases x ? custom scheduling requests with unique parameters. I am facilitating all of the interactive reporting and scheduled reporting through one copy of the 76 reports.
In addition, to minimize the number of objects to maintian I have a single stored procedure that is designed to act as a data source for all data-driven subscriptions for any report, receiving in some combination of ReportID and SettingGroup. This allows me to use structure to drive the entire operation and later develop an application front end where these complex schedule setups can be managed by anyone with the business knowledge to modify boolean values and make selections in an interface.
The complexity enters with the input parameters for reports which vary greatly. Some reports require no input parameters and always use default values coded into the report. Others may use two specific input parameters in one schedule and just one (with default on others) for a different schedule. Plus the input parameters for different reports are notably going to be different based on the nature of the report.
I have addressed input parameters in a way that requires no manual schema administration. The parameter setting names, values, and datatypes are stored in rows, one row per column name, and identified by the ReportID, SettingGroup, and ClientCode. When the stored procedure for the data source of the data-driven subscription executes, it checks for any related parameter setting records. If they exist, it creates a table on the fly, converting the row values for column definitions/values into actual columns of data. Then the concatenated SQL selects the standard columns plus any parameter settings as needed.
It all works perfectly, except that the "schema discovery" doesn't work in the data-driven subscription setup process. If i could work around that somehow, I would be set. Otherwise, i will probably be forced to have my process dynamically create a table for every report using the same stored procedure. The problem with this solution is that I can't execute the stored procedure at run time for the report so that the data is fully updated with any setting changes or ClientCode activiation/inactivations before the report runs.
This may be too much information, but I'm just frustrated because I'm sitting on the edge of completion for an elegant design that will make it possible for me to manage all these subscriptions while fulfilling my DBA, System Admin, and Developer roles. Otherwise, I fear a lot of unpaid overtime in my future.
If there is a way for me to trigger the stored procedure to run before the data-driven subscriptions run against a static table that will work too. I just don't know if that is an option.
Thanks in advance for your help.
|||HI there,
I do appreciate the complexity of your situation and honestly didn't mean to minimize it by trying to explore how and what wasn't working out in a simple way.
The important thing to remember is that all complexity must be sieved down to a pure problem and most wide architecture has to be funnelled into a narrow place when it comes to integrating your work with some standardized, simplified solution (which is what RS data subscriptions is).
As I see it there are two ways we can go about this:
1) You can tell us more about your architecture and provide a simplified, reproducible version that, while not sharing all the features of your app, does serve to illustrate the place at which things go wrong with subscriptions. Then we can kick it around and try to see what changes would serve to "jimmy" the thing to work as you need.
In the process we may suggest things that will not work in your more fully-worked scenarios, and this may frustrate you further -- please be patient with us if this happens <s>.
We may also suggest architectural changes that may be extremely frustrating, even though we understand you're really happy with what you've come up with (except for this one piece)... and we'll understand if you reject those solutions.
2) You can accept the fact that RS data subscriptions are only one way of handling what you need to do, and not the best way at that. I use them, but also do *not* use them where they do not fit a business need or where my framework seems to "stretch" them in ways they were not meant to stretch.
As I have described on another thread (see http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2019010&SiteID=1) there are many scenarios in which, quite frankly, RS data subscriptions simply do not do what one wants to do, whether in terms of exception reporting, subscription channels, or whatever -- and it is a lot better to roll your own.
It seems to me that your scenario is one of them, and that SSIS is a good fit here. You want to run a stored proc before running the report, go for it.
Just remember that RS data subscriptions are only one potential flow through which scheduled reports can be managed and this is really not a "one size fits all" type of story. The actual elements of the story can be endlessly recombined (see that thread) and each task element is really pretty simple to do so from my POV RS data subscriptions isn't adding all that much. If it's getting in your way, jettison it.
I hope this helps.
>L<
|||First, I didn't mean to imply your were minimizing the complexity of my situation. I know you were just responding to a minimally defined issue that I threw out there, and it is appreciated.
Second, you are my hero! I'm a huge fan of SSIS and use it often for looping automation of tasks as well as data flows. It never occured to me to use it for this, but after looking at your other forum post and your script code, I see that this is probably the road for me. It allows me the flexibility to use very open ended, branching logic so I can handle all exceptions in one package making for easier management. I can even consolidate an FTP SSIS package I created for report delivery into this one package for an all encompassing process.
Thank you for your time and insight.
Sincerely,
Hugh
|||Good for you! The important thing is that you are willing to do the work and you see the possibilities in the tools. It's people who think that there is something holy about the shortcuts presented along with the base tool set -- again, IMHO, data subscriptions is just one such shortcut -- that really have problems.
One needs to see that the shortcuts provide you with insight into the *potential* of the tools, not the only possible end result... you clearly get this, and you'll do fine!
Regards,
>L<
