SSRS matrix producing a blank page

The solution is to set the ConsumeContainerWhitespace property of the report to true.

Read Full Post »

SSRS subreport generating a blank page

When the subreport is run on its own, it does not generate any blank page, however, when it is placed on another report, it generates a blank page. To solve this, make sure the subreport has the same size as the original report (page size) for the subreport.

 

Read Full Post »

How to use different formats within a single textbox in SSRS?

I want to show a “80 out of 100” below the “80%” in a single textbox within a tablix in SSRS. To do it, create two placeholders on the same textbox and then place a break (“<br>”) at the end of the expression in the first placeholder. Format the font of the expression in the second placeholder to a smaller font.

 

Read Full Post »

How to pass multi value parameter in Oracle and SSRS

I have an SSRS report with a parameter named Responsible Department, that allows for multiple selection of values. For example: electrical, mechanical, shutdown.  These values  need to be split into  as individual records in one column as shown below.

How do we do this in Oracle? The answer is to use the regexp_substr function, introduced in Oracle 10g.

The query below shows how the Responsible Department parameter values are split. Note that there is no space in between the comma and Shutdown.

DECLARE @ResponsibleDepartment VARCHAR(MAX) = ‘Electrical,Shutdown’

DECLARE @Statement VARCHAR(MAX)
= ‘
SELECT regexp_substr(‘ + ”” + @ResponsibleDepartment + ”” + ‘,’ + ”” + ‘[^,]+’ + ”” + ‘, 1, level) as RESPONSIBLE_DEPARTMENT from dual
CONNECT BY regexp_substr(‘ + ”” + @ResponsibleDepartment + ”” + ‘,’ + ”” + ‘[^,]+’ + ”” + ‘, 1, level) is not null
‘;

EXEC (@Statement) AT BABEL

 

The query below shows how to pass them into a table. Note that there is a space in between the comma and Shutdown, hence the need to use the LTRIM function to remove the white space returned from the regexp_substr function.

DECLARE @ResponsibleDepartment VARCHAR(MAX) = ‘Electrical, Shutdown’

DECLARE @Statement VARCHAR(MAX)
= ‘
SELECT *
FROM EVENT_CODE
WHERE RESPONSIBLE_DEPARTMENT IN ( SELECT LTRIM(regexp_substr(‘ + ”” + @ResponsibleDepartment + ”” + ‘,’ + ”” + ‘[^,]+’ + ”” + ‘, 1, level)) from dual
CONNECT BY regexp_substr(‘ + ”” + @ResponsibleDepartment + ”” + ‘,’ + ”” + ‘[^,]+’ + ”” + ‘, 1, level) is not null
)
‘;

EXEC (@Statement) AT BABEL

 

To pass the multiple values of the parameter from SSRS into the SQL query, these values need to be concatenated with a space and comma. The parameter in the dataset containing the query needs to be set to below, otherwise the dataset will fail to run.

= Join(Parameters!ResponsibleDepartment.Value, ” , “)

 

Read Full Post »

How to build a pareto chart in SSRS 2012

I need to do a pareto chart in SSRS where the values on bar chart is shown in descending order but the values on the line chart in ascending order. The values on the line chart is accumulated from the values on the bar chart.

The easiest way would be to set the bar chart custom attribute of ShowColumnAs to Pareto, however, my client wants the line y-axis shown as values and not as percentage. An example is shown below.

 

So in order to do what my client wants, I need to sort the bar chart values (i.e. tonnes lost) in descending order first and then create a sort order using ROW_NUMBER function. Once my dataset is sorted correctly, I then calculate the rolling total of the tonnes lost according to the sort order. This will then be plotted against the line chart. As you can see here, I can modify the property on the Line chart (i.e. axis name,  markers etc) and the line chart should always go up or flat if the next value is zero.

SortOrder = ROW_NUMBER() OVER (ORDER BY TonnesLost DESC)

RollingTonnesLost = SUM(TonnesLost) OVER (ORDER BY SortOrder)

 

 

 

 

Read Full Post »

SSRS bar chart – set an action to open another report conditionally

I have a bar chart that will only open another report if a certain condition is met.

For example, I have a bar chart that displays the number of tons milled by location (main report).  I also have another report that only returns with data for just location A (sub report). I want to set action so when user clicks on the bar chart where the location A is, it will open the sub report. No action (no “hand” cursor” display) when the user clicks on the bar chart for the other locations.

To achieve this, open the series properties then select “Action”. Enable “Go to report” then  click on the function fx next to the “Specify the report”. Set the expression to

=IIF(Fields!Location.Value = “A”, “subreport”, Nothing)

This expression says when the Location is A then go to the report called subreport otherwise do nothing.

Read Full Post »

CRM 2013 – how to get a URL that links directly to the CRM entity

I have a few exception reports developed in SSRS to catch dirty or incomplete data entered into CRM 2013. The idea is for my clients to run these reports and then click on a record, which will open in CRM to correct the data.

Thanks to Curtis Rutland for his post on how to build URL that links directly to the CRM entity.

https://www.crmug.com/blogs/curtis-rutland/2014/04/23/how-to-make-urls-that-link-to-crm-2011-entities

Below is the information that I require from his post.

Existing Records

You need three pieces of information to link to any existing entity in CRM 2011:

• The base URL to CRM
• The Entity’s ID (GUID)
• The Entity’s ETC (entity type code) or the Entity’s ETN (entity type name, aka logical name)

With these three pieces, you can build one of these two URLs:

• http(s)://<crmUrl>/main.aspx?etc=<etc>&id=<id>&pagetype=entityrecord
• http(s)://<crmUrl>/main.aspx?etn=<etn>&id=<id>&pagetype=entityrecord

ETN is the entity’s logical name. For example, the logical name for Lead is “lead”. Note that the ETN and the Display Name is not always the same. For example, the ETN for the Notes entity is “annotation”. You can look up ETC and ETN for all built-in entities in the table at the end of the page.

Example URLs:

• https://myCrmUrl/myOrgName/main.aspx?etn=lead&id=1f569a6c-1355-e211-9b87-005056881833&pagetype=entityrecord
• https://myCrmUrl/myOrgName/main.aspx?etc=4&id=1f569a6c-1355-e211-9b87-005056881833&pagetype=entityrecord

 

 

 

Read Full Post »

RS.exe error “CreateCatalogItem is not a member of Microsoft.sqlserver.ReportingService2005.ReportingServices2005”

I am on SQL Server 2008R2 and got the above error from my RSS script, which was odd. How could it throw an error about Reporting Services 2005?

To resolve this, add the endpoint -e Mgmt2010 into the rs syntax. Example is shown below.

rs -i C:\temp\Reports.rss -s http://sql01/ReportServer -e Mgmt2010

 

Microsoft (R) Reporting Services RS
Version 10.50.1600.1 x86
Executes script file contents against the specified Report Server.
RS -i inputfile -s serverURL [-u username] [-p password]
[-l timeout] [-b] [-e endpoint] [-v var=value] [-t]

-i  inputfile   Script file to execute
-s  serverURL   URL (including server and vroot) to execute
script against.
-u  username    User name used to log in to the server.
-p  password    Password used to log in to the server.
-e  endpoint    Web service endpoint to use with the script.
Options are:
Exec2005 – The ReportExecution2005 endpoint
Mgmt2005 – The ReportService2005 endpoint
Mgmt2006 – The ReportService2006 endpoint
Mgmt2010 – The ReportService2010 endpoint
-l  timeout     Number of seconds before the connection to the
server times out. Default is 60 seconds and 0 is
infinite time out.
-b              Run as a batch and rollback if commands fail
-v  var=value   Variables and values to pass to the script
-t  trace       Include trace information in error message

Read Full Post »

RS.exe warning about the shared dataset and data source must be published before this report can be run

I created an RSS script to deploy a new report to the existing report server where the shared data source and folder have already been set up.

I got the warning error complaining that the shared data  set and data source must be published before this report can be run, which was odd.

In order to make this warning go away, the following VB code was taken out.

If Not (warnings Is Nothing) Then
Dim warning As Warning
For Each warning In warnings
Console.WriteLine(warning.Message)
Next warning
Else
Console.WriteLine(“ReportPart: {0} published successfully with no warnings”, reportPart)
End If

It is safe to take this code out because my purpose is to deploy a new report or the latest existing report where the shared components have been set up.

Read Full Post »

How to switch from native mode to integrated mode (on SSRS 2012 ONLY)?

Whilst configuring PowerView, I stumbled across the fact that I was no longer able to switch the mode from native to integrated using the Reporting Services Configuration Tool. Microsoft has decided to change this setting by integrating it to SharePoint and it is not a straight forward process as usual!  

My SQL Server 2012 developer edition (eval copy) was installed with ALL features selected. Same goes with my SharePoint Server 2010. I would presume that the SQL Server Reporting Service should have been installed and configured automatically on SharePoint but that was not the case!

Steps to start the Reporting Services SharePoint Service:

(source: http://msdn.microsoft.com/en-us/library/gg492276(v=sql.110).aspx#bkmk_create_serrviceapplication

http://msdn.microsoft.com/en-us/library/gg492276(v=sql.110).aspx#bkmk_install_SSRS)

1. Click the Start button
2. Click the Microsoft SharePoint 2010 Products group.
3. Right-click SharePoint 2010 Management Shell click Run as administrator.
4. Run the following PowerShell command to install the SharePoint service. A successful completion of the command displays a new line in the management shell. No message is returned to the management shell when the command completes successfully.

   Install-SPRSService

5. Run the following PowerShell command to install the service proxy:

   Install-SPRSServiceProxy

6. Run the following PowerShell command to start the service or see the following notes for instructions to start the service from SharePoint Central administration

   get-spserviceinstance -all |where {$_.TypeName -like "SQL Server Reporting*"} | Start-SPServiceInstance

You can also start the service from SharePoint central Administration rather than running the third PowerShell command. The following steps are also useful to verify that the service is running.

1. In SharePoint Central Administration, click Manage Services on Server in the System Settings group.
2. Find SQL Server Reporting Services Service and click Start in the Action column.
3. The status of the Reporting Services service will change from Stopped to Started. If the Reporting Services service is not in the list, use PowerShell to install the service.

Steps to create a Reporting Services Service Application on SharePoint 2010:

1. In SharePoint Central Administration, in the Application Management group, click Manage Service Applications.
2. In the SharePoint ribbon, click the New button.
3. In the New menu, click SQL Server Reporting Services Service Application.
4. 
5. Configure using the sample below:
6. 
7. If you receive an error of “Login failed for user” as below, then make sure that the NT AUTHORITY\Network Service has access to the database.
8. 
9. If  you receive an error of “Could not find stored procedure sp_dboption” then make sure that the Microsoft SharePoint Foundation Server 2010 (MUST BE 173,633KB in size) and Microsoft SharePoint Server 2010 SP1 were installed. If not, install them. The Microsoft SharePoint Server 2010 SP1 must be installed in order to support SQL Server 2012! Microsoft SharePoint Foundation Server 2010 must be installed first then followed by the Microsoft SharePoint Server 2010 SP1!!!
10. 
11. SSRS Service Application (Integrated mode) installed successfully.

Read Full Post »