Capital first letter of every word in string Reporting Services

Today I found a very good function in reporting services to Converts the first letter of every word in string to uppercase.
Either Sql server or Reporting Services does not provide any straight forward way to implement the same, we need to write the any user defined function to make the proper case.

But using "strConv" function we can simply achive.

Here is the syntex:

=StrConv(Fields!Name.Value, vbProperCase)

Converts the first letter of every word in string to uppercase.

To ge more details, click on :
http://msdn.microsoft.com/en-us/library/microsoft.visualbasic.strings.strconv.aspx

Difference between XType and Type Column in sysobjects view Sql Server 2005?

Both are same , used to identity the type of objects.

Object type. Can be one of the following object types:

C = CHECK constraint

D = Default or DEFAULT constraint

F = FOREIGN KEY constraint

L = Log

FN = Scalar function

IF = In-lined table-function

P = Stored procedure

PK = PRIMARY KEY constraint (type is K)

RF = Replication filter stored procedure

S = System table

TF = Table function

TR = Trigger

U = User table

UQ = UNIQUE constraint (type is K)

V = View

X = Extended stored procedure



Type column is for backward compatibility.


Randhir

Inline queries in Dataset Reporting Services

Suppose we are planning to change database schema, we are sure that we can use syscomments table to find out that which Stored Procedure may get break after made changes.
But what about those reports having dataset with inline query.

Today I found a very good article on this topic.
(http://feodorgeorgiev.com/blog/2010/07/find-t-sql-in-your-report-server/)

We can find all dataset queries to dbo.Catalog table in ReportServer database.
Here is the query :


WITH XMLNAMESPACES
(
'http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition' AS REP
)

SELECT
c.Path,
c.Name,
DataSetXML.value('@Name','varchar(MAX)') DataSourceName,
DataSetXML.value('REP:Query[1]/REP:CommandText[1]','varchar(MAX)') CommandText
FROM
(SELECT
ItemID,
CAST(CAST(Content AS varbinary(max)) AS xml) ReportXML
FROM
dbo.Catalog
WHERE
Type = 2) ReportXML
CROSS APPLY ReportXML.nodes('//REP:DataSet') DataSetXML (DataSetXML)
INNER JOIN dbo.Catalog c
ON ReportXML.ItemID = c.ItemID
-- Search by part of the query text
WHERE (DataSetXML.value('REP:Query[1]/REP:CommandText[1]','varchar(MAX)')) LIKE '%...enter text here...%'

After running this query , we can check which report may get break.


Randhir

Apply Filter on XML column

If Object_ID ('tempdb..#tblXML') Is Not Null Drop Table #tblXML
Create Table #tblXML (Id Int, colXML XML)

Declare @XML XML
Set @XML =
'
 













   
 
 
 
'

Insert Into #tblXML
Select 1, @XML

Select *, colXML.value('(//@id)[1]','varchar(200)') As [XMLData]
From #tblXML
Where colXML.value('(//@id)[1]','varchar(200)') ='D3AB51B3-B3F8-4D5F-B928-F48EA2996EA6'

Drop Multiple Objects using single statement

Sometime we need to drop multiple database objects, and then we simply write Drop statement for each object.

But SQL Server provides a facility to drop multiple objects using single DROP statement.

Syntax:
DROP ObjectType ObjectName1, ObjectName2, ObjectName….

For Example :
Drop Table Employee, Address, Contact ;

Add multiple projects to a BIDS solution

This is not a good habit to create a solution for each report or project, if we do this, not easy to manage the all reports/projects.

I have learnt a good technique to my last company and today implemented same in new company, while implementing I had faced some problems.
But ultimately get the solution after sometime.
So I want to share that solution to all of others who are looking to implement the same.

After implementing this, BIDS looks like in hierarchy type:

• Solution

   o Project1

          Shared Data Sources

          Reports

   o Project2

         Shared Data Sources

         Reports

  o Project3
        Shared Data Sources
        Reports



Here are the steps:

Personalize the Visual Studio:

1. Open Visual Studio
2. Go to Tool > Option
3. Choose Projects And Solutions
4. Check “Always show solution”
5. Uncheck “Warn user when the project location is not trusted”.

Create a new Blank Solution:
1. On the File menu, select New and then click Project.
2. In the Project type’s pane, select Other Project Types and then select Visual Studio Solutions.
3. In the Templates pane, select Blank Solution.
4. Enter a name for the project.
5. To change the location of the solution directory, choose Browse and specify a new location.
6. Select Add to Source Control if we want to add the solution to a source control database or repository. (will be disable if we don’t have any Source Control installed)
7. Click OK.
After creating an empty solution, we can add new or existing projects and items to the empty solution by using the Add New Item or Add Existing Item command from the Project menu.

Add new Project to solution:
1. On the File menu, select New and then click Project.
2. In the New Project dialog box, select one of the Project types from the left pane.
3. Select one of the project Templates from the right pane.
4. Enter a Name for the new project.
5. In Solution: choose “Add to solution”.
Or
Simply right click on Solution > Properties > New Projects>Choose Template > Enter a Name > Ok.

So in this way, we can manager our all reports and projects under one solution. And also we can now add complete solution to any source control.

It’s pretty quite simple, I hope we enjoy it. !!!!!!!