7 Replies Latest reply: Jan 7, 2011 6:26 AM by Deepak Kurup RSS

    Limit the data based on input parameters

    Piyunisha

      Hi,

      My straight table has a field CALENDAR_DATE and I want to show data for the date range given by the user in Input box.

      I have written the following code in a Macro but does not work.

       

       

      startDate = ActiveDocument.variables("vStartDate").GetContent.String & "*"
      endDate = ActiveDocument.variables("vEndDate").GetContent.String & "*"
      selexpr = ">=" & startDate & "<=" & endDate
      ActiveDocument.GetField("CALENDAR_DATE").Select(selexpr)

      Any suggessions?

       

        • Limit the data based on input parameters
          Deepak Kurup

          hi,

          Create two variable vMinDate and vMaxDate and add it into two Calendarobject.

          Create a Numeric value of date in script level.

          eg: Num(Date) as Datenum,

          and then used the below expression in your pivot table

          Sum({<Datenum {">=$(vMinDate) <= $(vMaxDate)}>} Amount)

           

            • Limit the data based on input parameters
              Piyunisha

              Hi deepak, Thanks for the quick reply.

              I do not want to show any sum(amount) in my table. I am quite new to QV, and not sure if I need to use pivot table for this simple report . I am already selecting into vminDate & vmaxDate values from a input box. what do you mean by calendar object?

                • Limit the data based on input parameters
                  Deepak Kurup

                  hi,

                  WHat i understand from your scenario(correct me if i am wrong) is, you need to restrict the data based on the date inpput from the user,

                  eg: if you user types 01/01/2010 and 23/01/2010. you should see data for only these days... right ???

                  if yes

                  right click the sheet -->new sheet object --> calendar /slider object -->

                  On top instead of Slider ,select calendar. Add one of the variable. Give a min and Max date.

                  eg : min : '01/01/2010' max : date(today(),'DD/MM/YYY')

                  In you sceanrio use a staright table add the dimesions required. Add the expression what i justed above just change it to count() and instead of Amount chang it to 1

                  count(....... 1).

                  Then go to Presentation --- select the expression field and select hidden.

                  I hope this helps.

                   

                   

                   

                   

                    • Limit the data based on input parameters
                      Deepak Kurup

                      Ops.. I messed up the spellings... I hope its clear and your able to understand it. Geeked

                        • Limit the data based on input parameters
                          Piyunisha

                          Deepak, you understood my problem right. I do not understand but linking between calender object & straight table data. I have tried all what you suggested but don't know where am I going wrong?

                          As I said, I am taking vStartDate and vEndDate from user into these variables in input box. Now, how to link this input box selection to the straight table data limitation?

                            • Limit the data based on input parameters
                              Deepak Kurup

                              hi,

                              Good... We are on right track.. I will explain you what is the relation between Calendar object and Straight table.

                              The Claendar object is used for geting input from the user. Its user interface make easy for user to select dates, hence we opt for calendar object insted of input box.

                              We dont write macro for such cases because it can be easily achieved using Straight tables or any chart .

                              Now once we get the input from the user the expression in straight table will filters

                              Count({<Datenum {">=$(vMinDate) <= $(vMaxDate)}>} 1)

                              in the above case it will retrieve all the dates between vMinDate and vMaxDate from the field Datenum and it will display 1 for where ever the condition is true. Since you dont need to display the 1 value we can hide it from the user by selecting it as hidden (In Presentation tab).

                               

                               

                              Staright table or pivot table(Pivot table doesnt have the hidden field feature) are used for aggregating data based on the user selection.

                              We cant use a table object because we can filter the data based on certain condition.

                              just check the value in you field in datenum and in vMinDate and vMaxDate. The format should be same.

                              eg: if the field contains 01/01/2010 then vMindate and vMaxDate should contain the same format.

                              0r if it contains 4009 the vMinDate and vMaxDate should contain the same.

                              I hope this helps.

                               

                               

                               

                               

                               

                               

                               

                    • Limit the data based on input parameters

                      Hi,

                      I usually in such cases create button with action 'Run macro'

                      Script for macro is:

                      sub DateRange
                      set d=ActiveDocument.GetField("Date")
                      d.Select ">=" & ActiveDocument.Evaluate("date([Start date])") & "<=" & ActiveDocument.Evaluate("date([End date])")
                      end sub

                      So user must enter two dates and press this button

                      Regards,

                      Andris