Database

14-Nov-08 22:03

Creating a role in a database helps you to restrict access for objects in database. If you don't want that a user has access to a database at all, you'll manage database users.

First create a login and then add that login as a user to those databases you want. See:
- sp_addlogin[^]
- sp_adduser[^]

The need to optimize rises from a bad design.

My articles[^]

Rolling Sums

StevenWalsh14-Nov-08 9:46

StevenWalsh

14-Nov-08 9:46

I have a table that looks like this:

11
28
18
.
.
.

i'm trying to roll up the values so it would take the value and add it to the previous sum of all the rows above it

11
39
57
.
.
.

I am having the hardest time, any ideas?

Einstein argued that there must be simplified explanations of nature, because God is not capricious or arbitrary. No such faith comforts the software engineer.
-Fred Brooks

Re: Rolling Sums

StevenWalsh14-Nov-08 10:19

StevenWalsh

14-Nov-08 10:19

ahhh stupid me, forgot about fetch

DECLARE SalesYearCursor CURSOR FOR
SELECT * FROM #tmpSalesYear

	OPEN SalesYearCursor

	FETCH NEXT FROM SalesYearCursor
	INTO @Percent,@xxxxx,@xxxxx,@xxx

	WHILE @@FETCH_STATUS = 0
	BEGIN
		SET @Current = @percent + @previous
		SET @previous = @Current

		UPDATE #tmpSales SET Roll = @Current
		WHERE Percentage = @percent
		AND SalesYear = @year
		
		FETCH NEXT FROM SalesYearCursor
		INTO @Percent,@xxxxx,@xxxxxx,@xxxxxx
	END
	
	CLOSE SalesYearCursor

DEALLOCATE SalesYearCursor

EDIT: removed names that identify business Smile | :)

Einstein argued that there must be simplified explanations of nature, because God is not capricious or arbitrary. No such faith comforts the software engineer.
-Fred Brooks

Re: Rolling Sums

Wendelius14-Nov-08 10:19

Creating a dynamic date parameter in a SQL Reporting Services subscription

14-Nov-08 10:19

Not sure, but sounds like ROLLUP / GROUPING operations. Have a look at this, it might help you: GROUPING (Transact-SQL)[^]

The need to optimize rises from a bad design.

My articles[^]

Mike Devenney14-Nov-08 8:46

Re: Creating a dynamic date parameter in a SQL Reporting Services subscription

14-Nov-08 8:46

What I need to do seems really easy, but I don't know how to do it and haven't been able to find the answer. Mad | :mad:

I have a SQL reporting services report that takes a date parameter. I want to create a subscription to the report that will run early each morning and show the data for the day prior. Nothing fancy, just a Now - 1. Only Now - 1 doesn't work. Does anyone know if/how this can be done?

Mike Devenney

Wendelius14-Nov-08 8:50

Re: Creating a dynamic date parameter in a SQL Reporting Services subscription

14-Nov-08 8:50

Not sure about SSRS, but in SQL you would say GETDATE()-1. If both of those don't work, what's the error message (or any other problem description)?

The need to optimize rises from a bad design.

My articles[^]

Mike Devenney14-Nov-08 9:23

Re: Creating a dynamic date parameter in a SQL Reporting Services subscription

14-Nov-08 9:23

I'm familiar with the SQL command to do this, I wanted to know if there is a way to specify a dynamic parameter in an SSRS report. GETDATE()-1 would work perfectly, but the question is how to set that as the parameter?

Mike Devenney

Wendelius14-Nov-08 9:36

Re: Creating a dynamic date parameter in a SQL Reporting Services subscription

14-Nov-08 9:36

Where are you launching the report from? C#? Could you post the code that's causing the problem.

I must admit that I haven't used SSRS very much, only client side invocation and from IIS so this may be beyond my knowledge...

The need to optimize rises from a bad design.

My articles[^]

Mike Devenney14-Nov-08 9:38

Re: Creating a dynamic date parameter in a SQL Reporting Services subscription

14-Nov-08 9:38

SSRS reports can be scheduled for delivery (called a subscription) through the web interface. When setting up a subscription you can specify the value you want passed to the report when it runs. I want to pass GETDATE()-1 but don't know how.

