none
SSRS - Very dynamic columns

    Question

  • Hi all,

    As part of a data driven dataload process, I'm providing users with a facility to look at errors that have occurred at a row level in the data cleaning process. i.e. those rows that failed the cleaning process and were left in the staging database, but not copied to the clean database.

    Each table in the staging database has an additional column, DL_LoadErrorMessage which indicates the reason why the row failed cleaning, and is empty otherwise.

    Part of my dataload reference data are tables:

    DL_System - indicates details about the source system, the staging and clean databases

    DL_Table - a row per table. Is a child table of DL_System. Contains rules IN SQL snippets.

    DL_Field - a row per field. Is a child table of DL_Table. Contains rules and field validation in SQL snippets.

    The dataload processes uses these three tables to perform the entire dataload in an entirely data driven way using dynamic SQL.

    I have a series of reports that then let the user drill down from system level to look at what staging errors occurred and how many rows failed for which reason. I then want a click through to the rows that errors for that table. I just want to wrote on report that will show all the rows on a staged table where DL_LoadErrorMessage is equal to a specific value. The SQL that returns this from the server will be constructed in dynamic SQL and is of the form 'SELECT * FROM '+ @TableName + ' WHERE DL_LoadErrorMessage = ''' + @ErrorMessage +''''

    This will give the user the ability to then go back to the source system and address the error (mostly Informix dates, currently).

    I have no idea what any of the column names might be at report design time, their type, or the number of them. The only thing I do know is that they will all have a DL_LoadErrorMessage column.

    Is there any way in SSRS where I can supply a dataset to a control that may have an entirely different structure each time it is called and it will derive the column names and populate itself accordingly?

    Yours, in hope,

    Mark

    PS. SSRS 2008R2.

    Wednesday, March 13, 2013 6:06 PM

Answers

  • Hi Mark,

    From your description, you want to retrieve the table information which rows that failed the cleaning process, right? Since you don’t know any of the column name and type of it, I am afraid you cannot get the information just base on the DL_LoadErrorMessage column. Furthermore, the columns are dynamic each time it is called, we cannot display the dynamic column on the report at the run time.

    Hope this helps.

    Regards,


    Charlie Liao
    TechNet Community Support

    Monday, March 18, 2013 8:38 AM

All replies

  • Hi Mark,

    From your description, you want to retrieve the table information which rows that failed the cleaning process, right? Since you don’t know any of the column name and type of it, I am afraid you cannot get the information just base on the DL_LoadErrorMessage column. Furthermore, the columns are dynamic each time it is called, we cannot display the dynamic column on the report at the run time.

    Hope this helps.

    Regards,


    Charlie Liao
    TechNet Community Support

    Monday, March 18, 2013 8:38 AM
  • Hi Charlie,

    That's a shame; I hoped their might be a tablix version of SELECT *.  Alas not. I figure I'm going to have to derive some other sort of technology to display the error rows rather than Reporting Services, as I have well over a thousand tables, and I'm not going to generate (programmatically or otherwise) a thousand reports.

    Darn, Microsoft! Get it together.

    Thanks,

    Mark

    Saturday, March 23, 2013 12:17 PM