Recent Posts

Pages: 1 2 [3] 4 5 ... 10
21
SQL Server Reporting Service / User Roles and Permissions in SSRS
« Last post by BhushanShah on December 05, 2012, 04:24:30 PM »
To know about User Roles and Permissions in SSRS, check below link :

http://bhushan.extreme-advice.com/user-roles-and-permissions-in-ssrs/
22
QUE:- The processing of Parent for the tablix tblxyz cannot be performed. Cannot compare data of types System.Decimal and System.String. Please check the data type returned by the Parent.

ANS:- in Parent group pass the value as like

in 2005

Grouping and Sorting Property >> General >> Parent Group (Fields!xyz.Value)



in 2008

Grouping and Sorting Property >> General >> Parent Group (Fields!xyz.Value.ToString)

See Attachments



23
SQL Server Reporting Service / Data-driven Subscriptions in SSRS
« Last post by BhushanShah on December 05, 2012, 04:22:15 PM »
To know about Data-driven Subscriptions in SSRS, check my article on following link :

http://bhushan.extreme-advice.com/data-driven-subscriptions-in-ssrs/
24
You can find details about SSRS report from below stored procedure :

Note in below script ReportServer$SQL2K12 is my Report Server Database.

Code: [Select]
CREATE PROC sp_HelpSSRSReport
 @ReportName NVARCHAR(850)
,@ShowExecutionLog bit = 0
AS 
 
Declare @Namespace NVARCHAR(500)
Declare @SQL   VARCHAR(max)
 
SELECT @Namespace= SUBSTRING(
                   x.CatContent   
                  ,x.CIndex
                  ,CHARINDEX('"',x.CatContent,x.CIndex+7) - x.CIndex
                )
      FROM
     (
         SELECT CatContent = CONVERT(NVARCHAR(MAX),CONVERT(XML,CONVERT(VARBINARY(MAX),C.Content)))
                ,CIndex    = CHARINDEX('xmlns="',CONVERT(NVARCHAR(MAX),CONVERT(XML,CONVERT(VARBINARY(MAX),C.Content))))
           FROM ReportServer$SQL2K12.dbo.Catalog C
          WHERE C.Content is not null
            AND C.Type  = 2
     ) X
 
SELECT @Namespace = REPLACE(@Namespace,'xmlns="','') + ''
 
SELECT Name,CreatedBy = U.UserName
      ,CreationDate = C.CreationDate
      ,ModifiedBy = UM.UserName
      ,ModifiedDate
  FROM ReportServer$SQL2K12.dbo.Catalog C
  JOIN ReportServer$SQL2K12.dbo.Users U
    ON C.CreatedByID = U.UserID
  JOIN ReportServer$SQL2K12.dbo.Users UM
    ON c.ModifiedByID = UM.UserID
 WHERE Name = @ReportName
 
----------------------------------------------------------------------------------------------------------------------------------------------------------
-- Get parameters of the report
----------------------------------------------------------------------------------------------------------------------------------------------------------
 
 SELECT  Name = Paravalue.value('Name[1]', 'VARCHAR(250)') 
       ,Type = Paravalue.value('Type[1]', 'VARCHAR(250)') 
       ,Nullable = Paravalue.value('Nullable[1]', 'VARCHAR(250)') 
       ,AllowBlank = Paravalue.value('AllowBlank[1]', 'VARCHAR(250)') 
       ,MultiValue = Paravalue.value('MultiValue[1]', 'VARCHAR(250)') 
       ,UsedInQuery = Paravalue.value('UsedInQuery[1]', 'VARCHAR(250)') 
       ,Prompt = Paravalue.value('Prompt[1]', 'VARCHAR(250)') 
       ,DynamicPrompt = Paravalue.value('DynamicPrompt[1]', 'VARCHAR(250)') 
       ,PromptUser = Paravalue.value('PromptUser[1]', 'VARCHAR(250)') 
       ,State = Paravalue.value('State[1]', 'VARCHAR(250)') 
 FROM (   
         SELECT C.Name,CONVERT(XML,C.Parameter) AS ParameterXML
           FROM  ReportServer$SQL2K12.dbo.Catalog C
          WHERE  C.Content is not null
        AND  C.Type  = 2
        AND  C.Name  =  @ReportName
      ) a
CROSS APPLY ParameterXML.nodes('//Parameters/Parameter') p ( Paravalue )
 
----------------------------------------------------------------------------------------------------------------------------------------------------------
-- Get Datasources Associated with the report
----------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT @SQL = 'WITH XMLNAMESPACES ( DEFAULT ''' + @Namespace +''', ''http://schemas.microsoft.com/SQLServer/reporting/reportdesigner'' AS rd )
                SELECT  ReportName         = name
                       ,DataSourceName     = x.value(''(@Name)[1]'', ''VARCHAR(250)'') 
                       ,DataProvider     = x.value(''(ConnectionProperties/DataProvider)[1]'',''VARCHAR(250)'')
                       ,ConnectionString = x.value(''(ConnectionProperties/ConnectString)[1]'',''VARCHAR(250)'')
                  FROM (  SELECT C.Name,CONVERT(XML,CONVERT(VARBINARY(MAX),C.Content)) AS reportXML
                           FROM  ReportServer$SQL2K12.dbo.Catalog C
                          WHERE  C.Content is not null
                            AND  C.Type  = 2
                            AND  C.Name  = ''' + @ReportName + '''
                  ) a
                  CROSS APPLY reportXML.nodes(''/Report/DataSources/DataSource'') r ( x )
                ORDER BY name ;'
 
EXEC(@SQL)
 
----------------------------------------------------------------------------------------------------------------------------------------------------------
-- Get Data Sets , Command , Data fields Associated with the report
----------------------------------------------------------------------------------------------------------------------------------------------------------
 
SELECT @SQL = 'WITH XMLNAMESPACES ( DEFAULT ''' + @Namespace + ''', ''http://schemas.microsoft.com/SQLServer/reporting/reportdesigner'' AS rd )
SELECT  ReportName        = name
       ,DataSetName        = x.value(''(@Name)[1]'', ''VARCHAR(250)'') 
       ,DataSourceName    = x.value(''(Query/DataSourceName)[1]'',''VARCHAR(250)'')
       ,CommandText        = x.value(''(Query/CommandText)[1]'',''VARCHAR(250)'')
       ,Fields            = df.value(''(@Name)[1]'',''VARCHAR(250)'')
       ,DataField        = df.value(''(DataField)[1]'',''VARCHAR(250)'')
       ,DataType        = df.value(''(rd:TypeName)[1]'',''VARCHAR(250)'')
  FROM (  SELECT C.Name,CONVERT(XML,CONVERT(VARBINARY(MAX),C.Content)) AS reportXML
           FROM  ReportServer$SQL2K12.dbo.Catalog C
          WHERE  C.Content is not null
            AND  C.Type = 2
            AND  C.Name = ''' + @ReportName + '''
       ) a
  CROSS APPLY reportXML.nodes(''/Report/DataSets/DataSet'') r ( x )
  CROSS APPLY x.nodes(''Fields/Field'') f(df) 
ORDER BY name '
EXEC(@SQL)
 
----------------------------------------------------------------------------------------------------------------------------------------------------------
-- Get subscription Associated with the report
----------------------------------------------------------------------------------------------------------------------------------------------------------
 
SELECT Reportname = c.Name
      ,SubscriptionDesc=su.Description
      ,Subscriptiontype=su.EventType
      ,su.LastStatus
      ,su.LastRunTime
      ,Schedulename=sch.Name
      ,ScheduleType = sch.EventType
      ,ScheduleFrequency =
       CASE sch.RecurrenceType
       WHEN 1 THEN 'Once'
       WHEN 2 THEN 'Hourly'
       WHEN 4 THEN 'Daily/Weekly'
       WHEN 5 THEN 'Monthly'
       END
       ,su.Parameters
  FROM ReportServer$SQL2K12.dbo.Subscriptions su
  JOIN ReportServer$SQL2K12.dbo.Catalog c
    ON su.Report_OID = c.ItemID
  JOIN ReportServer$SQL2K12.dbo.ReportSchedule rsc
    ON rsc.ReportID = c.ItemID
   AND rsc.SubscriptionID = su.SubscriptionID
  JOIN ReportServer$SQL2K12.dbo.Schedule Sch
    ON rsc.ScheduleID = sch.ScheduleID
WHERE c.Name = @ReportName
 
----------------------------------------------------------------------------------------------------------------------------------------------------------
-- Get Snapshot associated with the report
----------------------------------------------------------------------------------------------------------------------------------------------------------
 
SELECT C.Name
      ,H.SnapshotDate
      ,S.Description
      ,ScheduleForSnapshot = ISNULL(Sc.Name,'No Schedule available for Snapshot')
      ,ScheduleType = sc.EventType
       ,ScheduleFrequency =
       CASE sc.RecurrenceType
       WHEN 1 THEN 'Once'
       WHEN 2 THEN 'Hourly'
       WHEN 4 THEN 'Daily/Weekly'
       WHEN 5 THEN 'Monthly'
       END
      ,sc.LastRunTime
      ,sc.LastRunStatus
        ,ScheduleNextRuntime = SC.NextRunTime
        ,S.EffectiveParams
      ,S.QueryParams
  FROM ReportServer$SQL2K12.dbo.History H
  JOIN ReportServer$SQL2K12.dbo.SnapshotData S
    ON H.SnapshotDataID = S.SnapshotDataID
  JOIN ReportServer$SQL2K12.dbo.Catalog c
    ON C.ItemID = H.ReportID
LEFT JOIN ReportServer$SQL2K12.dbo.ReportSchedule Rs
    ON RS.ReportID = H.ReportID
   AND RS.ReportAction = 2
LEFT JOIN ReportServer$SQL2K12.dbo.Schedule Sc
    ON Sc.ScheduleID = rs.ScheduleID
 WHERE C.Name = @ReportName
 
 
 ----------------------------------------------------------------------------------------------------------------------------------------------------------
-- Get Users List having access to reports and tasks they can perform on the report
----------------------------------------------------------------------------------------------------------------------------------------------------------
 
SELECT C.Name
      ,U.UserName
      ,R.RoleName
      ,R.Description
      ,U.AuthType
  FROM ReportServer$SQL2K12.dbo.Users U
  JOIN ReportServer$SQL2K12.dbo.PolicyUserRole PUR
    ON U.UserID = PUR.UserID
  JOIN ReportServer$SQL2K12.dbo.Policies P
    ON P.PolicyID = PUR.PolicyID
  JOIN ReportServer$SQL2K12.dbo.Roles R
    ON R.RoleID = PUR.RoleID
  JOIN ReportServer$SQL2K12.dbo.Catalog c
    ON C.PolicyID = P.PolicyID
 WHERE c.Name = @ReportName
ORDER BY U.UserName
 
 
 
----------------------------------------------------------------------------------------------------------------------------------------------------------
-- Execution Log fo the report
----------------------------------------------------------------------------------------------------------------------------------------------------------
 
If @ShowExecutionLog = 1
    SELECT C.Name
          ,Case E.Requesttype 
           WHEN 1 THEN 'Subscription' 
           WHEN 0 THEN 'Report Launch'
           ELSE ''
           END
          ,E.TimeStart 
          ,E.TimeProcessing
          ,E.TimeRendering
          ,E.TimeEnd
          ,E.Status
          ,E.InstanceName
          ,E.UserName
     FROM ReportServer$SQL2K12.dbo.ExecutionLog E
     JOIN ReportServer$SQL2K12.dbo.Catalog C
       ON E.ReportID = C.ItemID
    WHERE C.Name = @ReportName
    ORDER BY E.TimeStart DESC

After creating above stored procedure , execute it :

Code: [Select]
EXEC sp_HelpSSRSReport @ReportName = 'YourReportName',@ShowExecutionLog=0
or

Code: [Select]
EXEC sp_HelpSSRSReport @ReportName = 'YourReportName',@ShowExecutionLog=1
i found above script from below link :

http://gallery.technet.microsoft.com/scriptcenter/42440a6b-c5b1-4acc-9632-d608d1c40a5c
25
SQL Server Reporting Service / Caching SSRS Reports
« Last post by BhushanShah on November 30, 2012, 03:35:35 PM »
To know how to use Caching for SSRS Reports, check my article on following link :

http://bhushan.extreme-advice.com/caching-ssrs-reports/
26
SQL Server Reporting Service / Report Snapshots in SSRS
« Last post by BhushanShah on November 30, 2012, 03:34:37 PM »
To know about Report Snapshots in SSRS, check my article on following link :

http://bhushan.extreme-advice.com/report-snapshots-in-ssrs/
27
To know about Report Subscription(Windows File Share Delivery) in SSRS, check my article on following link :

http://bhushan.extreme-advice.com/report-subscriptionwindows-file-share-delivery-in-ssrs/
28
SQL Server Reporting Service / WCF Service as DataSource in SSRS
« Last post by BhushanShah on November 22, 2012, 05:41:08 PM »
To know how to use WCF Service as DataSource in SSRS, check below link :

http://bhushan.extreme-advice.com/wcf-service-as-datasource-in-ssrs/
29
SQL Server Reporting Service / WebService as DataSource in SSRS
« Last post by BhushanShah on November 21, 2012, 05:07:41 PM »
To know how to use WebService as DataSource in SSRS, check below link :

http://bhushan.extreme-advice.com/webservice-as-datasource-in-ssrs/
30
SQL Server Reporting Service / Gauge Data Region in SSRS
« Last post by BhushanShah on November 21, 2012, 05:07:02 PM »
To know how to use Gauge Data Region in SSRS, check below link :

http://bhushan.extreme-advice.com/gauge-data-region-in-ssrs/
Pages: 1 2 [3] 4 5 ... 10