Reporting from multiple tables at once

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Reporting Services » Reporting Services » Reporting from multiple tables at once

Reporting from multiple tables at once

Rate Topic

Display Mode

Topic Options

Author

Message

Jasmine D. Adamson

Posted Monday, October 01, 2012 3:18 PM

SSC Veteran

Group: General Forum Members
Last Login: Thursday, May 16, 2013 3:56 PM
Points: 280, Visits: 140

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.

Post #1366787

Ray M

Posted Monday, October 01, 2012 3:27 PM

UDP Broadcaster

Group: General Forum Members
Last Login: Today @ 12:42 PM
Points: 1,468, Visits: 941

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

Post #1366791

Jasmine D. Adamson

Posted Monday, October 01, 2012 3:48 PM

SSC Veteran

Group: General Forum Members
Last Login: Thursday, May 16, 2013 3:56 PM
Points: 280, Visits: 140

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.

Post #1366804

Ray M

Posted Monday, October 01, 2012 4:45 PM

UDP Broadcaster

Group: General Forum Members
Last Login: Today @ 12:42 PM
Points: 1,468, Visits: 941

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. Tongue

.
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
building-reports-with-dynamic-datasets

Post #1366823

Jasmine D. Adamson

Posted Tuesday, October 02, 2012 4:03 PM

SSC Veteran

Group: General Forum Members
Last Login: Thursday, May 16, 2013 3:56 PM
Points: 280, Visits: 140

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 BigGrin

Might actually work, though... w00t

Post #1367318

« Prev Topic | Next Topic »

Permissions