Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics CRM (Archived) / SQL report writing
Microsoft Dynamics CRM (Archived)

SQL report writing

(0) ShareShare
ReportReport
Posted on by Community Member Microsoft Employee

Hi everyone

I am a beginner in report writing for MS CRM,this is my question I want to show opportunities on my SQL report based on the probability value  e.g 

Probability > 80 the select opportunity name,solution,.....etc

else 

select select opportunity name,solution,.....etc

I have a value integer value called probability (holds between 1-100)

I want a case statement based on this value

 

Eg

When probability >80 then select one, two, three

Else

Select one, two three .

End

 

 

The columns one,two,three are similar columns but will be selected based on the probability value.

 

How do I write this statement in the correct SQL syntax?

*This post is locked for comments

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: SQL report writing

    thanks to gfafa456 for the advice! I also know what it is like when you can't write an essay, and because of this, problems with academic performance arise. To solve this problem, you need a <a href=uk.bestessays.com/research_proposal_service.html>research proposal help</a>, and only then find a service or a company with real professionals that will help you.

  • gfafa456 Profile Picture
    gfafa456 5 on at
    RE: SQL report writing

    Got enough information and content after visiting this 365 dynamics. so be the one to have this information and get cheap assignment writing service uk in order to finish your academic work easily. Thank you for sharing this worthy content.

  • Verified answer
    Aiden Kaskela Profile Picture
    Aiden Kaskela 19,694 on at
    RE: SQL report writing

    Hi,

    There are two ways to use a case statement, depending on how complex your comparisons are.

    Here's how you'd do it with simple cases:

    select
 case statuscode
  when 1 then 'Status is 1'
  when 2 then 'Status is 2'
 end as MyStatusCode
from contact


    and with more complicated cases:

    select
 case 
  when statuscode is null or statuscode <= 1 then 'Status is 1 (or less or null)'
  when statuscode = 2 then 'Status is 2'
 end as MyStatusCode
from contact


    Hope this helps! If so I'd appreciate if you would mark this as a verified answer.

    Thanks,

      Aiden

  • Verified answer
    Jason M. Cosman Profile Picture
    Jason M. Cosman 5,234 on at
    RE: SQL report writing

    Hello MyDoll;

    When it comes to writing SSRS reports I'd start with reading technet.microsoft.com/.../dn531161.aspx. It's important to note that you should always be using Filteredentity views; in your case FilteredOpportunity. In order to simplify the SQL I'd get all of the columns for each of the different possibilities of probability and then do a case field on the table on the report designer.

    select name,probability,field1, field2,field3 from FilteredOpportunity

    Not sure if your doing a table or a chart inside of your report but your able to do filtering and case statements within the grids or value charts themselves. Hope this gives a bit of direction or at least a simplified approach instead of trying to do case statements within SQL.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Congratulations 2024 Spotlight Honorees!

Kudos to all of our 2024 community stars! 🎉

Meet the Top 10 leaders for December!

Congratulations to our December super stars! 🥳

Get Started Blogging in the Community

Hosted or syndicated blogging is available! ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,622 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,354 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans