July 27, 2015 at 6:37 am
Need to change a Excel report to SSRS.
Excel report has around 15 tables all with different columns.
Is there a way , I can show all data in SSRS by avoiding creation of 15 datasets and 15 tables.
Note -- All 15 tables have differnt columns list.
July 27, 2015 at 7:04 am
sunnysoni88 (7/27/2015)
Need to change a Excel report to SSRS.Excel report has around 15 tables all with different columns.
Is there a way , I can show all data in SSRS by avoiding creation of 15 datasets and 15 tables.
Note -- All 15 tables have differnt columns list.
Not easily. The larger question is whether those columns are likely to change names at any point in time, or perhaps have a need for additional columns. You could, with one heck of a lot of work, use a script task to read each table and "transform" it, simultaneously creating a column map table and a new table within the spreadsheet (at least temporarily anyway), and then populate that new table with column headers that are something along the lines of COL1, COL2, etc..., using the column map table to have the table name, the real column name, and the new COLx column name. Then the "transform" will just be a matter of writing out records from the original tables into the new table, including the table name of the original in the new row, along with all it's columns appearing in their original order, but under the various COLx column headers. Does that make sense? Unless you're likely to have heavy maintenance on the column names, this could work, but at the cost of significant time investment to write the transformation code and test the crap out of it. Of course, this will complicate the report portion, but at that point, each report section that you create can have it's own filter on what records will appear, and you can then limit based on the table name column. Again, this is an awful lot of work.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 27, 2015 at 7:17 am
This is seriously too much work:-D
But this gives me idea if needed we can try something of this kind.
As this report needs less investment , may be will try a easier option/
Thank You!!
July 27, 2015 at 7:27 am
sunnysoni88 (7/27/2015)
This is seriously too much work:-DBut this gives me idea if needed we can try something of this kind.
As this report needs less investment , may be will try a easier option/
Thank You!!
You're welcome. Keep in mind that should you ever adopt this idea, think about how you would deal with ensuring that each column keeps an identical data type across ALL rows coming from ALL tables. That is an absolute necessity, and date, time, and/or datetime columns will be "problematic", to say the least.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply