SQL report writing

(0) Share

Report

Posted on by Community Member

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

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

I have the same question (0)

All responses (4)

Answers (2)

Sort by

Verified answer

Jason M. Cosman 5,234 Moderator on at

Like (0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
Verified answer

Aiden Kaskela 19,696 on at

Like (0)

Report
Copy link

Link copied!
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

Was this reply helpful? Yes No
gfafa456 5 on at

Like (0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No