SSRS: How do I know where a report item is on the report?

Have you ever got a SSRS message saying something like textbox107 refers to an invalid measure or measure used in gaugepanel7 is not valid, etc? The problem is how do you find that “Textbox107” report item on your SSRS report? After all, you might have lots of text boxes on your report.

Here’s how you can know where a report item is on the report:

Step 1: Go to view > Properties Window

Step 2: Open the report.

Step 3: if you select a report item then it’s going to show the properties of the report item in the properties pane.

SSRS Properties Window Report ItemStep 4: And you can select a report item by it’s name like textbox107 or gaugepanel11 for which you are seeing an error, that way you can find the location of the report item and troubleshoot it!

SSRS Properties Window Report Item list

 Conclusion:

I hope this tip helps you find the report item on your SSRS reports.

How to use TSQL checksum to compare data between two tables?

In any BI project, data validation plays an important part. You want to make sure that the data is right! usually business helps in this validation. As a developer, you might also want to do some preliminary data validation. One of the techniques that I’ve learned recently is to use TSQL checksum to compare data between two tables. In this post , I’ll describe the technique & post a pseudo code.

we’ll create a pseudo code to compare all columns but you should be able to use that to tweak that if you need it.

1) Run checksum(*) on Tables:

On Table1:

select checksum(*) as chktb1 from table1
go

On Table 2:

select checksum(*) as chktb2 from table2
go

At this point, you should get two result sets each populated by checksum values computer over all columns since you passed * in the checksum function.

2) Now let’s join these tables & look at rows w/ different checksum: (in other words, it is going to list all rows that are different between table1 & table2)

select * from
(
select checksum(*) as chktb1 from table1
) as tb1
left join
(
select checksum(*) as chktb2 from table2
) as tb2
on tb1.someid=tb2.someid /* you can have more ids */
where tb1.chktb1 != tb2.chktb2

3) You can add individual column now to see what changed:

select * from
(
select checksum(*) as chktb1, columnname1, columnname2 from table1
) as tb1
left join
(
select checksum(*) as chktb2, columnname1, columnname2 from table2
) as tb2
on tb1.someid=tb2.someid
where tb1.chktb1 != tb2.chktb2

Conclusion:
I hope this helps especially if you don’t have rights to install 3rd party tools on your dev machine.

TSQL Script: How to get list of all tables or view in a database?

I was documenting the list of tables/views in a data mart & staging databases & I found the following scripts useful:

TSQL To get list of all tables:

SELECT * FROM <DatabaseName>.information_schema.tables
where TABLE_TYPE='BASE TABLE'

TSQL To get list of all views:

SELECT * FROM <DatabaseName>.information_schema.tables
where TABLE_TYPE='VIEW'

Alternatives (for SQL 2005 onwards):

SELECT * FROM SYS.TABLES

SQL Spatial Data Types: A Tip to fix STIntersects Method returning NULL values.

I was working on some queries that used SQL Spatial data types and ran into a problem where STIntersects function was returning NULL. I was expecting it to say 1 or 0, but it kept on returning NULL. Here’s how I solved it:

1. On Technet article for STIntersects, it said “This method always returns null if the spatial reference IDs (SRIDs) of the geometry instances do not match.

here’s my pseudo code for you:
- – Function
- – Input Parameters @Area Geometry, @Point1 varchar(10), @Point2 varchar(10)
DECLARE @p point;
SET @p = geometry::STGeomFromText(‘POINT(@Point1 @Point2)’, 0);
- -Note that the 0 in the above line is SRID
return(SELECT @p.STIntersects(@area));

2. So next step was to make sure that the two geometry instances that I was using had same SRID’s.

3. Since I was passing the @area (geometry data type) to the function, I had to check the SRID for @area.
so wrote a single line of code that used the STSrid function.

Select @area.STSrid

That told me that the SRID of the geometric data type was 4xxx

4. So now I modified the original code to change the SRID of @p variable to same as that of @area variable.

So here’s the updated code:
DECLARE @p point;
SET @p = geometry::STGeomFromText(‘POINT(@Point1 @Point2)’, 4xxx);
return(SELECT @p.STIntersects(@area));

5. Now since the SRID of @area and @p matched, it started returning 1 or 0 values as expected.

Time Intelligence in DAX: Last n days

Problem:

How to formulate Last n days in DAX?

Solution:

It can be achieved using the combination of CALCULATE & DATESINPERIOD function.

Formula to sum up values for last 10 days

MeasureName:=CALCULATE(SUM([YourColumnName]),DATESINPERIOD(Calendar[DateKey],TODAY(),-10,day))

Note the use of a calendar table in the DATESINPERIOD. It is pretty common to have a “date” dimension & I’ve used it the DateKey from the dimension as the “date” column which is required by the DATESINPERIOD function.

You can use the above example as a starting point now.

SSRS: Why I prefer shared data source over embedded data source?

In one word. Manageability.

Let me explain. In SSRS, reports need data sources. And data sources can be of two types: 1) Embedded 2) Shared. When a report uses the embedded data source, the data source properties are stored along with the report. If you’ve 5 reports that use the same data source & you decide to go via the embedded data source route then you’ll have 5 data source properties. If you need to change the data source property like change the server name then you’ll have to edit 5 different data source properties. Not efficient!

But if you use the Shared Data Source, then you can update data source properties from one location. Multiple reports can link to the shared data source & if there is a need to change the data source property, then you’ll have to do it only once.

Other benefit, you can take a data source offline if you ever have that need. This will pause all reports that are using this data source & also, stop all report subscriptions.

So, where do you configure embedded/shared data source? When you “Add a data source” to a report, you have the option to choose the data source type:

1.

SSRS Add Data Source2.

SSRS embedded vs shared connection

conclusion:

In this blog post, we saw how shared data source can help you manage your reports efficiently.

SSIS Error on opening a package: The connection “{GUID}” is not found. The error is thrown by connections collection when the specific connection element is not found

In my case, This error came up in SSIS after some copy-pasting happened in our TFS. I tried opening a package & it gave the “The connection {GUID} is not found…” error. So here’s how I was able to solve it:

1. After I got the latest version of files, I navigated to the Integration Services package file on my local machine.

2. Opened the  file in notepad to look at XML.

3. After I was able to see the XML code, I searched for the connection GUID “xyz…” that was showing up in the error

4. Now, once you locate the GUID, figure out the package component that the connection is being used. In my case it was a “Execute SQL Task”

5. I then opened my package and fixed the connection in the Task.

That’s about it for this post. I hope this helps someone out there.

How to strip double quotes while importing data from CSV or TSV using SSMS Import Data wizard OR SSIS?

Long Title! Let me explain. This post will help you solve following problem if you run into it:

1) You are using SSMS Import data wizard to load data from a comma (or tab) separated value (CSV/TSV) file into SQL Server Table & you find that your source data values has double quotes and so you want to strip them before loading to destination table.

2) You are using SSIS to load data from a CSV/TSV file into SQL Server Table & you want to strip the double quotes in source table fields before you load the data to destination table.

Double Quotes CSV file SSMS SSIS LoadSolution:

1. After you’ve configured the Flat File connection. you’ll reach to a point where you’ll see “Flat File Connection Manager” in SSIS. Or in the SQL Server Import & Export data wizard, you’ll see a dialog box to configure flat file connections.

2. In the Text Qualifier, enter

Strip Double Quotes SSMS SSIS Import Wizard3. Make sure to Preview the data to verify that the double quotes around data fields have been trimmed.

4. That’s it! You’ve successfully configured the flat file connection manager to strip double quotes.

Power Pivot DAX: Difference between two DATE values

Requirement:

Take difference between two data values.

Example:

EndDate: 11/20/2013

StartDate: 11/14/2013

DateDifference: 6

Let’s start writing some DAX!

Ok, seems simple, right? Try creating a measure DateDifference:=[EndDate]-[StartDate]

Did that work? NO? Does it return date?

Don’t worry, Here’s the solution. Try creating following DAX Measure:

DateDifference:=1.0*([EndDate]-[StartDate])

That’s about it for this post. Here are some related Posts:
Calculate the difference between two dates in DAX
Q: How can I calculate difference between two dates in DAX (seconds, minutes, hours, days and months)
NETWORKDAYS() Equivalent in PowerPivot?

Power Pivot: Casting DateTime to Date in SQL Server source query

DateTime columns can be tricky for analysis purposes. They don’t work well with Pivot Tables because of the time part, each value seems unique to the Pivot Table & it also creates problems while creating relationships with Date Dimensions. And so, It’s a common need to convert them to just Date before analyzing data & also a common need to create a relationship between the Date (and not DateTime) with Date Dimension Table.

So if it’s possible, I try to do the data type conversion in the source system query. If your source system is SQL Server, you could use this piece of code:

 select [your-fields],cast([DateTime_Col] as date) as Date_Col from TableName
 

Doing the data type conversion upfront in the source system query is a good thing to do. And I hope this is helpful.

Related Posts:
Strange date relationships with #PowerPivot
Date and Date/Time – Sneaky Data Types!