Custom Code for Dividing Numbers in SSRS

Hi,

Use this custom code when you perform division of 2 numbers. This is essential as when you to try to divide 2 numbers & if the denominator is 0, during run-time the textbox will show error. Below is the code that you can use for eliminating this :

Custom Code:
Public Function SafeDivide(ByVal Numerator, ByVal Denominator)
If Microsoft.VisualBasic.IsNumeric(Numerator) And Microsoft.VisualBasic.IsNumeric(Denominator) Then
If Denominator = 0 Then
  SafeDivide = 0
Else: SafeDivide = Numerator/Denominator
End If
Else
SafeDivide = "N/A"
End If
End Function








                                                                                                                                                                              
write the code function Name in required field like
 =Code.SafeDivide(Fields!Name1.Value,Fields!Name2.Value)
Now if you see the result, You will get exact result. Problem Solved!!!!!!!!

Page Header need to be changed based on Group and Page Header should be appear if data is processed more than one page

While I am working with Reports today I faced one problem,” Page Header is not displaying dynamically based on group, if data is processed more than one page”.
For this I did some R&D and finally got solution for this
Below is the solution
Step 1:
Custom Code:
Dim lastHeader As String = Nothing
Function GetHeader(ByVal currentfooter As String) As String
If Not currentHeader = Nothing Then
lastHeader = currentHeader
End If
Return lastHeader
End Function

Step 2:
Create a group on which your report needs to be display on Page header and Make it asVisble/Hidden ( Depend upon your requirement)

If it is hidden ,then apply hidden property like below

Step 3:
=Code.GetHeader(ReportItems!Fields.Value)



Now you can see the Result . Report Header will change Dynamically according to group and If group data is More than one page in all pages report header will appear

Different format types of DATE and TIME in SQL SERVER 2008

Hi…
Today i will discuss about the displaying DATETIME in different formats in SQL SERVER 2008
While storing DATE and TIME  sometimes client may ask like(dd-mm-yyyy) format  other than default format(yyyy-mm-dd)  . At that time we can use CONVERT function.
First we start with the conversion options available for sql datetime formats with century (YYYY or CCYY format). Subtracting 100 from the Style (format) number will transform dates without century (YY). For example Style 103 is with century, Style 3 is without century. The default Style values – Style 0 or 100, 9 or 109, 13 or 113, 20 or 120, and 21 or 121 – always return the century (yyyy) format.
– Microsoft SQL Server T-SQL date and datetime formats
– Date time formats – mssql datetime 
– MSSQL getdate returns current system date and time in standard internal format

SELECT convert(varchar, getdate(), 100) – mon dd yyyy hh:mm AM (or PM)

                                        – Oct  2 2008 11:01AM          

SELECT convert(varchar, getdate(), 101) – mm/dd/yyyy - 10/02/2008                  

SELECT convert(varchar, getdate(), 102) – yyyy.mm.dd – 2008.10.02           

SELECT convert(varchar, getdate(), 103) – dd/mm/yyyy

SELECT convert(varchar, getdate(), 104) – dd.mm.yyyy

SELECT convert(varchar, getdate(), 105) – dd-mm-yyyy

SELECT convert(varchar, getdate(), 106) – dd mon yyyy

SELECT convert(varchar, getdate(), 107) – mon dd, yyyy

SELECT convert(varchar, getdate(), 108) – hh:mm:ss

SELECT convert(varchar, getdate(), 109) – mon dd yyyy hh:mm:ss:mmmAM (or PM)

                                        – Oct  2 2008 11:02:44:013AM   

SELECT convert(varchar, getdate(), 110) – mm-dd-yyyy

SELECT convert(varchar, getdate(), 111) – yyyy/mm/dd

SELECT convert(varchar, getdate(), 112) – yyyymmdd

SELECT convert(varchar, getdate(), 113) – dd mon yyyy hh:mm:ss:mmm

                                        – 02 Oct 2008 11:02:07:577     

