Default RS Report parameters to predefined dates
-
What is the easiest way to default a Reporting Services variable (presented on the form for the User to enter) to a predertermined value.
Specifically, I have an RS Report which prompts the User for 2 Date's: "Begin Date" and "End Date". I'd like to default the form values w/ 1st day of this year and today's date (respectively).
In the Visual Studio Reporting Services (for BI projects / SQL Server 2000) where do I plug in the underlying SQL used to default these two dates?
BT -
We use a 'Time' table for all dates in our DW, which makes for writing easy queries to solve this type of question. Assuming you have one, just add two datasets to the report, one that selects the Min(<some_date_key>
from <time_table> where <year_col> = Year(GETDATE()) , this will give you the first day of the year (note you can get the same result using striaght SQL but your warehouse really should ahve a time table). The other query will just be select <some_date_key> where <date_key> = CAST(CONVERT(CHAR(8), GETDATE(), 112) AS INT) , note, this assumes your time table has an integer key for dates, e.g. 20051130 is 2005-11-30 or in US format, 11/30/2005.To do what you require in a 'quick and dirty' example, just use two datasets, where one is:
For todays date --> SELECT CAST(CONVERT(CHAR(8), GETDATE(), 112) AS INT) which as mentioned will give you an integer like 20051130
For first day of year --> SELECT YEAR(GETDATE()) * 10000 + 101 , which will give you an integer representign 1st jan (20050101).
Obviously you'll have to adjust these if your time periods are different (ie accounting ones), but again, this si why it's advantageous to have a Time table.
Steve.
-
Steve - thanks for the reply. I actually have the SQL to render the DATEs (see samples below) What I need to know is how to render these in the actual Reporting Services Report fields as a default presentation. To date, I've created 2 datasets (1 for Begin Month and 1 for End Month) -- In Reporting Services - under LAYOUT - I see the Report Parameters section and my 2 parms - question is - how do I link each dataset to the individual Parms being presented.
--FIRST DAY of CURRENT MONTH
select DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)
--FIRST Day of the Year
select DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)
--FIRST Day of the Quarter
select DATEADD(qq, DATEDIFF(qq,0,getdate()), 0)
--LAST Day of Prior Month
select dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate() ), 0))
--LAST Day of Prior Year
select dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate() ), 0))
--LAST Day of Current Month
select dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate() )+1, 0))
--LAST Day of Current Year
select dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate() )+1, 0))
--FIRST Monday of the Month
select DATEADD(wk, DATEDIFF(wk,0,dateadd(dd,6-datepart(day,getdate()),getdate())), 0)
BT -
Ok, gotcha, sorry for the mis-direction.
When in design, single left click the rop left hand corner of the report designer, this: i) selects the report as a whole and ii) puts a little balck square in that top left corner. Right cick that square, select Report parameters from the context menu. In the report parms dialog, you'll see: i) on the left hand side, the existing parameters, select one from the list; ii) on the right hand side you'll see the properties for hte parm you selected from the list on the left. Included in these properties is a section for the default, set it to be from a dataset, select the dataset, select the field that represents the value (ie what gets passe to other datasets/queries) and the display name (ie what the user sees).
I think you can do this from the properties pane also (wher you saw your parameters collection, by clicking the ellipses where the report parameters collection is named in the report properties (but i never do it that way, so can't guarantee that this works
).Cheers,
Steve.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply