September 4, 2012 at 2:19 am
Hello,
i am generating a matrix report using Visual studio 2005. The report as data contains project names on a daily basis assigned to resources. I want to display a background color for each project name. Example Project A will be red, Project B will be blue and so on.
If i do it manually as expression in the data column properties, this will become a very bulky IIF statement and moreover i will have to edit it manually if in case am having a new project tomorrow.
How can i include it directly in my SQL statement ?
SELECT
epmr.ResourceName,
epmp.ProjectName,
epma.AssignmentStartDate,
epma.AssignmentFinishDate,
epmad.TimeByDay,
--added for week display
convert(varchar(10),DATEADD(d,8 - DATEPART(dw, epmad.TimeByDay), epmad.TimeByDay),101) as [Week Ending],
DATENAME(m,epmad.TimeByDay) as [Month]
FROM
MSP_EpmAssignmentByDay_UserView epmad,
MSP_EpmAssignment_UserView epma,
MSP_EpmProject_UserView epmp,
MSP_EpmResource_UserView epmr,
MSP_EpmTask_UserView epmt
WHERE
epma.AssignmentUid = epmad.AssignmentUID
AND
epmp.ProjectUID = epma.ProjectUID
AND
epmr.ResourceUID = epma.ResourceUID
AND
epmt.TaskUID = epma.TaskUID
AND
epmad.TimeByDay BETWEEN (@Start_Date) AND (@End_Date)
AND
epmr.ResourceName IN (@ParaRes)
ORDER BY
epmad.TimeByDay
September 4, 2012 at 3:12 am
avesh_h (9/4/2012)
Hello,i am generating a matrix report using Visual studio 2005. The report as data contains project names on a daily basis assigned to resources. I want to display a background color for each project name. Example Project A will be red, Project B will be blue and so on.
If i do it manually as expression in the data column properties, this will become a very bulky IIF statement and moreover i will have to edit it manually if in case am having a new project tomorrow.
How can i include it directly in my SQL statement ?
SELECT
epmr.ResourceName,
epmp.ProjectName,
epma.AssignmentStartDate,
epma.AssignmentFinishDate,
epmad.TimeByDay,
--added for week display
convert(varchar(10),DATEADD(d,8 - DATEPART(dw, epmad.TimeByDay), epmad.TimeByDay),101) as [Week Ending],
DATENAME(m,epmad.TimeByDay) as [Month]
FROM
MSP_EpmAssignmentByDay_UserView epmad,
MSP_EpmAssignment_UserView epma,
MSP_EpmProject_UserView epmp,
MSP_EpmResource_UserView epmr,
MSP_EpmTask_UserView epmt
WHERE
epma.AssignmentUid = epmad.AssignmentUID
AND
epmp.ProjectUID = epma.ProjectUID
AND
epmr.ResourceUID = epma.ResourceUID
AND
epmt.TaskUID = epma.TaskUID
AND
epmad.TimeByDay BETWEEN (@Start_Date) AND (@End_Date)
AND
epmr.ResourceName IN (@ParaRes)
ORDER BY
epmad.TimeByDay
I don't think it will be a good idea to have it back-end. You should handle it at front end and you need not to change the color manually for every new entry as well.In VS 2005 we have various report tool where you can have this facility.So do a little google or put your question in ASP.NET forums like stakeoverflow etc 🙂
--rhythmk
------------------------------------------------------------------
To post your question use below link
https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂
September 4, 2012 at 6:15 am
Hi rhythmk,
in front i can use the expression statement in properties for the matrix data and include formula like =iif(Fields.ProjectValue = "Project 1","Red",iif(Fields.ProjectValue = "Project 2","Blue",...)
If i get a new project i will have to edit it. I have been through the custom code in report properties but not able to understand that concept.
Am trying ...
Any help will be much appreciated. Thanks
September 4, 2012 at 6:22 am
Use a case statement which brings the colour code of the project back in the result set then use an expression based on the column to colour in the field.
Something like =Fields!ProjectColor.Value
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply