How to Display data based on the date selection.??

[#1]

Hi ,

I already posted this problem but didnt got much help.

I wanted to use from date and to date time span to get the data from my database. I am using slider /calender control, but i am not sure how to pass the value of this selected dated to my query i.e in my where condition. What i want to do is , when ever user select From Date and To Date then i should get all the data in that date range.

Could someone provide my a sample report. Below is my query that i want to execute.

select

nvl(sum(appt_cnt), 0), nvl(sum(semn_cnt), 0), nvl(sum(apply_cnt), 0

)

as

appts

from

web_track

where

client=

'03'

and trunc(crdt) >= to_date('12/25/2009', 'MM/DD/YYYY') and trunc(crdt) < to_date('12/25/2011', 'MM/DD/YYYY') + 1

[#2 · Accepted solution]

Create two variables,example, start_date and end_date
Assign these variable with a InputBox

Enter this code in the main Edit Script

// Title of source to display in QV
WEBTRACK:
SQL select nvl(sum(appt_cnt), 0), nvl(sum(semn_cnt), 0), nvl(sum(apply_cnt), 0 )
as appts from web_track
where
client= '03'
and trunc(crdt) >= to_date($start_date)
and trunc(crdt) < to_date($end_date) + 1

Change the dates in the inputbox and reload the document

[#3]

Thanks ,

I dont want to reload again and again. Do we have any option so that when ever user select dates the data gets displayed in my table box without doing reload

[#4]

If your query loads all data up front then it should be possible to filter out it(it can be done with the Chart object at least)

Otherwise , it is easy to use a macro to do an silent reload(and even partial)

Select Document Properties from the Settings menu

Select the variable in the Variable Event Triggers - list, click Add Action

Click Add

Select External and Run Macro, click OK

Click Edit Module

Add some code for a Reload-function

Sub ReloadStuff
// Check for valid content
 if ActiveDocument.Variables("ENDTIME").GetContent.String<>"-" then
 // This reload hides the splash dialog as well
 ActiveDocument.ReloadEx 2,1
 end if
End Sub

Close and select it in the dialog

And thats about it.

The same code can be applied on a Button-macro or whatever.

[#5]

Thanks Ramses,

This will help. What about is this is a statics report. Like in first go i will load all the data from my table abd then i run the query to fetch the data from the loded table. In this case will that be required to reload data or do we have any other method to do that. What basicallt i wanted to do is , i will create a report that can be veiw by some other end user , so they might dont have the database to which i am connecting . In this case they will be having the statics report . How should i proceed in this case.

I think i am making some sense here. Please let me know if u want any clarification.

Thanks for help

[#6]

I honestly don´t know, hopefully one of the other forum members can answer this?