INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Jobs from Indeed

Update columns automatically in reports

Update columns automatically in reports

(OP)
I'm not sure this can be done, but I have a report that in manually updated in Excel for distribution. The client would like to have it automatically sent monthly using SSRS. The reports shows trending of membership from month to month based on data from a table that gets populated with a SQL job monthly. So the first item was to update the count column, which I did with this query embedded in the report.

DECLARE @sql AS NVARCHAR(MAX)
DECLARE @col AS VARCHAR(MAX)

DECLARE @start_date datetime --first day of last month
DECLARE @finish_date datetime --last day of last month
DECLARE @this_month datetime --current day of last month
SET @this_month = DATEADD(mm,-1,GetDate())
SET @start_date = CONVERT(datetime,STR(MONTH(@this_month))+'/01/'+
STR(YEAR(@this_month)),101)
IF MONTH(@this_month)<12
SET @finish_date = DATEADD(day,-1,CONVERT(datetime,STR(MONTH(@this_month)+1,2)
+'/01/'+STR(YEAR(@this_month))+ ' 23:59:00',101))
ELSE
SET @finish_date=DATEADD(day,-1,convert(datetime,'01/01/'+STR(YEAR(@this_month)+1)+ ' 23:59:00',101))

select @col = STUFF((SELECT ',' + QUOTENAME(h10_month)
from client_period_counts
where h10_year = DATEPART(yyyy,@finish_date)
group by h10_month
order by ABS(h10_month) ASC
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')

SET @sql = N'
DECLARE @finish_date datetime --last day of last month
DECLARE @this_month datetime --current day of last month
SET @this_month = DATEADD(mm,-1,GetDate())

IF MONTH(@this_month)<12
SET @finish_date = DATEADD(day,-1,CONVERT(datetime,STR(MONTH(@this_month)+1,2)
+''/01/''+STR(YEAR(@this_month))+ '' 23:59:00'',101))
ELSE
SET @finish_date=DATEADD(day,-1,convert(datetime,''01/01/''+STR(YEAR(@this_month)+1)+ '' 23:59:00'',101))

SELECT *
from(
SELECT DISTINCT h10_chp_code,
h10_chp_name,
h10_calculated_balance,
h10_month
from client_period_counts pc

where h10_year = DATEPART(yyyy,@finish_date)
AND h10_delete_flag = 0
Group by h10_chp_code,h10_chp_name,h10_month,h10_calculated_balance
)SRC
PIVOT
(Sum(h10_calculated_balance)
FOR h10_month in ('+@col+'))AS PVT

order by ABS(PVT.h10_chp_code)'

;

--PRINT @sql;

execute sp_executesql @sql;

This returns this result with the new automatic column: With the number in the column heading being the month, so 1 = January, 2 = Feb and so on.
See Text file attached


Now here is what the client wants:
See Excel file attachment
In other words the membership count in one column and the difference between the previous column, then the difference between the current month column and the YTD which is column 1 or 1/31/2016.
I am attaching a text file to show the query results

I'm not sure if this is even possible to automate, but if anyone has seen something similar, please advise.




Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close