Sample Query for Unpivoting data:

Table Structure:
------------------------------------------------------------------------------------------------------
AccountNo AccountKey Datekey PeriodID Segment 1 Segment 2 Segment 3 Debit Credit
------------------------------------------------------------------------------------------------------
1                1                  234           12          456          567            346          3423   44353
-----------------------------------------------------------------------------------------------------

SELECT AccountNo, AccountKey, Datekey, PeriodID, Debit, Credit
FROM
(
  SELECT AccountKey, Datekey, PeriodID, Credit,
    [Segment 1] As Segment1, [Segment 2] As Segment2, [Segment 3] As Segment3
  FROM dbo.vw_FactAccount
) MyTable
UNPIVOT
(Debit FOR Accountno IN (Segment1, Segment2, Segment3))AS MyUnPivot



Result

----------------------------------------------------------------------------------------------
AccountNo     AccountKey  DateKey    PeriodID   Debit     Credit
-----------------------------------------------------------------------------------------------
 Segment1    1                       234           12              3423   44353
Segment2     1                       234           12              3423   44353
Segment3     1                       234           12              3423   44353
------------------------------------------------------------------------------------------

Batch Script to take SQL Server DB Bakup

@ECHO OFF
SETLOCAL
DEL D:\MercuryDBBackup\DBBACKUP\Mercury.bak
echo Deleting Mercury.bak finished at %date% %time%
DEL D:\MercuryDBBackup\DBBACKUP\SalesDW_Mercury.bak
echo Deleting SalesDW_Mercury.bak finished at %date% %time%
DEL D:\MercuryDBBackup\DBBACKUP\Mercury_Scope.bak
echo Deleting Mercury_Scope.bak finished at %date% %time%

REM Build a list of databases to backup
SET DBList=%SystemDrive%\SQLDBList.txt
SqlCmd -E -S 192.168.50.52\MERCURY -h-1 -W -Q "SET NoCount ON; SELECT Name FROM master.dbo.sysDatabases WHERE [Name] IN ('Mercury','SalesDW_Mercury','Mercury_Scope')" > "%DBList%"
REM Backup each database, prepending the date to the filename
FOR /F %%I IN (%DBList%) DO (
ECHO Backing up database: %%I
SqlCmd -E -S 192.168.50.52\MERCURY -Q "BACKUP DATABASE [%%I] TO Disk='D:\MercuryDBBackup\DBBACKUP\%%I.bak'"
ECHO.
)
REM Clean up the temp file
IF EXIST "%DBList%" DEL /F /Q "%DBList%"

echo Completed Successfully at %date% %time%>>_date_.txt

PPS Open Sub report in new window

<%@ Page language="c#" Codebehind="Details.aspx.cs" AutoEventWireup="false" Inherits="seminarRx03.Details" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN" >
<HTML>
 <HEAD>
  <title>Details</title>
  <meta content="Microsoft Visual Studio 7.0" name="GENERATOR">
  <meta content="C#" name="CODE_LANGUAGE">
  <meta content="JavaScript" name="vs_defaultClientScript">
  <meta content="http://schemas.microsoft.com/intellisense/ie5" name="vs_targetSchema">
 </HEAD>
 <body MS_POSITIONING="GridLayout">
  <form id="Details" method="post" runat="server">
   &nbsp;
   <asp:Button id="btnRegistration" style="Z-INDEX: 101; LEFT: 611px; POSITION: absolute; TOP: 155px" runat="server" Text="Registration " Width="180px" Height="28px"></asp:Button>
  </form>
 </body>
</HTML>

Performance Point Server Error: :Unable to view data across Domain"

While developing any reports or charts using Pivot report or Pivot Chart in PPS 2007, we often come across the below type of error
Performance Point Server Error: :Unable to view data across Domain...."

To fix this issue we need to customize our Security setting in IE browser.

1. Go to Internet Options --> Security tab
2. Click 'Custom Level' button and enable 'Access Data Across Domain' under Miscellneous part.

If you still find difficult to view data. install below exe's in your system:

1. SQLServer2005_ASOLEDB9.exe
2. msxml6.exe

Handling Null Date in SSIS Derived Column

To Replace Null Value with Current Date:

ISNULL((DT_DBTIMESTAMP)SUBSTRING((DT_STR,30,1252)modifiedon,1,10)) ? (DT_DBTIMESTAMP)SUBSTRING((DT_STR,30,1252)GETDATE(),1,10) : (DT_DBTIMESTAMP)SUBSTRING((DT_STR,30,1252)modifiedon,1,10)

The below conversion replace null value with 'Null' instead of allowing column to insert default '1900-01-01....' value.

ISNULL((DT_DBTIMESTAMP)SUBSTRING((DT_STR,30,1252)PromisedShipDate,1,10)) ? NULL(DT_DATE) : (DT_DBTIMESTAMP)SUBSTRING((DT_STR,30,1252)PromisedShipDate,1,10)

Configure connection String for Excel Source in Expression property

Configure connection String for Excel Source in Expression property

Step 1: Create variable containg excel file name.

Step 2: Go to Excel Data Source Properties, and in expression select Connection string and give expression as like below:
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Datafiles\\TestDrive\\Pending\\" + @[User::FileName] + ";Extended Properties=\"EXCEL 8.0;HDR=YES\";"

To get Sales of Current Year, Previous Year, etc using Lag anf Lead functions

Current Year Sales:

SELECT [Measures].[Sales (000's)] on ROws,
{CROSSJOIN([Date].[Fiscal Months].[Fiscal Months].Allmembers,STRTOMEMBER("[Date].[Financial Period].[Year].&["+ CASE WHEN
CINT(FORMAT(NOW(),"MM")) >= 4 THEN FORMAT(NOW(),"yyyy") ELSE  CSTR(CINT(FORMAT(NOW(),"yyyy"))-1) END +"]"))}
on Columns
FROM [Sales]

Result

Previous year Sales

SELECT [Measures].[Sales (000's)] on ROws,
{CROSSJOIN([Date].[Fiscal Months].[Fiscal Months].Allmembers,STRTOMEMBER("[Date].[Financial Period].[Year].&["+ CASE WHEN
CINT(FORMAT(NOW(),"MM")) >= 4 THEN FORMAT(NOW(),"yyyy") ELSE  CSTR(CINT(FORMAT(NOW(),"yyyy"))-1) END +"]").lag(1))}on Columns
FROM [Sales]

SELECT [Measures].[Sales (000's)] on ROws,
{CROSSJOIN([Date].[Fiscal Months].[Fiscal Months].Allmembers,STRTOMEMBER("[Date].[Financial Period].[Year].&["+ CASE WHEN
CINT(FORMAT(NOW(),"MM")) >= 4 THEN FORMAT(NOW(),"yyyy") ELSE  CSTR(CINT(FORMAT(NOW(),"yyyy"))-1) END +"]").lead(-1))}
on Columns
FROM [Sales]

Result