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!
  • Students Click Here

*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.

Students Click Here

Update columns automatically in reports

Update columns automatically in reports

Update columns automatically in reports

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 @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/'+
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))
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
).value('.', 'NVARCHAR(MAX)')

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))
SET @finish_date=DATEADD(day,-1,convert(datetime,''01/01/''+STR(YEAR(@this_month)+1)+ '' 23:59:00'',101))

SELECT DISTINCT h10_chp_code,
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
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! Already a Member? Login

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