Business Intelligence - SSRS

[Randy Kroeger]

I am a "newbie" on SSRS. Recently, I was given the project of converting rdl files to point to a new database. The rdl's were originally built in VS 2005 and now I am using VS 2008. No biggie. I don't think. Some of the reports, have date parameters where one parameter updates the other parameter, which is a text box. In this case, I have a date description drop down parameter that when changes, is suppose to update a text box parameter with that date. Below are the sql's behind each parameter. The problem is this: when the report loads in preview, the default item in the date description parameter, updates the date text box parameter fine. However, if the user changes the drop down in the date description drop down, the date in the date text box parameter does not change. It looks like the default values update perferctly, but the event that should trigger from an item change on the date description does nothing.

I have been trying to find an answer and cannot. I know there is something I am overlooking as the old reports had worked. Thank you in advance for you help. Also, please note, I have not created anything new. I used the rdl's that are currently working in production.


Date Description parameter dataset:
select * from (
select 'Yesterday' date_range, 0 display_order
union all
select 'Last 7 Days' date_range, 1 display_order
union all
select 'Last Full Week (Sunday-Saturday)' date_range, 2 display_order
union all
select 'Last 14 Days' date_range, 3 display_order
union all
select 'Last 2 Full Weeks' date_range, 4 display_order
union all
select 'Calendar Month (~30 Days)' date_range, 5 display_order
union all
select 'Last Full Month' date_range, 6 display_order
union all
select '2 Calendar Months (~61 Days)' date_range, 7 display_order
union all
select 'Last Two Full Months' date_range, 8 display_order
union all
select 'Calendar Quarter (~91 Days)' date_range, 9 display_order
union all
select 'Calendar Half-Year (~182 Days)' date_range, 10 display_order
union all
select 'Calendar Year (~365 Days)' date_range, 11 display_order
union all
select 'Last Two & Next Two Weeks' date_range, 12 display_order
union all
select 'Next Two Weeks' date_range, 13 display_order
union all
select 'Next Month' date_range, 14 display_order

) a order by display_order



Date text box parameter dataset:

select convert(varchar,case
-- all from yesterday -- default is yesterday
when @range='Last 7 Days' then dateadd(dd,-7,getdate())
when @range='Last 14 Days' then dateadd(dd,-14,getdate())
when @range='Calendar Month (~30 Days)' then dateadd(mm,-1,dateadd(dd,-1,getdate()))
when @range='2 Calendar Months (~61 Days)' then dateadd(mm,-2,dateadd(dd,-1,getdate()))
when @range='Calendar Quarter (~91 Days)' then dateadd(mm,-3,dateadd(dd,-1,getdate()))
when @range='Calendar Half-Year (~182 Days)' then dateadd(mm,-6,dateadd(dd,-1,getdate()))
when @range='Calendar Year (~365 Days)' then dateadd(mm,-12,dateadd(dd,-1,getdate()))
-- weekly or monthly ranges
when @range='Last Full Week (Sunday-Saturday)' then dateadd(dd,-6-datepart(dw,getdate()),getdate())
when @range='Last 2 Full Weeks' or @range='Last Two & Next Two Weeks' then dateadd(dd,-13-datepart(dw,getdate()),getdate())
when @range='Last Full Month' then dateadd(mm,-1,dateadd(dd,-day(getdate())+1,getdate()))
when @range='Last Two Full Months' then dateadd(mm,-2,dateadd(dd,-day(getdate())+1,getdate()))
when @range='Last Three Full Months' then dateadd(mm,-3,dateadd(dd,-day(getdate())+1,getdate()))
else dateadd(dd,-1, getdate()) end,101) start_date,
convert(varchar,case
when @range='Last Week (Sunday-Saturday)' or @range='Last 2 Full Weeks' then dateadd(dd,-datepart(dw,getdate()),getdate())
when @range='Last Full Month' or @range='Last Two Full Months' or @range='Last Three Full Months' then dateadd(dd,-day(getdate()),getdate())
when @range='Next Two Weeks' or @range='Last Two & Next Two Weeks' then dateadd(dd,14,dateadd(dd,-datepart(dw,getdate()),getdate()))
when @range='Next Month' then dateadd(dd,28,dateadd(dd,-datepart(dw,getdate()),getdate()))
else dateadd(dd,-1, getdate()) end,101) end_date

[melina386]

we showed how to incorporate SSRS into a BI model, specifically covering the business model of a software development company and health-care agency deploying a custom BI portal. Understanding how to transform and analyze the data that drives your business will help you make important business decisions. Delivering that data to decision makers is a pivotal link in the BI chain. With SSRS, Microsoft has provided organizations with another tool that can easily tap into and extend the reach of crucial data. From business applications such as Microsoft CRM to custom project management solutions, SSRS easily utilizes many types of data and can deliver it in a variety of formats. Working with other applications and products in the Microsoft BI platform—such as SharePoint Portal Server, Analysis Services, and Office—SSRS will prove to be an invaluable BI tool now and in the future.

Data entry india