SELECT convert(varchar, getdate(), 114) – hh:mm:ss:mmm(24h)

SELECT convert(varchar, getdate(), 120) – yyyy-mm-dd hh:mm:ss(24h)

SELECT convert(varchar, getdate(), 121) – yyyy-mm-dd hh:mm:ss.mmm

SELECT convert(varchar, getdate(), 126) – yyyy-mm-ddThh:mm:ss.mmm

                                        – 2008-10-02T10:52:47.513

– SQL create different date styles with t-sql string functions

SELECT replace(convert(varchar, getdate(), 111), ‘/’, ‘ ‘) – yyyy mm dd

SELECT convert(varchar(7), getdate(), 126)                 – yyyy-mm

SELECT right(convert(varchar, getdate(), 106), 8)          – mon yyyy

            Interview Point of view having knowledge up-to here is enough.
If anybody asks how to format datetime with functions then we have to write the follwoing query.
– SQL Server date formatting function – convert datetime to string
————
– SQL datetime functions
– SQL Server date formats
– T-SQL convert dates
– Formatting dates sql server

CREATE FUNCTION dbo.fnFormatDate (@Datetime DATETIME, @FormatMask VARCHAR(32))

RETURNS VARCHAR(32)

AS

BEGIN

    DECLARE @StringDate VARCHAR(32)

    SET @StringDate = @FormatMask

    IF (CHARINDEX (‘YYYY’,@StringDate) > 0)

       SET @StringDate = REPLACE(@StringDate, ‘YYYY’,

                         DATENAME(YY, @Datetime))

    IF (CHARINDEX (‘YY’,@StringDate) > 0)

       SET @StringDate = REPLACE(@StringDate, ‘YY’,

                         RIGHT(DATENAME(YY, @Datetime),2))

    IF (CHARINDEX (‘Month’,@StringDate) > 0)

       SET @StringDate = REPLACE(@StringDate, ‘Month’,

                         DATENAME(MM, @Datetime))

    IF (CHARINDEX (‘MON’,@StringDate COLLATE SQL_Latin1_General_CP1_CS_AS)>0)

       SET @StringDate = REPLACE(@StringDate, ‘MON’,

                         LEFT(UPPER(DATENAME(MM, @Datetime)),3))

    IF (CHARINDEX (‘Mon’,@StringDate) > 0)

       SET @StringDate = REPLACE(@StringDate, ‘Mon’,

                                     LEFT(DATENAME(MM, @Datetime),3))

    IF (CHARINDEX (‘MM’,@StringDate) > 0)

       SET @StringDate = REPLACE(@StringDate, ‘MM’,

                  RIGHT(’0′+CONVERT(VARCHAR,DATEPART(MM, @Datetime)),2))

    IF (CHARINDEX (‘M’,@StringDate) > 0)

       SET @StringDate = REPLACE(@StringDate, ‘M’,

                         CONVERT(VARCHAR,DATEPART(MM, @Datetime)))

    IF (CHARINDEX (‘DD’,@StringDate) > 0)

       SET @StringDate = REPLACE(@StringDate, ‘DD’,

                         RIGHT(’0′+DATENAME(DD, @Datetime),2))

    IF (CHARINDEX (‘D’,@StringDate) > 0)

       SET @StringDate = REPLACE(@StringDate, ‘D’,

                                     DATENAME(DD, @Datetime))   

RETURN @StringDate

END

GO

SQL Server Interview Questions

1.   What is DATABASE?
Collection of interrelated data is called database.
Database is two types.
1. Database (On-line Transaction process database) :- running the business
2. DataWareHouse (On –Line Analytical Process Database):- Analyzing the business .

• Databases are created to keep details about day-to-day transactions.

2.What is DBMS (Database Management System)?
DBMS is a specification set of features.
Any software which implements specifications of DBMS is called DBMS software.
Ex: Sql Server, Oracle
Using DBMS software users can create and manage database.
USER
↓
DBMS Software
↓
Database
                                                                                                                                                                3.Different types of keys in RDBMS?

