locked
Blank fields are shown, ssrs report based on date field, MS CRM 2013 Online RRS feed

  • Question

  • Hi,



    I have a requirement to create 3 reports based on opportunity expected close date field.


    1. List of opportunities whose expected close date is within next 15 days

    2. List of opportunities whose expected close date is between next 15 days to 30 days.

    3. List of opportunities whose expected close date is between next 30 days to 45 days.

    For the first report "expected close date is within next 15 days" i got the proper output  - as i have prepared the XML query with the filter as "Next 15 days".

    but for the other two reports , since we cannot take dynamic direct query of 15-30 days or 30-45 days, I have prepared the XML query with the filter as "Next 45 days" and created the field expression in ssrs as below:

    --- expected close date is between next 15 days to 30 days---

    =IIF(((Fields!estimatedclosedateValue.Value >= DateAdd(DateInterval.Day,15,Today) AND (Fields!estimatedclosedateValue.Value <= DateAdd(DateInterval.Day,30,Today) ))), (Fields!customerid.Value)&" ("& FormatNumber( Fields!new_projectedordervalueindollar.Value,0)+")", nothing)

    ---expected close date is between next 30 days to 45 days---

    =IIF(((Fields!estimatedclosedateValue.Value >= DateAdd(DateInterval.Day,30,Today) AND (Fields!estimatedclosedateValue.Value <= DateAdd(DateInterval.Day,45,Today) ))), (Fields!customerid.Value)&" ("& FormatNumber( Fields!new_projectedordervalueindollar.Value,0)+")", nothing)

    But the report is shown with blank rows in between where the condition is not satisfied. I have tried the row visibility property as well to hide the blank fields, but no success.

    Any other approach to achieve the above mentioned functionality is appreciated.

    Wednesday, April 2, 2014 11:19 AM

Answers

  • Hi Rekha,

    Rather than doing the logic on the field, perhaps you can use the filtering settings on the table to strip out rows that does not match your Est Close Date business logic. This will eliminate the empty rows from your table and only show the data that matches your criteria.

    Here is a sample screen shot on table filtering (I am using DateCreated as an example only). Hope this helps.

     


    Eric UNG [Senior Analyst Programmer :: Sydney, Australia]

    Wednesday, April 2, 2014 11:54 AM

All replies

  • Hi Rekha,

    Rather than doing the logic on the field, perhaps you can use the filtering settings on the table to strip out rows that does not match your Est Close Date business logic. This will eliminate the empty rows from your table and only show the data that matches your criteria.

    Here is a sample screen shot on table filtering (I am using DateCreated as an example only). Hope this helps.

     


    Eric UNG [Senior Analyst Programmer :: Sydney, Australia]

    Wednesday, April 2, 2014 11:54 AM
  • Hi,

    Its working.only thing was i used =DateAdd(DateInterval.Day,30,Today) this expression.

    Ur Approach is useful. Thank u so much...

    Wednesday, April 2, 2014 1:06 PM