Mike Devenney

Wendelius14-Nov-08 9:55

Re: Creating a dynamic date parameter in a SQL Reporting Services subscription

14-Nov-08 9:55

Okay, as I imagined, waaaayyyy beyond my knowledge, sorry!

The only things that I found were these:
- http://wills-blog.com/?p=111[^]
- http://www.developer.com/db/article.php/3513996[^]
- http://www.eggheadcafe.com/community/aspnet/13/10002869/ssrs-2005--errors-using.aspx[^]
- http://blogs.msdn.com/eugenebykov/archive/2007/07/10/report-parameter-controls.aspx[^]

I hope those will help you forward,

Mika

The need to optimize rises from a bad design.

My articles[^]

Mike Devenney14-Nov-08 10:01

Re: Creating a dynamic date parameter in a SQL Reporting Services subscription

14-Nov-08 10:01

Please don't apologize! I've been banging my head against the wall trying to find a way to do this that didn't involved modifying the underlying stored procedure, which I now think I'll end up doing. I've asked DBA's, developers, project managers and anyone else who would continue to listen to the question past the words SQL Reporting Services but no one seems to know. Well, to be more accurate most of them knew, but they didn't have the answer I was looking for. Wink | ;)

Thanks for your time, I'll hit the links you posted and see if someone out there is going to save me from some relatively minor stored proc updates. Now that I think about it I've expended more effort trying to find the easy answer than I would have if I'd just made the update and pushed it to production. *sigh* I guess the adventure is the journey, right?

Mike Devenney

Wendelius14-Nov-08 10:12

Re: Creating a dynamic date parameter in a SQL Reporting Services subscription

14-Nov-08 10:12

Mike Devenney wrote:
I guess the adventure is the journey, right?

You're absolutely right about that! But then again, don't we all know the answer already (check the link if not familiar): 42[^]. However, the ultimate question seems kind of... forgotten Smile | :)

Anyhow, I really hope you get your original question solved!

Mika

The need to optimize rises from a bad design.

My articles[^]

Alexandru Serban30-Dec-09 9:08

Alexandru Serban

30-Dec-09 9:08

You can use the following expression: =DateAdd("d",-1,Today())

Re: Creating a dynamic date parameter in a SQL Reporting Services subscription

RichardSouthworth14-Jan-10 2:02

RichardSouthworth

14-Jan-10 2:02

For parameters StartDate and EndDate build two new parameters StartDateDefault and EndDateDefault. Move these two 'Default' parameters up the parameter list so they are calculated before StartDate and EndDate.

Point StartDate and EndDate to StartDateDefault and EndDateDefault respectively so that default values are picked up eg. by StartDate from StartDateDefault.

For the StartDateDefault and EndDateDefault, make the 'available values' come from a data set using Sql such as...
select dateadd(day, -1, datediff(d,0,getdate())) as Code, 'Yesterday' as Description
union
select dateadd(day, -0, datediff(d,0,getdate())) as Code, 'Today' as Description

Set StartDateDefault and EndDateDefault to be hidden. Also give these two parameters default values themselves if required, which will be dates matching those in the Sql query data set, use non-queried default values to provide a date in the same format as the Sql query eg. =DateAdd("d",-1,today())

Now, when the report initially runs, StartDate gets its value from StartDateDefault, which has a default start date of =DateAdd("d",-1,today()) and when you go to create a subscription, the DefaultStartDate parameter is available to change, and the values you can select come from the Sql query/data set created earlier.

Re: Creating a dynamic date parameter in a SQL Reporting Services subscription

Alexandru Serban14-Jan-10 5:03

Alexandru Serban

14-Jan-10 5:03

A simpler solution is to add a hidden boolean parameter to the report, called for example 'IsSubscription' with a default value of false and add the folowing expression to the StartDate and EndDate default values:

=IIf(Parameters!IsSubscription.Value, DateAdd("d",-1,Today()), Today())

When running the report normally, the IsSubscription parameter is defaulted to false and the default date is today.
When creating the subscription, the IsSubscription parameter must is set to true, thus setting the default value to today-1 = yesterday.

