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 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy