# Thread: Report Expression Builder Question

1. ## Report Expression Builder Question

This is an example of the SQL data I have to work with:

I want my report to return the sum of 'Cap' but broken down by 'Primdiag' ...so the report would look something like this:

PrimDiag Total Cap
115.04 ........ 118
340 ............. 93
992.0 ........... 9

My question is: What expression could I write to get these results? ...I've been struggling on how to do this for about a week.

-Mike

2. ## Re: Report Expression Builder Question

I won't be much help on the expression builder in the report so maybe someone else can help you out. Can you do it with the datasource or a subreport? If you are using SQL as your datasource you could do something like this:

select primdiag, SUM(cap) AS Summary
from yourtablename
group by primdiag

3. ## Re: Report Expression Builder Question

Originally Posted by -Jinx-

select primdiag, SUM(cap) AS Summary
from yourtablename
group by primdiag
It just occurred to me that I could do this easier in SQL rather than in the report then I saw your post ... thank you!

-Mike

4. ## Re: Report Expression Builder Question

Is there a way, in an alpha report, to add together specific 'Summary' values? ... For example I need a calculated field that adds 'summary' where 'Primdiag' is between 150.0 and 300. Using the table below the calculated field would return a value of 407.

I need to build an expression to get this ...something like: total(Summary WHERE Primdiag>='150.00' .AND. Primdiag<='300.00')

I know that my expression example isn't correct...just wrote it as an example to help you understand what I need.

-Mike

5. ## Re: Report Expression Builder Question

i'd do it at the datasource; assuming you are using microsoft SQL and know how to build CTEs....

with prelim as (
select
case when primdiag between 150 and 300 then '150-300'
when primdiag between 300 and 400 then '300-400'
else end as BreakPoint
summary
from
yourTable
)

select
BreakPoint
,sum(summary) as BreakTotal
from
prelim
group by Breakpoint

6. ## Re: Report Expression Builder Question

http://msgboard.alphasoftware.com/al...ed-Report-Help

7. ## Re: Report Expression Builder Question

Originally Posted by Al Buchholz

http://msgboard.alphasoftware.com/al...ed-Report-Help
I'll take a look ...thanks

-Mike

8. ## Re: Report Expression Builder Question

@Mike

Al's thread, and my suggestion, are the same.
If you want to sub-total on something, the 'something' needs to exist as a column

ie, if you want to sub-total everything from PrimDiag 150-300, then you need a column which identifies each row as being in that range.

It doesn't need to be a hard column within a table; it can be a virtual, calculated column. Which is what the older thread, and my post, are both suggesting.

9. ## Re: Report Expression Builder Question

Originally Posted by MarionT
@Mike

Al's thread, and my suggestion, are the same.
If you want to sub-total on something, the 'something' needs to exist as a column

ie, if you want to sub-total everything from PrimDiag 150-300, then you need a column which identifies each row as being in that range.

It doesn't need to be a hard column within a table; it can be a virtual, calculated column. Which is what the older thread, and my post, are both suggesting.
Thank you Marion ... I'm still relatively new to Alpha and SQL. Thanks for the clarification.

-mike

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•