I have to create a single report in MSSSRS to view calculated columns (Salary, Total Compensation, Performance Bonus, 401 (k)) by Employee, Department, Service Line, Geography, Quarter, etc.

My modeler is going to give me one and only one single flat TABLE (No FACTS and Dimensions) that he has developed in MS Access and now he is going to load into MS SQL SERVER 2008 R2.

Therefore, the question is how do I develop this report without having Dimensional Structure in place? Do I create a Multi-dimensional Views on a given single table or no matter what but he should create Star-Schema model?
What if he doesn't create a Star schema structure then what are my options to create a report that requires viewing calculated columns by Employee, by Department, by Service Line, by Geography, by Quarter?

Also, should I write multiple Stored Procedures or perform simple calculations in MSSSRS?

1 Answer 1


It sounds like you are talking about creating a report that allows the users to choose the grouping of the data, e.g. there is a report parameter that allows the user to select "Employee", "Department" etc. which will then drive the grouping in the report data (presumably a table).

You have a couple of options to achieve this:

1) Create a dataset that uses dynamic SQL to build the query selected columns and GROUP BY clause based on the user's selection. This is probably the best performing option but in my experience is more trouble than it's worth due to the inherent difficulties of debugging or changing dynamically built SQL statements.

2) Create a dataset that contains all the possible grouping columns using a simple select statement, and then design the report to dynamically group the results based on the user selection. In your case this is the simplest choice since your data is already in a single table.

e.g. if you have a report parameter called "GroupBy", then you can set the grouping expression in the report data region (e.g. a table) to:


So if one of the dataset columns is called "Employee" then you need a parameter option with a value of "Employee". If the user selects this parameter option then the table grouping is set to employee. You also need to use a similar expression to set the display value in the grouping column (e.g. if the grouping is by Employee, then you need to display the Employee name).

  • Thank You. Here is a problem ***My end user gave an Excel spreadsheet with 15 columns and 50K rows out of which 5 columns are calculated and 10 are not. My data modeler has converted Excel into a single TABLE on MSSQLServer platform. Now I am being asked to create a report that let my end users view calculated columns/numbers/FACTS by Department, By Employee working for those department, By Geography, By Service Lines, etc. Therefore the question is how do I create such report in MSSSRS using FLAT table? Should I ask Modeler to create a Star-schema model? How can he convert Excel into model?
    – Samuel
    Mar 12, 2014 at 15:49
  • As I understand your modeler took all calculated columns to DB as values, not as calculations. Use Select statements with filteres to retun rows from the table. Aggregation would not be possible (meaningless) for most of calculated columns. Modeller should redo those calculations in DB. Use Char-type columns as dimensions and number columns as facts.
    – Stoleg
    Apr 11, 2014 at 10:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.