April 8, 2015 at 3:36 am
Hi,
I have a requirement to add new calendar columns from Jan to Dec in an existing SSRS report based on certain conditions.
The report uses a simple query at the moment.
SELECT [PROJECT ID]
,[PROJECT]
,[STATUS]
,[ACTUAL START]
,[ESTIMATED END]
FROM Projects
The requirement to add the new calendar columns is based on the following conditions:
1. If the Status value is “2” then for each Month in the current year, shade in green for the particular current month
For example, if for a particular Project for month/year of April 2015
Year/Month(Actual Start date) is < = April 2015
and Year/Month(Estimate End date) > = April 2015, then April should be shaded green.
2. The logic needs to be repeated from each month in the current year.
3. Some Estimate and Actual dates run from previous year, or into next year, so year also needs to be taken into consideration.
Please find the DDL and sample data below.
CREATE TABLE [dbo].[Projects](
[PROJECT ID] [nvarchar](255) NULL,
[PROJECT] [nvarchar](255) NULL,
[STATUS] [nvarchar](255) NULL,
[ACTUAL START] [datetime] NULL,
[ESTIMATED END] [datetime] NULL
) ON [PRIMARY]
insert into Projects
values('1','TFD','2','2015-01-31 00:00:00.000','2015-12-31 00:00:00.000')
insert into Projects
values('2','JUH','1','2015-02-01 00:00:00.000','2015-02-27 00:00:00.000')
insert into Projects
values('3','REFFD','2','2015-01-31 00:00:00.000','2015-11-30 00:00:00.000')
insert into Projects
values('4','DFRT','2','2015-04-01 00:00:00.000','2015-05-31 00:00:00.000')
insert into Projects
values('5','DFDF','2','2015-03-20 00:00:00.000','2015-04-20 00:00:00.000')
insert into Projects
values('6','HTYU','3','2015-01-31 00:00:00.000','2015-03-31 00:00:00.000')
insert into Projects
values('7','WEW','2','2015-01-31 00:00:00.000','2015-12-31 00:00:00.000')
insert into Projects
values('8','FHF','2','2015-01-31 00:00:00.000','2015-12-31 00:00:00.000')
insert into Projects
values('9','ERT','1','2015-02-20 00:00:00.000','2015-05-31 00:00:00.000')
insert into Projects
values('10','FGH','2','2015-01-02 00:00:00.000','2015-01-31 00:00:00.000')
Please find the expected output screenshot attached.
Could somebody please advice how to rewrite the existing query to add new calendar columns in the report and then in the SSRS output shade green based on the above condition. Thanks.
April 8, 2015 at 4:59 am
This sounds more like an Excel question as SQL Server does not color output???
April 8, 2015 at 5:13 am
djj (4/8/2015)
This sounds more like an Excel question as SQL Server does not color output???
I have clearly mentioned it is a SQL server and SSRS related question. Where did Excel come from ?
SSRS has the functionality to use background colours based on conditions.
April 8, 2015 at 5:15 am
pwalter83 (4/8/2015)
djj (4/8/2015)
This sounds more like an Excel question as SQL Server does not color output???I have clearly mentioned it is a SQL server and SSRS related question. Where did Excel come from ?
SSRS has the functionality to use background colours based on conditions.
Actually I do not see SSRS mentioned. Sorry for the misunderstanding.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply