Issue month to date in SSRS Reporting

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

Reporting Services Development

Issue month to date in SSRS Reporting

Author

Topic

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2011-11-03 : 23:04:12

Hi Guys,

I have an existing report with startdate and enddate parameters.
I would like this date to use the month to date in subscription.
I could not figure out to do this in SSRS. this will generate report based on the parameters. I think i dont have problem in enddate. the startdate will be my problem. Is this possible to setup in date parameter to be use in subscription.? need your input guys.

the scenario is like this.

if month(Parameters!StartDate.Value)=11---november
startdate: 11/1/2011-- the startdate should be like this
if month(Parameters!StartDate.Value)=12---december
startdate: 12/1/2011-- the startdate should be like this
if month(Parameters!StartDate.Value)=01---january
startdate: 01/1/2011-- the startdate should be like this
OR
if month(Parameters!StartDate.Value)=month(Parameters!StartDate.Value)
the days should start always to 1.
the month will depend on the current month.

for the enddate i will use now() or today() function.

Other query.
What is the use of Use Default in Report parameter values.
in case the use default is uncheck or check what will happen.

Regards,

JOV

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-04 : 05:41:05

you can use an expression like

=DateAdd(DateInterval.Month,DateDiff(DateInterval.Month,#01/01/1900#,Parameters!StartDate.Value),#01/01/1900#)
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2011-11-08 : 03:26:50

got an error when i run the script.
I place this codes from the expression of:

Startdate -->Report parameters-->default values-->specify values-->expression.

"An error occurred during local report proccessing. Startdate"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-08 : 03:32:57

oh you're using it in parameters. then use like

=DateAdd(DateInterval.Month,DateDiff(DateInterval.Month,#01/01/1900#,Now()),#01/01/1900#)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2011-11-09 : 01:02:35

Thank you visakh16,

Can you give a little bit explation on how this scripts works on.
btw, what if the changes is every year. or year to date.

01/01/2011--the default should always like this. then next year
the dault value should be 01/01/2012.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-09 : 01:20:23

quote:
Originally posted by Villanuev

Thank you visakh16,

Can you give a little bit explation on how this scripts works on.
btw, what if the changes is every year. or year to date.

01/01/2011--the default should always like this. then next year
the dault value should be 01/01/2012.

ok. heres the explanation

1/1/1900 is base date
what code is doing is finding number of months elapsed since base date. it will then add those many months to basedate which forces it to reach start of current month always(1st day of current month)

for getting year start, use same logic but with years

=DateAdd(DateInterval.Year,DateDiff(DateInterval.Year,#01/01/1900#,Now()),#01/01/1900#)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-09 : 01:22:35

see blog i've written for similar logic in t-sql

http://visakhm.blogspot.com/2010/01/some-quick-tips-for-date-formating.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2011-11-09 : 19:55:58

Thank you very much for explaining and your time. thumbs up guys.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-10 : 00:36:39

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2011-11-17 : 21:42:45

Hi Visakh16,

How to do the date in report parameter for every 1st
day of the month (month to date) of previous month.

Ex.

if month will turn to December the process will start from previous
month and start like Startdate is 11/01/2011 and EndDate is 11/30/2011.

If month will change to january startdate will be 12/01/2011 and enddate is 12/31/2011.

I will set up this for my SSRS report parameters date and
will use for Report subscription.

thanks.

JOV

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2011-11-18 : 01:02:33

I got it. my problem is how to set in SSRS subscription
to run the report every first day of the month.
Ex. 11/01/2011

first day of the previous month
=DateAdd("m", -1, DateSerial(YEAR(Now()), MONTH(Now()), 1))
10/01/2011

Last day of the previous month
=DateAdd(DateInterval.DAY, -1, DateSerial(YEAR(Now()), MONTH(Now()), 1))
10/31/2011

thanks,

JOV

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-18 : 03:54:08

quote:
Originally posted by Villanuev

I got it. my problem is how to set in SSRS subscription
to run the report every first day of the month.

first day of the previous month
=DateAdd("m", -1, DateSerial(YEAR(Now()), MONTH(Now()), 1))

Last day of the previous month
=DateAdd(DateInterval.DAY, -1, DateSerial(YEAR(Now()), MONTH(Now()), 1))

thanks,

JOV

how we implement this logic was by using sql agent job which calls a procedure which checks if its first day of month and then publishes the report from report manager instead of using standard subscription options

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2011-11-20 : 22:21:38

Meaning the subscription cannot do the configuration? i need to use SQL agent?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-20 : 23:39:38

quote:
Originally posted by Villanuev

Meaning the subscription cannot do the configuration? i need to use SQL agent?

Subscription in effect does the same on background (it adds the job and does publishing through it). But UI doesnt have flexibility to set schedule as first day of month so we need to simulate it in sql agent job

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2011-11-21 : 01:00:55

Thanks for your explanation Visakh16.
But I dont have any idea in SQL agent. This is my first time do this SQL agent.
can you give me an idea.

Btw, I dont have the SQL server agent node.
-Management
+Policy Management
SQL server logs
Database mail
Distributed Transaction Coordinator

Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-21 : 03:03:07

quote:
Originally posted by Villanuev

Thanks for your explanation Visakh16.
But I dont have any idea in SQL agent. This is my first time do this SQL agent.
can you give me an idea.

Btw, I dont have the SQL server agent node.
-Management
+Policy Management
SQL server logs
Database mail
Distributed Transaction Coordinator

Thanks.

it should come outside management. check if sql server agent is installed and service is running

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2011-11-21 : 04:45:18

I dont see any sql server agent from object explorer. i assume they could not installed.
anyway, how could i find outside from BIDs if SQL agents is installed?
is there any icon reflect from my task manager(bar)?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-21 : 04:53:15

quote:
Originally posted by Villanuev

I dont see any sql server agent from object explorer. i assume they could not installed.
anyway, how could i find outside from BIDs if SQL agents is installed?
is there any icon reflect from my task manager(bar)?

just type services.msc in run ,click enter and see if you can find SQL Server agent service listed

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2011-11-21 : 19:48:06

Hi Visakh16,

Yes. It was included in the list. Here is the information I got from the list.

Name---------------------------Status--StartType--Log On As
SQL Server Agent(MSSQLSERVER)-- --Manual --NT AUTHORITY\NETWORK SERVICE

What should i do next?
To run this SQL server agent, what is the rights or permission should be the user? Thanks.

Regards,
JOV

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-22 : 00:54:16

quote:
Originally posted by Villanuev

Hi Visakh16,

Yes. It was included in the list. Here is the information I got from the list.

Name---------------------------Status--StartType--Log On As
SQL Server Agent(MSSQLSERVER)-- --Manual --NT AUTHORITY\NETWORK SERVICE

What should i do next?
To run this SQL server agent, what is the rights or permission should be the user? Thanks.

Regards,
JOV

first start the service
then login as any admin account or sa to your sql server and then give your account SQLAgentUserRole for msdb database

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2011-11-22 : 02:25:28

I try to start the service and i got this error.
I think I dont have the permission/rights to run this. what your thoughts?

Could not start the SQL Server Agent (MSSQLSERVER) service on Local Computer.
Error 5. Access Denied

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources