Traditional web-based reporting with SAS BI tools

SQL Query to Expression Conversion

Reply
Super Contributor
Posts: 443

SQL Query to Expression Conversion

select sum(hs_GPA)/sum(enrolled) from mytalbe

where ADMISSIONS_POPULATION = 'Enrolled' and enrolled >0 and hs_gpa is not null;

 

The closest thing I have is

case when (<<Measures.HS_GPASUM_for Calc>> is null) then null else sum(<<Measures.HS_GPASUM_for Calc>>)/suM(<<Measures.ENROLLEDSUM>>) end

 

Then I set a filter on where enrolled.

However, the result comes out to .2 rather than an expected GPA.  Its records with null GPA are still being calculated.

Grand Advisor
Posts: 16,393

Re: SQL Query to Expression Conversion

I'm confused, what are you trying to convert to an expression?

 

Super Contributor
Posts: 443

Re: SQL Query to Expression Conversion

select sum(hs_GPA)/sum(enrolled) from mytable

where ADMISSIONS_POPULATION = 'Enrolled' and enrolled >0 and hs_gpa is not null;

Grand Advisor
Posts: 16,393

Re: SQL Query to Expression Conversion

Ok, but why convert, why not leave as is?

This may be an alternative:

 

sum(hs_GPA*(admissions_population='Enrolled' and enrolled>0 and hs_gpa is not null))/sum(enrolled*(admissions_population='Enrolled' and enrolled>0 and hs_gpa is not null)) 
Super Contributor
Posts: 443

Re: SQL Query to Expression Conversion

I'm confused.  If I need to access the dimension in Web Report Studio don't I need to convert it to an expression to use a custom data item?

Grand Advisor
Posts: 16,393

Re: SQL Query to Expression Conversion

My bad. Missed the WRS portion. I have no idea about it. 

 

 

Post a Question
Discussion Stats
  • 5 replies
  • 191 views
  • 0 likes
  • 2 in conversation