Feeds:
Posts
Comments

Archive for the ‘Reporting Services’ Category

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 »

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.

dept

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 »

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.

Example Pareto Chart 2

 

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)

 

Example Pareto Chart

 

 

 

Read Full Post »

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.

ssrs

Read Full Post »

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:

 

 

 

Read Full Post »

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 »

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 »

Older Posts »

%d bloggers like this: