October 1, 2012 at 3:18 pm
I have a weird situation... I have multiple tables which are basically the same in structure, and I need to make a similar report for all of them. I would rather not make a report for each table. The tables are differentiated by a code - the "source" and this would need to be a parameter for the report.
So here's what I want...
1. The user selects the report
2. Report asks user to name the source (this could be a dropdown pulled from another table)
3. Depending on the source, report goes to proper table and pulls report data from that table
This is causing me headaches because I can't figure out how to create a view or something which works on parameters, and if I try to use a stored procedure, the reporting designer doesn't understand what columns it returns. So, I need to know how to tell the report designer that the stored procedure returns certain columns, or I need to create a view or something else. I don't know... how would you approach this?
My stored proc is basically this - "getdata (beginTime, endTime, sourceName)" and it uses dynamic SQL to return "X, Y, Time" from the table in "sourceName" - this works great for producing line graphs in javaScript, but I want to hook it up to reporting also.
October 1, 2012 at 3:27 pm
Using the View, and not dynamic SQL.
I'm pretty sure you can do this in a procedure too, you just have to alias the columns in the select so they are all the same.
-- The view way.
CREATE VIEW AllTables
AS
SELECT
'Table1' AS src,
Col1,
Col2,
TimeCol
FROM Table1
UNION ALL
SELECT
'Table2' AS src,
Cola,
Colb,
TimeCol2
FROM Table2
UNION ALL
SELECT
'Table3' AS src,
Colx,
Coly,
TimeColz
FROM Table3
-- And so on adding more tables.
GO
SELECT *
FROM AllTables
WHERE src = @Source
October 1, 2012 at 3:48 pm
OK that's kinda what I was thinking, but these tables change periodically, so I'll have to figure out a way to generate the view, as I don't want to write it by hand every time 🙂
There's about 675 tables, with possibly 50 more to be added. It's for telemetry from a spacecraft, very complicated and I don't understand it all, I don't think anyone does.
October 1, 2012 at 4:45 pm
Doesn't sound like your creating a report. Sounds more like a data viewer for 650 different tables.
They have a tool for that, its called Sql Server Management Studio. :-P.
ha, all kidding aside, you can try a google search for SSRS Dynamic Columns, which presents a ton of results, but not quite like yours.
This post might help you along the way
October 2, 2012 at 4:03 pm
LOL, I generated that view, and it's 4900 select statements, just for the numeric columns 🙂
It was good for a laugh anyway... I wish I could post the graphical execution plan it generated - one of the silliest things I've ever seen 😀
Might actually work, though...:w00t:
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply