May 17, 2011 at 4:47 pm
Hello All,
I am attempting to design my first SSRS report and I good use some big picture guidance on how I should proceed.
I'll call my report ClientList. It will return basic information about my client(name, account, address,etc) and it will return a series of lists, say favorite colors, favorite vegetables, favorite movie stars, etc)
I have two stored procedures.
1-uspCientInfo with one parameter being passed @client
2-uspCientfavoriteLists with two parameters being passed, @client and @ListType
It seems for each list being returned I need a separate dataset. I would be calling uspCientfavoriteLists multiple times with different parameters.
EXEC uspCientfavoriteLists 'Client1','Colors'
EXEC uspCientfavoriteLists 'Client1',Vegetables
EXEC uspCientfavoriteLists 'Client1',MovieStarts
Is this a valid approach given that I will have multiple datasets calling the same procedure? The parameter list doesn't seem to accept multiple versions of @ListType.
Should I look at subreports?
May 18, 2011 at 9:47 am
You certainly can have multiple datasets using the same stored procedure. I do it when I want to show MTD and YTD information on the same report - I call the same stored procedure with different date ranges.
As for whether to use sub-reports, I think that depends on how you want to present the data. You say that there are lists of favourite colours - so presumably 1 client might have 1 favourite colour and another might have 10. If you're planning to have the client information, following by 3 separate tables (one for each list), I would think multiple datasets would work as effectively as subreports. If each client has only 1 favourite colour, etc, that you want to display in one table then it might be better to adjust or create a new stored procedure that returns all the data in one data set.
Just my 2 cents.
Leonard
Madison, WI
May 18, 2011 at 2:10 pm
I ended up modifying the procedure uspCientfavoriteLists to accept one parameters @client instead of 2, @client and @ListType.
I am then filtering the returned results at the tablix level. ListType = 'Vegetables' for example.
What's also nice is I can add a TOP filter at the tablix level. Some types I need to restrict to TOP 10 and other types I want to return all.
Thanks.
May 18, 2011 at 2:29 pm
Makes total sense!
Leonard
Madison, WI
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply