SSRS Practice

practice Questions –SSRS

Reports Requirements:

Report  –  1 :

1. 1.       Database : Pubs
2. 2.       Column’s in the report :Title_id, Title, Type and Price from the Titles table

And  Publisher_Name from Publishers table.

1. 3.       Report Name : Rpt_Titles.
2. 4.       Report Requirements :
   1. 1.       Create the report using the report wizard / report.
   2. 2.        Create a Shared data-source for the Pubs database.
   3. 3.       Group on Publishers_Name.
   4. 4.       Details have to be hidden.
   5. 5.       Add the report page Header and Footer, report name.

Report  –  2 :

1. 1.        Database : NorthWind
2. 2.       Column’s in the report : Company Name, Contact Name, City, Region, Country from Customers Table.
3. 3.       Report Name: Rpt_Customers.
4. 4.       Report Requirements :
   1. 1.       The report should contain single group based on the Country Field.
   2. 2.       The project name Should be IntroducingSSRSLab.
   3. 3.       The Heading on the report should read Northwind Customers.
   4. 4.       The style of the report should be stepped and Bold.

Report  –  3 :

1. 1.        Database :  NorthWind
2. 2.       Column’s in the report : Category Name, Product ID, Product Name, Quantity Per Unit, Unit Price,            

                                             CompanyName from tables Products, Categories, Suppliers.

1. 3.       Report Name : Rpt_Basic.
2. 4.       Report Requirements :
   1. 1.       Create a Tabular Report.
   2. 2.       The Product Unit Price has to be <40.
   3. 3.       The Column’s Category Name and Product Name has to be Order BY.
   4. 4.       Add the Sub totals.
   5. 5.       Add the Grand totals.

Report — 4:

1. 1.       Database : NorthWind
2. 2.       Column’s in the report :Type (Alias Column), Contact Name, City, Region, Country from tables

                                             Customers and Employees.      

1. 3.       Report Name : Rpt_Union.
2. 4.       Report Requirements :
   1. 1.       Create a Tabular report.
   2. 2.       Create an Alias Column “Type” to see whether it is an Employee / Customer.
   3. 3.       Order by Contact Name.

Report — 5:

1. 1.       Database : NorthWind
2. 2.       Column’s in the report : Employee, Product Name, Quantity, Cost from tables

                                              Employee, Products, Orders.

1. 3.       Report Name : Rpt_Employee_Sales.
2. 4.       Report Requirements :
   1. 1.       Create a Tabular report.
   2. 2.       The report should contain each of the 4 fields.
   3. 3.       Add Subtotal for the Cost.
   4. 4.       Add a page header containing the NorthWind.gif logo and heading text with

        “ NORTHWIND TRADERS EMPLOYEE SALES “.

1. 5.       Add totals for the report.

Report — 6:

1. 1.        Database :  NorthWind
2. 2.       Column’s in the report :  Company Name,  Order Date, Quantity, Product Name from tables

                                                Customers, Orders, Order Details and Products.

1. 3.       Report Name: Rpt_Customer_Orders.
2. 4.       Report Requirements :
   1. 1.       The query should be ordered by Company Name and Order Date.
   2. 2.       The report should contain a table region.
   3. 3.       Add a page header containing the Northwind.gif logo and heading text with

“NORTHWIND TRADERS CUSTOMER ORDERS”.

Report – 7:  

1. 1.       Database : NorthWind
2. 2.       Column’s in the report : Customers, Year, Sale Amount from tables
3. 3.       Report Name: Customer_Sales_By_Year.
4. 4.       Report Requirements :
   1. 1.       Create a Matrix report.
   2. 2.       Sales Years are 2006, 2007, 2008.
   3. 3.       Add totals sides and bottom.

Report – 8:

1. 1.        Database :  NorthWind
2. 2.       Column’s in the report: Last Name, First Name, Title, and Notes from Employee table.
3. 3.       Report Name: Rpt_Employee.
4. 4.       Report Requirements :
5. 1.         Concatenate the Last Name and First Name as a Employee Column using the Expression.
6. 2.       In the employee note column the string length has to be only 30 characters & …

Report – 9:

1. 1.        Database : Pubs
2. 2.       Column’s in the report :
3. 3.       Report Name : Rpt_Publishers
4. 4.       Report Requirements : (Global collections and Conditional formatting)
   1. 1.       Shorten the length of the Publisher_info values to max 50 characters.
   2. 2.       Add page footer to the report that includes the following exp built using the global collections.

<<Name of the report>> created<<Date & Time>>by<<User Name>>.

1. 3.       Apply italics to the footer expression.

      

Report – 10:

1. 1.       Database :  Pubs
2. 2.       Column’s in the report :Order Date, Store Name, Title, Quantity, Price from the tables

                                                Titles, Stores and Sales.

1. 3.       Report Name: Rpt_Book_Sales.
2. 4.       Report Requirements :
   1. 1.       Calculate the sale price for each book.
   2. 2.       Need to add additional column “Cost”.
   3. 3.       That calculates the cost of the Transaction by multiplying Quantity by Price.
   4. 4.       Both the price column’s need to be formatted as Currency and the Order-Date should be

Formatted as a Short Date.

1. 5.       You can use the expression editor to build the Cost.

Report – 11:

1. 1.       Report Requirements :
   1. 1.       Take the same report Rpt_Book_Sales.
   2. 2.       Add some conditional formatting.
   3. 3.       You need to highlight any Orders with a cost exceeding $500 with the following formatting:
      1. 1.       Color the Cost field “Red”.
      2. 2.       “Bold” the entire row.

Report – 12: (Grouping and Sorting)

1. 1.        Database : Pubs
2. 2.       Column’s in the report : Publisher, Publisher City, Book Titles, Book Type, Price, Publisher Date from

                                             Tables Titles and Publishers.

1. 3.       Report Name : Rpt_Titles_GroupedByPublisher.
2. 4.       Report Requirements :
3. 1.       Create a Tabular report.
4. 2.       First do the Group on Publisher and Publisher City.
5. 3.       Add second group on Book type.
6. 4.       Sort them Order By (Alphabetically).
7. 5.       When you add groups please add the group headers and footers.

Report – 13: (Grouping and Sorting)

1. 1.        Database : NorthWind
2. 2.       Column’s in the report : Customer ID, Company Name, Contact Name, Contact Title, City, Region,   

                                              Country from Customers table.

1. 3.       Report Name: Rpt_Customers_GroupedByCountry.
2. 4.       Report Requirements :
   1. 1.       Create a Tabular report.
   2. 2.       Add the group on the Country.
   3. 3.       Order BY Country, Company Name.

Report – 14: (Calculating Totals and Percentages)

For the above report.

Report – 15:

1. 1.       Database : NorthWind
2. 2.       Column’s in the report: Take the Rpt_CustomersGroupedByCountry.
3. 3.       Report Name: Rpt_CustomersGroupedByCountry2.
4. 4.       Report Requirements :
   1. 1.       Add  group on Country.
   2. 2.       This report has to show the total Companies based on the group (Company).
   3. 3.       This report has to show all the rows in the report (Count of how many rows).

Report – 16:

1. 1.       Database: NorthWind
2. 2.       Column’s in the report:  Name(First Name, Last Name) , Title, Product Name, Unit Price, Quantity

                                                  And Cost from tables

1. 3.       Report Name: Rpt_Summing_Sales.
2. 4.       Report Requirements:
   1. 1.       Concatenate the Employee Last name and First name.
   2. 2.       TxtcostProduct Total: 2nd group footer.
   3. 3.       Txtcostsubtotal: 1st group footer.
   4. 4.       Txttotalcost in the table footer.

= Sum (Fields! Cost.value) (expression).

1. 5.       Need to add the employee name on the Subtotal row.

Report – 17:

1. 1.       Report Requirements :
   1. 1.       Add percentages to the Rpt_Employee_Sales report.

Example: Product Total / Subtotal cost.

Report – 18:

1. 1.       Database: Northwind
2. 2.       Column’s in the report: Country, Company Name, City, and Region from Customers table.
3. 3.       Report Name: Rpt_Customers_Interactive_Sort.
4. 4.       Report Requirements:
   1. 1.       Create Tabular report.
   2. 2.       Add the Interactive Sort option.
   3. 3.       Add the group on Country and add the interactive Sort to the grouped column.

Report – 19: (Drill-down reports)

1. 1.       Database: Northwind
2. 2.       Column’s in the report:
3. 3.       Report Name: Rpt_drilldown.
4. 4.       Report Requirements:
   1. 1.       To complete this report, you’ll need to work through 3 reports.
      1. a.       Create a report with Nested group’s
      2. b.       Create a report with Percentages.
      3. c.        Create a Drill-down report.
      4. 2.       Create Rpt_CustomerOrders and add two groups to it as follow.

GroupName: GrpCompany.

         Grp on expression: Company Name.

         Expression to display in grp header: Same.

Group Name: GrpOrder Month

Grp on Expression: expression that extracts four-digit year from OrderDate and

                                     Concatenates with name of the month of the Order date.

Expression to display in grp header: Same.

1. 3.       You can use year function to return the four-digit year portion of a Date time value.
2. 4.       You can use the month function to return as integer representing the month of Date time value.
3. 5.       You can use the Month Name function to return the name of Month when passed an integer month value.

Report – 20: (Working With Parameters)

1. 1.        Database: Northwind
2. 2.       Column’s in the report: Stored Procedure (All the Column’s from the Customers table).
3. 3.       Report Name: Rpt_Customers_Country_Prm.
4. 4.       Report Requirements:
   1. 1.       Create a simple tabular report by creating the Stored Procedure.
   2. 2.       Select the Country as a Parameter.
   3. 3.       In the table region show only 4 columns (Company Name, Contact Name, City, country).
   4. 4.       Report heading should be

Example: (Report Of All Customers from France  .

Report – 21:

1. 1.        Database: Northwind
2. 2.       Column’s in the report: Product Name, Supplier, Order date, Quantity, Cost from tables Orders,

                                            Order details, Products and Suppliers.

1. 3.       Report Name: Rpt_Product_Sales_Prm.
2. 4.       Report Requirements:
   1. 1.       Create a simple tabular report using the ad-hoc sql.
   2. 2.       Select the Begin Date and End Date as parameters.

Report – 22:

1. 1.        Database: Northwind
2. 2.       Column’s in the report: Company Name, Contact Name, Contact title, City from Customers table.
3. 3.       Report Name: Rpt_Customer_City_Prm1.
4. 4.       Report Requirements:
   1. 1.       Create a simple tabular report using the ad-hoc sql.
   2. 2.       Select the city as parameter with the default city value (User will type the city).

Report – 23: (Drop-down Parameters)

1. 1.       Report Requirements:
   1. 1.       Use the same above report Rpt_Customers_City_Prm1.
   2. 2.       In this report we need provide the user to select the city from the parameters list.
   3. 3.       Name the report as Rpt_Customer_City_Prm2.
   4. 4.       Report header should contain “Northwind Customers”.
   5. 5.       In the report header it should show (All the Customers from which city/Country based on the user selection).

Report – 24:

1. 1.       Report Requirements:

1. 1.       Use the same above report Rpt_Customers_City_Prm2.
2. 2.       Make this report as Multi-Value parameter report.
3. 3.       Give the user an option to select the parameters as many as they want.
4. 4.       Use the expression on the report header

[= “Report of All Customers from” & join (Parameters! city. Value, “, “)].