Find Start Date, End Date and Number of Days in each from the Date Range given through SQL

One of my friend asked me how to get the Start Date, End Date and Number of Days in each from the Date Range given through SQL. To answer to his question I wrote the below SQL Query. Hope this is an useful one to share with you all.

DECLARE @StartDate DATETIME

DECLARE @EndDate DATETIME

SET @StartDate = ’06-01-2012′
SET @EndDate = ’06-01-2013′

IF @StartDate > @EndDate
BEGIN
PRINT ‘Friend! Please check your parameters’
END ;

WITH DateRange
AS (
SELECT @StartDate DateInfo

UNION ALL

SELECT dateadd ( MM, 1 , DateInfo ) DateInfo
FROM DateRange
WHERE DateInfo < @EndDate
)
SELECT
convert(varchar, DATEADD ( dd, – ( DAY( DateInfo ) – 1), DateInfo ), 101) as FirstDay,
convert(varchar, DATEADD( dd , – ( DAY (DATEADD ( mm, 1 , DateInfo ))), DATEADD ( mm, 1 , DateInfo)), 101) AS LastDay,
DAY(DATEADD(DD,-1,DATEADD(MM ,1,DATEADD(DD, 1 – DAY(DateInfo), DateInfo)))) as NoOfDays
FROM DateRange

Days

To find the number of weeks days in a month you can use the below query

This query referred from sqlservercentral

DECLARE @CurrentDate DATETIME

,@StartOfMonth DATETIME
,@EndofMonth DATETIME;

SELECT @CurrentDate = GETDATE();
SET @StartOfMonth = ’02-01-2013′;
SET @EndofMonth = ’02-28-2013′;

WITH MaxNumberOfDaysInAnyMonth (N) AS
(
SELECT 0
UNION ALL SELECT 1
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 4
UNION ALL SELECT 5
UNION ALL SELECT 6
UNION ALL SELECT 7
UNION ALL SELECT 8
UNION ALL SELECT 9
UNION ALL SELECT 10
UNION ALL SELECT 11
UNION ALL SELECT 12
UNION ALL SELECT 13
UNION ALL SELECT 14
UNION ALL SELECT 15
UNION ALL SELECT 16
UNION ALL SELECT 17
UNION ALL SELECT 18
UNION ALL SELECT 19
UNION ALL SELECT 20
UNION ALL SELECT 21
UNION ALL SELECT 22
UNION ALL SELECT 23
UNION ALL SELECT 24
UNION ALL SELECT 25
UNION ALL SELECT 26
UNION ALL SELECT 27
UNION ALL SELECT 28
UNION ALL SELECT 29
UNION ALL SELECT 30
),
DaysOfCurrentMonth AS
(
SELECT DATEADD(DD, N , @StartOfMonth ) DY
FROM MaxNumberOfDaysInAnyMonth
WHERE N <= DATEDIFF(DD,@StartOfMonth,@EndofMonth)
)
SELECT DATENAME(DW,DY) NameOfTheDay , COUNT(*) CountOfDays
FROM DaysOfCurrentMonth
GROUP BY DATENAME(DW,DY);

Daysinmonth

Advertisements

SSRS –Join Multiple SharePoint List Columns Using LookupSet Function

Problem:

How to create SSRS reports by joining multiple SharePoint List columns with foreign key relationship

Solution:

There are three lookup functions in SQL Server 2008 R2 Reporting Services:

  1. Lookup
  2. LookupSet
  3. MultiLookup

In this article I will explain the functionality of LookupSet Function and provide a simple report to show how it is used.

LookupSet:

Returns the set of matching values for the specified name from a dataset that contains name/value pairs.

Syntax:

Lookup(source_expression, destination_expression, result_expression, dataset)

Parameters:

source_expression

(Variant) An expression that is evaluated in the current scope and that specifies the name or key to look up. For example, =Fields!ID.Value.

destination_expression

(Variant) An expression that is evaluated for each row in a dataset and that specifies the name or key to match on. For example, =Fields!CustomerID.Value.

result_expression

(Variant) An expression that is evaluated for the row in the dataset where source_expression = destination_expression, and that specifies the value to retrieve. For example, =Fields!PhoneNumber.Value.

dataset

A constant that specifies the name of a dataset in the report. For example, “ContactInformation”.

Return:

Returns a VariantArray, or Nothing if there is no match.

Example

  • Create two Data Sets as shown below(here “ds_Orders” is the main dataset and “ds_Products” is the master dataset):

SSRS1

  • Create a report and drag and drop the necessary columns from the “ds_Orders” dataset as shown below

SSRS2

  • In the fourth column of this report, we have to bring the data from the “ds_Products” dataset with lookup option. To do this write the below expression on the fourth column

=Join(LookupSet(Fields!Product_Code.Value,Fields!Code.Value,Fields!Title.Value,”ds_Products”), “,”)

  • Now your Report will look something similar to this

SSRS3

  • That’s it you are done. Run the report you will see the below output

