Passing “LIKE” Parameters To Fetch XML in SSRS Reports

Today I encountered a situation where I needed to do a LIKE lookup in the Account entity in Dynamics CRM 2013 in SQL Server Reporting Services.

I have a simple Fetch XML Query:

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true">
     <entity name="account">
          <attribute name="name" alias="Name" />
          <filter type="and">
               <condition attribute="name" operator="like" value="@AccountName" />
          </filter>
     </entity>
</fetch>

The problem here is that I need to add “%”s to the condition to search for multiple items (i.e. the LIKE) … if I want to find any instances where the name has “aca” (to find any academy, academics, etc.) … my condition needs to be:

<condition attribute="name" operator="like" value="%" + @AccountName + "%" />

But that in itself doesn’t work … how can I get the “%”‘s into the query … don’t worry, there is a way.

1) Add a parameter to the SSRS Report as you normally would

parameter

2) Create a dataset and add a custom parameter to the dataset

- Name it whatever you would like, but set it to the value of an expression.  Make the expression look like this

="%" & Parameters!AccountName.Value & "%"

dataset

3) Use the “LIKE” parameter just created in your Fetch XML

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true">
     <entity name="account">
          <attribute name="name" alias="Name" />
          <filter type="and">
               <condition attribute="name" operator="like" value="@AccountNameLike" />
          </filter>
     </entity>
</fetch>

 

At this point, test your work, and you should be able to use LIKE parameters in Fetch XML in SQL Server Reports Service Reports.

Leave a comment

Getting the Get FormType CRM 2011+

In CRM 4, we could call the form type very easily … CRM 2011+ isn’t very difficult either

CRM4
crmForm.FormType

CRM 2011+
var type = Xrm.Page.ui.getFormType();

Possible Form Types Returned

 0 – undefined
1 – Create
2 – Update
3 - Read Only
4 - Disabled
5 - Quick Create (being depreciated)
6 - Bulk Edit

Leave a comment

SharePoint Waiting for SP.js to Load

We spent our day here at my FT job trying to figure this our out … me a senior level .NET developer and two senior level SharePoint masters.

First we had nice newly created web part, running a jQuery based carousel of pictures.  Everything was working fine, until we launched it to our SharePoint 2013 publishing page.  SP 2013 Team Pages worked just fine, SP 2010 worked just fine, and edit mode of the SP2013 publishing page worked just fine … but when we moved it to Checked In and Published, literally the web part just stopped.

Also 3 hours later we found what was happening:

We were running the command:

ExecuteOrDelayUntilScriptLoaded(function () { myFunc(); }, "sp.js");

We wanted to make sure that “sp.js” was loaded BEFORE calling our “myFunc” call … well on publishing pages in SP 2013 … they have changed the function for this to

SP.SOD.executeFunc('sp.js', 'SP.ClientContext', myFunc);

Gosh … there is 3 hours lost on my day … jeepers; we made the switch and everything worked immediately

Leave a comment

Cast OData Date to Standard Javascript Date Time

Thank you to Rajeev Pentyala for this solution (link)

The CRM DateTime value is returned from an OData call as a Edm.DateTime … which is of format “/Date(1314763200000)/” … that’s really not of use to me in Javascript.

The following code (Rajeev developed) converts this really nicely;

var dt = THE_EDM_DATETIME;
dt = dt.replace(“/Date(“, “”);
dt = dt.replace(“)/”, “”);
var dateValue = new Date(parseInt(dt, 10));

Leave a comment

Adding a Custom Font Face on Your Website

One of the things on the web for quite some time is being limited to the fonts available.

Just a few basics fonts are available:

From http://www.ampsoft.net/webdesign-l/WindowsMacFonts.html

Windows fonts / Mac fonts / Font family
Normal style Bold style
Arial, Arial, Helvetica, sans-serif Arial, Arial, Helvetica, sans-serif
Arial Black, Arial Black, Gadget, sans-serif Arial Black, Arial Black, Gadget, sans-serif
Comic Sans MS, Comic Sans MS5, cursive Comic Sans MS, Comic Sans MS5, cursive
Courier New, Courier New, monospace Courier New, Courier New, monospace
Georgia1, Georgia, serif Georgia1, Georgia, serif
Impact, Impact5, Charcoal6, sans-serif Impact, Impact5, Charcoal6, sans-serif
Lucida Console, Monaco5, monospace Lucida Console, Monaco5, monospace
Lucida Sans Unicode, Lucida Grande, sans-serif Lucida Sans Unicode, Lucida Grande, sans-serif
Palatino Linotype, Book Antiqua3, Palatino, serif Palatino Linotype, Book Antiqua3, Palatino, serif
Tahoma, Geneva, sans-serif Tahoma, Geneva, sans-serif
Times New Roman, Times New Roman, Times, serif Times New Roman, Times New Roman, Times, serif
Trebuchet MS1, Trebuchet MS, sans-serif Trebuchet MS1, Trebuchet MS, sans-serif
Verdana, Verdana, Geneva, sans-serif Verdana, Verdana, Geneva, sans-serif
Symbol, Symbol (Symbol2, Symbol2) Symbol, Symbol (Symbol2, Symbol2)
Webdings, Webdings (Webdings2, Webdings2) Webdings, Webdings (Webdings2, Webdings2)
Wingdings, Zapf Dingbats (Wingdings2Zapf Dingbats2) Wingdings, Zapf Dingbats (Wingdings2Zapf Dingbats2)
MS Sans Serif4, Geneva, sans-serif MS Sans Serif4, Geneva, sans-serif
MS Serif4, New York6, serif MS Serif4, New York6, serif

There may not be a font that you want to use … easy enough to help with that.

You can use a site like Font Squirrel and download a font face to use.

@charset 'utf-8';

@font-face
{
     font-family: {name of font};
     src: url({location of font});
}

body
{
     font-family: {name of font};
}

Or you can use a site link Google Fonts. Google provides multiple ways to include the font into your page via standard text/css stylesheet, @import to your stylesheet, or via Javasscript directly.

Leave a comment

Stored Procedure Parsing

“Find all the stored procedures that contain cursors”
“Do any of the stored procedures have temporary tables”
“Have you used function xyz in any of the procedures”

This is a difficult; I have a site that has over 600 stored procedures, and I recently needed to change a type of a major field in a table.  I need to find all the stored procedures that access this field and make sure that the type is correct.

In SQL Server, use the following command filling in MY_SEARCH appropriately. This will return to you all the stored procedures that contain your search terms.


For shorter than 4000 character stored procedures, this is fine

SELECT ROUTINE_NAME, ROUTINE_DEFINITION
FROM   INFORMATION_SCHEMA.ROUTINES
WHERE  ROUTINE_DEFINITION LIKE '%MY_SEARCH%'
       AND ROUTINE_TYPE='PROCEDURE'

This route is better and removes the 4000 character limitation

SELECT OBJECT_NAME(object_id), OBJECT_DEFINITION(object_id)
FROM   sys.procedures
WHERE  OBJECT_DEFINITION(object_id) LIKE '%MY_SEARCH%'

Leave a comment

Quick reminder on how to create an XML file in C#

1) Declare the XML  document

XmlDocument xmlDoc = new XmlDocument();

2) Add the XML Declaration Statement

XmlDeclaration xmlDeclaration = xmlDoc.CreateXmlDeclaration("1.0","utf-8",null);
xmlDoc.InsertBefore(xmlDeclaration, xmlDoc.DocumentElement);

3) Create the Root Elements can be done in two different ways

  • First way utilites the CreateElement function
XmlElement rootNode  = xmlDoc.CreateElement("Family");
xmlDoc.AppendChild(rootNode);
  • Second way is more gritty with a string
string documentStart = "<Family></Family>";
xmlDocument.Load(new StringReader(documentStart));

4) Create Parent and Child Elements

XmlElement parentNode  = xmlDoc.CreateElement("Parent");
XmlElement childNode  = xmlDoc.CreateElement("Child");
parentNode.AppendChild(childName);

Leave a comment

Follow

Get every new post delivered to your Inbox.