SSRS PROBLEMS while Server Connecting

Q)

Hi all ; I'm stuck for a long days in SSRS , the probleme is when I enter http://servername/reportserver I receive a pop up where i should put username and password, I click on ok nothi!

Ans) 

1.Open Internet Explorer (IE9) and from the tools menu, choose Internet Options: 

2. From the Security Tab, choose Local intranet and click on Sites:
3. Click on Advanced button:
4. Type in localhost in Add this website text field and click on Add button (Un-tick the box Require server verification (https:) for all sites in this zone):

All done. If you open your IE again and type in Reporting Services URL, you will no longer be prompted to enter login details.

SSRS Formatting Expressions

 All formatting options described will be available for download on a .pdf in the next few weeks at the new site.

After working for years with other reporting products I made the move to SSRS and found that one of the more difficult learning curves was to find formatting codes I began with what I thought was a rather simplistic report that contained a simple tabular report with only a few columns. I had no problem with grouping my data, creating the expressions for the footers and headers, or even creating default values for parameters, but hit a wall when I began trying to format my dates and numbers. After finally finding the Format property I was at a loss of what to put in it. The below are the formatting codes that I have compiled since that day:

Date/Time Formatting Codes

 

Date Format Codes	Description	Example
d	Short date	3/12/2006
D	Long date	Monday, June 1,2006
t	Short time	7:12 PM
T	Long time	6:15:55 AM
f	Long date/short time	Monday, June 1,2006 7:12 PM
F	Long date/long time
g	Short date/short time	3/12/200619:12
G	Short date/long time	3/12/200619:12
M or m	Month and day only	5-Nov
Y or y	Month and year only	October, 2002
Yyyy	Four digit year	2008
yy	Two digit year	8
MMMM	Full month name	April
MMM	Three character month	Jan
MM	One or two digit month	4
Dddd	Full day name	Friday
Ddd	Three character abbrev.	Sat
dd	Two digit day always	4
d	One or two digit day	4
hh	Two digit hour always 12 hr clk	8
h	One digit hour 12 hr clk	8
HH	Two digit hour always 24 hr clk	20
H	One or two digit hour 24 hr clk	8
Mm	Two digit minutes	24
ss	Two digit seconds	24
tt	Two character AM or PM	AM
t	One character AM or PM 24 hr clk	A

 

NUMERIC FORMAT CODES

Numeric Format Codes	Description	Example
#	Optional place holder
0	Required number placehoolder
%	Percentage .95 becomes 95%
C	Two digit numbers & local currency char	$12,345.95(For US currency)
D or D2	Decimal value, optionally can specify precision	56.95
E or E12	Specific notation, optionally can specify precision	1.23E+08
P or P1	Percentage, optionally can specify precision	87.4
N or N#	Numeric, including thousands comma separator prec.Optional	1,000

 

Now that you have these formatting codes where exactly do they go?  From within Business Intelligence Development Studio you can right click on the textbox or group and choose Propertiesand from the properties window select the Format tab :

 

or click on the object and browse to the format property in the Properties pane

and enter the formatting code.

 

 

The results for numeric formatting are:

Format	Value	Formatted Value
N0	10000.00	10,000
N1	10000.00	10,000.0
C	10000.00	$10,000.00
D2	10000.00	10000
P1	10000.00	1,000,000.0%
E	10000.00	1.000000E+004

 

The results for date/time formatting are:

Format	Value	Formatted Value
d	2/3/2010 12:30.000AM	2/3/2010 12:30:00AM
D	2/3/2010 12:30.000AM	Wednesday, February03, 2010
t	2/3/2010 12:30.000AM	12:30 AM
T	2/3/2010 12:30.000AM	12:30:00 AM
f	2/3/2010 12:30.000AM	Wednesday, February03, 2010 12:30:00 AM
F	2/3/2010 12:30.000AM	Wednesday, February03, 2010 12:30 AM
g	2/3/2010 12:30.000AM	2/3/2010 12:30 AM
G	2/3/2010 12:30.000AM	2/3/2010 12:30:00AM
M or m	2/3/2010 12:30.000AM	February 03
Y or y	2/3/2010 12:30.000AM	February, 2010
Yyyy	2/3/2010 12:30.000AM	2010

This post covered simplistic out of the box formatting and will be followed by more complex formatting of numeric, data/time, and string types, until then you can also refer to MSDN on line documentation:

http://msdn.microsoft.com/en-us/library/ms157406.aspx

 

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, “, “)].