SSRS - SUM IF

Had nothing but problems today with Microsoft Business Intelligence Development Studio, so after spending nearly 7 working hours on the problem I thought I'd post the solution to save someone else the hassle!

Problem

When using groupings in a table, an expression consisting of a SUM and an immediate if (IIF) can be used to sum/count/aggregate values where another value in the dataset meets a certain condition.  In my example below where the academic year is = "2010/2011" I want to include the grade value (a numeric). 

=SUM(IIF(fields!Academic_year.value = "2010/2011",Fields!Grade.value,0))

However despite the report working in in BIDS, after deploying the report and viewing it on the SSRS server I get #error on some/all of the groups.

Solution

For some reason the SSRS server when rendering the report is struggling to interpret the grade field as a numeric and also the 0.  The use of the VAL conversion function removes this problem.

=SUM(VAL(IIF(fields!Academic_year.value = "2010/2011",Fields!Grade.value,0))) 

 

Organisations and Operations Management - STEEP and the three enviroments

An organisation will encounter three environments that will effect the business and possibly be a catalyst for change within the organisation;-

·         Internal Environment

This is within the organisation and includes staff, resources and faculties.  These can be controlled by the organisation.

·         Near Environment

This is the environment outside of the organisation, such as customers suppliers and compeitiors, these can be influenced and coerced.

·         Far Exterior Environment

This is the environment identified below (STEEP), and cannot be controlled or influenced by an organisation.

STEEP is used to demonstrate how different pressures on an organisation drive the need for change, the drivers make up the acronym STEEP and are thought of as the fingers pushing into a balloon, in that too much pressure and the balloon (organisation) will burst (fall apart).  The factors in a STEEP analysis are regarded as being in the Far External Environment and as such cannot be controlled or influence, only reacted to.

It comes back to the question “why change” and as shown below; because outside factors keep changing.

Social

The “want it yesterday” attitude more prevalent these days is different to the days of mail order companies in the 80’s that aimed for 28 days for delivery, next day delivery is now considered normal.  As such organisations have had to dramatically change their logistics side of their business to provide such a service.

Technology

Internet and electronic transactions are common place these days, for example an organisation specialising in the sale of car insurance, would be failing if it didn’t take advantage of the online insurance quotes.  Another example would be a bank that didn’t provide online/telephone banking services, failure to keep up-to-date with technological developments, whilst their competitors take full advantage of them, will quickly result in the failure of an organisation.

Economic

A very important pressure on an organisation is the economy, changes in exchange rates can be disastrous.  For example a supplier only accepts payment in their native currency (i.e. Euro) and there is an decrease in the value of the pound could result in a large difference in what the organisation requires to pay.

Environmental

Another changing area has been the movement by companies towards so called “green credentials”, fifteen years ago an organisation would not be broadcasting their CO2 emissions and sustainability as much as organisations do now.  The environmental credentials of an organisation could add extra value to an organisation from the perspective of a customer.

Political

There are political factors that can put pressure on an organisation, this could include changes to laws, which could have a severe impact on what can/who it can be sold to.  A strong example would be the changes to the smoking in enclosed places law which was passed in April 2007, which many believe has contributed to a decline in the number of public houses.

Organisations and Operations Management - Resistances to change Kotter and Schlesinger

A six pronged approach to overcoming resistance to change, the six methods can be used on their own or as a group of strategies to implementing change in an organization.  Introduced by John Kotter and Schlesinger

1.    Education and Communication - Where there is a lack of information or inaccurate information and analysis. One of the best ways to overcome resistance to change is to educate people about the change effort beforehand. Up-front communication and education helps employees see the logic in the change effort. this reduces unfounded and incorrect rumors concerning the effects of change in the organization.

2.    Participation and Involvement - Where the initiators do not have all the information they need to design the change and where others have considerable power to resist. When employees are involved in the change effort they are more likely to buy into change rather than resist it. This approach is likely to lower resistance and those who merely acquiesce to change.

3.    Facilitation and Support - Where people are resisting change due to adjustment problems. Managers can head-off potential resistance by being supportive of employees during difficult times. Managerial support helps employees deal with fear and anxiety during a transition period. The basis of resistance to change is likely to be the perception that there some form of detrimental effect occasioned by the change in the organization. This approach is concerned with provision of special training, counseling, time off work.

4.    Negotiation and Agreement - Where someone or some group may lose out in a change and where that individual or group has considerable power to resist. Managers can combat resistance by offering incentives to employees not to resist change. This can be done by allowing change resistors to veto elements of change that are threatening, or change resistors can be offered incentives to leave the company through early buyouts or retirements in order to avoid having to experience the change effort. This approach will be appropriate where those resisting change are in a position of power. 

5.    Manipulation and Co-option - Where other tactics will not work or are too expensive. Kotter and Schlesinger suggest that an effective manipulation technique is to co-opt with resisters. Co-option involves the patronizing gesture in bringing a person into a change management planning group for the sake of appearances rather than their substantive contribution. This often involves selecting leaders of the resisters to participate in the change effort. These leaders can be given a symbolic role in decision making without threatening the change effort. Still, if these leaders feel they are being tricked they are likely to push resistance even further than if they were never included in the change effort leadership. 

6.    Explicit and Implicit Coercion - Where speed is essential and to be used only as last resort. Managers can explicitly or implicitly force employees into accepting change by making clear that resisting to change can lead to losing jobs, firing, transferring or not promoting employees.

Organisations and Operations Management - Glossary of important terms

All of these are useful terms to the subject of OMO:-

Closing the Loop –  The process of changing the variables in the conversion process (i.e. manufacture) to increase the output/yield.  This is frequently followed with measuring the hard and soft objectives to identify progress.

[Closing the loop refers to not just collecting data, but using it to inform decision-making] [ We can collect all kinds of data but using it to change the process can be the most challenging part of the process.]

http://authenticassessment.wordpress.com/2008/05/07/course-embedded-assessment-part-two-closing-the-loop/

Yield – Is typically used in a manufacturing context to identify the success to failure rate, so an 80% yield in a ceramics factory indicates that a failure of two out of ten ceramics was recorded.  Close the loop can be applied to increase the yield without increasing actual production (by using better kilns/manufacturing processes).

Value – Value is the increase of function over cost, i.e an additional two airbags but costing the factory £30 extra per car, there is increase to cost but at the same time an increase in function, in turn giving extra value to the product (providing the cost to function ratio is not too high).

 

Silent Majority – In a distribution curve, the indifferent large number of persons that generally should be targeted and listened to (despite showing no interest).

Hard Objectives – Those objectives that are easier to identify and put a figure to, and namely measure performance against.  I.e. attainment in an education setting is clearly a hard or tangible objective.

Soft Objectives – These are objectives that are difficult to put a physical value or measure, although these are often important than the easier to measure hard objectives, and it is generally regarded that the good management and meeting of soft objectives result in the achievement of hard objectives.

Horizontal Ambition – Aspiring to do a job very well (i.e. police officer, teacher etc)

Vertical Ambition – Seeking promotion 

Ripple Effect – The knock on effect an action has on an organisation

Contingency – It depends on or is contingent

Defer – Delay until later

Role – Position (i.e. checkout operator)

Productivity = Efficient / (Input/Output)

TQM – Total quality management

Quality – Fitness for purpose (not necessarily unnecessary extras or bells ‘n’ whistles)

Jargon – Limit boundary creating jargon that non-technical SME users cannot understand, if necessary explain fully.

Organisations and Operations Management - Total Quality Management

TQM implies that all parts of an organisation should be looked at for quality; all staff and every operation/system to get things right.  TQM never stops and to introduce requires a full commitment, to avoid the bell effect (which is typically regarded as a management fad for TQM).

A “bell” curve showing a short increase in quality, then a return to the norm, an example of TQM being treated as a fad - Image courtes of www.economicshelp.org/

TQM suggest building quality in and not inspecting it out (sometimes referred to as Z D – Zero Defect), and looks at non-conformance i.e. why a product doesn’t meet the standard.  In the case of a pottery factory with 10% failure rate because of over loading of the kiln, the TQM approach would be to load the kiln appropriately to avoid the wastage of materials and the need to inspect the failure.  This would result in a higher yield, so less wastage in terms of materials and lower customer dissatisfaction from receiving faulty goods.  Although there are inherent costs in the process such as staff training/education, the costs for implementation of TQM and education of suppliers. 

• Remember that culture effects behaviour
• Behaviour affects quality, obviously more so in the service industry.
• Quality ultimately effects business success, for example Sony has had huge business success with its televisions and hifi systems despite the relatively high price, mainly down to the quality of their products.

Management Accountancy : Investment Appraisal

 Investing in a project is judged financially using “investment appraisal” techniques, as there is little point in investing in a project for financial if the saving/return will not cover the initial investment.

Methods

•          Payback Period (PP) & Discounted Payback period 
•          Accounting Rate of return (ARR)
•          Net Present Value (NPV)
•          Internal Rate of Return (IRR)

Payback Period (PP)

The PP is in simplicity terms the length of time that it will take to recoup the initial investment.  Discounted payback method simply makes use of a discount table (see NPV).

Accounting Rate of Return (ARR)

ARR is the average capital employed expressed as a percentage of the profit that it will turn over the life of the investment.

ARR =	Profit	ARR (10%) =	£10,000 (Profit)
	Average Investment *		£1,000 (Average investment)

* Average Investment  =	Cost of investment + Disposal Value
	Length of investment

Net Present Value (NPV)

NPV looks at the cost/returns of an investment and considers the timing of cash flows, i.e. would the capital spent on a five year project return the same amount as if the capitol was attracting interest in the bank.

NPV makes use of a “discount table” to discount future cash flows, as cash (in the form of savings) received in 5 years time is worth less than (i.e. it would discount more a cash flow in year 5 than year 2).

Years	3.00%	3.50%	4.00%	4.50%
1	£0.97	£0.97	£0.96	£0.96
2	£0.94	£0.93	£0.92	£0.92
3	£0.92	£0.90	£0.89	£0.88
4	£0.89	£0.87	£0.85	£0.84
5	£0.86	£0.84	£0.82	£0.80

Example of a NPV discount table

Internal Rate of Return (IRR)

IRR is the discount rate that when applied to the cash flows of a project cause it to have a zero NPV. 

Carry out NPV against a project and do so with two discount rates (i.e. once at 10% and one at 15%), using the values and percentage discounts carryout the following formula.

IRR =	L% +	L(#)	* (H(%) – L(%))
		( L(#) + H(#)

L% = Lowest discount percentage

L# = Lowest discounted value

H% = Highest discount percentage

H# = Highest discount value

IRR is used to evaluate at which level of discount a project would break even and at which level.