• Primary key:-

     Primary key is used to uniquely identify the records.

In the Below table Primary key is “Empid”

EX:

Empid	Salary	Name
100	2000	A
101	5000	B
102	4000	C

• Composite key:-

     If a Combination of uniquely identify the records present in the table, then those combination keys is called “composite key”.

Student ID(SID)	Course ID(CID)	Student name(SNAME)	Course name(CNAME)	Date of Completion(DOC)
100	10	A	C	26/10
100	11	A	C++	31/11
101	11	B	C	26/10

In the Above table SID, CID are called as Combination key

• Candidate key:-

     Attributes which are eligible for primary keys are called as candidate keys.

Vehicle no	Engine no	Vehicle Type	Price
AP09 2345	12345	NANO	1,00,000
AP09 9988	56789	TAVERA	3,25,000
AP12 7777	12567	SUMO	2,00,000

In the above table Vehicle no and Engine No is called as Candidate keys.
If we take one as primary key (vehicle no), other will automatically considered as Secondary key (Engine No).
A table may contain many candidate keys but only one primary key is present.
A primary key can be candidate key but a candidate key cannot be primary key.

• Foreign key:-

     Foreign key is used to establish relationship between two tables.
4.What are the versions of Sql Server ?
Sql Server 6.5
Sql Server 7.0
Sql server 8.0(2000)
Sql Server 9.0(2005)
Sql Server 10.0(2008)
5.What are the default databases installed in the server?
Master: The Master database holds information for all databases located on the SQL Server instance and is the glue that holds the engine together. Because SQL Server cannot start without a functioning master database, you must administer this database with care.
Model:-The model is essentially a template database used in the creation of any new user database created in the instance.
MSDb:- The msdb database stores information regarding database backups, SQL Agent information, DTS packages, SQL Server jobs, and some replication information such as for log shipping.
TempDb:- The tempdb holds temporary objects such as global and local temporary tables and stored procedures.
Once the server is closed again Temporary database is again recreated freshly.
6.What is a Query?
Instruction, command, request given to perform some operations.
7.What are the types of Sub languages in SQL SERVER?
Depends on operations performed by user, SQL is categorized into following Sub languages

1. DDL( Data Definition language)
2. DML(Data manipulation Language)
3. DRL(Data Retrieval Language)
4. TCL(Transaction Control language)
5. DCL(Data Control Language)

DDL:-
Data definition is also called Metadata
DDL Commands:
          CREATE: To Create Data Definition
          ALTER: To Modify Data Definition
          DROP: - To Delete Data Definition
          TRUNCATE: - Deletes all the data in the table. It makes table as Empty set.
Data definition can exist without data but data never exist without data definition.
DML Commands
          INSERT:- To insert Data/Record
          UPDATE:- To Modify data
          DELETE: – To delete Record(s).
In Sql Server 2008 one more command is introduced.
          MERGE: - It is Combination of insert & Update. It also called as UPSERT.
DRL Commands
          SELECT:- It is used to retrieve data from database
TCL Commands
          COMMIT:- To save Transactions
          ROLLBACK:-To cancel transactions
DCL Commands
          GRANT:- To give the permissions.
          REVOKE:- To Take back the permissions

Sql server Reprting Services interview Questions

1.What is difference b/w matrix, tabular and list ?
Tablular report: A tabular report is the most basic type of report. Each column corresponds to a column selected from the database.
Matrix report: A matrix (cross-product) report is a cross-tabulation of four groups of data:
a. One group of data is displayed across the page.
b. One group of data is displayed down the page.
c. One group of data is the cross-product, which determines all possible locations where the across and down data relate and places a cell in those locations.
d. One group of data is displayed as the “filler” of the cells. Martix reports can be considered more of a Pivot table.
List:A list data region is a data-bound report item that contains a single column of multi-instance data on a report. A list can be used for free-form reports or in conjunction with other data regions. You can define lists that contain any number of report items. A list can be nested within another list to provide multiple groupings of data.
To read on more on List data region click the below link. this one is official microsoft link
http://msdn.microsoft.com/en-us/library/ms251742.aspx
2.What is shared datasource and embedded datasource?
in SSRS 2008 mainly two types of Data connections or datasources available.
1.Shared datasource
2.Embedded datasource
Shared Datasource:A shared data source is defined as a separate item and can be used by multiple reports.
Embedded Datasource:An embedded data source is defined in a report definition and used only by that report
For more details click the below link
http://msdn.microsoft.com/en-us/library/ms159165.aspx
http://msdn.microsoft.com/en-us/library/dd239381.aspx
3.What is Running Value?
Returns a running value of all non-null numericvalues specified by the expression, evaluated for the given scope.RunningValue is an SSRS expression that aggregates values at runtime.
This function takes 3 parameters
Syntax: Runningvalue(expression,function, scope)
Ex: -=runningvalue(Fields!Dailysales.value, sum,”table1_details group”)
4.What is report viewer controller?
with the report viewer we can see list of available reports . If we click on single report it will open from report server.
5.What is documentation map?
Documentation map provides set of navigation links to report items in a rendered report 6.What is data driven subscriptions?
Reporting Services supports 2 types of subscriptions

1. Standard subscriptions:  created and managed by individual user. It consists of static data
2. Data-driven Subscriptions : data can be change at run time.

These are two types

• E-mail subscriptions
• File share subscriptions

7.What is report builder?
Report Builder provides individuals with a quick and easy way to create attractive and ad-hoc reports.
8.What is default size memory on report server?
Report and model size is based on the size of report definaition and report model files(.smdl). however the report server doesnot limit the size or model of the report that we publish in report server. But ASP.NET imposes the size of the model or report that posted to the reportsever. By default server size 4MB.
9.How will integrate dot net dll into reports?
open the Report properties dialog box from the the visual studio designer and select refernces tab.
Browse and select the custom code .dll file and preview it , we will get error. Take the .dll file and copy in
C:ProgramFiles/Microsoft visual studio 9.0/Common/IDE/PrivateAssemblies
10.How do you provide data source security?
Goto reports in the reports select particular report and click on properties there we can select security and assign roles.
11.Types of Reports?
Types of Reports:

• Parameterized reports

     A parameterized report uses input values to complete the report. With the parameterized reports , report can be varied depending upon the input variable we assign. Parameterized paramters used in Drill through Reports.

• Linked Reports

A linked report is a report server item that provides an access point to an existing report.
A linked report is derived from an existing report and retains the original report definition.

• Snapshot reports

A Report snapshot is a report that contains layout information and query results that were retrieved at a specific point of time.

• Cached Reports

A cached report is a saved copy of a processed report. Cached reports are used to improve performance by reducing the number of processing requests.

• Adhoc Reports

Adhoc Reports are useful for end users to develop and design report in their own format.

• Click through Reports

A clickthrough report is a report that displays related data from a report model when you click the interactive data contained within your model-based report. These reports are generated by the report server based on the information contained within the report model. The person who created the model determines which fields are interactive and which fields are returned when a clickthrough report is opened. These field settings cannot be changed in the report authoring tools.

• Drill Down reports

Drilldown reports initially hide complexity and enable the user to toggle conditionally hidden report items to control how much detail data they want to see. Drilldown reports must retrieve all possible data that can be shown in the report.

• Drill Through Reports

Drillthrough reports are standard reports that are accessed through a hyperlink on a text box in the original report. Drillthrough reports work with a main report and are the target of a drillthrough action for a report item such as placeholder text or a chart. The main report displays summary information, for example in a matrix or chart. Actions defined in the matrix or chart provide drillthrough links to reports that display greater details based on the aggregate in the main report. Drillthrough reports can be filtered by parameters, but they do not have to be. Drillthrough reports differ from subreports in that the report does not display within the original report, but opens separately. They differ from clickthrough reports in that they are not autogenerated from the data source, but are instead custom reports that are saved on the report server. They differ from drilldown reports in that they retrieve the report data only for the specified parameters or for the dataset query.

• Sub Reports

A subreport is a report that displays another report inside the body of a main report. Conceptually, a subreport is similar to a frame in a Web page. It is used to embed a report within a report. Any report can be used as a subreport. The subreport can use different data sources than the main report. The report that the subreport displays is stored on a report server, usually in the same folder as the parent report. You can set up the parent report to pass parameters to the subreport.
Although a subreport can be repeated within data regions using a parameter to filter data in each instance of the subreport, subreports are typically used with a main report as a briefing book or as a container for a collection of related reports
For more information click on the following link
http://msdn.microsoft.com/en-us/library/bb630404.aspx#Click
12.What are all the ways we can provide security for report?
we can provide security in 3 types
1. Datasource Level
2. Dataset Level
3. Report Level
13.What are the gauges available in reports?
there are two types of Gauges in SSRS 2008
1.Radial Guages
2.Linear Guages
14.What are the charts available in reports?
They are 8 different charts are available
They are

1. column
2. Line
3. Shape
4. Bar
5. Area
6. Range
7. Scatter
8. polar

15.What are the sections available in charts?
3 sections available in charts

1. Category field( x-axis)
2. Data field (y-axis)
3. Series field

Differences Between SSRS2005 and SSRS 2008

• SSRS 2008 has HTML compatibility( It can read HTML data fields)
• Interface upgrade–easier to use and looks better.
• SSRS 2008 now allows you to format text. that is, Think of a large block of text, such as “Disclaimer”. In SSRS 2008, we can selectively BOLD or ITALIAZE words. In SSRS 2005 we can only format entire Block.
• SSRS 2008 no longer requires IIS. This makes deployment much easier.
• Rendering Reports into Word Format.

RunningValue Function in SQL Server Reportings(MSBI)

RunningValue Function
Returns a running aggregate of the specified expression.

Syntax

RunningValue(Expression, Function, Scope)

Parameters

Expression

(Data type is determined by the aggregate function specified in Function.) The expression on which to perform the aggregation. The expression cannot contain aggregate functions.

Function

(Enum) The name of the aggregate function to apply to the expression. This function cannot be RunningValue, RowNumber, or Aggregate.

Scope

(String) The name of a dataset, grouping, or data region that contains the report items to which to apply the aggregate function. If a dataset is specified, the running value is not reset throughout the entire dataset. If a grouping is specified, the running value is reset when the group expression changes. If a data region is specified, the running value is reset for each new instance of the data region. For more information about the Scope parameter, see Aggregate Functions.

Return Type

Determined by the aggregate function that is specified in the Function parameter.

Remarks

Restrictions for RunningValue are also determined by the aggregate function specified in the Function parameter. For more information, see the topic for the aggregate function that you are interested in using.

Example

The following code example provides a running sum of the cost field in the outermost data region:

 RunningValue(Fields!Toal.Value, Sum, “ORDERBY”)

     Where ORDERBY is the Group Name

String Concatenation using RunningValue Function

Sometimes, we need to aggregate strings in SSRS designer. In situations, when we are using Report Model as data source this is very much needed.
There are many Aggregation functions available in SSRS, but to implement string concatenation/aggregation, we can’t directly use any of the provided function directly.  But, by using SSRS custom code and RunningValue aggregate function we can achieve this as shown below.
For reference the same report is attached herewith. You can find the report below.
Step-1 Create a dataset.
Note:-  This example is just created to simulate the situation.

DECLARE @tab TABLE ( id INT, name NVARCHAR(100), phoneNumber NVARCHAR(100))

INSERT INTO @tab (id,name,phonenumber)

VALUES

 (1,'Sachin','9824512345')

, (1,'Sachin','26612345' )

, (2,'Rahul','99922299922')

, (2,'Rahul','1234567890')

, (2,'Rahul','234561234')




SELECT a.id ,a.name ,a.phoneNumber FROM @tab a

