SQL Report from iTunes

Mini weekend Project.

Had this idea it might be interesting to pull out some visualisation from my iTunes library.

1st step was to get the information out of iTunes. Fortunately Apple have made it nice and easy by providing a nice XML file that stores all your information called iTunes Music Library.xml

The next step is to take this file and push the data in to SQL. I found a few blogs that had some SQL scripts, but none of them seemed to work properly. I then found a great article at ‘The Code Project’ web site - http://www.codeproject.com/KB/database/sqltunes.aspx

This will take your XML file and create a DB in SQL for you:

Once this is run you should then find your data in your SQL database:

So what can we do with this information?

Why not create a dashboard?:

No Comments about music choice please!

FetchXML Reporting in Microsoft Dynamics CRM 2011–Part 2

 

I wrote a blog a couple of months ago about the ability to create reports in CRM 2011 using FetchXML instead of standard SQL.

In part two I’m going to cover a couple of techniques that will also be useful:

Contextual FetchXML Reports
Parameters

 

Contextual FetchXML Reports

In the ‘Normal’ world of SQL there is a process for creating reports for CRM to run contextually, that is return results contextual to the record you are running it from, for example running a report to pull back all Cases for a Contact.

The Prefix CRMAF_ was used in the query which CRM recognised as a reference to contextualise the results.

The same is accomplished in CRM with FetchXML using the following syntax (Highlighted):

<fetch distinct="false" no-lock="false" mapping="logical">
<entity name="contact" enableprefiltering="1" prefilterparametername="CRM_FilteredContact">
<attribute name="contactid" />
<attribute name="fullname" />
<link-entity name="incident" to="contactid" from="customerid" link-type="outer" alias="incident1" enableprefiltering="1" prefilterparametername="CRM_FilteredIncident">
<attribute name="prioritycode" alias="incident1_prioritycode" />
<attribute name="createdon" alias="incident1_createdon" />
<attribute name="title" alias="incident1_title" />
<attribute name="incidentid" />
<link-entity name="contact" to="customerid" from="contactid" link-type="outer" alias="LE_53CF0568">
<attribute name="fullname" alias="LE_53CF0568_fullname" />
</link-entity>
</link-entity>
</entity>
</fetch>

Parameters

So as in SQL reports, adding parameters in FetchXML reports allows the user to decide what data will be returned, based on criteria. For the following example we’ll add a date parameter for when the returned cases where created:

<fetch distinct="false" no-lock="false" mapping="logical">
<entity name="contact" enableprefiltering="1" prefilterparametername="CRM_FilteredContact"><attribute name="contactid" />
<attribute name="fullname" />
<link-entity name="incident" to="contactid" from="customerid" link-type="outer" alias="incident1" enableprefiltering="1" prefilterparametername="CRM_FilteredIncident">
<attribute name="prioritycode" alias="incident1_prioritycode" />
<attribute name="createdon" alias="incident1_createdon" />
<attribute name="title" alias="incident1_title" />
<attribute name="incidentid" />
<filter type="and">
  <condition attribute="createdon" operator="on-or-after" value="@date" />
</filter>
<link-entity name="contact" to="customerid" from="contactid" link-type="outer" alias="LE_53CF0568"><attribute name="fullname" alias="LE_53CF0568_fullname" />
</link-entity>
</link-entity>
</entity>
</fetch>

Worth noting here that there are several options for the ‘Operator’ part of the filter. They include:

eq – Equal
on-or-after
on-or-before
gt – Greater Than
lt – Less Than
ne – Not Equal To

Also note that in the example above, you could also hard code a date parameter in or use a date picker.

Hope that helps, but for more information it’s worth checking the section on Technet - http://technet.microsoft.com/en-us/library/gg328117.aspx

Customise The CRM 2011 Masthead

Before I start, I have to say, like the fix in CRM 4.0, the following (While simple) is still unsupported.

One of the nice tweaks you could do in CRM 4.0 was change elements of the masthead.

Essentially the process is the same in CRM 2011, but some things have changed.

There are a couple of elements to the Masthead area:

Microsoft CRM Logo (Center) – C:\Program Files\Microsoft Dynamics CRMWeb\_imgs\CRMMastheadLogo.png

Blue Masthead (Right) - C:\Program Files\Microsoft Dynamics CRMWeb\_imgs\theme\Outlook14Silver\Masthead.png

Both can be customised.

ALWAYS back up the originals and maintain the dimensions.