Creating Dynamic Reports in SQL Server: Question And Answer

Q.1. How to Create a Report Server Project (Reporting Services)?

Ans. To create a report server project for developing a report in SQL Server, follow these steps:

• Click the "Start" button, go to "All Programs," then select "File," and click on "SQL Server Data Tools." If you are using SQL Server Data Tools for the first time, check "Business Intelligence Settings" to view the default environment settings.
• In the File menu, click on "New," then select "Project."
• Under "Installed Templates," click on "Business Intelligence".
• Choose "Report Server Project" and enter "Tutorial" in the "Name" field.
• Click "OK" to create the project.

Q.2. What is The Procedure for Creating a Report Definition File?

Ans. A Report Definition Language (RDL) is an XML-based language used to define SQL Server Reporting Services report definitions. To create a report definition file, follow these steps:

• In the Solution Explorer, right-click on "Reports," then click on "Add" and select "New Item."
• In the "Add New Item" dialog box, choose "Report" under Templates.
• Enter "Sales Orders.rdl" in the "Name" field, and click "Add."
• The Report Designer will open in Design view. Here, you can define your report data in the "Report Data" pane and layout in the Design view. You can preview the report in the "Preview" tab.

Q.3. What are The Requirements for Data-Driven Subscriptions?

Ans. To create and maintain data-driven subscriptions on a report server, the following requirements must be met:

• You need to have SQL Server version that supports data-driven subscriptions.
• The report server must be running in native mode. While subscriptions are supported on report servers running in SharePoint mode, the user experience may differ.
• The SQL Server Agent service must be running.
• You must have a report with specifications, and it's recommended to use a sample report like "Sales Orders" from the tutorial.
• The sample database should provide data for the sample report.
• You must have the appropriate role assignment that includes the "Manage all subscriptions" task for the sample report.
• A shared folder with write access must be available, and a network connection should allow access to the shared folder.

Q.4. How to Specify Connection Information?

Ans. To specify connection information for creating a report in SQL Server, follow these steps:

• Click "New" in the "Report Data" tab, then select "Data Source."
• Enter the name of the connection in the "Name" field.
• Choose "Microsoft SQL Server" in the "Type" field.
• Enter the appropriate connection string based on your database setup.
• Click on the "Credentials" tab and select "Use Windows Authentication" for integrated security.
• Click "OK" to add the data source to the report.

Q.5. What is Connection?

Ans. A SqlConnection object represents a specific session to a SQL Server data source. It is used to establish a connection to the SQL Server database. When you link to a Microsoft SQL Server database, you use SqlConnection, SqlDataAdapter, and SqlCommand to interact with the database. Use OleDbConnection for third-party SQL Server products and other OLE DB-compatible data sources.

Q.6. What is The Procedure for Setting Up a Connection?

Ans. To set up a connection in SQL Server, follow these steps:

• In the Report Data tab, click "New" and then select "Data Source."
• Enter the name of the connection in the "Name" field.
• Choose "Microsoft SQL Server" in the "Type" field.
• Enter the connection string that corresponds to your database setup.
• Click on the "Credentials" tab and select "Use Windows Authentication" for integrated security.
• Click "Test Connection" to ensure successful connection.
• Click "OK" to add the data source to the Report Data pane.

Q.7. How Do You Add A Table To A Report?

Ans. To add a table to a report in SQL Server, follow these steps:

• Select the "Table" option from the Toolbox and drag it to the design surface.
• Expand the "AdventureWorksDataset" dataset in the "Report Data" pane to see the available fields.
• Drag the desired fields from the "Report Data" pane and drop them into the table's columns.
• Arrange and format the table as needed in the Design view.
• Use the Preview tab to run the report and check how the table appears.

Q.8. How to Modify Report Data Source Properties?

Ans. To modify report data source properties, follow these steps:

• Open Report Manager with administrator privileges.
• Browse to the folder containing the report and click on "Manage" in the report's context menu.
• Click on the "Data Sources" tab.
• Select "Microsoft SQL Server" as the data source type.
• Enter the connection string for your database.
• Click on the "Credentials" tab and enter the necessary login information.
• Click "Test Connection" to verify the connection.
• Click "Apply" to save the changes.

Q.9. How Do We Create a View Filter?

Ans. To create a view filter in SQL Server, follow these steps:

• Click on the "Filters Area" on the Home tab.
• In the Fields list, select the field you want to use for filtering, e.g., "Date."
• Drag the field to the "Filters Area."
• Choose the filter conditions, e.g., "on or after" and "on or before."
• Select the desired values for the filter condition, e.g., "August 31, 2011."
• Apply the filter.

Q10. How Do We Create a Shared Data Source (RSDS) File To Point To The PowerPivot (XLSX) File?

Ans. To create a Shared Data Source (RSDS) file to point to the PowerPivot (XLSX) file, follow these steps:

• Create a folder named "HelloWorldPicnicSQL2012" in Shared Documents and save the "HelloWorldPicnicPowerViewRTM.xlsx" file there.
• Click on the Documents tab in the "HelloWorldPicnicSQL2012" folder, then click on the arrow next to "New Document," and select "Report Data Source."
• Enter "HelloWorldPicnicRSDS" in the Name box (note the .rsds file extension).
• Select "Microsoft BI Semantic Model for Power View" in the Data Source Type box.
• Enter the connection string for the XLSX file, e.g., "Data Source = httpFor Example, the URL could be -https:///reports/browse/Tutorial."
• Select the dropdown arrow next to Sales Orders. Select Manage. Select the Properties tab, and then select the General tab. Select OK after typing Tutorial as the folder in which you want to place the report. Click the down arrow in the report field, and then click Browse. Click the desired report and then select Open. Click OK. Select Apply.

Q.11. How to Create a View Filter?

Ans. The process to create a view filter includes:

• Click on the Filters Area located on the Home tab.
• In the fields list of the field section, go to the Dates table and click on Date.
• Drag the Date field to the filters area. Now, you can select individual dates in Basic Filter Mode.
• To switch to Advanced Filter Mode, click the Advanced Filter Mode button to the right of the Date field.
• Click on the arrow next to "is on or after" and then select "is on or before."
• Click on the calendar and choose August 31, 2011.
• Click on the "Apply Filter" button to remove dates after August 2011.

Q.12. How to View The Report?

Ans. Your report can be viewed in presentation and preview mode, and it remains interactive in both approaches. When you click on the bars or columns in the charts, the rest of the report will be filtered and highlighted. However, in these modes, you won't be able to add fields, create visualizations, or move/resize existing visualizations, as the field list and ribbons are missing. Moreover, any interactions made will not be saved in the report. Reports are an essential component of SQL Server reporting.

To Preview Your Report:

• Click on Reading Mode located on the Home tab.
• Notice that the filter area remains visible.
• Switch views by clicking on the numbers in the lower right or the icons in the lower left.
• Try some of the interactions you did earlier in the lesson, such as clicking on the charts' bars, selecting various items from the container of tiles, or choosing an item in the slicer.
• To go back to design mode or full-screen mode, you can use the buttons in the upper-left corner.

To Present Your Report:

• Click on Full Screen located on the Home Tab.
• Notice that the filter area is still visible.
• Try the interactions you tested earlier in reading mode.
• Press the ESC key to leave full-screen mode.

Q.13. How to Save Your Report?

Ans. To save your report, follow these steps:

• Click on Save or Save As in the File menu.
• Use the Save As dialogue box to specify the location where you want to save the report. It is recommended to save the report to the same SharePoint server as the model, but you have the flexibility to choose a different location, like a subfolder.
• Give the report a unique name, ideally one that clearly indicates the model.

Additionally, you may leave the checkbox next to "Include an image of each view in its present state as a preview for other users" checked if you are using PowerPivot Gallery and other applications. This will show sneak peek pictures of the views.

Q.14. How to Export Your Report to PowerPoint?

Ans. You can export the report to PowerPoint, and the interactive features will be similar to those in reading and full-screen modes.

To Export to PowerPoint:

