Historically I have an Access 2003 Frontend talking to a SQL2000 backend. It is quite a complicated process which I shall attempt to explain here.
I have a report on Access that has a record source of 'spSixMonth' and Input Parameters of
@vch_Site_ref = [Forms]![frmdashboard]![cbxSiteRef], @dt_Src_Date = Format( [Forms]![frmdashboard]![cbxMonth],"yyyy/mm/dd"), @dt_Src_Date_Pre = Format( DateSerial(Year([Forms]![frmdashboard]![cbxMonth]),Month([Forms]![frmdashboard]![cbxMonth])-1, 1),"yyyy/mm/dd"), @dt_Src_Date_Pre2 = Format( DateSerial(Year([Forms]![frmdashboard]![cbxMonth]),Month([Forms]![frmdashboard]![cbxMonth])-2, 1),"yyyy/mm/dd"), @dt_Src_Date_Pre3 = Format( DateSerial(Year([Forms]![frmdashboard]![cbxMonth]),Month([Forms]![frmdashboard]![cbxMonth])-3, 1),"yyyy/mm/dd"), @dt_Src_Date_Pre4 = Format( DateSerial(Year([Forms]![frmdashboard]![cbxMonth]),Month([Forms]![frmdashboard]![cbxMonth])-4, 1),"yyyy/mm/dd"), @dt_Src_Date_Pre5 = Format( DateSerial(Year([Forms]![frmdashboard]![cbxMonth]),Month([Forms]![frmdashboard]![cbxMonth])-5, 1),"yyyy/mm/dd"), @dt_Src_Date_Pre6 = Format( DateSerial(Year([Forms]![frmdashboard]![cbxMonth]),Month([Forms]![frmdashboard]![cbxMonth])-6, 1),"yyyy/mm/dd").
Part of spSixMonth contains a number of references to a function called fnReport.
I won't paste either the SP or FN text in here as I may get in trouble for making the longest post on here ever.
The bottom line of my question is that I wish to eliminate the use of Access totally and recreate this report in Reporting Services for SQL 2005.
I will be totally honest and say that I have not got a clue where to start. I have worked through the tutorials for SQL 2005 RS twice now and am comfortable with what I can do in the tutorial but am having difficulties translating this to my real world.
And pointers or help would be gratefully rec'd. I am willing to paste the code into here if that is required, or failing that I will PM someone if that is preferred or email for that matter.
Kindest Regards
I have a report on Access that has a record source of 'spSixMonth' and Input Parameters of
@vch_Site_ref = [Forms]![frmdashboard]![cbxSiteRef], @dt_Src_Date = Format( [Forms]![frmdashboard]![cbxMonth],"yyyy/mm/dd"), @dt_Src_Date_Pre = Format( DateSerial(Year([Forms]![frmdashboard]![cbxMonth]),Month([Forms]![frmdashboard]![cbxMonth])-1, 1),"yyyy/mm/dd"), @dt_Src_Date_Pre2 = Format( DateSerial(Year([Forms]![frmdashboard]![cbxMonth]),Month([Forms]![frmdashboard]![cbxMonth])-2, 1),"yyyy/mm/dd"), @dt_Src_Date_Pre3 = Format( DateSerial(Year([Forms]![frmdashboard]![cbxMonth]),Month([Forms]![frmdashboard]![cbxMonth])-3, 1),"yyyy/mm/dd"), @dt_Src_Date_Pre4 = Format( DateSerial(Year([Forms]![frmdashboard]![cbxMonth]),Month([Forms]![frmdashboard]![cbxMonth])-4, 1),"yyyy/mm/dd"), @dt_Src_Date_Pre5 = Format( DateSerial(Year([Forms]![frmdashboard]![cbxMonth]),Month([Forms]![frmdashboard]![cbxMonth])-5, 1),"yyyy/mm/dd"), @dt_Src_Date_Pre6 = Format( DateSerial(Year([Forms]![frmdashboard]![cbxMonth]),Month([Forms]![frmdashboard]![cbxMonth])-6, 1),"yyyy/mm/dd").
Part of spSixMonth contains a number of references to a function called fnReport.
I won't paste either the SP or FN text in here as I may get in trouble for making the longest post on here ever.
The bottom line of my question is that I wish to eliminate the use of Access totally and recreate this report in Reporting Services for SQL 2005.
I will be totally honest and say that I have not got a clue where to start. I have worked through the tutorials for SQL 2005 RS twice now and am comfortable with what I can do in the tutorial but am having difficulties translating this to my real world.
And pointers or help would be gratefully rec'd. I am willing to paste the code into here if that is required, or failing that I will PM someone if that is preferred or email for that matter.
Kindest Regards
