ConCat DateTime

select FullDateAlternateKey,

str(YEAR(FullDateAlternateKey))+
RIGHT('00' + (CAST(datepart(mm, FullDateAlternateKey)AS NVARCHAR(2))), 2)+
RIGHT('00' + (CAST(datepart(dd, FullDateAlternateKey)AS NVARCHAR(2))), 2)from DateWorkingDimension

Filtering Rows not equal zero

WITH

MEMBER [Measures].[Travel Expense as % of Salary] ASiif( [Measures].[Salary] = 0, null, [Measures].[Total Travel Expenses]/ [Measures].[Salary]),

Format_string = 'percent'
MEMBER [Measures].[Average FTE] AS --same as data source view calculation[Measures].[Hours]/[Measures].[Monthly Hr Rate]
MEMBER [Measures].[Average Cost Per FTE] AS[Measures].[Total Expenses]/[Measures].[Avg FTE]
SELECT NON EMPTY { [Measures].[Add Exp], [Measures].[Training Instructor Count], [Measures].[Salary], [Measures].[Total Expenses], [Measures].[Total Travel Expenses], [Measures].[Expenses], [Measures].[Hours], [Measures].[Monthly Hr Rate], [Measures].[Travel Expense as % of Salary],[Measures].[Average FTE],
[Measures].[Average Cost Per FTE]
}
ON COLUMNS, [Cost Center].[RC Charge Code].[RC Charge Code].
ALLMEMBERShaving [Measures].[Hours] <> 0 ON ROWS From[CRAnalytics]

Select Weekend

SELECT FULLDT FROM FACTHOURS
WHERE DATENAME(dw, FULLDT) in ('Saturday', 'Sunday')

timesheet entry

project management and accounting -> setup->timesheets->timesheet period types
create a new type as monthly. Generate Period,
Click on "Workers" to create a worker, (needs to import a worker beforehand from AD).
In create worker, type in first name and last name to select the user from the AD.
On project management tab, project setup, also update period status.

Link created worker on the "Worker" screen to the user imported via AD.
System administration --> users, select the users, on the users tab, "relations". Add the worker to the user relation. This will assign imported AD user to the AX system worker.

DateDimension

select SUBSTRING(convert(nvarchar, FullDateAlternateKey, 120), 0, 5)+ SUBSTRING(convert(nvarchar, FullDateAlternateKey, 120), 6, 2)+ SUBSTRING(convert(nvarchar, FullDateAlternateKey, 120), 9, 2) as datekey, SUBSTRING(convert(nvarchar, FullDateAlternateKey, 120), 0, 5) as CalendarYear, CalendarQuarter = CASE CAST(SUBSTRING(convert(nvarchar, FullDateAlternateKey, 120), 6, 2) AS Int) WHEN 1 THEN 1 WHEN 2 THEN 1 WHEN 3 THEN 1 WHEN 4 THEN 2 WHEN 5 THEN 2 WHEN 6 THEN 2 WHEN 7 THEN 3 WHEN 8 THEN 3 WHEN 9 THEN 3 WHEN 10 THEN 4 WHEN 11 THEN 4 WHEN 12 THEN 4 END, CAST(SUBSTRING(convert(nvarchar, FullDateAlternateKey, 120), 6, 2) AS Int) as MonthNumber, CAST(SUBSTRING(convert(nvarchar, FullDateAlternateKey, 120), 9, 2) AS Int) as MonthDay, * from DateDimension Where FullDateAlternateKey is excel generated date format as 2010-01-01. update DateDimension set DateKey = SUBSTRING(convert(nvarchar, FullDateAlternateKey, 120), 0, 5)+ SUBSTRING(convert(nvarchar, FullDateAlternateKey, 120), 6, 2)+ SUBSTRING(convert(nvarchar, FullDateAlternateKey, 120), 9, 2), CalendarYear = SUBSTRING(convert(nvarchar, FullDateAlternateKey, 120), 0, 5), CalendarQuarter = CASE CAST(SUBSTRING(convert(nvarchar, FullDateAlternateKey, 120), 6, 2) AS Int) WHEN 1 THEN 1 WHEN 2 THEN 1 WHEN 3 THEN 1 WHEN 4 THEN 2 WHEN 5 THEN 2 WHEN 6 THEN 2 WHEN 7 THEN 3 WHEN 8 THEN 3 WHEN 9 THEN 3 WHEN 10 THEN 4 WHEN 11 THEN 4 WHEN 12 THEN 4 END, CalendarMonth = CAST(SUBSTRING(convert(nvarchar, FullDateAlternateKey, 120), 6, 2) AS Int), CalendarDay = CAST(SUBSTRING(convert(nvarchar, FullDateAlternateKey, 120), 9, 2) AS Int) from DateDimension