Step-2 Create a report with RowGrouping on ID.
Step-3 Add custom code

Dim Num As Integer = 0

 Dim tempstr As String = ""

Function AggregateString(ID as Integer, PhoneNumber as String)

If ID = Num Then

tempstr = tempstr + "," + PhoneNumber

Else

Num = ID

tempstr = PhoneNumber

End If

Return tempStr

End Function

Step-4 Add colum with following expression =RunningValue(Code.AggregateString(Fields!id.Value,Fields!phonenumber.Value),Max,"id")

Step-5 Run Report.
Here, we can see the concatenated rows as shown in below.

SQL Server – Normalization Forms With Examples

Normalization Forms with Examples
According to the database rules, below are the lists of Normalization forms
1.    1NF (Eliminating Repeating Groups)
•    Make a separate table for each set of related attributes and give each table a primary key. In simple words I can say it as
•    There are no repeating or duplicate fields.
•    Each cell contains only a single value.
•      Each record is unique and identified by primary key
Let’s see the example below :
Prior to Normalization
Item    Colors         Price    Tax
Pen       red, blue        2.0        0.20
Scale     red, yellow     2.0       0.20
Pen        red, blue       2.0        0.20
Bag      blue, black    150.00    7.80
This table is not in first normal form because:
•      A. There are multiple fields in color lab.
•      B. Records are repeating (Duplicate records) or no primary key.
First Normal Form (1NF)
Item    Colors    Price    Tax
Pen         red         2.0          0.20
Pen        blue        2.0         0.20
Scale       red       2.0         0.20
Scale      yellow    2.0        0.20
Bag        blue      150.00    7.80
Bag       black      150.00    7.80
•    This table is now in first normal form.
2.    2 NF (Eliminating Redundant Data)
•    If an attribute is dependent on only part of the multivalued key, then remove it to a separate table. In simple words,
•    It should meet all the requirements of the first normal form.
.  It should remove subsets of data that apply to multiple rows of a table and place them in separate tables.
.  It create relationships between these new tables and their predecessors through the use of foreign keys.
The First Normal form deals with the atomicity whereas the Second Normal Form deals with the relationship between the composite key columns and non-key columns. To achieve the next progressive level your table should satisfy the requirement of First Normal Form then move towards the Second Normal Form.
Let’s introduce a Review table as an example :
Item    Colors    Price    Tax
Pen        red           2.0        0.20
Pen        blue          2.0        0.20
Scale       red         2.0         0.20
Scale       yellow    2.0        0.20
Bag         blue       150.00    7.80
Bag        black    150.00    7.80
Table is not in Second Normal Form because the price and tax depends on the item, but not color.
Item    Colors
Pen    red
Pen    blue
Scale    red
Scale    yellow
Bag    blue
Bag    black
Item    Price    Tax
Pen        2.0         0.20
Scale      2.0        0.20
Bag     150.00     7.80
Tables are now in Second Normal Form.
3.    3NF (eliminate Columns not dependent on the Key)
•    If attributes do not contribute description of the key, then remove it to the separate table. All the attributes must be directly dependent on the primary key. In simple words,
•    It should meet all the requirements of the second normal form.
•     It should remove columns that are not dependent upon the primary key.
In the Third Normal Form all columns depend upon the primary key. When one column depends upon the other column, tables break the rule and turn into the dependency on the primary key.


Item    Colors
Pen        red
Pen      blue
Scale    red
Scale    yellow
Bag      blue
Bag      black
Item    Price    Tax
Pen        2.0    0.20
Scale       2.0    0.20
Bag       150.00    7.80
Tables are not in Second Normal Form because tax depends on price, not item.
Item      Colors
Pen         red
Pen         blue
Scale      red
Scale      yellow
Bag       blue
Bag       black
Item       Price
Pen          2.0
Scale       2.0
Bag          150.00
Price       Tax
2.0           0.20
150.00     7.80
Tables are now in Third Normal Form.