Date Range

[Former Member]

Hi,

I have a Main report which has all its data coming from the subreport(detail section of Main report is suppressed) placed in the report footer section. I have 3 such subreports and all teh data is coming from single table. I have written an SQL command for all 3 subreports to extract data.

Now I want to provide an option for user to select the start and end date. So this will be done using prompts . The table has only one single date field (START_DATE) . So basically both start date and end date would come from a single column.

How do I display the date (both start and end) in the main report so that we getto know the period for which the report is run??

Also since all the data is coming from teh subreports(SQL) , I presume i have to pass this date selected by user , from main report to all the subreports inorder to select only the data corresponding to that date.

Please let me know the steps and the formulas on how to do this.

Thanks in Anticipation.

[Former Member · Accepted solution]

Hi

Create StartDate and EndDate parameters in your main report and drag and drop on your man report page header or report header to display the start date and end date ( report period ).

Now go in each sub report and click on Change sub report links and select StartDate and EndDate from main report parameter , once you select this you will see a parameter field under sub report parameter fields with ?pmStartDate and ?pmEndDate link them.

Now go in your sub report record selection and compare sub report parameter (?pmStartdate) with your database field

Ex : >= {?pmStartdate} and <= {?pmEndDate}

This will filter the data based on user selection in all your sub reports.

Thanks,

Sastry

[Former Member · Accepted solution]

Thanks a lot for the detailed steps.

Also I have one more report where they want to run it automatically from 1st till the last day of every month. No prompts for user.

This report also has no data in details , it just has subreports. So what formula or logic do i implement here in teh main and subreport??The subreport data is extracted using SQL , so is there any chnage I should do to the sql to have the last day and start day hardcoded (generalised format)??

Please let me know the steps as I'm new to this implementation.

Thanks.

[Former Member · Accepted solution]

Hi,

Since you do not have any data in your main report, you can very well addthe following in sub report record selection :

>= dateadd('d',-day(currentdate),currentdate)+1 and <= dateadd('m',+1,currentdate)- day(currentdate)

This formula will consider the current month biging and ending and filter the data .

Thanks,

Sastry

[Former Member · Accepted solution]

Hi ,

I was trying the same logic you mentioned for Date Range in one of my reports (which also has no data in main report and has 3 subreports , in which we write an SQL to retrieve data from diferent tables) , but the issue is after I create the parameters in my main report and try to link my first subreport with these 2 param fields , (with all the option at the lower portion of the window unchecked) , these paramters are not appearing in my subreport!!

I'm not sure if I'm missing out on any step. After I try to change the subreport link by selecting n adding those paramters to the "fields to link to" tab of chnage subreport links wndw n clik "ok" the parameters dont appear in subreports and when I go back n edit again to link , those parameters are not added to the "fields to link to" tab. I tried many times but doesnt seem to be working. They get added but dont reflect the next time I check!! Its weird.

Please help. I always get stuck with this date issue at the end of every report I develop!!

Thanks.

[Former Member]

You can also embed Start and end parameters into your Commands using the command edit box.

This will filter data more efficiently that using Subreport linking parameters.

Add Start and end params to main report. Then in SR linking dialog drag main param into window, then in lower left hand corner use drop down and scroll to relevant param in sub report and link.

Ian