In tis “How to” series I would like to explain ways you can edit existing report. Suppose that we have already existing report in Report Server and we would like to change it or extend it. Here is short overview, how you can manage it:
Open report in report builder application and change it
Download report and open report in visual studio with data tools and modify it
Since report is xml document you can download it and use any text editor 🙂
Use Reporting Services web service (C# application, powershell, etc.) to make changes
Reporting Services extensions …
Do you know any other approach, “how to” edit your report?
With my friend Mark, we recently discussed how the SQL server can deal with data retrieval, if the data are sorted by index in specified direction and query requests data in the opposite direction. As you can see below SQL server optimizer can deal with this request without the sort operator, which brings less costs in query execution. But it is very important that query meets with index design, otherwise sort operator occurs in query plan.
From samples bellow it is clear how it is important to pay attention to query with comparation to index design to avoid Sort operator
Let’s create a simple table with non-clustered index – ordered in ASC direction.
CREATE TABLE dbo.TestTable(id INT,sec_no INT)
CREATE NONCLUSTERED INDEX IX__TestTable__sec_No ON dbo.TestTable (sec_no ASC)
Let’s fill it with data.
INSERT INTO dbo.TestTable SELECT object_id,s.schema_id FROM sys.objects s
Now let’s look at execution plans of queries bellow. The first one ordered in ASC direction and the second one in DESC.
SELECT sec_no FROM dbo.TestTable ORDER BY sec_no ASC
SELECT sec_no FROM dbo.TestTable ORDER BY sec_no DESC
As you can see on both execution plans there are scans of non-clustered index and no sort operators.
Picture 1 – Execution plan
If we look closer on Index Scan operators F4 we can see that optimizer used Scan Direction Forward on the first query execution plan and Backward in plan with DESC ordering.
Forward scan direction
Picture 2 – Execution plan FORWARD scan direction
Backward scan direction
Picture 3 – Execution plan BACKWARD scan direction
Now if we remove non-clustered index we and run above mentioned queries we can see that Sort operator appears in our execution plans.
DROP INDEX IX__TestTable__sec_No ON dbo.TestTable
Picture 4 – Execution plan – sort operator
Create non-clustered index again and change little bit its design. Now the data are ordered by ID column first.
CREATE NONCLUSTERED INDEX IX__TestTable__sec_No ON dbo.TestTable (id,sec_no)
Picture 5 – Execution plan – sort operator
Now query optimizer uses index to retrieve data again but because index design does not correspond with our query (ordering of query does not fit to index), data has to be ordered in Sort operator. If we look by F4 on Index scan operator we can see that data are not returned in requested order – Ordered attribute is set to false.
Picture 6 – Execution plan index scan
We can get similar situation if we go back to our first example with right index design.
DROP INDEX IX__TestTable__sec_No ON dbo.TestTable
CREATE NONCLUSTERED INDEX IX__TestTable__sec_No ON dbo.TestTable(sec_no ASC)
But now we change our query little bit.
SELECT sec_no,id FROM dbo.TestTable ORDER BY sec_no ASC
From picture we can see that execution plan is similar like the one where we removed index. Here index exist on table but cannot be used to retrieve data because we changed our query the way index cannot be used and sort operator occurs again.
Lets create second table with index designed in right way as our first examples states. And compare both execution plans.
SELECT * INTO dbo.TestTableII FROM dbo.TestTable
CREATE NONCLUSTERED INDEX IX__TestTableII__sec_No ON dbo.TestTableII (sec_no)
Picture 7 – Execution plans comparation
From picture above, we can see that sort operator is a resource consuming operation from the perspective of query optimizer. In case you see it in the execution plan it is necessary to pay attention to it. If we want to avoid sorting in the execution plan, a suitable index design matching our query can help. However, this is not all yet. In the next posts I want to look at the Sort operator in more detail. And we will show that in some cases it also depends on the ordering direction of the index.
We need to create a quick overview of the AdventureWorks spreadsheet showing customer revenue for selected period. Our goal is not to describe Report Builder options in detail. This will be described in other separate posts. I would like to create quick “how to” procedure for people who see Reporting services for the first time.
Requirements:
To create a report, you will need a report builder application that you download directly from Reporting Services Report Manager. If you are not able to download it, please contact your administrators to make the application available or set you permissions accessing Report Manager. The application can be downloaded at following link https://bit.ly/2xaRQxB.
Run Report builder application and Report Builder wizard
Defining data sources and connections
Create a dataset and build query
Define report design
Modify generated report
Publication of the report
Detailed procedure:
Go to your reporting services manager url and run Report builder application.
To run the builder report, click on menu link at top menu (picture 1 – bellow).
Picture 1 – Report manager menu
After starting the application, left click on New-> File at the top-left and select Tablix or Matrix Wizard
Picture 2 – Table or Matrix Wizard
Then you have to create new dataset or select an existing one. In our scenario you will create new dataset.
Picture 3 – Dataset dialog
In next window, select connection type and name your data source. You connect to the SQL server – you leave default connection – Microsoft SQL Server.
Picture 4 – datasource
Clicking on Build button next to Connection string new dialog window appears. (or you can type connection string manually to Connection string text area as you can see on picture above).
Picture 5 – Connection properties
Select server you will connect to, then select correspondent database and authentication type that will be used to connect to the server. If you are trying on your local machines suppose you will use Windows Authentication as default.
Picture 6 – Query designer
As next step you have to define dataset for your report. Dataset returns requested data from defined connection. In our scenario we connect to SQL server database so you need to build query to return data form database. Report Builder Wizard offers possibility to create query by query designer – on picture above. You can write your query directly after clicking Edit as Text button, top on the dialog window.
On the left menu there are database objects such views or tables. In the upper right, you define fields for query (picture 6).
Picture 7 – Query designer
In our scenario, select the SalesAmound, Sum_order Quantete field from the FactInternetSales table to be monitored. Use the OrderDateKey column to select a period in the report. To by able to track data by customers, you have to select the columns LastName, MiddleName, Gendeer, AdressLine1. For selected fields in the Aggregate columns, we choose their behaviour. For the fields we want to track Sum_SalesAmount, Sum_OrderQuantity selects Sum operator. For other columns, let Grouped by (Picture 7 – 2;4). According to these columns, the data will be grouped. Select the OrderDateKey column at the bottom of the filters to select the period for which we want to track the data (Picture 7 – 6).. In the field operator, we define behaviour of filters.
If there is reference integrity defined on the source tables (foreign keys, primary keys), then using Auto Detect (Picture 7 – 5), we create links between the tables in the Relationships section (Picture 8 – 1).
Picture 8 – Query designer – query definition
Click on the Run Query icon at top of the screen to return query data in Query results grid. (Picture 8- 3)
In case that reference integrity is not defined, relationship can be set manually. (Picture 9 ) First click on Auto Detect icon to enable icon on the right. Clicking on that icon creates new relationship (Picture 9 – 2). You have to define tables, join type and join fields to create required relationship.
Picture 9 – Relationships
From the next section Next, we define the report appearance and the layout of the individual elements.
Picture 10 – Report layout
First, select the location of the summation lines in the table. In our case, just leave Show subtotals and grand totals checked.
Click next to select one of the predefined themes. Leave Ocean and click Next.
Picture 11 – Report style
Picture 12 – Report fields definition
In the following window we select requested values and organize them. Our report will have data organized in row group, select the Last Name field in the Row groups and select Sum_SalesAmount, Sum_OrderQuantity in the Values area. The Sum operator was selected by default. By clicking on the arrow of the selected attribute (Picture 11 – 1), it is possible to change the aggregate function.
Clicking Next in report builder wizard displays a preview of the predefined report. At this stage, it is possible to edit the report. We will change the report header in the pre-generated textbox field above the table. (picture 12 – 1)
Picture 13 – Report design
On the left, in the Report Data window, you can check the generated filters after clicking Parameters (Picture 12 – 1). We could view, edit, add Datasets or Data Sources.
Report design
Picture 14
The last step is to publish the report to the report server. Click on the save icon at the top.
Picture 15 – Report builder menu
The next step is a dialog box for selecting a path to save the report and selecting a name.
Picture 16 – Save report dialog
Voila, our first report see the light of day.
Picture 17 – Report
Now we could add more columns to our Tablix, first name for example. It will be explained in next post how to edit existing report.
As you can see you can create your first report very quickly and Report builder wizard helps you to achieve it without detail knowledge of SQL language.
