In Cr 2008 I have main report (just container for sub reports) and two sub reports that are based on same command.
I am passing Run_Date parameter from main report to those two sub reports.
In each sub I created Run_Date parameter same name as in main report.
Report run but no showing any records empty.
I assume that problem is with my date filed in command:
Part of SQL command script:
Declare @Run_date Date
Declare @Edge_date Date
Set @Run_date ={?Run_Date}
Set @Edge_Date = DATEADD(YEAR, -2, DATEADD(DAY, DATEDIFF(DAY,0, @Run_Date),0))
-- Range 1:
-- Range1 is range from Run_Date parameter back to previous YTD beging January 01. To Current Parameter full month date e.g. Sep.26.2018
-- Range will be from 2017-01-01 to 2018-09-31.
--Range 2:
-- Range 2 is range from laset YTD relating to Current Run_Date Parameter full month e.g. if Run_Date parameter is Sep.26.2018
-- Range2 will be from 2017-01-01 to 2017-09-31.
Declare @Start_date_Range1 Date
Set @Start_date_Range1 = DATEADD(YEAR, DATEDIFF(YEAR,0,@Run_date)-1, 0) -- beginning of previous year
Declare @End_date_Range1 Date
Set @End_date_Range1 = DateAdd(mm, DateDiff(mm, 0, @Run_date) +1, 0) -- End of Run_Date month this year
Declare @Start_date_Range2 Date
Set @Start_date_Range2 = DATEADD(YEAR, DATEDIFF(YEAR,0,@Run_date)-1, 0) -- beginning of previous year
Declare @End_date_Range2 Date
Set @End_date_Range2 = DATEADD(YEAR, -1, DATEADD(DAY, DATEDIFF(DAY,0,@Run_date),0)) -- End of Run_Date month a year ago
In my Selct statement I have
.......
Where convert(datetime,convert(varchar(10),i.[InvoiceDate],120)) < @Edge_Date
in SQL Server database [InvoiceDate] field is (int , not null) type and in this format 20180223
20170904
20171228
..........
I assume that this conversion is not proper one.
Our community of experts have been thoroughly vetted for their expertise and industry experience.
The Most Valuable Expert award recognizes technology experts who passionately share their knowledge with the community, demonstrate the core values of this platform, and go the extra mile in all aspects of their contributions. This award is based off of nominations by EE users and experts. Multiple MVEs may be awarded each year.
This award recognizes a member of Experts Exchange who has made outstanding contributions to the community within their first year as an expert. The Rookie of the Year is awarded to a new expert who has the highest number of quality contributions.