SSRS4

 

Reference:

http://msdn.microsoft.com/en-us/library/ee210576(SQL.105).aspx

Hope you liked this article  🙂

Load unique values from a SharePoint List column on a SSRS Parameter

Business Case 

When you create Dataset using SharePoint List as datasource the list columns includes duplicate records. There is no direct way to remove duplicates and get only the distinct values.

Solution

  • Open Report builder and create a datasource and name it as ‘SPListDS’
  • Go to Credential tab and select ‘Use current windows user’ option. And click Ok.
  • Right click on Datasets on Report Data pane and select Add Dataset. Name it as ‘SharePointListDetails’.
  • Select ‘Use a dataset embedded in my report’ option. Select the SPListDS, which you create at the previous step as the datasource. Use the Query Designer to select whatever the list you want to include in the dataset. Click Ok. I selected the column I have to use in my parameter from SharePoint List for my dataset.
  • Right click on the Parameters at the Report data pane and select Add parameter. Name it as ‘DummyParameter’ select ‘Allow multiple values’ check box, select ‘Hidden’ option. Go to Available Values tab, select Get values from query tab. Select dataset, value field and label field from the drop downs.
  • Go to Default values tab; select ‘Get values from a query option’ and select dataset and value field as follows.
  • Next you need to add the Custom code that will get the duplicate values and return only the distinct values.
  • Right click at the report body and select report properties. Go to Code tab. Add the following code to Custom code box. (Thanks to Mohamed for this code 🙂 )

Public Shared Function RemoveDuplicates(parameter As Parameter) As String()

Dim items As Object() = parameter.Value

System.Array.Sort(items)

Dim k As Integer = 0

For i As Integer = 0 To items.Length – 1

If i > 0 AndAlso items(i).Equals(items(i – 1)) Then

Continue For

End If

items(k) = items(i)

k += 1

Next

Dim unique As [String]() = New [String](k – 1) {}

System.Array.Copy(items, 0, unique, 0, k)

Return unique

End Function

  • And click Ok.
  • Next you are going to add the distinct parameter dropdown.
  • Add a new parameter and name it as ‘OriginalParameter’, go to Available values tab and select Specify values option. Click on the Add button. Add the following expression to Label and value fields.

=Code.RemoveDuplicates(Parameters!DummyParameter)

That’s it done

How to restrict user to select only 3 values from the multi value parameter – SSRS

Requirement

How to restrict user to select only 3 values from the multi value parameter – SSRS?

Solution

To take the count of the number of items selected in your parameter use the below expression in your hidden parameter

=Parameters!ParameterName.count

Based on the count in the hidden parameter you can check the condition and pop up the error message. Follow this article written by me

http://www.innovativerndlab.blogspot.com/2009/04/displaying-pop-up-message-in-ssrs-2005.html

Online BI Generator

Nice one

Online BI Generator – http://www.leganto.com/

SharePoint 2010 : Configure RDLC report with ReportViewer

Visit the below link :

Configure RDLC report with ReportViewer in SharePoint 2010

Conditional formatting with Reporting Services

Anyone who has created reports with SQL Server Reporting Services has tweaked the presentation of a table or matrix to enhance its visual appeal. However in most cases, you do this once and for all, to produce a static result. The row or table cell is set to fixed and final presentation, with background colors, font attributes and borders defined once and for all. This is great when you want a uniform presentation, but in these days of information overload, you often need to allow certain pieces of data to stand out and to draw the reader’s attention. Even better, you want to automate this process, and have certain values draw attention to themselves when specific thresholds are met. What you need is conditional formatting – just like Access and Excel have had for years.

Fortunately Reporting Services allows you to vary almost any aspect of a report’s presentation based on the underlying data. So when a sales figure shoots up – or down – you can have the report draw attention to this fact by:

* changing the colour cell backgrounds;
* changing font colour and attributes;
* adding or altering cell borders;

Indeed, you can combine most of these techniques in many ways to ensure that your reports deliver analysis as well as data to their users.

Just to be clear, I am not pretending that this is some wildly useful but undiscovered feature of Reporting Services. Far from it. I am merely attempting to draw your attention to useful ways of enhancing your reports and reaching further levels of automation in report creation using Reporting Services expressions.

Example conditional formatting

A report example

Firstly, we are going to need some data. I suggest using the Adventureworks database to return a few rows about sales people and their sales figures, as this will give us a set of data which can then be used to highlight the best- (and the worst-) performing sales staff.

So here is the stored procedure that will return data to Reporting Services. If you have not already done so, I suggest that you download the AdventureWorks database from the CodePlex web site, install it, and then create the following procedure in the AdventureWorks database:

CREATE PROCEDURE [dbo].[pr_GetSalesData]
AS
DECLARE @Topseller INT
DECLARE @Bottomseller INT

SELECT @Topseller = MAX(Bonus) FROM Sales.SalesPerson
SELECT @Bottomseller = MIN(Bonus) FROM Sales.SalesPerson

