June 21, 2011 at 11:38 am
Background info: I am trying to customize the generic SCOM 2007 reports. There is a report server on a SERVER that contains these reports. I just want to view the SQL code or copy the .rdl file to my filesystem so I can see where the data is being pulled from and make my changes to it.
So when I navigate to http://SERVER/Reports I can view all the reports just fine. But when I log into reporting services via SQL Management Stuido there are no reports visible. I believe they should all be listed there and I could right click, edit, and save them to my file system then open them in visual studios.
Why are the reports not visible when using Management Studios? I have tried using different versions of management studio but no luck.
When I check the properties of the reporting services isntalled on that server it says the report server path is http://SERVER:80/ReportServer. This is not the path I was using so I thought that might be the problem but when I open that link in a browser it still shows all the reports, it's just not in a pretty format.
So if someone could shed some light onto why these reports aren't showing up in Management Studios or give me another way to view the code for those generic reports or another way to copy the .rdl files to my filesystem I would be ecstatic.
Thanks,
Jason
June 21, 2011 at 1:19 pm
If you don't have and can't get the original source, then I think the only way you can get what you need is to start pulling apart the catalog table in your reporting services database. To get you started here is a query that will show the command text (query) for the report. To get the full RDL, involves a lot more work, and I don't have any scripts that will do the job.
set transaction isolation level read uncommitted
;WITH ReportItems (ItemID, Name, Path, reportXml) AS
(
SELECT ItemID
, Name
, Path
, CAST(CAST([Content] AS VARBINARY(MAX)) AS XML) AS reportXml
FROM Catalog
WHERE (Type = 2)
)
SELECT ItemID
, Name
, Path
, report.commandText.value('.', 'nvarchar(MAX)') AS commandText
into #temp
FROM reportItems
CROSS APPLY reportXml.nodes(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition"; //CommandText/text()') AS report(commandText)
UNION ALL
SELECT ItemID
, Name
, Path
, report.commandText.value('.', 'nvarchar(MAX)') AS commandText
FROM reportItems
CROSS APPLY reportXml.nodes(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition"; //CommandText/text()') AS report(commandText)
select * from #temp
drop table #temp
June 21, 2011 at 2:00 pm
Thank you so much. This is exactly what I needed. I didn't need the full rdl just the query is fine. Thank you, Thank you, Thank you!
June 21, 2011 at 8:18 pm
If you have been granted the appropriate rights on the report server, you can change from the default view to the detail view (on the right side - header).
Once you have that view, you can select the report properties - then select the Edit option from the General page. This will allow you to save the rdl file to the system.
Once you have the rdl file, you then create a new BIDS Report Project - add existing item and the rdl file you just saved and you can now edit the report.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 22, 2011 at 6:22 am
Jeff,
I keep reading this everywhere but I must be doing something wrong. I hit show details and I can see the edit option but there is no way to save. I can right click the icon under the edit column and save target as but that just saves the html code.
Am I missing something?
June 22, 2011 at 7:05 am
Once you have that view, you can select the report properties - then select the Edit option from the General page. This will allow you to save the rdl file to the system.
I did not know this! Thanks!
To elaborate a bit.
In Report Manger
>>on the report itself on the Properties tab
>>Under the General settings, there is a heading Report Definition
>>Click the Edit Link and you will be given an option to open or save the rdl.
June 22, 2011 at 9:34 am
There is no edit link option for me. Is this because these are default reports from SCOM? Is there a way to make it so there is an edit link option?
Under report definition is just has "Change Link" with no edit link option.
June 22, 2011 at 9:45 am
I have admin privileges.
The catalog script would work for me if I could get the damn reports to run on the default report server. It doesn't seem to pick up reports if they are run from inside SCOM operations manager application. When I try to run them on the report server I cannot get the parameters filled in correctly because it's in a crazy format because the reports are usually run right from inside the operations manager which automatically fills in most of the 15 parameters.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply