Adding StripLines with Report Builder

 Computer, Sharepoint, Software  Comments Off on Adding StripLines with Report Builder
Feb 062014
 

I have a Forecast/Trending Chart that has past and future dates. I needed to denote Today with a vertical StripLine on the chart. (Click on images for larger version.)

Forecast



You can add a Marker with an expression easily.

MarkerExpress



In your series properties; create an expression for your Marker. In my scenario, it was: =IIF(Fields!Date.value = Today(), “Star10”, “None”)
This sets a Star at the Today point on the X axis.
This is just OK, but I needed to make this a little clearer based on the X-Axis interval.

To add a StripLine, click on your X Axis to bring up the properties window and look for the StripLine section under appearance.
Open the ChartStripLine Collection Editor and Add a new stripline.
Give your line a Color and set BorderStyle to Solid. By default it’s Default and won’t show up on your chart.
Set your IntervalOffsetType to Days.
In IntervalOffset; Use an expression to calculate the number of days between the minimum date in your dataset and Today.
The expression in this scenario is: =DateDiff(DateInterval.Day,min(Fields!date.Value), Today())+1



StripEditor

Manage all SSRS Subscriptions in Sharepoint

 Computer, Sharepoint, Software  Comments Off on Manage all SSRS Subscriptions in Sharepoint
Oct 042013
 

I needed a way for me and my teammates to see and edit all report subscriptions across a Sharepoint site
with SQL Reporting Services in Integrated mode.

There are two parts here; the first is a function that returns text between delimiters. This is important to get
the site and folder from the ItemPath in the ReportServer’s Catalog table.

This function takes a string, delimiter and position. For example
dbo.fn_ParseStr(‘Server/SubSite/Folder’,’/’,2)
Will Return the SubSite as this is the 2nd item delimited.

dbo.fn_ParseStr(‘Server/SubSite/Folder’,’/’,3)
Will Return the Folder as it is the 3rd item delimited.

create Function dbo.fn_ParseStr(@Message Varchar(1000), @delimiter char(1), @index int )
Returns Varchar(1000)
As
Begin
Declare
@curIndex int = 0,
@pos int = 1,
@prevPos int = 0,
@result varchar(1000)
while @pos > 0
Begin
set @pos = CHARINDEX(@delimiter, @Message, @prevPos);
if(@pos > 0)
begin-- get the chars between the prev position to next delimiter pos
set @result = SUBSTRING(@message, @prevPos, @pos-@prevPos)
end
else
begin--get last delim message
set @result = SUBSTRING(@message, @prevPos, LEN(@message))
end
if(@index = @curIndex)
begin
return @result
end
set @prevPos = @pos + 1
set @curIndex = @curIndex + 1;
end
return ''--not found
End

Here is the query to return the fields for the report and also builds the link to edit the subscription.

USE [ReportServer]
GO
SELECT
distinct(S.[SubscriptionID]),
S.[ModifiedDate],
S.[Description],
S.[LastStatus],
S.[LastRunTime],
C.ItemID as CatalogID,
--- This substring removes the GUID from the Path in the Catalog table.
substring(C.[Path], 40, 255) as Path ,
dbo.fn_ParseStr(C.[Path],'/',2) as Site,
dbo.fn_ParseStr(C.[Path],'/',3) as Folder,
C.[Name],
[U1].[UserName] AS [Owner],
[URL2] = 'https://yourservername/'+ dbo.fn_ParseStr(C.[Path],'/',2) +
'/_layouts/ReportServer/SPSubscriptionProperties.aspx?SubscriptionID=' + CAST(S.[SubscriptionID] AS VARCHAR(80)) +
'&list=' + CAST(F.[tp_ID] AS VARCHAR(80)) + '&ID=' + CAST(D.[DoclibRowId] AS VARCHAR(80))
FROM
ReportServer.dbo.[Subscriptions] S
JOIN ReportServer.dbo.[Catalog] C ON S.[Report_OID] = C.[ItemID]
JOIN ReportServer.dbo.[Users] [U1] ON S.[OwnerID] = [U1].[UserID]
-- Your Sharepoint Content Database
JOIN WSS_Content_.dbo.AllDocs D on D.Id = C.[ItemID]
JOIN WSS_Content_.dbo.Lists F on F.tp_ID = D.ListId