When using SharePoint you can add, rearrange or remove different fields and also filter or sort out data by applying a query SharePoint list to the data source. By default query options are always available only to classified SQL based data-sources that includes: individual databases, Microsoft SharePoint lists and libraries, Database connection-sections and linked databases. If you would like to create or modify the fields included in the default SharePoint list, you must first create a data source connection for that particular list and then modify-it to ways that you like.
How to Query SharePoint List Data
SharePoint provides you with well customized options that enables you to specify a particular query. Each of the data-source type contains additional options that enables one to locate and specify a particular query.
Libraries and SharePoint Lists
When using SharePoint you can easily specify a library and SharePoint list query by using options found in the “Item and Folder-scope” list.
The “item and folder-scope” helps to indicate whether the current query spans sub-folders and folders . This feature is very helpful especially when you are using SharePoint libraries. If you would like the Shared-documents folder to contain files and sub-folders( that also carry files) you can easily specify which folders or files to be included in a query.
If don’t know how to query SharePoint list on files follow this simple procedure to learn how to go about it:
- If you would like to query files in a current folder and not to any other sub-folder, click on the “Files-Only”
- To query all files in a folder without interfering with other sub-folders of your SharePoint site, click on “Recursive” option.
- If you would like to query all files in a particular sub-folder located in a main folder, click on “default”
- If you would like to apply query to files in a folder and all sub-folders of the entire site, choose or click on the “Recursive-All”
The SharePoint List’s Query-designer
The List designer helps one to create and design both text based-query designer and graphical query designer. This designs can help you query data and retrieve it from a SharePoint site for a report Data-set. You can use the graphic’s query-designer to interactively build data, explore SharePoint list Meta-data and view all results available in your Query. If you want to view a query that was built with the graphics (graphical) query-designer, use the text based query-designer to modify the query.
- The graphical (graphics) query designer
- If you are not familiar with how SharePoint works, you can use the graphic designer to explore the entire SharePoint website and interactively build comprehensive commands that can retrieve SharePoint List data for a Data-set. Optionally the graphic query-designer allows you to specify filters that can limit the data and also choose fields to include in the data-set.
- By default the the SharePoint list data includes a large number of unnecessary SharePoint specific fields that are not useful when included in the reports. With the query designer, you can hide all fields that you think are unnecessary and leave only fields that can be used.
The Graphical (graphics) Query Designer is Made Up of Three Parts
1. The design are where a query is made
2. The result panes, which helps one to view the query result
3. The Explore Pane, where you select the lists items and the fields to use
The Graphical Query Result Pane
- When you query SharePoint list, you can use the query result pane to display all results that are automatically generated by the graphical (graphics) query designer. On the results-pane, the columns in the results sets are fields that specify particular selected fields in the pane. The row data is some how limited by the filters that you specify in the Applied-filters pane. The data displayed represents values from the data-source at the specific time when make a query.
- With the Query result pane you cannot save data in the report definition, the queried data in the report is retrieved once the actual report is processed.
The Applied Filters Pane
On SharePoint the applied filters-panes simply displays the criteria used to limit the number of row’s of a data that is retrieved at a run-time. The criteria specified on this pane is used to regenerate a transact SQL clause. You can select one of the parameter option to automatically create an automatic parameter report. All reports parameters that are mainly based on query-parameters helps to enable SharePoint users to easily specify values for a particular query in order to control the data contained in the report.
The list below shows the columns that are displayed:
1. The value- it displays the ultimate value to be used in the filter-expression.
2. The field name- it display’s the names of the fields to apply the criteria
3. The parameter- This displays all options to be added to a query-parameter. If you don’t know much about the parameter, use the data-set properties to view the difference between a report parameter and a query parameter.
4. The Operator– it displays the operations to be used in the filter-expression.
The Selected Fields Pane
The fields pane displays all the list items fields that are selected for on the SharePoint list items. All fields displayed by this pane becomes the field collections for the report data-set. Once you create a query and a data-set on SharePoint, use the report-data pane to view all fields collections for the report data-set. The fields simply represents data that can be displayed in charts, tables and other report-items when viewing a report.
The SharePoint List Pane
SharePoint List-pane displays the Meta-data for data-base objects that are not protected by passwords or restricted to particular users for viewing. With this pane you can expand the node for every schema to easily view tables, stored procedures, views and table valued functions.
If you are confused on how to query your SharePoint list data, use the text based query-designer easily specify a query by using a query language that is supported by the Data-source. With the text based query you specify multiple Transact SQL statements and easily command-syntax for data processing extensions. If you learn the basic of how to query SharePoint list, you can easily advance to more complex and detailed querying techniques.
