Home RAP
New Blog Posts: Merging Reports - Part 1 and Part 2

Creating SQL on the fly in RAP

edited June 2008 in RAP
Greetings All -

Once again, looking for some RAP help...

I'm creating a query (and right now it works fine so there is no query
issue)...

Here is a Query:

select substring([account number],5,3),
sum(CASE WHEN [account number] like '4%' THEN ifnull([credit amount],0)
- ifnull([debit amount],0) ELSE 0 END) as Sales,
sum(CASE WHEN [account number] like '5%' THEN ifnull([debit amount],0) -
ifnull([credit amount],0) ELSE 0 END) as Cost,
sum(CASE WHEN [account number] like '4%' and [date] between '4/1/2007'
and '4/30/2007' THEN ifnull([credit amount],0) - ifnull([debit
amount],0) ELSE 0 END) as LastSales,
sum(CASE WHEN [account number] like '5%' and [date] between '4/1/2007'
and '4/30/2007' THEN ifnull([debit amount],0) - ifnull([credit
amount],0) ELSE 0 END) as LastCost
from genledg
where ([account number] like '423%' or [account number] like '523%')
and [date] between '4/1/2008' and '4/30/2007'
group by substring([account number],5,3)

This query works fine...

But here is what I want to do...

The date range is an autosearch, and I want to be able to take that,
take a year off of it, and put that as the date range in the 2 CASE
portions of the select statement...

Is that possible?

I'm assumming that at some point the sql code has to be recreated
manually in code, editing the 2nd set of case statements to change the
dates manually...

Any help/advice would be appreciated!

Thanks in advance...

- Chris Kiraly

Comments

  • edited June 2008
    Here is an example that shows how to manually edit the sql text and
    implement autosearch.

    www.digital-metaphors.com/tips/EditSQLAndAutoSearch.zip

    --
    Nard Moseley
    Digital Metaphors
    www.digital-metaphors.com

    Best regards,

    Nard Moseley
    Digital Metaphors
    www.digital-metaphors.com
  • edited June 2008
    Nard Moseley (Digital Metaphors) wrote:
    Well, I tried that and nothing seemed to happen...

    Here is what I have:
    The report itself is just 5 fields linked to GENLEDG, which is the
    single query piepline. Fields are Acct, Sales, Cost, LastSales, LastCost.

    The Query, created with the Query Designer, is as follows (didn't use
    the SQL field, but created it with the fields and calcs in the designer.
    However, the query itself looks like this:

    SELECT
    GENLEDG."Account Number" AS Account_Number,
    sum(CASE WHEN [account number] like '4%' THEN ifnull([credit
    amount],0) - ifnull([debit amount],0) ELSE 0 END) AS
    sum_CASE_WHEN_account_num,
    sum(CASE WHEN [account number] like '5%' THEN ifnull([debit
    amount],0) - ifnull([credit amount],0) ELSE 0 END) AS
    sum_CASE_WHEN_account_n_2,
    sum(CASE WHEN [account number] like '4%' and [date] between
    '4/1/2007' and '4/30/2007' THEN ifnull([credit amount],0) -
    ifnull([debit amount],0) ELSE 0 END) AS sum_CASE_WHEN_account_n_3,
    sum(CASE WHEN [account number] like '5%' and [date] between
    '4/1/2007' and '4/30/2007' THEN ifnull([debit amount],0) -
    ifnull([credit amount],0) ELSE 0 END) AS sum_CASE_WHEN_account_n_4
    FROM GENLEDG GENLEDG
    WHERE (( GENLEDG."Account Number" LIKE '423%' )
    OR ( GENLEDG."Account Number" LIKE '523%' )
    )
    GROUP BY GENLEDG."Account Number"

    There is 1 autosearch field on the date, asking for a between range.

    I made the following changes in the CALC section of RAP:
    [code start]
    Global Variables:
    var
    sdt1,
    edt1,
    sdt2,
    edt2: string;

    procedure ReportBeforeOpenDataPipelines;
    begin
    Report.ShowAutoSearchDialog := True;
    end;

    procedure ReportOnGetAutoSearchValues;
    var
    lSQLBuilder: TdaSQLBuilder;
    s: string;
    dategrab: TppAutoSearchField;
    begin
    lSQLBuilder := TdaSQLBuilder.Create(Report.DataPipeline);

    s := 'select substring([Account Number],5,3) as "Acct" from genledg';

    if not Report.AutoSearchFields[0].ShowAllValues then
    begin
    dategrab := Report.AutoSearchCriteriaByName('GENLEDG', 'Date');
    sdt1 := datetostr(dategrab.values[0]);
    edt1 := datetostr(dategrab.values[1]);
    sdt2 := copy(sdt1,1,6) + inttostr(strtoint(copy(sdt1,7,4))-1);
    edt2 := copy(edt1,1,6) + inttostr(strtoint(copy(sdt1,7,4))-1);
    s := 'select substring([Account Number],5,3) as "Acct",';
    s := s + 'sum(CASE WHEN [account number] like ''4%'' and [date]
    between '+ chr(39) + sdt1 + chr(39) + ' and '+ chr(39) + edt1 + chr(39)
    + ' THEN ifnull([credit amount],0) - ifnull([debit amount],0) ELSE 0
    END) as Sales,';
    s := s + 'sum(CASE WHEN [account number] like ''5%'' and [date]
    between '+ chr(39) + sdt1 + chr(39) + ' and '+ chr(39) + edt1 + chr(39)
    + ' THEN ifnull([debit amount],0) - ifnull([credit amount],0) ELSE 0
    END) as Cost,';
    s := s + 'sum(CASE WHEN [account number] like ''4%'' and [date]
    between '+ chr(39) + sdt2 + chr(39) + ' and '+ chr(39) + edt2 + chr(39)
    + ' THEN ifnull([credit amount],0) - ifnull([debit amount],0) ELSE 0
    END) as LastSales,';
    s := s + 'sum(CASE WHEN [account number] like ''5%'' and [date]
    between '+ chr(39) + sdt2 + chr(39) + ' and '+ chr(39) + edt2 + chr(39)
    + ' THEN ifnull([debit amount],0) - ifnull([credit amount],0) ELSE 0
    END) as LastCost,';
    s := s + 'from genledg';
    s := s + 'where ([account number] like ''423%'' or [account
    number] like ''523%'')';
    s := s + 'group by substring([Account Number],5,3)';
    end;

    lSQLBuilder.SQL.SQLText.Text := s;

    lSQLBuilder.Free;
    end;

    [code end]

    Now, I couldn't make the change in the OnInitializeParameters section,
    because when I tried to enter what was in the sample I was given, RAP
    kept giving me "( or [ expected". What I had in there was:

    procedure ReportOnInitializeParameters(var aCancel: Boolean);
    begin
    if (Report.AutoSearchFieldCount = 0) then
    Report.CreateAutoSearchField('GENLEDG', 'Date', 'Between',
    sdt2+','edt1, True);

    end;

    but since it kept erroring, I simply deleted it.

    When I ran the report - it only gave me figures from the auto criteria
    range, and since the sql manually created in the OnGetAutoSearchValues
    section changes the look of account number, I can tell that when I run
    the report that its not doing anything in there, and its running the
    original query.

    How do I fix this?

    Again, TIA...

    Chris
  • edited June 2008

    If you are using the Query Designer to define Calc fields, then you can use
    TdaSQLBuilder.CalcFields[ ] to acess the calc fields items at run-time. The
    items are of type TdaCalculation and have an Expression property.

    TdaSQLBuilder can be used from RAP or Delphi code.

    See the TdaSQLBuilder topic in the Reportbuilder help file. And from there
    traverse to the subtopics.



    --
    Nard Moseley
    Digital Metaphors
    www.digital-metaphors.com

    Best regards,

    Nard Moseley
    Digital Metaphors
    www.digital-metaphors.com
This discussion has been closed.