Default parameter values in RS 2005

Question

Default parameter values in RS 2005

Bill-290450

SSC Veteran

Points: 246
More actions
May 13, 2008 at 10:24 am

#185408

I have written several reports that report off of an AS cube. The time dimension that the cube is using is:
Fiscal Year
Fiscal Quarter
Fiscal Period
Fiscal Week
Date
I have been able to figure out how the make the daily reports default to yesterdays date. I am having problems figuring out how to get the weekly reports or higher default to the correct period. For example, I would like for the weekly reports to default to the week that yesterday's date is in.
Any suggestions?
Thanks.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Carolyn Richardson SSCrazy Eights Points: 8357 More actions · Answer 1

Calculate the date in a stored procedure or view, load that procedure/view as a dataset and call that in a parameter in reporting services.

Facts are stubborn things, but statistics are more pliable - Mark Twain
Carolyn
SQLServerSpecialists[/url]

Ben Sullins-437405 SSCertifiable Points: 5397 More actions · Answer 2

I would change the parameter for the Weekly/Monthly reports to only show those appropriate values rather than letting them choose a specific date and deriving the week/month down stream...

Cheers,

Ben Sullins
bensullins.com
Beer is my primary key...

Bill-290450 SSC Veteran Points: 246 More actions · Answer 3

The report that I created only includes the year and week. What I am wanting to do is to default that week parameter to the correct week for yesterday's date. So, I have created a stored procedure that returns the correct string ([DimDate].[Week Num].&[2008]&[20]) that will go into the default value box. I have had the stored procedure where it returns all of the dates with the correct string and where it only returns the correct week string for yesterday's date. I can run the stored procedure in the report but I am now having trouble figuring out how to populate the default value from the query in the stored procedure.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 4

Bill Whisnant (5/14/2008)
The report that I created only includes the year and week. What I am wanting to do is to default that week parameter to the correct week for yesterday's date. So, I have created a stored procedure that returns the correct string ([DimDate].[Week Num].&[2008]&[20]) that will go into the default value box. I have had the stored procedure where it returns all of the dates with the correct string and where it only returns the correct week string for yesterday's date. I can run the stored procedure in the report but I am now having trouble figuring out how to populate the default value from the query in the stored procedure.

Why not just let the parameter default to null or empty string? if so, then have your stored proc insert the appropriate week for you?

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Bill-290450 SSC Veteran Points: 246 More actions · Answer 5

The users would like the report to default to the correct week but then they still need to be able to choose other weeks.

When I create the dataset, should there be fields associated with the stored procedure? When I tried to use the dataset in a query in a parameter, there aren't any fields.

Thanks for the responses.

Bill-290450 SSC Veteran Points: 246 More actions · Answer 6

I got it working. I guess I had messed with the dataset and the stored procedure too much. I deleted the dataset and started over again and got it to work. Thanks for the help.