Hi,
I have a report that that needs to be filtered by StartDate, where StartDate is the first day in the week of a specific year. The values will be each week of the year (52 weeks = 52 Start Dates) and the range of years is between 2006 (current year) back to 2003.
My questions is how to generate these values for the StartDate parameter. I don't want to define actual database tables to store these values, as it seems like something I should be able to easily generate via some code.
After trying it out for while and searching the forum, I'm starting to think I will need to statically store these parameters.
Can anyone offer any advice on how to solve this parameter value generation issue
Thanks,
- Jim
Report Parameters creation via Code
Anarchy
Thanks - Alexandre,
Thats pretty much what we ended up doing (a little bit more elaborate in my case beacuse of the the year parameter).
Generating the Start Date parms in SQL isn't bad, but it seems like in this case it would have been a whole lot easy to do in VB.Net.
Anyway - thanks for the response!
- Jim
ejamashu
If you have access to Analysis Services, I would suggest using a dimension to store these values. For example, create a dimension that contains DayOfWeek, WeekInYear, and Year.
Then, link together Year by WeekInYear by DayOfWeek. Now, you can draw the DayOfWeek for any given week for any given year. This will enable you to cycle through each week for each year and obtain the correct DayOfWeek.
Once you get the dimension working, you will be able to draw upon these values in Reporting Services.
Sayure
QWERTY890
Currently there is no way to generate a dataset in Code and pass it into parameters or a data region.
What you can do is to create a sql statement that is server/database independant with all the rows/values you need and use it to provide default values for the parameter.
The sql statement will look like
select date1 as [date]
union
select date2
union
select date3
...
Since you are not selecting any table, your connection string may only specify some sql server like "server=myserver;"
xcvzzzzcv
Thanks,
Unfortunately, I don't have Analysis Services setup (doesn't sound like a bad idea).
I thought I might be able to do add some code like,
Shared Function GetStartDates(year As String) As Object
Dim s(52) As String
' Code to generate the Start Dates based on the Year
'
Report.Parameters.Parm(0).Value = s
Return
End Function
Kestutis
Below is the other way to accomplish the task using RS Xml Data Provider.
It is the powerful combination of the two features:
1) XmlDP allows embedding data into its query. Sort of a self-contained query. Note that connection string for XmlDP in this case is empty (not used).
2) RDL allows Query.CommandText to be an expression. this allows generating XmlDP query with embedded data dynamically. Or even taking data set completely from outside thru a report parameter.
So, my statement about inability to generate datasets in previos comment is not valid :)
< xml version="1.0" encoding="utf-8" >
<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<DataSources>
<DataSource Name="DataSource1">
<ConnectionProperties>
<IntegratedSecurity>true</IntegratedSecurity>
<ConnectString />
<DataProvider>XML</DataProvider>
</ConnectionProperties>
<rd:DataSourceID>9bac4b7a-ee83-4e83-9077-e31f3550b4ab</rd:DataSourceID>
</DataSource>
</DataSources>
<BottomMargin>1in</BottomMargin>
<RightMargin>1in</RightMargin>
<ReportParameters>
<ReportParameter Name="Report_Parameter_0">
<DataType>DateTime</DataType>
<DefaultValue>
<DataSetReference>
<DataSetName>DataSet1</DataSetName>
<ValueField>Date</ValueField>
</DataSetReference>
</DefaultValue>
<AllowBlank>true</AllowBlank>
<Prompt>Report_Parameter_0</Prompt>
<ValidValues>
<DataSetReference>
<DataSetName>DataSet1</DataSetName>
<ValueField>Date</ValueField>
<LabelField>Date</LabelField>
</DataSetReference>
</ValidValues>
</ReportParameter>
</ReportParameters>
<rd:DrawGrid>true</rd:DrawGrid>
<InteractiveWidth>8.5in</InteractiveWidth>
<rd:SnapToGrid>true</rd:SnapToGrid>
<Body>
<ReportItems>
<Textbox Name="textbox1">
<Left>1.75in</Left>
<Top>0.625in</Top>
<rd:DefaultName>textbox1</rd:DefaultName>
<Width>2.5in</Width>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Height>0.25in</Height>
<Value>=Parameters!Report_Parameter_0.Value</Value>
</Textbox>
</ReportItems>
<Height>2in</Height>
</Body>
<rd:ReportID>11538d0e-9e17-4061-be2b-788898657ec3</rd:ReportID>
<LeftMargin>1in</LeftMargin>
<DataSets>
<DataSet Name="DataSet1">
<Query>
<CommandText>=Code.GenerateParameterValuesQuery()</CommandText>
<DataSourceName>DataSource1</DataSourceName>
</Query>
<Fields>
<Field Name="Date">
<rd:TypeName>System.DateTime</rd:TypeName>
<DataField>Date</DataField>
</Field>
</Fields>
</DataSet>
</DataSets>
<Code>
Function GenerateParameterValuesQuery() as string
GenerateParameterValuesQuery = _
"<Query> " + _
" <XmlData> " + _
" <Dates> " + _
" <Date>2001-01-15</Date> " + _
" <Date>2001-01-16</Date> " + _
" <Date>2001-01-17</Date> " + _
" <Date>2001-01-18</Date> " + _
" <Date>2001-01-19</Date> " + _
" <Date>2001-01-20</Date> " + _
" <Date>2001-01-21</Date> " + _
" <Date>2001-01-22</Date> " + _
" <Date>2001-01-23</Date> " + _
" </Dates> " + _
" </XmlData> " + _
" <ElementPath>Dates/Date{@(date)}</ElementPath> " + _
"</Query>"
End Function
</Code>
<Width>6.5in</Width>
<InteractiveHeight>11in</InteractiveHeight>
<Language>en-US</Language>
<TopMargin>1in</TopMargin>
</Report>
Joel Martinez
Sorry ... no, I used sql to generate the parameter values. Like I said, we didn't want to create tables with static data, so when we coudn't use VB.Net to generate the parameters, we came up with this SQL code in the dataset. It can probably be tuned up a bit, but it works for me ;>
Thanks for your feedback!
DECLARE @StopYear INT
DECLARE @StartYear INT
DECLARE @CalDt SMALLDATETIME
SET @StartYear = @Year
SET @StopYear = @Year + 1
SET @CalDt = convert(smalldatetime,convert(varchar,@Year) + '-01-01')
DECLARE @Cal TABLE (cal_dt SMALLDATETIME)
WHILE @StartYear < @StopYear
BEGIN
INSERT INTO @Cal (cal_dt)
VALUES (@CalDt)
SET @CalDt = DATEADD(dd,1,@CalDt)
SET @StartYear = year(@CalDt)
END
SELECT convert(varchar(12),cal_dt,101) cal_dt_text, cal_dt from @Cal
WHERE cal_dt >= convert(varchar,@Year) + '-01-01' and cal_dt < convert(varchar,@Year + 1) + '-01-01' and datepart(dw,cal_dt) = 1 and cal_dt <= getdate() ORDER BY cal_dt DESC