Archive for May, 2009

I copied and pasted an SSIS 2005 dtsx package into a different folder then opened it in the MS Visual Studio/BIDS 2005. I then changed the connection managers to point to different data source/destination. The package was executed successfully, but it ended up updating data in the previous data source/destination. This happened because the package was still using the previous global configuration file. To change it, go to Control Flow of the package, right click anywhere empty then choose "Package Configuration.." option. Point the global configuration file to the correct one. Executed the package again and it updated the correct data in the data mart. Note: if the package is still pointing to incorrect data source, check the XML configuration file to ensure that it is in fact pointing to the correct data source; if not change it.

Read Full Post »

I encountered this error thrown by Microsoft Visual Studio while trying to switch from Control Flow to Data Flow while running one of the SSIS dtsx packages on a client data mart. I clicked OK to close the error window and switched back to Control Flow to see the progress of the ETL. I saw that the last step has turned green in the Control Flow but most transforms were still yellow in the Data Flow. The Data Flow task was yellow in the Control Flow and the 2 tasks after the Data Flow task didn’t turn yellow or green but the last task was already green. How odd!. The package said "Running" but the row count remained the same in the data flow. It looked like Visual Studio had freezed and therefore failed to refresh .`
I checked the number of rows in the Error bucket table and the destination table and discovered that there were about 8 rows in the Error bucket table and over 2,5 millions of rows in the destination table, which were expected.  I concluded that most likely the ETL process has finished. I then killed the Visual Studio process to stop the package. Check the data again and it looks fine.

Read Full Post »

Let say I have the following table that has 2 table groupings and has the ability to drill down.

The formula to get the Total Variance value:
              Total Variance = Total Dip Variance for MOBILETANK + Total Dip Variance for TANK
,which is  -9,186 + (-4097) = -13,283. This is the result that I would like to achieve.

I used the following expression to get the total dip variance value for each type, which produced correct values.

=IIF(Fields!Type.Value = "TANK"
        , Sum(Fields!Last_Day_Dip_Reading_Level1.Value) – (Sum(Fields!Previous_Day_Dip_Reading.Value) + Sum(Fields!Deliveries.Value) – Sum(Fields!Transfers.Value) – Sum(Fields!Issues.Value))
        , Sum(Fields!Last_Day_Dip_Reading_Level1.Value) – (Sum(Fields!Previous_Day_Dip_Reading.Value) + 
Sum(Fields!Transfers.Value) – Sum(Fields!Issues.Value))

I then used the above expression to get the Total Variance value but it produced incorrect value. The reason behind this is that the Total Variance value is placed under table footer, which adds the whole items within the table. I also tried using calculated field on dataset; however, you cannot use any aggregrate functions such as Sum() on calculated field and hence cannot be used in this scenario. The other option that I could think of would be using VB functions in the custom code under Report Properties.

To resolve this:
I decided to put the above expression in custom code as follows:

— Declare global variables —
Public Shared Dim TotalVariance As Decimal
Public Shared Dim TankVariance As Decimal
Public Shared Dim  MobileTankVariance As Decimal

Public Shared Function AddVariance(ByVal Type As String, ByVal LastDayDipValue As Decimal, ByVal  PreviousDayDipValue As Decimal, DeliveriesValue As Decimal, TransfersValue As Decimal, IssuesValue As Decimal) As Decimal
     If Type = "TANK" Then
                    TankVariance =  LastDayDipValue – (PreviousDayDipValue + DeliveriesValue – TransfersValue – IssuesValue)
        Return TankVariance
           Else If Type = "MOBILETANK" Then
                      MobileTankVariance =  LastDayDipValue – (PreviousDayDipValue + TransfersValue – IssuesValue)
        Return MobileTankVariance
           End If
End Function

Public Shared  Function GetTotalVariance()
    TotalVariance =  TankVariance + MobileTankVariance
    Return TotalVariance
End Function

The Total Dip Variance for each Type field will have the following expression:
=Code.AddVariance(Fields!Type.Value, Sum(Fields!Last_Day_Dip_Reading_Level1.Value), Sum(Fields!Previous_Day_Dip_Reading.Value), Sum(Fields!Deliveries.Value), Sum(Fields!Transfers.Value), Sum(Fields!Issues.Value))

I then copied and pasted the same table just to display the Total Variance value on the report and the Total Variance field will have the following expression. Remember to hide table headings and remove table groupings as necessary.


Read Full Post »

I came across this error very often especially when deploying reports into the client environment. The error happens when one or more report parameters have default values that are empty.

To resolve this, unhide them and the report should prompt the user to select the report parameters before the report can be executed.

Read Full Post »

I am using a dundas chart with 3 series of data. When I click on the first series, it should pass the report parameters for the first series to the relevant subreport.
To do that, right click on the chart then choose Properties. Select "Data" tab then select the first series. Click the "Edit" icon on top then select "Actions" tab. You will see that the "Jump to report:" option is ticked. Click on "Parameters.." button. Change the value of the multi-value report parameter to use the following generic format:
               Join(Parameters!Field.Value, "\#\")

Read Full Post »

I came across this error while drilling through a report on client environment. My subreport failed to run when a dundas chart was clicked. Executing the relevant stored procedure producing this error. Running another SP for other report worked perfectly fine. I then compared the two SPs to see what the differences were and discovered that I have used 2 temporary tables in the report SP that produced this error.
This error happens when a temporary table is used and the TEMPDB database has different collation to the database.
To get a list of collation lists on SQL Server:


* FROM ::fn_helpcollations()  WHERE [name] LIKE ‘SQL_Latin%’;


Go to http://www.databasejournal.com/features/mssql/article.php/3302341/SQL-Server-and-Collation.htm to find out more about collation in SQL Server.

To fix this error, I explicitly forced the collation on the CREATE TABLE and comparison statement on the field(s) of CHAR/VARCHAR/NVARCHAR type in my query. Alternatively, change the default collation on the SQL Server, which requires master database to be rebuilt (http://www.sp-configure.com/change-sql-server-default-collation/).
CREATE TABLE #CostCodes (     
           CostCode          NVARCHAR(50)         COLLATE SQL_Latin1_General_CP1_CI_AS           NOT NULL )



   CASE WHEN c.CostCode IS NOT NULL THEN e.ActivityEventID

            ELSE NULL


FROM dm_date d

LEFT OUTER JOIN Core.fact_Equipment_events e ON e.DateId = d.date_key

AND e.SiteId = @SiteId

AND e.MeasureID = @Measure_CostRef

LEFT OUTER JOIN #CostCodes c ON c.CostCode COLLATE SQL_Latin1_General_CP1_CI_AS = e.TextValue COLLATE SQL_Latin1_General_CP1_CI_AS

WHERE d.date between @StartDate AND @EndDate


Read Full Post »

Say I have the following resultset and the Delivery No. depends on the Fuel Docket No. and the date the fuel was delivered. The Delivery No would be an incremental number for each delivery that is listed in the report. As you can see the row number next to the first Fuel Docket No is 35, and there were only 9 dockets in that month. Adding the following into the query will not produce the correct number.
                 row_number() OVER ( ORDER BY [Fuel Delivery Date]) AS [Delivery No]
Resolution: Only select fields that relate to the Fuel Delivery (i.e. Fuel Delivery Date, Fuel Docket No, Litres Received) from the query then add the row_number() OVER ( ORDER BY [Fuel Delivery Date]) AS [Delivery No] as [Delivery No] field. Remember also to filter the query so that any rows with NULL Fuel Delivery Date are not included. Place the resultset into a table variable then do a LEFT OUTER JOIN back to the main query.

— Get the Delivery No for each Fuel Delivery Date and Docket No.

INSERT INTO @ResultTemp3

SELECT DISTINCT row_number() OVER ( ORDER BY [Fuel Delivery Date]) AS [Delivery No]

,[Fuel Delivery Date]

,[Fuel Docket No]

,[Litres Received]

FROM @ResultTemp t

WHERE [Fuel Delivery Date] IS NOT NULL


— Final Result —-



,@TotOpeningDip AS [Opening Dip]

,t3.[Delivery No]

,t.[Fuel Delivery Date]

,t.[Fuel Docket No]

,t.[Litres Received]

,t.[Equipment Energy Category]

,t.[Report Category]

,t.[Total Litres Issued]

,t.[Litres On-Site]

,t.[Litres Off-Site]


,t.[Purchase Name]


,t.[Litres Sold]

,@TotClosingDip AS [Closing Dip]

FROM @ResultTemp t

LEFT JOIN @ResultTemp3 t3 ON t3.[Fuel Delivery Date] = t.[Fuel Delivery Date] AND t3.[Fuel Docket No] = t.[Fuel Docket No]

ORDER BY [Fuel Docket No] desc



Read Full Post »

Say I have the following result set from the execution of my temporay table #temp and I would like to get the SUM of the opening dip with the following condition:

 "If the dip reading for the night shift (N/S) is available then this should be used, else the dip reading for the day shift (D/S) should be used."

The answer to that is to count how many dip readings have been read on that day and if the count is more than one, then it means that  there have been more than one shift exist. If the count is more than 1 then do SUM of the dip when the shift is N/S, else take the SUM of the dip for D/S. Note the usage of OVER (PARTITION BY) clause used, which is very important to get the correct result. Basically it takes how many times the dip reading have been recorded per site AND per day.





           ,[opening dip]

           ,( CASE WHEN (COUNT([opening dip]) OVER (PARTITION BY Siteid, Date)) > 1 THEN

                     ( SELECT SUM([opening dip])

                       FROM #temp t1

                       WHERE t1.siteid = t.siteid

                       AND t1.date = t.date

                       AND t1.shift = t.shift

                       AND t1.shift = ‘N/S’


             ELSE ( SELECT SUM([opening dip])

                       FROM #temp t1

                       WHERE t1.siteid = t.siteid

                       AND t1.date = t.date

                       AND t1.shift = ‘D/S’

                    )  END

            ) AS [Total Opening Dip]


          #temp t



The result of the above query:

Read Full Post »

I came across this error while deploying a report that contains a dundas chart and a report link to another report to a client environment. The report link is using "Jump to Report" hyperlink type. The main report has 3 report parameters, while the other report where the link jumps to, has 5 report parameters. Hence, there are only 3 reports parameters being passed down from the main report to this report. When the main report is run and the link is clicked, SSRS will prompt the user to choose the 2 report parameters before the report can be run. This is what I expect to see and this is what I get on my development and testing environment at my office. However, on my client environment, what I got was "Cost Group parameter is missing a value" error. SSRS did not prompt the user for the parameters but directly executed the report.

It took me a while to understand why this has occurred. I first thought "no big deal, the default value of the report parameter is missing. Should be easy to fix". I checked the setting of my report parameters on the report manager to make sure that the default values were entered correctly and that the hidden/visible property was right. OK, everything looked good. Viewed the report, bang! Still getting the same error. Checked SSRS log and couldn’t find any clues that were useful. Checked my client SSRS configuration settings and it looked fine. I started to run out of ideas. I then checked the software side by comparing the SSRS version of my development and test environment with my client’s. My client report server is SSRS 2005 SP2. My development report server is SSRS 2005 SP2. My test report server is SSRS 2005 SP3. The error only occurrs on my client report server. So why isn’t it working? I googled search on how to check the version of SSRS. To check SSRS report server version, open IE and type in http://%5Byour server]/reportserver. For example: http://localhost/reportserver. You will find the report version number appearing as 9.0…. at the bottom of the web page. I then found out that even though my client report server has been patched with SP2, its version number is lower than my development report server. My development report server appears to have been patched with more hot fixes than the client’s.  Hmmm.. it might worth a try to patch my client report server with SP3. Last hope! Yay! my report worked perfectly fine on the client’s environment now. I concluded that this error must have been a bug in SSRS 2005 that got fixed in the SP2 hot fixes or SP3.

Read Full Post »

%d bloggers like this: