Solved

sql server 2005 reporting services description displayed on subscriptions

Posted on 2007-04-04
12
578 Views
Last Modified: 2009-12-16
Is there any way to change the description used for subscriptions in reporting services (sql server 2005) I would like to be able to see my comment i put in when i setup the subscription.  Since the email addy doesn't mean much to me when you are dealing with several suppliers.
0
Comment
Question by:Fraser_Admin
12 Comments
 
LVL 21

Expert Comment

by:Yurich
ID: 18853197
No, you can't change your description, the only descriptive things of your subscription you can change is a subject and commetns, but you won't see them in the list of subscriptions :(... Another thing to possible have a look at is RS database, where you can change subscription name, but I wouldn't recommend doing it from the back end.

Cheers,
Yurich
0
 
LVL 13

Expert Comment

by:Megan Brooks
ID: 18853842
Report Manager allows you to edit the description of a data driven subscription, so one option would be to use data driven subscriptions.

Otherwise, if you connect to Reporting Services using Management Studio, you can create a script for the subscription. You can then change the description in that script and then run it using the RS utility.

I would be inclined to just change it in the Description column of the Subscriptions table in the ReportServer database using an UPDATE query, rather than go to the trouble of creating, editing, and running scripts. The end result ought to be exactly the same, and you could could create the description from the comment, which you should be able to find buried in the ExtensionSettings column. You might need to take precautions to ensure that only certain subscriptions (fitting some pattern that you determine) were modified this way, rather than changing every description in the table.
0
 
LVL 13

Expert Comment

by:Megan Brooks
ID: 18854547
Apart from updating the ReportServer database directly, you could write a script to scan for reports having subscriptions, matching whatever criteria you wish, and to update the descriptions as needed. The script produced by Management Studio could serve as a starting point.
0
 

Author Comment

by:Fraser_Admin
ID: 18857522
What are the downfalls to just updating the ReportServer database subscriptions table directly.  I just tried one record and it gives me the result I'm looking for, but you guys say that isn't a good idea to do.  Just wondering why?
0
 

Author Comment

by:Fraser_Admin
ID: 18857581
i guess one downfall is that it resets it back if you update the subscription.
0
 

Author Comment

by:Fraser_Admin
ID: 18857622
which table is the comment field stored in?
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 13

Expert Comment

by:Megan Brooks
ID: 18857849
If you are updating the subscription frequently, you could have the query or script for changing the descriptions run on a regular basis. With caution, the update might also be done using a trigger.

The comments are in the Subscriptions table, embedded in the ExtensionSettings column. This column is an XML fragment, and you would have to do something such as use string functions to locate the portion of the data that represents the comment text (a function could make this easier). If the comment contains characters that must be escaped in XML then the comment string will also have to be unescapted.
0
 

Author Comment

by:Fraser_Admin
ID: 18857882
could you post some code on how to do that?
0
 
LVL 13

Accepted Solution

by:
Megan Brooks earned 500 total points
ID: 18858921
You can extract the comment from the XML fragment using a query such as
SELECT CONVERT(xml, ExtensionSettings ).value(
    '(/ParameterValues/ParameterValue[Name="Comment"]/Value)[1]', 'varchar(60)'
) AS Comment
FROM Subscriptions

The update query would be straighforward, and would depend upon how you wanted to filter it, and how you wanted to construct the new description string from the comment.It could look something like
UPDATE    Subscriptions
SET Description = ES.Comment
FROM Subscriptions INNER JOIN
    (SELECT     SubscriptionID, CONVERT(xml, ExtensionSettings ).value'(
     /ParameterValues/ParameterValue[Name="Comment"]/Value)[1]',
     'varchar(60)') AS Comment
     FROM          Subscriptions AS Subscriptions_1) AS ES ON
        Subscriptions.SubscriptionID = ES.SubscriptionID
WHERE (Subscriptions.DataSettings IS NULL) AND
    (Subscriptions.Description LIKE N'Send e-mail to %') AND
    (NOT (ES.Comment IS NULL))
 
This query only updates non-data-driven subscription descriptions, and only if the description appears to be a default description and a comment field is defined. The comment is copied directly to the description. Despending on how you use the report server, you might need a more strict filter.
0
 

Author Comment

by:Fraser_Admin
ID: 18860163
worked perfect.  had to change it to this to work for an automatic job.

SET QUOTED_IDENTIFIER  OFF

UPDATE    Subscriptions
SET Description =
 (SELECT     CONVERT(xml, z.ExtensionSettings ).value('(/ParameterValues/ParameterValue[Name="Comment"]/Value)[1]', 'varchar(60)') AS Comment
  FROM         Subscriptions z
  where subscriptions.subscriptionid = z.subscriptionid)
Where
Subscriptions.Description LIKE N'Send e-mail to %'

SET QUOTED_IDENTIFIER  ON
0
 

Author Comment

by:Fraser_Admin
ID: 18860209
I posted another question for the second part, can you move to it???
22494982
0
 

Expert Comment

by:underhillm
ID: 23438284
You can solve the problem of the description being reset every time you update a subscription by commenting out the Description field in the UpdateSubscriptions stored procedure, as below:

update Subscriptions set
        [DeliveryExtension] = @DeliveryExtension,
        [Locale] = @Locale,
        [OwnerID] = @OwnerID,
        [InactiveFlags] = @InactiveFlags,
        [ExtensionSettings] = @ExtensionSettings,
        [ModifiedByID] = @ModifiedByID,
        [ModifiedDate] = @ModifiedDate,
        --[Description] = @Description,
        [LastStatus] = @LastStatus,
        [EventType] = @EventType,
        [MatchData] = @MatchData,
        [Parameters] = @Parameters,
        [DataSettings] = @DataSettings,
    [Version] = @Version
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Hello, In my precious Article  (http://www.experts-exchange.com/Database/Reporting/A_15280-Create-Project-in-Microstrategy-Part-I.html)we saw the Configuration part for Microstrategy which included Metadata Creation and DataSource Preparation as …
Jaspersoft Studio is a plugin for Eclipse that lets you create reports from a datasource.  In this article, we'll go over creating a report from a default template and setting up a datasource that connects to your database.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now