Post reply

SSRS - Multiple resultsets and dynamically create tables or matricesin a report

  • November 10, 2008 at 3:24 am

    #383339

    I'm wondering if it possible to create a report based on a stored procedure with multiple resultsets and depending on how many resultsets are returned, for each resultset a table/matrix is made with all the fields of this resultset.

    Explanation: I have a database with a couple of log tables. For each table a non-technical user wants to see which events are logged. A log table could be added. Now, I have to add a table/matrix in the report each time a log table is added. I could forget to do this en so the administrators would not know if there are errors in the new log tables.

    I can create a stored procedure with multiple resultsets with all the fields of a logtable, but I can not add a table/matrix dynamically in a report (or subreport).

    Maybe, there is another way (with other software) to this. In that case, I like to hear it too.

    Thanks.

  • November 11, 2008 at 8:20 am

    #896969

    Dynamically adding another matrix control isn't possible. What you need is a way to bring all the log table data into a common field structure, with the log table name as one of the fields, so you can then group on that basis. Without that kind of setup, you're probably going to have to continue to remember the necessary maintenance tasks.

    Getting a common field structure may not be as hard as it seems, but it might make sense to ensure you reserve a few extra NULL fields for those occasions when you have to add a new table that has more fields than any of the tables from the past. CAST or CONVERT can translate DateTime or numeric fields to all character format for this purpose, so that field formatting is not an issue, but you'll have to treat every DateTime field the same way, as well as every numeric field, in order to make this easy. It could get complicated if you have to introduce new logic to handle every other non-character field, or just recreate the original problem.

    I might tend toward another solution - that of scheduling a job to determine if there are any new log tables to add to your report, and if the job detects such, it sends you an e-mail.

    Steve

    (aka smunson)

    :):):)

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • November 11, 2008 at 9:29 am

    #897016

    Can you provide how the report output would look like. I am thinking maybe you can create a Crosstab which would dynamically update the report matrix based on the resultsets.

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply