Link to home
Start Free TrialLog in
Avatar of Dale Fye
Dale FyeFlag for United States of America

asked on

New to SSRS, extremely slow running report.

I'm new to SSRS but have a lot of experience with SQL Server and am an Access MVP.

I've inherited a number of SSRS reports form a prior developer and this particular report takes forever to run.  User generated imageThis report contains three distinct sections, all of which have different Tablix properties and datasets.

1.  At this point, my biggest problem is that I have not figured out how to determine where the data is coming from to fill in the textboxes which contain <<Expr>> values.  When I click in those cells, I get a drop down list of well over 50 fields, but I do not see any expression definitions.

2.  Can I use a view or stored procedure from the database as a  data set for the report?

3.  In Access, when I create a report, I can create groupings, so that even though every record in the reports recordset contains the Supervisor, County, Service Dates, Family Name, Worker, and Team Leader, these only appear once in that top grouping.  I see there are "Row Groups" and "Column Groups" sections at the bottom of the report.  How would I go about defining these groups so that the top section of the report is grouped by [ReferralName] or the @ReferralID parameter.  All of those other fields other fields in that top section are all directly related to the [ReferralID] field in the database.  Then upper section has a filter on it for [ReferralID] = @ReferralID.

Thanks for your assistance.
ASKER CERTIFIED SOLUTION
Avatar of Jeff Tennessen
Jeff Tennessen
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Vitor Montalvão
Try to find the whole SQL query and post it here so we can analyze it.
Avatar of Dale Fye

ASKER

Vitor,

It's not the query that is causing the problem, it is all of the expressions interspersed throughout this report.  Although I do have a query (viwe or SP) written which will solve most of the problems.  Let me play with this a bit and see what I can do.  I'm sure I'll be back as I delve more into SSRS.

Dale
Thanks, J.  Appreciate the references.

I'm sure I'll see you here again.
I see how to link the stored procedure to the report, but not a view.

The SP that I have contains several parameters.  How do I pass those parameters to the SP if I assign the SP as the dataset for a tablix.  The parameters needed by the SP are all defined in the report.
Hi Dale,

Are you talking about using a SQL Server view as a datasource for the report?

Having the parameters defined in the report should automatically feed them to the SP, as long as they're named correctly. Is that not working for you?
@J

yes, I was considering using a view, but if the parameters that are defined for the report will automatically feed into the SP, that is great, and that should resolve my issue.
Gotcha. To use a view, you'd have to do something like this:
User generated image
But yeah, the SP should work. Let me know if it causes you any trouble.