Feeds:
Posts
Comments

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

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

 

 

 

The answer is by using TRUNC function in Oracle.

https://docs.oracle.com/cd/B28359_01/olap.111/b28126/dml_functions_2127.htm

 

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

This is rather annoying as all dates stored into CRM database need to be converted to the local time.

There are two options:

  1. Use the CRM standard views prefixed with Filtered
  2. Use the CRM standard function calleddbo.fn_UTCToTzCodeSpecificLocalTime

Using option 1 with the Filtered standard view is slow.

I ended up with option 2 as it is quicker, but I need to get the time zone code from the UserSettingsBase table.

Example code:

DECLARE @TimeZoneCode INT

= ( SELECT TOP 1 TimeZoneCode

FROM UserSettingsBase

WHERE SystemUserId = dbo.fn_FindUserGuid()

)

 

SELECT DateOfAssessment = CAST(dbo.fn_UTCToTzCodeSpecificLocalTime(DateofAssessment, @TimeZoneCode) AS DATE)

 

Reference:

https://community.dynamics.com/crm/b/develop1/archive/2011/12/06/dynamics-crm-datetimes-the-last-word

 

 

 

 

 

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:

 

 

 

I am presented with a task to support an existing tabular cube for a client.

ssas tabular model

There is a bug within the report where the number of the individuals achieving their plans are incorrect because an individual can have more than one achieved plan. It is also incorrect because it used a measure that count the number of plans achieving goals instead of the number of individuals achieving goals.

As an example, the Individual ID of 47313 and 52478 each achieved two plans, so it should be counted as one participant instead of two.

An individual can have more than one plan

As you may have known, when you create a report in SSRS over a tabular cube, the dataset query is in MDX, instead of DAX. As I am so familiar with MDX, this task is a piece of cake, however, as my client is also using Pivot Table in Excel, you can’t really modify anything in there. So the tabular cube needs to be fixed and I still need to learn how to do this in DAX.  I needed to add another measure to count the total distinct individual achieving plan goals i.e. [Measures].[Total Individuals Achieving Goals] into the cube in DAX.

MDX:

WITH

MEMBER [Measures].[Total Individuals Achieving Goals]
AS COUNT(
FILTER( [Participant].[Participant_SK].[Participant_SK].MEMBERS , [Measures].[Total Plans Achieving Goals] >= 1 )
)

MEMBER [Measures].[Total Individuals Not Achieving Goals]
AS COUNT(
FILTER( [Participant].[Participant_SK].[Participant_SK].MEMBERS , [Measures].[Total Plans Not Achieving Goals] >= 1 )
)
MEMBER [Measures].[Total Individuals Achieving Goals %] AS CASE WHEN [Measures].[Total Reviews] = 0 THEN 0 ELSE [Measures].[Total Individuals Achieving Goals] / [Measures].[Total Reviews] END

MEMBER [Measures].[Total Individuals Not Achieving Goals %] AS CASE WHEN [Measures].[Total Reviews] = 0 THEN 0 ELSE [Measures].[Total Individuals Not Achieving Goals] / [Measures].[Total Reviews] END

SELECT NON EMPTY { [Measures].[Total Reviews]
,[Measures].[Total Plans Achieving Goals]
,[Measures].[Total Plans Not Achieving Goals]
,[Measures].[Total Individuals Achieving Goals]
} ON COLUMNS
,NON EMPTY { [Participant].[CostCentreName].&[Cockburn Kwinana] } ON ROWS
FROM [Model]
WHERE ( [Participant].[IsCostCentre].&[Yes]
,[Review Date].[CurrentYTD].&[Yes]
,[Plan Details].[IsApproved].&[Yes]
)

Total Individuals Achieving Goals

As shown in the screenshot above, the report should show the total individuals achieving goals of 49 instead of 51, because two of the individuals achieved 2 plans.

At first thought, how do I convert my calculated measure of [Measures].[Total Individuals Achieving Goals]  in MDX query to DAX? I needed to filter how many participants have achieved their plan goals and then count them.

So I tried a straight conversion to DAX as shown below. It complains about semantic error of “The COUNT function only accepts a column reference as an argument”.

MDX:

WITH

MEMBER [Measures].[Total Individuals Achieving Goals]  AS COUNT(FILTER( [Participant].[Participant_SK].[Participant_SK].MEMBERS , [Measures].[Total Plans Achieving Goals] >= 1 ))

DAX:

Measure 1:=COUNT(FILTER(Plan[ParticipantID], [Total Plans Achieving Goals] >= 1))

DAX is very different from MDX so there is no simple conversion.

To do that in DAX, first count the number of distinct participants for the whole fact table and then filter how many of the distinct participants have achieved plan goals.

Total Participants:=DISTINCTCOUNT([Participant_SK])

Total Individuals Achieving Goals:=CALCULATE([Total Participants], ‘Plan Details'[PlanGoalsAchieved] = “Yes”)

Following the same logic:

Total Individuals Not Achieving Goals:=CALCULATE([Total Participants], ‘Plan Details'[PlanGoalsAchieved] = “No”)

Total Individuals Achieving Goals Percentage:=DIVIDE([Total Individuals Achieving Goals], [Total Reviews], 0)

Total Individuals Not Achieving Goals Percentage:=DIVIDE([Total Individuals Not Achieving Goals], [Total Reviews], 0)

Once the the table in the model has been processed (menu: Model -> Process -> Process Table), it will auto populate the selected table with data and deploy a copy of cube with your username included in the name. Once you are happy with the changes made, you can then deploy to overwrite the existing cube.

ssas tabular model 2

I also noticed that only the calculated measures will be listed under Measures in the tabular model. The “base” measures are listed under Dimensions. In multidimensional, all base and calculated measures appear under Measures. Furthermore, tabular does not support role playing dimensions. Say your sales fact table has got a few different dates such as OrderDate, DueDate, and ShipDate. You would need to import the Date table three times and then rename them including the meta data accordingly. You would then need to create the relationship between the sales table and date table.

You really need to change your thinking around DAX versus MDX and Tabular vs Multidimensional.

%d bloggers like this: