SurendraThotaMSBIProfessional

I just want to show you how to  design your Report if the Requirement is to show last 5 weeks data from the day the user selected  Date ( default is current day ). Step 1 : Create Parameter  SelectDate  with Data Type  Date/Time  and Its Default value =today() Step 2 : Create Paratemeter StartDate with DataType  Date/Time and Make it Hidden and Its Default value is  DateAdd ( "d" ,- 35 , Fields ! BirthDate . Value )   Step 3 :  Create  Dataset  with Fallowing Query i am just taking sample columns select convert ( varchar ( 10 ), DATEADD ( DAY , 1 - DATEPART ( WEEKDAY , DateColumn ), DateColumn ), 103 ) as [week] , Measure from TableName where DateColumn Between @startDate and @SelectDate   Step 4 : In the graph select  Week In Category Groups   and Measure column in Measure values section and  Make Sure In Series Properties The Value Filed should have SUM aggreative function on Measure column . 

In Some Cases we need to show the Data from 2 different Datasets . For Example if we take a Telecome example  If we want to see the [Number Calls] and [Total duration] of those  calls  to Diifferent countries  On montly basis and Yearly Basis side by side . To Achieve This we need to Use LookUp function . For the Above example we can create 2 different data sets one for Montly Result based on country code and another Yearly Result based on country code . We can join 2 data sets with the Help of LookUp Function . See the below sample =Lookup(Fields!CountryCode.Value,Fields!CountryCode.Value,Fields!Number_of_calls.Value, "YearDataSet" ) MonthDataset is the Primary Dataset for the table so to get the yearly data we need to use Lookup function on CountryCode column .

Case :In Most of the businesses we will always compare the Measure value with the Same datetime Measure value of the Previous weekday .For Example :   If we have Measure called NumberOFCalls   and we have value at 10 AM of Wednesday   ie 19 th Feb 2014 Then we need to compare this measure value with same time of previous week that is   10 am of 12 th Feb 2014. The Business will compare reference value , If the difference between Current and Refernce value is more than 20% they will take some action. Solution : when we want to   calculate the reference value based on same weekday data of previous week   in SSAS Cube   we need to Create a Named Sets for Each Weekday. The First and very important thing is dimensional Model . You need to have the fallowing attributes in you date dimension [The Day name] à   Like Monday , Tuesday etc.. [Is Week End] à If business wants check the reference based on Weekends/WeeKDays [Is   Holiday] à If business wants check the reference bas

In some Cases we will have multiple consecutive records(Ref to  Startdate and enddate ) of same status If we want see only one record for each status instead of many consecutive records of same status , here is the solution. It will be nice if we explain with an example . I have the base data in a table as follows: BASE DATA SUBR_NUM_ID SUBR_NUM_CONN_KVD_TO_HOME_DIST SUBR_NUM_CONN_KVD_PRF_START_DT SUBR_NUM_CONN_KVD_PRF_END_DT 8847696 -1 01/01/1600 31/01/2013 8847696 -1 01/02/2013 28/02/2013 8847696 -1 01/03/2013 31/03/2013 8847696 6892 01/04/2013 30/04/2013 8847696 -1 01/05/2013 31/05/2013 8847696 -1 01/06/2013 31/12/9999 I want to consolidate it as follows: MERGED DATA SUBR_NUM_ID SUBR_NUM_CONN_KVD_TO_HOME_DIST SUBR_NUM_CONN_KVD_PRF_START_DT SUBR_NUM_CONN_KVD_PRF_END_DT 8847696 -1 01/01/1600 31/03/2013 8847696 6892 01/04/2013 30/04/2013 8847696 -1 01/05/2013 31/1

SSIS   error   Code: 0xC0209302      When we are using Excel source to load data into sql table we will get an error Code: 0xC0209302      Source: DbWFMSimCard - dataLoad Connection manager "UpdatesExcel"      Description: SSIS Error Code DTS_E_OLEDB_NOPROVIDER_ERROR.   The requested OLE DB provider Microsoft.ACE.OLEDB.12.0 is not registered. Cause : Due Microsoft office not installed on the server you will get that error Solution : In general Microsoft office wont be installed on production servers   , so we need to install office drivers to avoid this error . Install Microsoft Access Database Engine 2010 Redistributable   to facilitate transfer data . http://www.microsoft.com/en-us/download/details.aspx?id=13255  

Integration services – Complex Structured Source File   What is the complexity in the File . The data is available in   Source file   in the   form of “Key = Value “ in each row of file .   The complexity here is some Key   names are columns in Table1 of the same row in destination and Some Key names are Row data in Table2. The data is Available   in some sets in Source File and each Set represents to one test case . Each set is separated by 2 new rows . The Complexity here is each set needs to be load in table1 as well as in Table2 . So one set of data   which presents as each row of source files needs to transfer as columns in Table1 and Rows in Table2 based on Key Name . The souce file name naming convention is like   TcRunExport_From20120425_110700_To20120425_112200_244258.txt The complexity here is we need to extract To datetime and store in a destination tables in Datetime column . The other complexity here is when ever the new file not available the SSIS p