Feeds:
Posts
Comments

I reprocessed an existing tabular model in order to refresh data but it came up with connection related errors as shown below.

Tabular model processing error

I do have permission to the views, cube and everything else but I still can’t reprocess the model. How odd!

The fix is to change the connection manager provider from SQL Server Native Client 11.0 to Microsoft OLE DB Provider for SQL Server.

Tabular model processing error FIX

 

 

 

 

 

Advertisements

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

 

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:

 

 

 

%d bloggers like this: