9 Replies Latest reply: Mar 5, 2013 9:59 AM by 959186 RSS

    Prompt for one month and show 3 months of Data

    959186
      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
          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
            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
              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
                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
                  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
                    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
                      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
                        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
                          i am sorry in the localedefinations.xml the date format was changed to dd/MM/yyyy.