BHScripter
Technical User
Hello:
I am creating a report using BIDS in sql server 2005. Within my query is the following case statement:
case req.reqstatus when 0 then 'Assigned'
when 1 then 'In Progress'
when 2 then 'Fulfilled'
when 3 then 'Overdue'
when 4 then 'Expired'
else 'Not Defined' end assignment_status,
Then within the report I sum the instances of each status by an organization group Example of calculated field "PreAssign" which I sum by group:
(=IIF(Fields!assignment_status.Value = "Assigned", 1, 0)
The total is all statusess summed up by organization group Example of total that is summed:
=Fields!PreAssigned.Value + Fields!Expired.Value+Fields!Fulfilled.Value + Fields!InProgress.Value + Fields!Overdue.Value.
When I divide the total of each status by the total of all statuses, Example of percentage calc:
=Round(IIF((Sum(Fields!Total_Assigned.Value))=0,0,((Sum(Fields!PreAssigned.Value))/(Sum(Fields!Total_Assigned.Value)))),4)
and then format the text box (using the format tab of the properties dialogue box) as a percentage with 2 decimal places I can get really close to 100% each time but not always sometimes I get 99.99. When I was formatting with no decimal spaces then I would get 101% instances instead. How can I do this to always get 100%?
Thanks in advance!
I am creating a report using BIDS in sql server 2005. Within my query is the following case statement:
case req.reqstatus when 0 then 'Assigned'
when 1 then 'In Progress'
when 2 then 'Fulfilled'
when 3 then 'Overdue'
when 4 then 'Expired'
else 'Not Defined' end assignment_status,
Then within the report I sum the instances of each status by an organization group Example of calculated field "PreAssign" which I sum by group:
(=IIF(Fields!assignment_status.Value = "Assigned", 1, 0)
The total is all statusess summed up by organization group Example of total that is summed:
=Fields!PreAssigned.Value + Fields!Expired.Value+Fields!Fulfilled.Value + Fields!InProgress.Value + Fields!Overdue.Value.
When I divide the total of each status by the total of all statuses, Example of percentage calc:
=Round(IIF((Sum(Fields!Total_Assigned.Value))=0,0,((Sum(Fields!PreAssigned.Value))/(Sum(Fields!Total_Assigned.Value)))),4)
and then format the text box (using the format tab of the properties dialogue box) as a percentage with 2 decimal places I can get really close to 100% each time but not always sometimes I get 99.99. When I was formatting with no decimal spaces then I would get 101% instances instead. How can I do this to always get 100%?
Thanks in advance!