SSRS

On multiple computer SSRS deployment, Kerberos authentication is needed. If Kerberos hasn’t been set up in the environment, trusted account will work as an alternative. Trusted account settings can be added through central admin, “General Application Settings” à “Report Services Integration”.

On report service site, the IIS manager site requires to set anonymous authentication to be disabled. If not, this may cause lots of issues; one of those issues is that it won’t let you to deploy reports from your BIDS to the SharePoint site.  The most common issue is the sharepoint site keep on popping up login windows. (http://technet.microsoft.com/en-us/library/bb326290.aspx )

To verify Report Server and SharePoint integration, type in browser window http://server_sitename/reportserver, where server_sitename is the report server name.

RSReportServer.config file location: for non Kerberos, the config needs to have RSWindowNTLM.

 C:\Program Files\Microsoft SQL Server\MSRS10.<InstanceName>\Reporting Services\ReportManager

Once Integration has been set up, you can create report from SharePoint document library by using report builder.

Understanding Groups (Report Builder and SSRS) http://technet.microsoft.com/en-us/library/dd255263.aspx

Other References (Please Select the appropriate version from the "Other Version" option)
SQL server 2008 r2 sp2: http://www.microsoft.com/en-us/download/details.aspx?id=30437
SQL Server 2008 r2 sp2 CU4: http://support.microsoft.com/kb/2777358/en-us
Microsoft® SQL Server® 2008 R2 Service Pack 1 http://www.microsoft.com/en-us/download/details.aspx?id=26727  
Microsoft® SQL Server® 2008 R2 Service Pack 2 http://www.microsoft.com/en-us/download/details.aspx?id=30437

Reporting Services Execution log & Log Files:

Report Server Execution Log and the ExecutionLog3 view http://msdn.microsoft.com/en-us/library/ms159110(v=sql.105).aspx  
Reporting Services Log Files http://msdn.microsoft.com/en-us/library/ms157403(v=sql.105).aspx  

Reporting Services Report Design Best Practices:

Reporting: Best Practices http://msdn.microsoft.com/en-us/library/windows/desktop/bb226815(v=vs.85).aspx
Report Design: Best Practices and Guidelines http://msdn.microsoft.com/en-us/library/cc966445.aspx  
Report Design Tips and Tricks http://msdn.microsoft.com/en-us/library/bb395166(v=sql.90).aspx  
Reporting Services Recipes Book Released http://blogs.msdn.com/b/robertbruckner/archive/2010/03/21/reporting-services-recipes-book-released.aspx  | http://blogs.msdn.com/b/robertbruckner/archive/2010/04/11/reporting-services-recipes-book-downloads.aspx
Deciding Whether to Use an Expression or a Variable in SSRS http://sqlserverpedia.com/blog/sql-server-bloggers/deciding-whether-to-use-an-expression-or-a-variable-in-ssrs/  
Global variables in embedded code Reporting Services 2008 deployment http://social.msdn.microsoft.com/Forums/en-NZ/sqlreportingservices/thread/17b25a5a-e7d2-4813-8afc-6fb002357542
 

SP Upgrade from 07 to 10

Move from 07 to 10
Restore DB to the SQL Server
Create a new web app
Remove content DB for this web app
Do command line (power shell) test-spdatabase
Add database: mount-spdatabase

check result: web application --> site collection list
manage content databases --> if the database is there.



Move from 10 to 10
restore DB to SQL server
create a new web application
remove content DB from this web app
run command: new-spcontentdatabase
check result: web application --> site collection list
manage content databases --> if the database is there.