SELECT
CT.FirstName + N’ ‘ + CT.LastName AS PersonName,
SP.Bonus,
SP.SalesQuota,
SP.CommissionPct * SP.SalesYTD AS Commission,
SP.SalesYTD,
SP.SalesLastYear,
SP.SalesYTD – SP.SalesLastYear AS SalesIncrease,
CASE
WHEN (SP.CommissionPct * SP.SalesYTD) > 75000 THEN 5
WHEN (SP.CommissionPct * SP.SalesYTD) BETWEEN 50001 AND 75000 THEN 4
WHEN (SP.CommissionPct * SP.SalesYTD) BETWEEN 25001 AND 50000 THEN 3
WHEN (SP.CommissionPct * SP.SalesYTD) BETWEEN 1 AND 25000 THEN 2
ELSE 1
END AS CommissionFlag
FROM
Sales.SalesPerson SP
INNER JOIN HumanResources.Employee EMP ON SP.SalesPersonID = EMP.EmployeeID
INNER JOIN Person.Contact CT ON EMP.ContactID = CT.ContactID AND EMP.ContactID = CT.ContactID

The first thing to note here is that I am Using T-SQL to perform all calculations, rather than use Reporting Services. This is a personal choice, but I consider that this approach is fundamental to efficient and easy conditional formatting with Reporting Services.

You will then need to create a new Reporting Services project. Then create a new data source which connects to the AdventureWorks database. Finally add a new report (Right-click Reports, then Add/New Item/Report), and create a new dataset for the report, being careful to use pr_GetSalesData as the query string, and “Stored Procedure” as the command type.

Then switch to the layout tab, and drag a table object from the toolbox onto the report body. Add another four columns (making seven in all), and drag the data fields, one by one, into the table details row. You should end up with something like Person Name,Sales Last Year,Sales YTD,Sales Increase,Sales Quota, Bonus, Commission

Applying basic conditional formatting

Suppose that you wish for a series of colors to be applied to the cell background of the “Commission” column, to draw attention to the lowest and highest commissions, as well as indicating the lower and higher of the intermediate commissions.

For argument’s sake, I suggest the following requirement:

Color Thresholds Amount Color
0 red
1 – 25,000 yellow
25,001 – 50,000 light green
50,001 – 75,000 dark green
75,001 + blue

The commission column will change color according to where in the range of data the figure lies.

At this point, we need to do some strategic thinking, as we are trying to do two things here:

1. Apply a set of conditions, and not just a simple IF..ELSE.
2. Test a range of figures, and not just carry out a simple comparison,

Now we could use the IIF function that we used above have negative numbers appear in red. However using more than two IIF functions can get very difficult to get right, so I suggest using the Reporting Services SWITCH function to apply the color coding. However, while this solves the first problem, it does not help with the second problem at all. Therefore I think that using T-SQL to apply range-based conditional analysis is a much better idea than attempting this with Reporting Services.

CASE
WHEN (SP.CommissionPct * SP.SalesYTD) > 75000 THEN 5
WHEN (SP.CommissionPct * SP.SalesYTD) BETWEEN 50001 AND 75000 THEN 4
WHEN (SP.CommissionPct * SP.SalesYTD) BETWEEN 25001 AND 50000 THEN 3
WHEN (SP.CommissionPct * SP.SalesYTD) BETWEEN 1 AND 25000 THEN 2
ELSE 1
END AS CommissionFlag

The above snippet will calculate the commission for each salesperson, and categories it according to the amount of commission received. This is then returned as a figure between 1 and 5.

You will need to add the new field that the stored procedure is returning to your reporting services dataset. To do this:

1. Click on the “Data” tab of your report.
2. Click the “Edit Selected Dataset” ellipsis on the tab toolbar.
3. The “Dataset” dialog will appear. Click the “Fields” tab.
4. Scroll to the bottom of the list of fields and click in a blank row.
5. Add “CommissionFlag” as field name to both the “Field Name” and “Value” cells.
6. Select “Type” as “DatabaseField”

You can then Click “OK” and right=click on “Report Datasets” in the “Datasets” pane, and select “Refresh” to make the new field appear in the fields collection.

Now that you have fed the analytical flag for commissions through to the report, you can use it to apply some conditional formatting.

1. Click on the table cell for the detail row in the “Commission” column.
2. Display the properties window (pressing F4 will do this if it is not already visible).
3. Click the popup box to the right of the “Background Color” attribute, and select “Expression”. The “Edit Expression” dialog will be displayed.
4. Enter the following code: =switch(Fields!CommissionFlag.Value=1,”Red”,Fields!CommissionFlag.Value=2,”Yellow”,Fields!CommissionFlag.Value=3,”LawnGreen”,Fields!CommissionFlag.Value=4,”DarkGreen”,Fields!CommissionFlag.Value=5,”Blue”)
5. Click “OK”
6. Preview the report.

That’s it Enjoy