SQL to Fetchxml converter

This is a cool tool.

As we all are from on-premise CRM arena, one of the obvious challenges we have now is doing the same complex SSRS reports in online CRMs. In fact, challenge is writing fetchxml queries in the same way we did in t-sql queries. This is a cool free online tool by Kingswaysoft. This converts your t-sql queries to fetch.

 

Note: Please read the help page before using it.

SSRS Date Format Issue

Formatting a date can be a common requirement in SSRS reports. Though, we have few built-in methods to play around, it is best if you can do it using format options within the textbox properties. I am just going to demystify something in it.

How to get there;
Right click the text box > Select text box properties > select Number tab > select Date

Now you see this;

Now you can select any format you need.. and that’s the short and sweet way.

Anyway, as I highlighted, I noticed two separate formats which look likes same.
(Do you see what I see?). Now what does this means?

First one shows date as “1/12/2012”
While
Second one shows date as “01/12/2012”

Two different formats, though little unclear.

Resolution for issue of wrong totals in tables grouped with filters in SSRS

Previous post I explained the fruit you can have by using filters when grouping table columns. Still, it can be frustrating if you need subtotals for any numeric values in table. What really happens is totals ignore the filters. (Just behave like there is no filter!)

Suppose we add group by some field, but only needs values for given two countries. (i.e. France and Belgium). Filter will be seen as below;

So we also need to get sub totals for groups (suppose, field name as revenueVal). So what we usually do is put below method in the total row;

SUM(revenueVal)

As I explained, issue is this will sum up all the revenues of all the countries not just for filtered ones.

This is the resolution.
Define another calculated field in the DataSet.

And add the same filter criteria as an expression;

Expression will be looked like this… hope you understand the simple logic. We are replacing zero for all the rows which do not meet the criteria.

* Note: If you are going to use the totals in Matrix (instead of a table) please use “nothing” instead of “0” in above expression.

Now we are using our own calculated field for calculation; 

SUM(revenueVal_Filtered)

Now this subtotal adheres our criteria.

Filter data of Table grouping in SSRS

Table grouping is one of the most frequently used techniques in SSRS reports in practice. They can be either easy or very complex. One cool thing we can do in grouping, is putting filters. Fruit is we can have groups for our desired records only.

Most cases we need to use two conditions together as “OR” or “AND” logical join.

This is a good example of using AND condition.
Logic here is Country is France AND Sector is Banking.

Now see the OR condition which is little tricky.
Logic here is Country is either France OR Belgium.

Caution: Using filters for grouping is fun, but there is a known issue of it, if you need to get totals of some numeric columns. Simply SUM() function ignores the filters. There is a workaround for this which I will explain in a later post.

Issue when passing same parameters to more than one data set in SSRS

In SSRS, it is allowed to pass same set of parameters to more than one DataSet which is great. Still it can drag you to troubles if you don’t do it correctly. Below is one error message you may hit with;

An error has occurred during report processing.

The Value expression for the query parameter XXXX contains an error. The expression that references the parameter XXXX does not exists in the Parameters collection. Letters in the name of  parameters must use the correct case.

 

I too encountered this and couldn’t solve for hours. Ultimately, I found this has nothing to do with anything else but the order of the parameters being read in the DataSets. Usually, one may not realise this since we assign it by name.. BUT NOT ENOUGH..  make the same order in all the DataSets using the arrow above..

hmm.. simple.

Caution: And if this error encountered, you may need to delete the parameters and set them again from the scratch.

Run SSRS report against specific CRM record

When we write SSRS reports to Dynamics CRM, it may need to write a report against opened record. For example, order report against selected account. Consider my example for custom entity called office. (Schema name: new_office).

Our usual select statement which returns all the offices;

SELECT
new_name as OfficeName,
new_code as OfficeCode
Filterednew_office

Special query to return the current record specific data;

SELECT    
CRMAF_Filterednew_office.new_name as OfficeName, 
CRMAF_Filterednew_office.new_code as OfficeCode
FROM  Filterednew_office as CRMAF_Filterednew_office

Prefix “CRMAF_Filtered” seems to be doing the filtering. What you really have to do is do your query this way and configure the report so that you can run from opened form of selected entity type. (In my example its Office) Please make sure you select below option so that report is ready to be run from opened record.

By the way, if the query I need to write is complex I read the Guid of current record to a variable so that I am free to use it for any other complex queries without any issue as below;

DECLARE @OfficeId uniqueidentifier

SELECT @OfficeId =CRMAF_Filterednew_office.new_officeid
FROM  Filterednew_office as CRMAF_Filterednew_office

Concerns in passing a parameter to a sub report

In most cases we need to pass parameters to sub reports in SSRS. If parameter is not available in certain scenarios it can raise issues. I think it’s always good to do the queries in below manner when it comes to datasets in sub report.

In my example I am passing a company name as a parameter which is not mandatory one. Typically my data set returns a Brand name.  In this case, if parameter is null, relevant data fields in the sub report will show a label called “Brand not available”.

IF (@companyName is NOT NULL)
BEGIN

    SELECT new_brandname as BrandName
    FROM ....
    WHERE new_companyName = @companyName  

END

ELSE

BEGIN

   SELECT 'Brand Name not Available' as BrandName

END

A simple trick.

Issue of Select into Temp table with NULL fields

Sometimes when we create Temporary tables we might need to create some null fields. Idea is to update them later.

Consider this script which seems correct.

SELECT 'JOHN' as Name, NULL as Company into #temp

Now we will try to populate the null field with some data, probably with char data.

UPDATE #temp
SET Company = 'ABC and Company'

We are getting this error.

If you check the error message carefully, you might notice it’s a converting error. Actual problem is null field is considered to be an int, unless it’s properly defined with a type.

So correct statement to create the temp table is like this;

SELECT 'JOHN' as Name, Cast(null as varchar(50)) as Company into #temp

Convert Guid to a String in SQL

Probably it’s just a simple thing, but I had to play a little bit to figure out this... so I thought of posting…

If you want to convert a Guid to a String in your query this is how you do it.

Id =CONVERT(VARCHAR(36),opportunityid)

Practical usage comes in SSRS reports; where you can’t play around with Guids within the report surface, in fact you need to get it as a string. I faced this when I needed to have a link to real record from the report.