This solution can be aplied to do all kinds of changes based on the IsSubscription parameter value and expresions for the rest of the paremeters based on its value.

The IsSubscription parameter must be defined before any other parameter that uses its value (moved up in the parameter list).

Re: Creating a dynamic date parameter in a SQL Reporting Services subscription

XavierSteel1-Feb-10 13:49

XavierSteel

1-Feb-10 13:49

I have used the code snippet above and it works great. However, for my purposes I would like to revert to using a user entered date, if it is not being run by subscription.

Any suggestions?

Re: Creating a dynamic date parameter in a SQL Reporting Services subscription

BStanneveld19-Jul-11 19:24

BStanneveld

19-Jul-11 19:24

Wow, it's been almost 3 years since this thread was started and the answer is still helpfull Big Grin | :-D

Cursor problem [solved/please ignore]

leckey14-Nov-08 6:02

leckey

14-Nov-08 6:02

It's been a few years since I have done cursors and my head is starting to hurt due to changing user requirements. We are getting a file with three fields, SKU, pkg, and owner. I want to look up in the parts master table if there is a record where these three items match.

What I'm really getting confused is what to put in my logic to look at all three fields. First they just wanted to see if the sku existed. Okay, that was easy enough. But since this is really a composite key my brain just can not wrap itself around the solution. There are a few lines still in the code when I was dealing with only the SKU.

DECLARE partCursor CURSOR
FOR
	
	SELECT
		count(sku)
	FROM
		' + @svr + '.' + @dbname + '.dbo.pm_f 
	WHERE 
		sku IN (
				SELECT
					sku, pkg, owner
				FROM
					WMS_InboundOrder_D 
				WHERE 
					source_Id = ''' + @source_id + '''
				AND	application = ''' + @application + '''
				AND trans_type = ''' + @trans_type + '''
				)

OPEN partCursor
DECLARE @sku VARCHAR(20)
DECLARE @pkg VARCHAR(6)
DECLARE @owner VARCHAR(20)
DECLARE @partErrors VARCHAR(1000)
DECLARE @partFlag bit
SET @partErrors = ''''

FETCH NEXT FROM partCursor INTO @sku, @pkg, @owner
WHILE (@@FETCH_STATUS <> -1)
BEGIN
	IF (@@FETCH_STATUS <> -2)
	 WHAT TO PUT HERE????

		BEGIN
			SET @partErrors = @partErrors + '' SKU: '' + @sku + '' SKU does not exist in master part table! | ''
		END
	FETCH NEXT FROM partCursor INTO @sku, @pkg, @owner
END

SELECT CASE WHEN @partFlag = 0 THEN ''FALSE'' ELSE ''TRUE'' END as Result, @partErrors as Errors

CLOSE partCursor
DEALLOCATE partCursor

Blog link to be reinstated at a later date.

modified on Friday, November 14, 2008 12:48 PM

Paddy Boyd14-Nov-08 6:08

Paddy Boyd

14-Nov-08 6:08

Not sure where the @pkg and the @owner come into this for the SQL you have a above, but i don't think that you need a cursor here:

DECLARE @PartErrors VARCHAR(1000) -- Dunno if you want to look out for this overflowing...

SELECT @PartErrors = ''

SELECT @PartErrors = @PartErrors + '' SKU: '' + SKU + '' SKU does not exist in master part table! | ''
FROM  -- Your sql to find the missing parts goes here --

Also think you might want to change your SQL for find this from an 'IN' to an 'EXISTS' e.g.

SELECT PARTS.A, PARTS.B
FROM PARTS 
WHERE NOT EXISTS (SELECT 1 FROM OTHERTABLE AS Check WHERE Check.A = PARTS.A and Check.B = PARTS.B

leckey14-Nov-08 6:09

leckey

14-Nov-08 6:09

I'm being forced to use a cursor. I tried if exists, but cursors do not seem to like them.

Blog link to be reinstated at a later date.

Paddy Boyd14-Nov-08 6:11

Paddy Boyd

14-Nov-08 6:11

Cursor should work ok with EXISTS. I note that your SQL above is returning less values than you have variables in your cursor loop.

(PS sorry about the multiple edits to the first reply).