Search for specified items in report.
-
Hi.
Is there possibility to write script/ request, or there exists other way to find a specified string within report?
More accurate: i'd like to search all reports on my server, to find something(string of variables f.e.), which is included in one report or more.
bless.
// I've found some sql scripts, but i don't know, where to put them:
https://gallery.technet.microsoft.com/scriptcenter/c0c57332-8624-48c0-b4c3-5b31fe641c58
-
yep it can be done, but it's not obvious.
the actual rdl report is stored in an [image] datatype.
to search it, you have to convert it to varbinary, then convert it to varchar(max, and then you can search for strings int he rdl:
select * from ReportServer.dbo.Catalog
where convert(varchar(max),convert(varbinary(max),Content) )
like '%''myVariableName%'''
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
TY.
Ok. there's some magic in here.
Let me ask another question:
Is possibility tu put such a script, and work on it?
//
Lowell (1/29/2015)
yep it can be done, but it's not obvious.the actual rdl report is stored in an [image] datatype.
to search it, you have to convert it to varbinary, then convert it to varchar(max, and then you can search for strings int he rdl:
select * from ReportServer.dbo.Catalog
where convert(varchar(max),convert(varbinary(max),Content) )
like '%''myVariableName%'''
Where such a code i should put?
-
you would execute that statementin SQL server Manangement Studio, connected to teh server that you know hosts the reporting services database.
for example:
select * from ReportServer.dbo.Catalog where convert(varchar(max),convert(varbinary(max),Content) ) like '%procedure%'
gets me a screenshot like this:
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Thanks a lot. 🙂
Is there one more possibility to create tool - search, directly on report server, f.e. for client, who hasnt got access to server management, he can use only 'SQL Server Reporting Services
Home >
Reports'?
-
pilikowaty (1/29/2015)
Thanks a lot. 🙂Is there one more possibility to create tool - search, directly on report server, f.e. for client, who hasnt got access to server management, he can use only 'SQL Server Reporting Services
Home >
Reports'?
well, anything is possible....
but search what?
search the database for any old value?
search for reports that happen to have a specific string?
or the definition of reports for a specific string?
the answeer depends on the details, and just having a report itself might not be enough, knowing that a result exists or not...they would probably want a link to the details, so the actual requirement depends on what to search for.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
I though so..
I was wondering, if there exists such a tool, which lets users to search a sequence of numbers (strings)
For example. Im looking for measurement of id:123123.
So it highlights me the whole row of this id.
Ive hope im easy to understand. Im new in such an environment..
-
pilikowaty (1/29/2015)
I though so..I was wondering, if there exists such a tool, which lets users to search a sequence of numbers (strings)
For example. Im looking for measurement of id:123123.
So it highlights me the whole row of this id.
Ive hope im easy to understand. Im new in such an environment..
you'd want to make a report that searches a specific column in a specific table for the value 123123.
they universal search the entire database for any value that you posted from Nyas Kondretti is a developer only tool
you would never, ever give that kind of tool for end users.
it would cripple performance on your server.
you would create a script that specifically searches one table/columns
ie SELECT * FROM Customers WHERE Address='123123' OR Address2='123123'
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Thanks:)
-
Something to note...
If you are only looking to extract report data from the reportserver..catalog table it's good to filter for Content that is not null and [Type] = 2. Otherwise you are needlessly returning things other than reports such as folders, data sources and shared datasets.
To modify the query to Lowell posted to only return reports it would look like this:
select * from ReportServer.dbo.Catalog
where convert(varchar(max),convert(varbinary(max),Content) )
LIKE '%''myVariableName%'''
AND Content IS NOT NULL AND [Type] = 2
"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."-- Itzik Ben-Gan 2001
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply