Microsoft Dynamics CRM (Archived)

CRM SSRS Report Parameter Issue

(0) Share

Report

Posted on by Community Member

I have requirment to create CRM 2016 Online report.

I need to filter report based on Parameter Created Frequency

Which have option Today,Last Week, This Week , Last Month, This Month

I made fetchXMl and this has Operator "today"

I can use 4 dataset with all these different operator but can i use single data set and pass operator(last-week, this-week, last-month, this-month, ) as parameter.

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
  <entity name="activitypointer">
    <attribute name="activitytypecode" />
    <attribute name="subject" />
    <attribute name="statecode" />
    <attribute name="prioritycode" />
    <attribute name="modifiedon" />
    <attribute name="activityid" />
    <attribute name="ownerid" />
    <attribute name="instancetypecode" />
    <attribute name="community" />
    <order attribute="modifiedon" descending="false" />
    <filter type="and">
      <condition attribute="ownerid" operator="in">
        <value uiname="Anderson, Leo" uitype="systemuser">{663D9E10-C9E9-E411-B563-9CB654959EA0}</value>
        <value uiname="Webb, Justin" uitype="systemuser">{412876C1-FED9-E411-952C-9CB65495732C}</value>
        <value uiname="Hamilton, Josh" uitype="systemuser">{17B8FC3C-62EE-E411-BD1D-9CB65495732C}</value>
      </condition>
      <condition attribute="activitytypecode" operator="eq" value="4210" />
      <condition attribute="createdon" operator="today" />
    </filter>
  </entity>
</fetch>

*This post is locked for comments

I have the same question (0)

All responses (8)

Answers (1)

Suggested answer

a33ik 84,331 Most Valuable Professional on at

Like (1)

Report

Hello,

AFAIK you can't inject parameter to operator. What you can do (and I'm sure because I implemeted similar functionality):

1. Add 2 hidden datetime parameters and populate both based on your period - picklist parameter.

2. Change operator of condition to between and usual 2 values as a parameters for your fetch.

In this way you will avoid 4 recordsets.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Thanks Andrii Butenko

I know how to use between date From: and To:

But requirement is to only Option List and user select (Today,Last Week, This Week ,Last Month, and This Month).

Can you guide how can implement as per my requirement.

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">

<entity name="activitypointer">

<attribute name="activitytypecode" />

<attribute name="subject" />

<attribute name="statecode" />

<attribute name="prioritycode" />

<attribute name="modifiedon" />

<attribute name="activityid" />

<attribute name="ownerid" />

<attribute name="instancetypecode" />

<attribute name="community" />

<order attribute="modifiedon" descending="false" />

<filter type="and">

<condition attribute="ownerid" operator="in">

<value uiname="Anderson, Leo" uitype="systemuser">{663D9E10-C9E9-E411-B563-9CB654959EA0}</value>

<value uiname="Webb, Justin" uitype="systemuser">{412876C1-FED9-E411-952C-9CB65495732C}</value>

<value uiname="Hamilton, Josh" uitype="systemuser">{17B8FC3C-62EE-E411-BD1D-9CB65495732C}</value>

</condition>

<condition attribute="activitytypecode" operator="eq" value="4210" />

<condition attribute="createdon" operator="on-or-after" value="@From" />

<condition attribute="createdon" operator="on-or-before" value="@To" />

</filter>

</entity>

</fetch>

Was this reply helpful? Yes No
a33ik 84,331 Most Valuable Professional on at

Like (1)

Report

I will show an example for Today and This Week so you will be able to complete for others.

1. Create int Parameter (I called it Period) and populate the list of available options (I used Today and This Week):

2. Add FetchXml DataSource with @From and @To parameters and click "Refresh Fields" button:

3. Click Parameters tab where you should see 2 parameters. Populate formulas for @From and @To parameters as it shown on following screenshots(first is for From, second for To):

I understand that formulas will be huge but it is what it is...

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi Andrii

I have final the report using expression it is working fine at local but when deploeyd on CRM Online 2016 it gave me error

Here is log Message

The report server has RDLSandboxing enabled and the Value expression for the query parameter '@FromDate' contains a reference to a type, namespace, or member 'Choose' that is not allowed. Change the expression to include only allowed namespaces, types, and members

Was this reply helpful? Yes No
a33ik 84,331 Most Valuable Professional on at

Like (1)

Report

Hi,

Post an expression you have here.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

=CDate(Choose(Parameters!Period.Value,Today(),DateAdd("d",1-DatePart("w",Today(),FirstDayOfWeek.Monday),Today()),DateAdd(DateInterval.Day, -6,DateAdd(DateInterval.Day, 1-Weekday(today),Today)),DateSerial(Year(Now()), Month(Now()), 1),DateAdd("m", -1, DateSerial(Year(Now()), Month(Now()), 1)))).ToString("yyyy-MM-dd")

I have extend the Expression you proived by adding More option for Last Week,This Month and Last Month

Was this reply helpful? Yes No
Verified answer

a33ik 84,331 Most Valuable Professional on at

Like (1)

Report

To make your report work in sandbox you will have to redesign query to use IIF instead of Choose. Check this for a reference - msdn.microsoft.com/.../ms157328.aspx

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

I have checked already the this Knowledge base https://support.microsoft.com/en-us/kb/2600639

but i think may be there is another reason.

Ok thanks for you support.

Was this reply helpful? Yes No