• 

  Keywords:A walk in the realm of LIST control,SSRS SQl 2005, SSRS,realm of LIST control

  Short Description:MSDN states list as - “A data region that repeats with each group or row in the report dataset. A list can be used to create free-form reports or forms, such as invoices, or in conjunction with other data regions. You can define lists that contain any number of report items. A list can be nested within another list to provide multiple groups of data”

  Posted By : Ramachandran Veluthattil Date:11/28/2012 8:36:59 PM

  Views:463

A walk in the realm of LIST control

MSDN states list as - “A data region that repeats with each group or row in the report dataset. A list can be used to create free-form reports or forms, such as invoices, or in conjunction with other data regions. You can define lists that contain any number of report items. A list can be nested within another list to provide multiple groups of data”.

But how different is it from a table or a matrix with a group as it also repeats the information as required with the different groups. Well let us stop all these “guesses” and jump right into the cold waters. As the say you got to enter the water to know how deep it is.. Below I will illustrate in my words about what is a list and how to use a list

I am not going to restate the definition of Microsoft on the list object but I would say that a list object is when you want to have multiple views of grouped data.  Confused? Hope the example below will help out. The example has both 2005 and 2008 methodologies

The below data is what I will be using for demonstration

The requirement

What I am trying to do here is have a report which will display the details of CUSTOMERID, SALESPERSONID and SALESAMOUNT in a tabular format and for when  the SALESPERSONID changes from say 1 – 2 in the example above it will also generate a pie chart with the details of the customers for whom this SALESPERSONID has done sales for.

SSRS 2005

The demo

·         Create a blank project with BIDS

·         Add the necessary data source and create a dataset also for the same. Here I am using the following properties for my dataset

o    Query Type – Text

o    Query - SELECT  CUSTOMERID, SALESPERSONID, SALES as SALESAMOUNT FROM SALESDATA

o    Drag and drop a LIST object from the TOOLBOX to the designer of the report

o    Your screen should look as follows

o     

o    The next task is to associate the created dataset with the list object. Select the list object and then right click and select Properties. The below screen will be displayed

o    Now select the dataset name and assign the data set. In the below images I have assigned the dataset created Dataset1 to the list object

o    After assigning the Dataset next click the Edit Details Group button. This is needed to associate the group to the list object. This is one of the most important items to do. In this example I am assigning the Salespersonid as the item on which the grouping must be done

o    Click Ok 2 times and come back to the main screen. Now drag a table object and put it inside the list object. Drag and drop the Customerid and Sales column from the dataset into the table. The designer should now look like this

o    Now save the report and click preview. There will be multiple SETS of data shown in the screen. Each set of data is corresponding to a single salesperson id. The data is broken  into different sets based on the grouping of sales person id we have created in the group. Below screenshot shows the output

o    Now go back to the designer and then drag a chart item into the list object. Change the chart item to a pie chart and drag the customerid column from the dataset to the “Drop category fields here” area. Also drag the salesamount column from the dataset to the “Drop data fields here” area. Set any other property that you want to set. Below screen shot shows how your designer should look

o    Now again preview your report voilà there is the report that we need. We can see the 2 different views for a given set of grouped data. In this example the tabular format and the pie chart format. Below screen shot shows the final output

Comments

Please Login to Post your comments Login