• Click on Export to PowerPoint under the File menu.
• The updated PowerPoint file will be saved wherever you choose.
• If it links to the Report saved on the SharePoint server, it will be saved there.
• Otherwise, you can save it elsewhere.

Launch the PowerPoint show:

• Each Power Perspective view will be introduced on a different slide. The slides can be static representations of the views in PowerPoint design mode.
• If you have saved a preview image when saving the report, you will see a static representation of the 'View' in PowerPoint design mode.
• You can make any desired changes to the PowerPoint presentation, and while you can resize Power View views in PowerPoint, you cannot edit them.
• To view the report interactively, click on Slide Show in PowerPoint, and in the lower-right corner of the slide, you can click to interact with the views, just like in reading and full-screen modes.

Q.15. How do We Add Dates and Numbers? Report Design with Datasets and Architecture.?

Ans. To add dates and numbers to your report, follow these steps:

• Click on the blank space in the view to start a new visualization.
• Expand the Dates table and drag the "Month Name" field to the view.
• Add the "Category" field from the Items table to the same table. 
• Note: For charts to work, you need at least one aggregated value in the table.
• In the Quantities table, click the drop-down arrow next to the "Qty Served" field and choose "Add to the Table as Sum."
• The charts should be available now that the table has a field with a sum.
• By default, Power View does not aggregate whole numbers like "Qty Served" unless you specify it in the model.
• Click on the "Line Chart" option.
• The visualization should display "Qty Served" in Values, "Month Name" in Axis, and "Category" in Series.
• If not, you can manually drag the fields to the appropriate boxes.
• One issue might be that the months are not ordered chronologically but alphabetically (e.g., starting with April instead of January). To resolve this, go back to the model and adjust the sorting of the "Month Name" column using the "Sort by Column" feature based on the "Month Number" column.

Q.16. How to Set Default Aggregates, Dates, Tooltips, and Calculated Measures and Columns in Power Pivot?

Ans. To set default aggregates, dates, tooltips, and calculated measures and columns in Power Pivot, follow these steps:

To Set Month Order:

1. Go to the Dates table in the PowerPivot window.
2. Click on "Mark as Date Table" on the Design tab.
3. Select the "Date" field and click "OK."
4. Ensure that the values in the "Date" field are unique.
5. On the Home tab, select the "Month Name" column and choose "Sort by Column," then sort "Month Name" based on the "Month Number" column.
6. Select the "Month Number" column, right-click, and click on "Hide from Client Tools."

To Set Default Aggregates:

• Go to the Quantities table in the PowerPivot window.
• Select the "Qty Served," "Qty Consumed," and "Attendees" columns.
• Click on "Summarize by," then select "Sum" on the Advanced tab to create the sum description.

To Set a Description:

• Right-click on the "Qty Served" column, choose "Description," and then enter "How Much We Served" in the Excel formula box.
• The description will be displayed in the fields (upper) part of the field list as a tooltip over the "Qty Served" field.
• You can give each table and field a tooltip.

To Create a Calculated Column:

• Insert a column and rename it "Leftovers" in the Quantities table.
• In the Excel formula box, type the following formula: =[Qty Served]-[Qty Consumed].
• As you type, PowerPivot will suggest matching column names.
• On the Advanced tab, select the "Leftovers" column, click on "Summarize by," and then choose "Sum."

To Create a Calculated Measure:

• In the Quantities table, click on any cell in the lower part of the table below the dividing line (the measuring grid).
• You can display or hide the measuring grid using a button on the Home tab.
• Any cell in any column can be used for entering measurements.
• Add the formula for the quantity served year to date (YTD) to the Excel formula box as follows:
• Formula: Qty Served YTD:=TotalYTD(SUM([Qty Served]), Dates[Date]).
• You can save your model by saving the Excel (XLSX) file.

Q.17. How to Define a Transact-SQL Query for Report Data?

Ans. To define a Transact-SQL query for report data, follow these steps:

• Choose "New > Dataset" from the Report Data pane. The Query section will be visible when the Dataset Properties dialogue box appears.
• Enter "AdventureWorksDataset" in the Name text box.
• Select the option to "Use a dataset contained in my report."
• Choose "AdventureWorks2016" from the drop-down box for the data source.
• Select the "Text" radio button under the Query type heading.
• Type or paste the Transact-SQL query into the Query text field.
• Optionally, you can select the "Query Designer" button to view the query in the text-based Query Designer and see the query results by selecting the "Run" button on the Query Designer toolbar. The Dataset will display six fields from four tables in the AdventureWorks2016 database, and it may use Transact-SQL features like aliases.
• Click "OK" to exit the Query Designer and "OK" again to exit the Dataset Properties dialog box.

Q.18. How to Define a Report Dataset?

Ans. To define a Report Dataset, follow these steps:

• Expand the node for the report you want to work with in Model Editor.
• Right-click the "Datasets" node, and then select "Add Dataset."
• Choose the Dataset's node.

Set the necessary properties in the Properties window:

• Data Source Type: The method for retrieving data from the data source. Your options for the Data Source property will depend on your choices. For example, if you choose Dynamics AX, you will have these options:
• Using a given Query from the Application Object Tree (AOT).
• Using a data method specified in a reporting project as Business Logical.
• Using business logic from a Microsoft Dynamics AX X++ class by the Report Data Provider.
• Using an enum type with the AX Enum Provider.
• To add data to your dataset, you can write a Transact-SQL query, use the Query Designer, or select tables, views, or stored procedures.

Q.19. What to Check With Dynamic Filters?

Ans. Set the Dynamic Filters property to True if you want to include dynamic filters in your report. When this value is set to True, both a Dataset parameter and report parameters with the same name will be created. These parameters allow the end-user of the report to add filters based on any of the report's fields using dynamic filters. It enables the creation of dynamic reports. If the Dynamic Filters property is set to False, the Dataset and report parameters for the default ranges are constructed. In this case, the final user of the report cannot add extra filters.

Q.20. How to Format The Currency in The SQL?

Ans. To format a number as currency in SQL Server, you can use the T-SQL FORMAT() function. For example:

SELECT FORMAT(1234, 'C') AS Result;

This will produce the following result:

The 'C' in the FORMAT() function is the currency standard numeric format string, and it formats the number as a currency with a dollar sign, comma as the thousands separator, and two decimal places.

Q.21. How To Add A Grand Total To A Report?

Ans. To add a Grand Total to a report, follow these steps:

• Right-click on the data region cell that contains the expression for the field you want to total (e.g., [LineTotal]) and select "Add Total."
• This will add a row with the sum of the dollar amounts for each order.
• Right-click on the cell that contains the expression for the field you want to total (e.g., [Qty]) and select "Add Total."
• This will add a row with the sum of the quantity for each order.
• Type "Order Total" in the empty cell to the left of the Sum([Qty]) cell to label the total.
• Optionally, you can add a background color to the total row and select the individual sum cells and label cell, then apply the desired formatting.

To Add the Daily Total to the Report:

• Right-click on the [Order] expression cell and select "Add Total -> After."
• This will add a new row containing sums of the values of [Qty] and [LineTotal] for each day, with the label "Total."
• Type "Daily Total" before the word "Total" in the new cell to label the total.
• Optionally, you can add a background color to the daily total row and apply the desired formatting.

To Add the Grand Total to the Report:

• Right-click on the [Date] expression cell and select "Add Total -> After."
• This will add a new row containing sums of the values of [Qty] and [LineTotal] for each day, with the label "Total."
• Type "Grand Total" before the word "Total" in the new cell to label the grand total.
• Optionally, you can add a background color to the grand total row and apply the desired formatting.

After making these changes, preview the report to see the grand total and other subtotals added to the report.

Conclusion

Creating dynamic reports in SQL Server opens up a world of possibilities for data analysis and presentation. With the step-by-step instructions provided in this guide, you have the tools to design and generate insightful reports that empower users to explore and understand data effectively. Whether you're a data analyst, business professional, or developer, mastering dynamic report creation is an essential skill that will enhance your ability to communicate data-driven insights. Start creating dynamic reports today and unlock the potential of your data like never before.