Feeds:
Posts
Comments

Archive for March, 2016

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, ” , “)

 

Advertisements

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 »

%d bloggers like this: