SSRS - Unchecked some values by Default from Multiple Value Parameter

SSRS - Unchecked some values by Default from Multiple Value Parameter.

There was one requirement in SSRS that Client wants to select some values in multiple value parameter by default but they need the functionality to see all values in same parameter. For example please have look in below screen shot..

here client wants to unchecked Afghanistan and Albania (marked in Red) by default but they want to see these values in parameter so that in future they can run report for Afghanistan and Albania by selecting these values. For these such type of functionality I tried a scenario as below:

Step-1
Make connection with AdventureWorks Database..

Step-2
Add Dataset1 in SSRS with SQL - select * from Person.CountryRegion.

Step-3
Add Dataset2 in SSRS with SQL - select * from Person.CountryRegion
where CountryRegionCode not in ('AF','AL','AQ') -- it will filter some countries like Afghanistan, Albania.

Step-4
Add Parameter "Counrty" select allow multiple values:

Step-5
Go to Available Values and choose Dataset1 for values from Query:

Step-6
Go to Default Values and Choose DATASET2 for values from Query:

Click OK..

Run the Report and you will get parameter as below:

Thanks .. 

Instant SQL Formatter

Instant SQL Formatter

Below is the link of Instant SQL Formatter 

We can Format our SQL Code or we can download this too.

http://www.dpriver.com/pp/sqlformat.htm

SQL SERVER – Query to Find Column From All Tables of Database

Below script will return all the tables containing specific column along with their schema name.

USE Master

GO

SELECT t.name AS table_name,

SCHEMA_NAME(schema_id) AS schema_name,

c.name AS column_name

FROM sys.tables AS t

INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID

WHERE c.name LIKE '%NAME%'

ORDER BY schema_name, table_name

Style Arguments for the CONVERT Datetime Function

Style Arguments for the CONVERT Function

Style	Associated standard	Input/Output ( 3 ) format	date	time(n)	datetime2(n)	datetimeoffset(n)
0 or 100 (1, 2)	Default	mon dd yyyy hh:miAM (or PM)	Jan 1 2001	12:20PM	Jan 1 2001 12:20PM	Jan 1 2001 12:20PM -08:00
101	U.S.	mm/dd/yyyy	1/1/2001	-	1/1/2001	1/1/2001
102	ANSI	yy.mm.dd	2001.01.01	-	2001.01.01	2001.01.01
103	British/French	dd/mm/yy	1/1/2001	-	1/1/2001	1/1/2001
104	German	dd.mm.yy	01.01.2001	-	01.01.2001	01.01.2001
105	Italian	dd-mm-yy	1/1/2001	-	1/1/2001	1/1/2001
106 (1)	-	dd mon yy	1-Jan-01	-	1-Jan-01	1-Jan-01
107 (1)	-	Mon dd, yy	1-Jan-01	-	1-Jan-01	1-Jan-01
108	-	hh:mi:ss	-	12:20:20	12:20:20	12:20:20
9 or 109 (1, 2)	Default + milliseconds	mon dd yyyy hh:mi:ss:mmmAM (or PM)	Jan 1 2001	12:20:20.1234567AM	Jan 1 2001 12:20:20.1234567PM	Jan 1 2001 12:20:20:1230000PM -08:00
110	United States	mm-dd-yy	1/1/2001	-	1/1/2001	1/1/2001
111	JAPAN	yy/mm/dd	1/1/2001	-	1/1/2001	1/1/2001
112	ISO	yymmdd	20010101	-	20010101	20010101
13 or 113 (1, 2)	Europe default + milliseconds	dd mon yyyy hh:mi:ss:mmm(24h)	1-Jan-01	20:20.1	20:20.1	01 Jan 2001 12:20:20:1230000 -08:00
114	-	hh:mi:ss:mmm(24h)	-	20:20.1	20:20.1	12:20:20:1230000 -08:00
20 or 120 (2)	ODBC canonical	yyyy-mm-dd hh:mi:ss(24h)	1/1/2001	12:20:20	1/1/2001 12:20	2001-01-01 12:20:20 -08:00
21 or 121 (2)	ODBC canonical (with milliseconds)	yyyy-mm-dd hh:mi:ss.mmm(24h)	1/1/2001	20:20.1	20:20.1	2001-01-01 12:20:20.1230000 -08:00
126 (4)	ISO8601	yyyy-mm-ddThh:mi:ss.mmm (no spaces)	1/1/2001	20:20.1	2001-01-01T 12:20:20.1234567	2001-01-01T 12:20:20.1234567
127(6, 7)	ISO8601 with time zone Z.	yyyy-mm-ddThh:mi:ss.mmmZ	1/1/2001	12:20:20.1234567Z	2001-01-01T 12:20:20.1234567Z	2001-01-01T20:20:20.1230000Z
		(no spaces)
130 (1, 2)	Hijri (5)	dd mon yyyy hh:mi:ss:mmmAM	1-Jan-01	12:20:20.1230000PM	01 Jan 2001 12:20:20.1230000PM	1 Jan 2001 12:20:20:1230000PM -08:00
131 (2)	Hijri (5)	dd/mm/yy hh:mi:ss:mmmAM	1/1/2001	12:20:20.1230000PM	01/01/2001 12:20:20.1230000PM	01/01/2001 12:20:20.1230000PM -08:00

Crystal Report - Cascading Parameters.

Below are the steps of cascading parameters in crystal report..

1. Right-click Parameter Fields and select New.
2. Name the parameter and change List of Values to Dynamic.
3. In the Value column, click 'Click here to add item'. Select your state field then in the Parameters column click 'Click to create parameter'.
4. In the Value column in the second row, click 'Click here to add item'. Select your county field then in the Parameters column click 'Click to create parameter'.
5. In the third row select your city field and create the parameter.
6. Change any Value Options (you can do this for each of the parameters by selecting them from their rows and changing their options. Notice that as you select a value the Value Options changes from Level 1, Level 2 and Level 3.
7. Click OK. You now have three parameters that all start with the name you designated to the whole dynamic parameter. They come across as three separate fields but they act as one and will work in a cascading effect.

Note: As per my knowledge you can not add one more cascading parameter into existing cascading effect once it is created. You have to delete the existing one and create the whole parameter once again include the new cascading parameter into it.

Crystal Report - How to add page break.

Below are the steps from which we can add page break in crystal reports:

• Step 1: Include dataset or database expert into your crystal report
• Step2: In the outside surface of the report area right click and goto report and goto group expert
• Step3: add the which field you include the group that time the bottom of the window options will appear
• Step 4: click the option and agian goto 2nd tab of the option.
• Step 5: finally check(check=true) check box of Repeat Group header on each page other check box or put uncheck

Note: Crystal Reports automatically add the sheet while exporting in excel if data is more than 65000 rows. 

SQL SERVER – Query to Find Column From All Tables of Database

Following script which will go return all the tables containing specific column along with their schema name.

SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%StateProvinceID%'
ORDER BY schema_name, table_name;

SSRS Page Break while exporting in Excel..

SSRS Page Break while exporting in Excel..

we can use below expression in Group Expression of SSRS Tablix.
group expression =Ceiling((RowNumber(Nothing)) / Nth row)
replace Nth with any number like 65000 ..

Suppose you want to print fix number of rows per page in excel file then we can use above expression.
For ex- 2000 rows per page..
Use like this - Ceiling((RowNumber(Nothing)) / 2000).
Add page break in Group.

Every page will contain only 2000 per excel sheet ..

Enjoy . . :)

New Features in SSRS 2008 R2

New Features in SSRS 2008 R2

Microsoft has released MS SQL Server 2008 R2 recently. Now Business Intelligence (BI) developers want to learn what is new in Microsoft SQL Server 2008 R2 Reporting Services features.


Here is a short list of new features in Reporting Services 2008 R2.
Shared Data Sets
Report Part Gallery
Sparklines, Data Bars and Indicators
Aggregation on Aggregates
Rendering Features
Browser Support

For more go to :--
http://www.kodyaz.com/articles/what-is-new-in-sql-server-2008-r2-reporting-services.aspx

HOW MUCH WOMEN LOVE THEIR HUSBANDS

There was a group of women gathered at a seminar on how to live in a loving relationship with your husband.
*
The women were asked, 'How many of you love your husbands?'
*
All the women raised their hands.
*
Then they were asked, 'When was the last time you told your husband you loved him?'
Some women answered today, some yesterday, some didn't remember..
*
The women were then told to take their cell phones and send the following text to their respective husband: I love you, sweetheart.
Then the women were told to exchange phones and read the responding text messages.
*
Here are some of the replies:
*
1. Eh, mother of my children, are you sick?
*
2. What now? Did you crash the car again?
*
3. I don't understand what you mean?
*
4. What did you do now? I won't forgive you this time!!!
*
5. ?!?
*
6. Don't beat about the bush, just tell me how much you need?
*
7. Am I dreaming? ???????
*
8. If you don't tell me who this message is actually for, you will die today...!!!
*
9. I asked you not to drink any more!!
*
and the best one
*
10. Who is this?

LOL 

SSRS 2012 - Excel Renderer for Microsoft Excel 2007-2010 and Microsoft Excel 2003

Excel Renderer for Microsoft Excel 2007-2010 and Microsoft Excel 2003

The Reporting Services Excel rendering extension, new in SQL Server 2012, renders a report as an Excel document that is compatible with Microsoft Excel 2007-2010 as well as Microsoft Excel 2003 with the Microsoft Office Compatibility Pack for Word, Excel, and PowerPoint installed. The format is Office Open XML and the file extension is XLSX.

This Excel-rendering extension removes limitations of the earlier version, compatible with Excel 2003. The following lists the improvement in the rendering extension:

• Maximum rows per worksheet is 1,048,576.
• Maximum columns per worksheet is 16,384.
• Number of colors allowed in a worksheet is approximately 16 million (24-bit color).
• ZIP compression provides smaller files sizes.

Data Alerts - New Feature in SSRS 2012

Data Alerts

Reporting Services data alerts are a data-driven alerting solution that informs you about changes in report data that are of interest to you, and at a relevant time.

Imagine a sales report that contains information such as year-to-day sales, average monthly sales, and sales targets for sales persons by territory. As a sales person, you would like to know how you are doing. By creating a data alert on the sales report, you can receive messages whenever year-to-date sales exceed or fall below a specified value or contain data that you consider noteworthy. Rules in the data alert definition specify the data values to trigger the data alert. By using AND and OR operators in the rules you can combine many clauses into complex rules that define precisely the report data values you want to be notified of.

In some cases, the presence of any data in a report is of interest. For example, a report that lists cancelled orders becomes interesting when the first cancellation occurs. You can create data alerts on reports in which the presence of data, rather than specific data values, is important.

Data alerts messages are sent by email. Depending on the importance of the information, you can choose to send alert messages more or less frequently and only when results change. You can specify multiple recipients for the alert messages and this way keep others informed and enhance efficiency and collaboration. If you want to send alert messages immediately, you can run data alerts directly instead of waiting for them to run automatically at the scheduled time.

The following summarizes the key areas of data alerting:

• Define and save data alert definitions—User views a report, adds a data alert, creates rules that identify interesting data values, defines recurrence patterns for sending the alert, and specifies the recipients of the data alert message.
• Run data alert definitions—Alerting service runs data alert definitions at a scheduled time, retrieves report data, and triggers data alerts based on the rules in the alert definition.
• Deliver data alert messages to recipients—Alerting service creates an alert instance and sends data alert messages by email to recipients.

Data alerts provide the following tools for creating and managing data alert instances and definitions:

• Data Alert Designer—Users create and edit data alert definitions.
• Data Alert Manager for users—Users view information about their data alerts, delete their data alert definitions, or open alert definitions for editing.
• Data Alert Manager for alerting administrators—Alerting administrators view a list of data alerts created by all users on the SharePoint site and delete alerts.