Any solution for What-if Analysis in SSRS reports? Please let me know. Thanks

You can build parameters that list the different amounts or bounds of your what-if scenario for each data point/field in the equation, then set the fields that would be effected by the what-if variables to be mathematically adjusted by the parameter choice in their expressions. So for a simple example let say you wanted to set up what-if on how a discount program may affect revenue and gross margin. You could create a parameter to list all the possible discount amounts, like 1% through 20% in a dropdown, or an input field so the user could put in exactly the percentage they wanted. Then the Net Sales Amount value field would have an expression something like = (Fields!NetSalesAmt.Value - (Fields!NetSalesAmt.Value * Parameter!DiscountPercent.Value)). So anytime the user changes the parameter value all fields that referenced it would change as well as any other fields that used the Net Sales Amount in their expressions. Also the expressions in each field can have complex logic built into them via an IIf function like = IIf(Parameter!Choice1.Value = TRUE, (Fields!NetSalesAmt.Value - (Fields!NetSalesAmt.Value * Parameter!DiscountPercent.Value)), Fields!NetSalesAmt.Value) Then you can take it further by having other parameters which limit the data in the report, say to only customers that had a sale greater than a million totals last year, or that are located in England or whatever you can think of. Then each time the parameters change the report is recalculated based on your parameter selections. In this way you can create highly complex scenarios. Anything you could do with Excel for What-if, you can do with SSRS, it just may take a bit longer to build.

Hi, Daniel Good points. Instead of pre-define the parameter choices in a list, is there a way to allow users to input arbitrary numbers in a field for what-if analysis in SSRS? LQ

Hi There Thanks for your posting. If you do not bound your parameter to a dataset or you do not define available values for your Parameter then your parameter will behave like textbox and you can input anything depending upon the type of parameter. I hope this will help If you have any questions please ask Many Thanks Syed Qazafi Anjum Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful.

Hi There Thanks for your posting. If you do not bound your parameter to a dataset or you do not define available values for your Parameter then your parameter will behave like textbox and you can input anything depending upon the type of parameter. I hope this will help If you have any questions please ask Many Thanks Syed Qazafi Anjum Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful.