Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

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:

=Fields(Parameters!GroupBy.Value).Value

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).

share|improve this answer
    
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 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 at 10:38

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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