Alpha Video Training
Results 1 to 9 of 9

Thread: Report Expression Builder Question

  1. #1
    "Certified" Alphaholic mikeallenbrown's Avatar
    Real Name
    Mike Brown
    Join Date
    Nov 2009
    Location
    United States
    Posts
    1,759

    Default 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.



    Thanks in advance!

    -Mike
    Last edited by mikeallenbrown; 02-11-2013 at 01:53 PM.

  2. #2
    Member -Jinx-'s Avatar
    Real Name
    Jinx
    Join Date
    Jul 2012
    Location
    Wisconsin
    Posts
    963

    Default 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. #3
    "Certified" Alphaholic mikeallenbrown's Avatar
    Real Name
    Mike Brown
    Join Date
    Nov 2009
    Location
    United States
    Posts
    1,759

    Default Re: Report Expression Builder Question

    Quote Originally Posted by -Jinx- View Post

    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. #4
    "Certified" Alphaholic mikeallenbrown's Avatar
    Real Name
    Mike Brown
    Join Date
    Nov 2009
    Location
    United States
    Posts
    1,759

    Default 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. #5
    Member
    Real Name
    Marion Taylor
    Join Date
    Jan 2012
    Location
    Hudson Valley, NY
    Posts
    481

    Default 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
    Last edited by MarionT; 02-11-2013 at 03:22 PM.
    MSQL since 2010
    A5V11 since Feb 2012

  6. #6
    Moderator
    Real Name
    Alan Buchholz
    Join Date
    Oct 2000
    Location
    Delavan, Wisconsin
    Posts
    9,563

    Default Re: Report Expression Builder Question

    This threads question sounds a lot like another thread...

    http://msgboard.alphasoftware.com/al...ed-Report-Help
    Al Buchholz
    Bookwood Systems, LTD
    Weekly QReportBuilder Webinars Thursday 1 pm CST

    Occam's Razor - KISS
    Normalize till it hurts - De-normalize till it works.
    Advice offered and questions asked in the spirit of learning how to fish is better than someone giving you a fish.
    When we triage a problem it is much easier to read sample systems than to read a mind.

  7. #7
    "Certified" Alphaholic mikeallenbrown's Avatar
    Real Name
    Mike Brown
    Join Date
    Nov 2009
    Location
    United States
    Posts
    1,759

    Default Re: Report Expression Builder Question

    Quote Originally Posted by Al Buchholz View Post
    This threads question sounds a lot like another thread...

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

    -Mike

  8. #8
    Member
    Real Name
    Marion Taylor
    Join Date
    Jan 2012
    Location
    Hudson Valley, NY
    Posts
    481

    Default 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.
    MSQL since 2010
    A5V11 since Feb 2012

  9. #9
    "Certified" Alphaholic mikeallenbrown's Avatar
    Real Name
    Mike Brown
    Join Date
    Nov 2009
    Location
    United States
    Posts
    1,759

    Default Re: Report Expression Builder Question

    Quote Originally Posted by MarionT View Post
    @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

Similar Threads

  1. Report Expression Builder Help
    By mikeallenbrown in forum Application Server Version 11 - Web/Browser Applications
    Replies: 2
    Last Post: 01-16-2013, 06:54 AM
  2. expression builder
    By John Burke in forum Alpha Five Version 6
    Replies: 1
    Last Post: 08-06-2004, 06:57 AM
  3. IF THEN ELSE in expression builder
    By Luc Verlee in forum Alpha Five Version 5
    Replies: 5
    Last Post: 02-05-2004, 03:39 AM
  4. Expression Builder
    By Bob Whitaker in forum Alpha Five Version 5
    Replies: 2
    Last Post: 01-16-2004, 02:15 AM
  5. Expression Builder
    By Scott Rusoff in forum Alpha Five Version 5
    Replies: 2
    Last Post: 01-17-2003, 07:41 AM

Bookmarks

Posting Permissions

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