This discussion is archived
9 Replies Latest reply: Mar 5, 2013 7:59 AM by 959186 RSS

Prompt for one month and show 3 months of Data

959186 Newbie
Currently Being Moderated
Hi,
I have this Issue with the Report.
When a User prompt for One Month Like July, Report should also show May & June Sales Data.

How should i do?

Any help Appreciated
Thanks
  • 1. Re: Prompt for one month and show 3 months of Data
    Srini VEERAVALLI Guru
    Currently Being Moderated
    Once you get the month from prompt, try to get the min(date) of that month
    that would be 07/01/2013 and substract 3 months and use between operator so that report can fetch 3 months data.

    You might need to use date field in the report and then convert that to SQL and follow as I said.

    Try it out couple of times if you are still required help then let me know with same data.

    If helps mark

    Any updates on this?

    Edited by: Srini VEERAVALLI on Feb 22, 2013 2:43 PM
  • 2. Re: Prompt for one month and show 3 months of Data
    959186 Newbie
    Currently Being Moderated
    Srini,
    Well your Answer helped me in displaying past 3 months data from the current date but it should be dynamic when one selects a month.

    SELECT Time."Month" saw_0 FROM "SA" WHERE Time.Date <= current_date
    and Time.Date>=(timestampadd(sql_tsi_month,-3,current_date)) ORDER BY
    saw_0
    But instead of Current date i need to use the Month selected via Prompt?

    Can you help me.. thanks
  • 3. Re: Prompt for one month and show 3 months of Data
    Srini VEERAVALLI Guru
    Currently Being Moderated
    I'm not sure about data format of your month YYYY/MM or MM/YYYY
    try to concat DD to that month value and cast it
    and use it instead of current_date in given sql

    that should work
  • 4. Re: Prompt for one month and show 3 months of Data
    959186 Newbie
    Currently Being Moderated
    We have the YYYY/MM format...Can you please help me in writing down that expression..

    i am also trying that.. thanks
  • 5. Re: Prompt for one month and show 3 months of Data
    Srini VEERAVALLI Guru
    Currently Being Moderated
    SELECT Time."Month" saw_0 FROM "SA" WHERE Time.Date <= '01/'||substring('2013/02',6,2)||'/'||substring('2013/02',1,4)
    and Time.Date>=(timestampadd(sql_tsi_month,-3,'01/'||substring('2013/02',6,2)||'/'||substring('2013/02',1,4))) ORDER BY
    saw_0

    repalce '2013/02' with actual column
  • 6. Re: Prompt for one month and show 3 months of Data
    959186 Newbie
    Currently Being Moderated
    SELECT "Time"."Month" saw_0 FROM "Sales - Invoice Lines" WHERE "Time"."Date" <= '01/'||substring("Time"."Month",6,2)||'/'||substring("Time"."Month",1,4) and Time.Date>=(timestampadd(sql_tsi_month,-3,'01/'||substring("Time"."Month",6,2)||'/'||substring("Time"."Month",1,4))) ORDER BY saw_0

    throws a ODBC error

    Error Codes: OPR4ONWY:U9IM8TAC:OI2DL65P
    State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 43113] Message returned from OBIS. [nQSError: 17001] Oracle Error code: 12801, message: ORA-12801: error signaled in parallel query server P002 ORA-01843: not a valid month at OCI call OCIStmtFetch. [nQSError: 17012] Bulk fetch failed. (HY000)


    SQL Issued: SELECT 0 s_0, "Sales - Invoice Lines"."Time"."Month" s_1, "Sales - Invoice Lines"."Fact - Invoice Lines"."Invoice Amount" s_2 FROM "Sales - Invoice Lines" WHERE (("Time"."Month" IN (SELECT "Time"."Month" saw_0 FROM "Sales - Invoice Lines" WHERE "Time"."Date" <= '01/'||substring("Time"."Month",6,2)||'/'||substring("Time"."Month",1,4) and Time.Date>=(timestampadd(sql_tsi_month,-3,'01/'||substring("Time"."Month",6,2)||'/'||substring("Time"."Month",1,4))) ORDER BY saw_0)))
  • 7. Re: Prompt for one month and show 3 months of Data
    Srini VEERAVALLI Guru
    Currently Being Moderated
    You said that month col is in the form of YYYY/MM

    I would suggest to debug the below code by putting in any col expression in criteria

    '01/'||substring("Time"."Month",6,2)||'/'||substring("Time"."Month",1,4))

    make sure you get the date with no spaces
  • 8. Re: Prompt for one month and show 3 months of Data
    959186 Newbie
    Currently Being Moderated
    Hi guys,
    Well the Problem with Month is solved by changing the default date format in Localedefinations.xml file to mm/dd/yyyy in the dateshortformat and altering the date format in Physical layer to dd/mm/yyyy

    SELECT "Time"."Month" saw_0 FROM "Sales - Invoice Lines" WHERE "Time"."Date" <= CAST('01/'||substring("Time"."Month",8,2)||'/'||substring("Time"."Month",1,4)AS DATE) and "Time"."Date">=timestampadd(sql_tsi_month,-3,CAST('01/'||substring("Time"."Month",8,2)||'/'||substring("Time"."Month",1,4)AS DATE)) ORDER BY saw_0

    Now i am facing one more issue from the above sql like

    Error Codes: OPR4ONWY:U9IM8TAC:OI2DL65P
    State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 43113] Message returned from OBIS. [nQSError: 17001] Oracle Error code: 12801, message: ORA-12801: error signaled in parallel query server P002 ORA-01858: a non-numeric character was found where a numeric was expected at OCI call OCIStmtFetch. [nQSError: 17012] Bulk fetch failed. (HY000)

    SQL Issued: SELECT 0 s_0, "Sales - Invoice Lines"."Time"."Month" s_1, "Sales - Invoice Lines"."Fact - Invoice Lines"."Invoice Amount" s_2 FROM "Sales - Invoice Lines" WHERE (("Time"."Month" IN (SELECT "Time"."Month" saw_0 FROM "Sales - Invoice Lines" WHERE "Time"."Date" <= CAST('01/'||substring("Time"."Month",8,2)||'/'||substring("Time"."Month",1,4)AS DATE) and "Time"."Date">=timestampadd(sql_tsi_month,-3,CAST('01/'||substring("Time"."Month",8,2)||'/'||substring("Time"."Month",1,4)AS DATE)) ORDER BY saw_0 )))

    any help appreciated
  • 9. Re: Prompt for one month and show 3 months of Data
    959186 Newbie
    Currently Being Moderated
    i am sorry in the localedefinations.xml the date format was changed to dd/MM/yyyy.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points