Very new to SSRS so unfamiliar with the syntax - Using Report Builder 2.0.
I've built a very simple table in Report Builder to display the total number of appointment slots made available to patients, and the total number of these slots that have been booked.
I'd like to add an additional column to show the % of booked slots (i.e. Available Slots/Booked Slots *100%):
| Total Slots | Booked Slots | % Booked |
+-------------+--------------+----------+
| 2000 | 1250 | 62.5% |
+-------------+--------------+----------+
Both the 'Total Slots' and 'Booked Slots' values are based on same field ('Booked_Flag') from the dataset. This field carries a 'Y/N' value.
The 'Total Slots' column has the underlying expression: =Count(Fields!Booked_Flag.Value)
The 'Booked Slots' column has the underlying expression: =SUM(IIF(Fields!Booked_Flag.Value="Y",1,0))
Please can anyone describe the most appropriate way to calculate the '% Booked' figure?
I've tried this: =SUM(IIF(Fields!Booked_Flag.Value="Y",1,0)) / SUM(Fields!Booked_Flag.Value) but it throws the following error - which I'm presuming means I can't perform a calculation on top of another calculation:
The expression used for the calculated field '=SUM(IIF(Fields!Booked_Flag.Value="Y",1,0)) / SUM(Fields!Booked_Flag.Value)' includes an aggregate function. Aggregate functions cannot be used in calculated field expressions.
Greatly appreciate any guidance.
You can directly access the content of the report cells and calculate using those. It's simple in your case. Let's a assume in the report design, the cell that contains '2000' is called TextBox1 (check the properties to get the name) and the cell that contains '1250' is called TextBox2 then your % column expression would be something like
=ReportItems!TextBox2.Value / ReportItems!TextBox1.Value
The just set the format of the calculated column to p1 (Percent with 1 decimal place) and your done.