European Windows 2012 Hosting BLOG

BLOG about Windows 2012 Hosting and SQL 2012 Hosting - Dedicated to European Windows Hosting Customer

SQL Reporting Service (SSRS) 2012 Hosting - HostForLIFE.eu :: How to Display SSRS Reports in Chrome, Firefox and Safari?

clock November 3, 2015 23:21 by author Peter

By default SSRS Reports do not render in Chrome, Firefox and Safari.  However, there's a hack that may be enforced to get this working. To fix the matter, we'd like to create a custom stylesheet for SSRS.  The new style sheet will use media queries to focus on the non-IE browsers.  So, In theory, i.e. won't be impacted using this solution.  This by no means that an ideal solution, and that i haven't had time to check it in every possible situation.  However, it's worked for me once I understand a user has to view a report in a non-IE browser.  It works best if you're hyper linking to a report from another location.

 

In SQL Server 2012,  you’ll find the SSRS css files located in 

C:\Program Files\Microsoft SQL Server\MSRS11.[Instance Name]\Reporting Services\ReportServer\Styles.

Create a new css file.  In the file paste the following styles:

@media screen and (-webkit-min-device-pixel-ratio:0)

{    div[style] {        overflow:visible !important;    }}@-moz-document url-prefix() {    div[style] {         overflow:visible !important;    }}

Then you will want to take the contents of the HtmlViewer.css file and paste it before the styles above.  From what I can tell, this will tell SSRS to override the entire default css file with the new css file.

In order to call the report with the new css file you will need to use the following url format:

http://[your server dns name]/[Your SSRS Instance Webservice Path]?[Report Path]&rc.StyleSheet=[the new css file name]

For the example

http://www.yourdomain.com/ReportServer?/MyReports/Reports/TestReport&rc:Stylesheet=NonIEStylesheet

where NonIEStylesheet is the name of the css file we placed into

C:\Program Files\Microsoft SQL Server\MSRS11.[Instance Name]\Reporting Services\ReportServer\Styles.  

Also notice we didn't use the /Reports/ endpoint for SSRS.  We have to use the /ReportServer/ endpoint.

HostForLIFE.eu SQL Reporting Service (SSRS) 2012 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

 



SQL Reporting Service (SSRS) 2012 Hosting - HostForLIFE.eu :: How to Fix Subscription Inventory, Failed Subscriptions in SSRS Utility Reports?

clock October 27, 2015 23:05 by author Peter

Today, let me show you How to Fix Subscription Inventory, Failed Subscriptions in SSRS Utility Reports. When delivering a SQL Server reporting Services (SSRS) solution with countless subscriptions, it's useful to conjointly include some utility reports regarding those subscriptions.

Here are a combine of queries you'll use against the ReportServer database (in this case, SQL Server 2014) to come up with some quick internal reports for an inventory of report subscriptions (who is receiving what, when, in what format, together with parameters?) and failed report subscriptions (what subscriptions have failed and why?), each with helpful info, timestamps and URL's.

Don't forget to alter the URL path's servername for these reports to reflect your own setup, keeping in mind that if you are using a named instance, the yourservername/Reports/ could look more like yourservername/Reports_instancename/.

 

Report Subscription Inventory:
This is an easy way to provide business users with an accurate and easy list of "who's getting what" that is pulled directly from the ReportServer metadata - it'll never be out of date and it's live.
SELECT Catalog.Name AS ReportName
,'http://yourservername/Reports/Pages/Report.aspx?ItemPath=' + Catalog.Path + '&SelectedTabId=PropertiesTab&SelectedSubTabId=SubscriptionsTab' AS ReportSubscriptionMgrUrl
,Subscriptions.Description AS SubscriptionDescription
,Subscriptions.LastStatus
,Subscriptions.LastRunTime
,'Next Run Date' = CASE next_run_date
WHEN 0 THEN null
ELSE
substring(convert(varchar(15),next_run_date),1,4) + '/' +
substring(convert(varchar(15),next_run_date),5,2) + '/' +
substring(convert(varchar(15),next_run_date),7,2)
END
, 'Next Run Time' = isnull(CASE len(next_run_time)
WHEN 3 THEN cast('00:0'
+ Left(right(next_run_time,3),1)
+':' + right(next_run_time,2) as char (8))
WHEN 4 THEN cast('00:'
+ Left(right(next_run_time,4),2)
+':' + right(next_run_time,2) as char (8))
WHEN 5 THEN cast('0' + Left(right(next_run_time,5),1)
+':' + Left(right(next_run_time,4),2)
+':' + right(next_run_time,2) as char (8))
WHEN 6 THEN cast(Left(right(next_run_time,6),2)
+':' + Left(right(next_run_time,4),2)
+':' + right(next_run_time,2) as char (8))
END,'NA')
 
,Subscriptions.Parameters
,ISNULL(
Convert(XML,[ExtensionSettings]).value('(//ParameterValue/Value[../Name="TO"])[1]','nvarchar(50)')
,Convert(XML,[ExtensionSettings]).value('(//ParameterValue/Value[../Name="PATH"])[1]','nvarchar(150)')
) as [To]
,
ISNULL(
 Convert(XML,[ExtensionSettings]).value('(//ParameterValue/Value[../Name="RenderFormat"])[1]','nvarchar(150)')
, Convert(XML,[ExtensionSettings]).value('(//ParameterValue/Value[../Name="RENDER_FORMAT"])[1]','nvarchar(150)')
) as [Render Format]
,Convert(XML,[ExtensionSettings]).value('(//ParameterValue/Value[../Name="Subject"])[1]','nvarchar(150)') as [Subject]
FROM [dbo].[ReportSchedule]
INNER JOIN [dbo].[Schedule]
ON ReportSchedule.ScheduleID = Schedule.ScheduleID
INNER JOIN [dbo].[Catalog]
ON ReportSchedule.ReportID = Catalog.ItemID
INNER JOIN [dbo].[Subscriptions]
ON ReportSchedule.SubscriptionID = Subscriptions.SubscriptionID
INNER JOIN [dbo].[Users]
ON Subscriptions.OwnerID = Users.UserID
INNER JOIN msdb.dbo.sysjobs J ON Convert(nvarchar(128),[ReportSchedule].ScheduleID) = J.name
INNER JOIN msdb.dbo.sysjobschedules JS ON J.job_id = JS.job_id

Report Subscription Failures:
Now, we allows the user to see if any subscriptions have failed most recently, for handling typical email or permissions errors, in the past 30 days. Because it uses the subscription's [laststatus] field (the same one you'll see in Report Manager), failures will drop off this report if they succeed again.
SELECT Catalog.Name AS ReportName
,'http://yourservername/Reports/Pages/Report.aspx?ItemPath=' + Catalog.Path + '&SelectedTabId=PropertiesTab&SelectedSubTabId=SubscriptionsTab' AS ReportSubscriptionMgrUrl
,Users.UserName AS SubscriptionOwner
,Subscriptions.Description AS SubscriptionDescription
,Subscriptions.LastStatus
,Subscriptions.LastRunTime
FROM [dbo].[ReportSchedule]
INNER JOIN [dbo].[Schedule]
ON ReportSchedule.ScheduleID = Schedule.ScheduleID
INNER JOIN [dbo].[Catalog]
ON ReportSchedule.ReportID = Catalog.ItemID
INNER JOIN [dbo].[Subscriptions]
ON ReportSchedule.SubscriptionID = Subscriptions.SubscriptionID
INNER JOIN [dbo].[Users]
ON Subscriptions.OwnerID = Users.UserID
WHERE ((Subscriptions.DataSettings IS NULL AND Subscriptions.LastStatus LIKE 'Failure%') -- handle standard subscription errors
OR (Subscriptions.DataSettings IS NOT NULL AND RIGHT(Subscriptions.LastStatus, 11) <> '; 0 errors.'))
and Subscriptions.LastRunTime > dateadd(day, -31, getdate())



SQL Reporting Service (SSRS) 2012 Hosting - HostForLIFE.eu ::: How to Access SSRS with Fully Qualified Domain Name?

clock October 13, 2015 11:24 by author Peter

If you installed SQL Server reporting Services (SSRS) on a server in a domain and you utilize a website user to start out the service and did not perform any further configuration, then you likely will only access the Report Manager using an IP and not the fully Qualified domain name (FQDN) of the server (if an SPN isn't set).

If you are trying to use the fully Qualified domain name to access reporting services then you may likely be prompted for username password several times ending with an empty page.

In order to be able to access reporting Services using FQDN you may need to perform the subsequent actions:
1. Register a Service Principal Name (SPN) for the Domain User Account that Runs SSRS
Please look at the following example:
sample computer name: testssrs01
sample domain: example.com
sample domain account: example\ssrsuser


Next, on the Domain Controller Server in a Command Prompt with Elevated Rights, you can Run as Administrator:
Example 1:
If SSRS are on port 80 (no need to specify 80 as it is the default http port):
Setspn -s http/testssrs01.example.com example\ssrsuser

Example 2:
If SSRS are on any other port (i.e. 2430):
Setspn -s http/testssrs01.example.com:2430 example\ssrsuser

2. Edit the RsReportServer.config File
On the Reporting Services server, in the virtual directory of SSRS, edit the "RsReportServer.config" file and locate the authenticationtypes section. Then add /rswindowsnegotiate as the first entry in the authenticationtypes section.

This above step will actually enable NTLM.

HostForLIFE.eu SQL Reporting Service (SSRS) 2012 Hosting

HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.



SQL Reporting Service with Free ASP.NET Hosting - HostForLIFE.eu :: How to Fix an Error on Reporting Services “The report server installation is not initialized” ?

clock April 14, 2015 06:40 by author Peter

In this tutorial, let me tell you about How to Fix an Error on Reporting Services “The report server installation is not initialized” ? I had an undertaking for restoring the Reporting Services database from a live situation to a test domain.  Directly after the effective restore, I took  opening the Report Manager site and I was given the error "The report server establishment is not introduced. (rsReportServerNotActivated)". As you can see on the following picture:

SOLUTION

This is a result of the  mismatch in the encryption key. To alter this, either restore the encryption key from the live environment or erase the encryption key from the restored reporting administrations database.  The restoration or cancellation of encryption key could be possible by utilizing the Reporting Services Configuration Manager.

I hope this tutorial works for you!

SQL Reporting Service with Free ASP.NET Hosting
Try our SQL Reporting Service with Free ASP.NET Hosting today and your account will be setup soon! You can also take advantage of our Windows & ASP.NET Hosting support with Unlimited Domain, Unlimited Bandwidth, Unlimited Disk Space, etc. You will not be charged a cent for trying our service for the next 3 days. Once your trial period is complete, you decide whether you'd like to continue.



SQL Reporting Service (SSRS) 2012 Hosting - HostForLIFE.eu :: How to Configure Folder Permissions in SSRS ?

clock November 25, 2014 10:49 by author Peter

The very first thing that we need to do is create a new SSRS 2012 Role that may be used across multiple users/groups which gave minimum permissions towards the SSRS folder structure, however permitted all of these to make, edit, and delete their own reports. In an effort to produce the new Role we linked to Reporting Services by using the Microsoft SQL Management Studio.

Now, expand Security and then right click on Roles and click New Role

Next Enter a name and description to the Role, assign the View Reports, Handle Reports, and Consume Reports permissions, and click OK. We named our Role “Users”.

Then Browse within your SSRS Report Manager website (http://yoursqlsite/Reports)  and click on Folder Settings.

Click on New Role Assignment and type inside the user or group name you would like the Role assigned to, click the check box next to the Browser Role then click OK.

In our case we've 2 folders made, TestFolder1 and TestFolder2. We'll assign the Test1 user to TestFolder1 and also the Test2 user to TestFolder2 after which lock it down wherever they can't view each others’ folders and can't delete their root folder (TestFolderX). Hover your mouse around TestFolder1 then click the down arrow and click Security.

Click Edit Item Security, click OK upon the inherited security alert, and eliminate any users which you don't need to discover or get access to the folder. Click Edit next towards the user which you would like to have create/edit/delete report capabilities and add the new Role you produced in stage 3. The user ought to have Browser and also the new Role permissions.

Do the same thing for each folder, limiting the permissions to only the users who need use of the folders.
Login as perhaps one of the users and verify which they simply notice their assigned folders and therefore are unable to delete their parent folder.

Once Reports are produced you are also able to limit the permissions upon the Reports themselves in an effort to avoid all of these from edited or deleted.



SQL Reporting Service (SSRS) 2012 Hosting UK - HostForLIFE.eu :: Remove HTML From a String (T-SQL) on SSRS

clock November 18, 2014 07:55 by author Peter

I have been focusing on SQL Reporting Service (SSRS) 2012 using a supply database that had terribly fascinating worth inside the field. It looked such as HTML upon the website :

It is very annoying for business users to discover some thing similar to this on SSRS report :
SELECT TheName = 'Put your <span style="color: #ff0000; font-weight: bold; text-decoration: underline">name </span>in the box:'

This was a " name " column simply beneath the ID in database and there wasn't some other columns along with no HTML. I'm not so positive in case somebody took a incorrect approach making this field using HTML or that was an intension of the developer creating his life easier on the online interface, however I've determined to get yourself a quick answer to eliminate it and to provide the report when I can.
CREATE FUNCTION fn_RemoveHTMLFromText (@inputString nvarchar(max))
RETURNS nvarchar(MAX)
AS
BEGIN

  /*Variables to store source fielde temporarily and to remove tags one by one*/
  DECLARE @replaceHTML nvarchar(2000), @counter int, @outputString nvarchar(max)
  set @counter = 0
  SET @outputString = @inputString

  /*This was extra case which I've added later to remove no-break space*/
  SET @outputString = REPLACE(@outputString, '&nbsp;', '')

  /*This loop searches for tags beginning with "<" and ending with ">" */
  WHILE (CHARINDEX('<', @outputString,1)>0 AND CHARINDEX('>', @outputString,1)>0)
  BEGIN
    SET @counter = @counter + 1

    /*
    Some math here... looking for tags and taking substring storing result into temporarily variable, for example "</span>"
   */
   SELECT @replaceHTML = SUBSTRING(@outputString, CHARINDEX('<', @outputString,1), CHARINDEX('>',   @outputString,1)-CHARINDEX('<', @outputString,1)+1)

   /* Replace the tag that we stored in previous step */
   SET @outputString = REPLACE(@outputString, @replaceHTML, '')

   /* Let's clear our variable just in case... */
   SET @replaceHTML = ''

   /* Let's set up maximum number of tags just for fun breaking the loop after 15 tags */
  if @counter >15
      RETURN(@outputString);
  END
  RETURN(@outputString);
END

And now, let use the fuction:
SELECT TheName = dbo.fn_RemoveHTMLFromText ('Put your <span style="color: #ff0000; font-weight: bold; text-decoration: underline">name </span>in the box:')

And finally, this is what I wanna see:

This is simply easy resolution that has been applied upon the field along with not so many HTML tags, other then may be simply changed into more serious HTML cleaner.



European SSRS 2012 Hosting - Amsterdam :: New Features SSRS 2012

clock May 8, 2012 09:56 by author Scott

This time, I'm going to speak about Reporting Services (SSRS) and some new features.

Power View


With his new file format RDLX, Power view is a new interactive BI feature.




Based on Silverlight, end-users can visualize and interact directly with an SQL Server 2012 Analysis Services (SSAS) tabular model.


Power view is a good additional tool to display reports and to give to the end-user a solution to create self-report.


For more information, click
here.
To have a set of sample to test it, click here.

SharePoint Mode

This new feature is based on a complete new architecture similar to SharePoint 2010 shared service. With this shared service, Reporting services can use a good range of SharePoint functionalities.

You have a lot of benefits like SharePoint cross-farm support for viewing reports or the SharePoint Central Administration which supports the Reporting Services SharePoint mode Configuration.

Data Alerts

A new feature for SQL Server 2012 Reporting Services called Data Alerts notifies a list of recipients about data's report changes.
Data alerts runs in the background and polls the reports for some changes.

To create and manage these new alerts, you have 3 tools:

1. Data Alert Designer


For users to create and edit data alert definitions.




For more information, click
here.

2. Data Alert Manager for Users


Users can view information, delete or change their data alerts.


3. Data Alert Manager for alerting administrators


Administrators can view information, delete or change all data alerts created by all users.


For more information, click
here.

Report Server Projects in SQL Server Data Tools for Visual Studio 2010


Add-in to Visual Studio 2010, SQL Server Data Tools (SSDT) is a development environment to modify BI Solutions.
With this tool, you can create, open and modify Report (rdl file) from version 2005, 2008 and 2008 R2.

Excel Renderer for Microsoft Excel 2003, 2007 & 2010


To enable this feature, Microsoft Office Compatibility Pack for Word, Excel, and PowerPoint must be installed.
This extension renders a report as an Excel document compatible with Excel version 2003, 2007 and 2010
You have some limits like maximum rows/worksheet with 1048576 or Maximum columns/worksheet with 16384 but normally you have a good marge Wink

For more information, click
here.

Word Renderer for Microsoft Word 2003, 2007 & 2010


Like Excel Renderer, to enable this feature, Microsoft Office Compatibility Pack for Word, Excel, and PowerPoint must be installed.
This extension renders a report as a Word document compatible with Word version 2003, 2007 and 2010

For more information, click
here.

Conclusion

I hope this article gives you a good vision of all new features in SSRS and all new possibilities for your business and users.

You can find all information about the new Reporting Services here on the Microsoft TechNet.

My SQL Server 2012 series will be continued soon with SSAS... Want to try SSRS 2012 hosting? Please visit our site at http://www.hostforlife.eu.



About HostForLIFE.eu

HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

We have offered the latest Windows 2016 Hosting, ASP.NET Core 2.2.1 Hosting, ASP.NET MVC 6 Hosting and SQL 2017 Hosting.


Tag cloud

Sign in