Limit the data based on input parameters

[#1]

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?

[deepakk]

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)

[#3]

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?

[deepakk]

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.

[deepakk]

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

[#6]

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?

[#7]

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

[deepakk]

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.