Feeds:
Posts
Comments

Archive for the ‘SharePoint 2010’ Category

Whilst configuring PowerView, I stumbled across the fact that I was no longer able to switch the mode from native to integrated using the Reporting Services Configuration Tool. Microsoft has decided to change this setting by integrating it to SharePoint and it is not a straight forward process as usual!  o_O

My SQL Server 2012 developer edition (eval copy) was installed with ALL features selected. Same goes with my SharePoint Server 2010. I would presume that the SQL Server Reporting Service should have been installed and configured automatically on SharePoint but that was not the case!

Steps to start the Reporting Services SharePoint Service:

(source: http://msdn.microsoft.com/en-us/library/gg492276(v=sql.110).aspx#bkmk_create_serrviceapplication

http://msdn.microsoft.com/en-us/library/gg492276(v=sql.110).aspx#bkmk_install_SSRS)

  1. Click the Start button
  2. Click the Microsoft SharePoint 2010 Products group.
  3. Right-click SharePoint 2010 Management Shell click Run as administrator.
  4. Run the following PowerShell command to install the SharePoint service. A successful completion of the command displays a new line in the management shell. No message is returned to the management shell when the command completes successfully.
    Install-SPRSService
  5. Run the following PowerShell command to install the service proxy:
    Install-SPRSServiceProxy
  6. Run the following PowerShell command to start the service or see the following notes for instructions to start the service from SharePoint Central administration
    get-spserviceinstance -all |where {$_.TypeName -like "SQL Server Reporting*"} | Start-SPServiceInstance

You can also start the service from SharePoint central Administration rather than running the third PowerShell command. The following steps are also useful to verify that the service is running.

  1. In SharePoint Central Administration, click Manage Services on Server in the System Settings group.
  2. Find SQL Server Reporting Services Service and click Start in the Action column.
  3. The status of the Reporting Services service will change from Stopped to Started. If the Reporting Services service is not in the list, use PowerShell to install the service.

Steps to create a Reporting Services Service Application on SharePoint 2010:

  1. In SharePoint Central Administration, in the Application Management group, click Manage Service Applications.
  2. In the SharePoint ribbon, click the New button.
  3. In the New menu, click SQL Server Reporting Services Service Application.
  4. create ssrs service on sharepoint
  5. Configure using the sample below:
  6. create ssrs service app
  7. If you receive an error of “Login failed for user” as below, then make sure that the NT AUTHORITY\Network Service has access to the database.
  8. SP error1
  9. If  you receive an error of “Could not find stored procedure sp_dboption” then make sure that the Microsoft SharePoint Foundation Server 2010 (MUST BE 173,633KB in size) and Microsoft SharePoint Server 2010 SP1 were installed. If not, install them. The Microsoft SharePoint Server 2010 SP1 must be installed in order to support SQL Server 2012! Microsoft SharePoint Foundation Server 2010 must be installed first then followed by the Microsoft SharePoint Server 2010 SP1!!!
  10. SP error2
  11. SSRS Service Application (Integrated mode) installed successfully.

SSRS integrated mode created

Read Full Post »

Found this useful reference from the blog below. The yeartodate formula only works if it exists in the Cube. If it does not then use the following formula instead:
Calendar YTD = Year.FirstMonth:Month

Parallel Calendar YTD = (Year-1).FirstMonth:(Year-1).Month

http://blogs.msdn.com/b/performancepoint/archive/2010/01/21/time-intelligence-formula-quick-reference.aspx

Content below:

Time Intelligence Formula (TI Formula) allows time periods, sets of time periods and ranges to be specified using a simple, easy to understand syntax.

Formula Structure

 [(]<Period>[<Offset>[)][.<Function>[<Offset>]]]

Quick Samples

The following are typical scenarios with their corresponding formula and results:

To Get… Formula Result
Yesterday day-1 The previous day relative to the current date.
Tomorrow day+1 The next day relative to the current date.
The current quarter and today quarter, day A set of time periods consisting of the current day and current quarter
Last 10 days day:day-9 A 10 day range including today.
Last 10 days (not including today) day-1:day-10 A 10 day range NOT including today.
Same day last year (year-1).day Parallel period: days by year.
Same quarter last year (year-1).month Parallel period: Quarter by year.
Same range of months last year (year-1).(month-5):
(year-1).(month)
Set of parallel periods last year to the last six months.
Same range of months to date for last year (year-1).firstmonth:
(year-1).month
From the first month of last year to the month parallel to the current month this year.
Year to date yeartodate A single time period representing the aggregation of values from the beginning of the year to the last completed period.
The period corresponds to the most granular time period defined for the data source.
Year to date (by month) yeartodate.fullmonth A single time period representing the aggregation of values from the beginning of the year to the last completed month.
Year to date (by day) yeartodate.fullday A single time period representing the aggregation of values from the beginning of the year to the last completed day.
Parallel year to date yearToDate-1 The aggregation of the same set of default time periods completed in the current year except for the prior year.
Parallel year to date (by month) (yearToDate-1).fullMonth The aggregation of the same set of months completed in the current year except for the prior year.

General Conventions

  • TI Formula is not case sensitive
  • White-space is ignored
  • Use parenthesis whenever you use a time period with an offset together with a function.
  • Offsets can be defined on any standard and “to date” time period.
  • The current period is assumed when no offset is specified.

Syntax Elements

TI Formula consists of Operators, Periods and Functions.   The formula is intentionally simple yet robust.

Operators

The following operators can be used in TI formula expressions.

Operator Use
“.” The period or “dot” operator delimits time periods from functions.  The part before the dot always corresponds to the time period.  The part after the dot always corresponds to the time period function.
Example:  year.firstMonth
“+” or “-” The plus and minus operators are used to determine the offset relative to the current date. Use the minus sign to specify time periods in the past.  Use the plus sign to specify time periods in the future.
Example:  day1
“()” Parenthesis are used to group a time period and its offset when used together with a function.  This is required.
Example:  (year-1).firstMonth:(year-1).month
“,” Comas are used to delimit multiple time periods in the same formula.
Example:  yearQuarterMonthday
“:” The colon operator specifies a range of time periods.
Example:  day:day-4

Standard Time Periods

Standard time periods supported in PPS include:

Period Example
Year Year-1, year+2
Quarter quarter, Quarter-4
Month Month-2
Week Week-51
Day Day-9, day+2
Hour Hour-12
Minute minute-30
Second second+5

Standard Time Period Functions

Time period functions can be applied to any standard time period using the dot operator.  The functions available in PPS include:

Function Use
FirstQuarter Specifies the first quarter in a year.
LastQuarter Specifies the last quarter in the year
FirstMonth Specifies the first month in a year or quarter.
LastMonth Specifies the last month in a year or quarter.
FirstWeek Specifies the first week in a year, quarter, or month.
LastWeek Specifies the last week in a year, quarter, or month.
FirstDay Specifies the first day in a week, month or higher time periods.
LastDay Specifies the last day in a week, month or higher time periods.
FirstHour Specifies the first hour in a day, week or higher time periods.
LastHour Specifies the last hour in a day, week or higher time periods.
FirstMinute Specifies the first minute in an hour, day or higher time periods.
LastMinute Specifies the last minute in an hour, day or higher time periods.
FirstSecond Specifies the first minute in an minute, hour, or higher time periods.
LastSecond Specifies the last minute in an minute, hour, or higher time periods.

Periods-To-Date

Periods “to-date” are a NEW type of TI formula added in Office 14.  The result of a to-date period is an aggregation of all time periods to date up to the last completed full period.  Incomplete time periods are automatically excluded.  They are evaluated to the lowest degree of granularity in the data source by default.  For example, if most granular time period in the data source were days, then the month to date expression will aggregate all days from the beginning of the month to the last completed full day in the month.  (The opposite is true for standard time periods.  They automatically include incomplete periods.)

PPS supports the following “to date” time periods:

Period Use Works with…
YearToDate Specifies a time period from the beginning of the year to the current period. FullQuarter functions or lower.
QuarterToDate Specifies a time period from the beginning of the quarter to the current period. FullMonth functions or lower.
MonthToDate Specifies a time period from the beginning of the month to the current period. FullWeek functions or lower.
WeekToDate Specifies a time period from the beginning of the week to the current period. FullDay functions or lower.
DayToDate Specifies a time period from the beginning of the day to the current period. FullHour functions or lower.
HourToDate Specifies a time period from the beginning of the hour to the current period. FullMinute functions or lower.
MinuteToDate Specifies a time period from the beginning of the minute to the current period. FullSecond function.

Period To Date Functions

Period to date functions allow you to control the granularity of which time periods to include.  If for example I want to show the year to date including up to the last completed full month, I can add a full period function.

PPS supports the following full period functions:

Function Use Works with…
FullQuarter Specifies that the period to date should include up to the last full quarter. YearToDate
FullMonth Specifies that the period to date should include up to the last full month. QuarterToDate periods of higher.
FullWeek Specifies that the period to date should include up to the last full week. MonthToDate periods of higher.
FullDay Specifies that the period to date should include up to the last full day. WeekToDate periods of higher.
FullHour Specifies that the period to date should include up to the last full hour. DayToDate periods of higher.
FullMinute Specifies that the period to date should include up to the last full minute. HourToDate periods of higher.
FullSecond Specifies that the period to date should include up to the last full second. MinuteToDate periods of higher.
Note this is included for consistency, though technically this period is not needed.

Parallel Period To Date

It is often useful to compare values for the equivalent set of time periods for the current year, but for the previous year.  For example, imagine that the current year to date includes Jan – July of 2010.  You may want to compare this value to the same set of time periods for the previous year.  In this case Jan – July 2009.  This is easy to accomplish using TI formula.  Simply add and offset value to the to-date period.

YearToDate-1

In fact, offsets can be specified on any to-date time period.   This also works when a full period function is applies as well.  Remember to use parenthesis in this case.

(YearToDate-1).FullMonth

Stephen Handy is a Program Manager on the PerformancePoint Services SharePoint BI team.

Read Full Post »

This error 7401 does not tell you much exactly what it means. The problem arose with the parameter value passing.

Error 7401

My dashboard has a time intelligence filter, a scorecard, a map and two analytic charts. When the filter of “Last 6 Months” or “Last 12 Months” or “Calendar YTD”  was selected, one of the charts threw an error of 7401. Digging through the SharePoint log files didn’t help either.

I then resolved to remove this filter from the chart and the error went away immediately.

I have the same dashboard deployed to SharePoint 2013 (SQL Server 2008) and the error 7401 never occurs. My VM has SharePoint 2010 and SQL Server 2012. Very odd!

I was then suspicious about the date dimension. Recreating the time intelligence filter didn’t help. I then checked how the date members were configured on the analytic chart. The screenshot below shows that the bottom axis has got Date Calendar Year and Date Month of Year and the background has got Date Date. The Date Date is required for parameter passing. This set up is the one that is causing the issue on PerformancePoint 2010 only! I have no idea why. o_O

Error 7401 chart

So I got rid of Date Calendar Year, Date Month of Year and Date Date and replaced them with Date Calendar hierarchy but with the month member all selected (from January 2008 to August 2008; this cube has got monthly data from Jan to Aug for Calendar Year 2008) so that the bottom axis will show month and year instead of showing the whole Date Calendar hierarchy. The error went away and never occurs! 😀

Error 7401 chart 2

Error 7401 chart 3

Read Full Post »

Sales Dashboard for Australia 3

When a cell in the scorecard for Bikes and South Australia is selected, the Trend by Product and Trend by State line graphs get updated accordingly.

Australia Sales Demo Dashboard 2

Drilldown to a list of cities when SA state on the map is selected. Why Perth is listed under SA?  😀 Obviously it is a data entry error.
Australia Sales Demo Dashboard drilldown

Read Full Post »

I am very proficient in using SSRS as a dashboard/report designer tool so it takes me a while to get my head around with PerformancePoint dashboard designer within SharePoint 2010 and 2013. Hence I have compiled a list of questions and answers learned:

1) How to set up a parameter in an analytic grid, analytic chart or report?

To set a State parameter on a chart, select the “Query” tab. Highlight the State member in the MDX query as per screenshot below. Type in the parameter name then click the “Insert” button. Do the same for CalendarDate parameter. THIS IS SO DIFFERENT FROM SSRS!    o_O

Configure Parameter in PerformancePoint

Configure Parameter in PerformancePoint 2

2) How to pass a parameter value from a scorecard to an analytic grid, analytic chart or report?

A scored card looks like a matrix in SSRS. I think in fact it is a matrix. It has a row, a column and data.  To pass a value from the column to another webpart (analytic grid, analytic chart or report), set its source value to “Member Column : Member Unique Name” for an MDX report.  Likewise, to pass a value from the row, set it to “Member Row: Member Unique Name”.

parameter value passing from scorecard column to a graph

3) How to pass a parameter value from an embedded SSRS report to an analytic grid, analytic chart or report?

Cannot be done 😦 You can only set a drilldown to another SSRS report using Report Builder or BIDS.

4) How to set up a time intelligence filter?

A time intelligence filter allows setting up a list of rolling time periods such as current month, last month, last 6 months, last 12 months, calendar year to date etc.

time period list

To set one up, create a new filter then select the time intelligence type. Set up the formula as per screenshot below:

The formula for  Calendar YTD is Year.FirstDay:Day

time intelligence filter setup

5) How to pass a time intelligence filter value to  an analytic grid, analytic chart or report?

Set its source value to the name of the OLAP Cube (for MDX report), in this example, AdventureWorksCube.

parameter value passing from time intelligence filter to a report

6) How to pass a time intelligence filter value to a scorecard?

This set up only applies for parameter value passing to a scorecard. Sert the Connect to option to use “TI formula” and the Source Value to use “Formula”. Otherwise it will not work!

scorecard parameter value setup

7) How to configure a date parameter using calender picker instead of drop down list and then pass its value to an embedded SSRS report?

To create a date filter (calendar picker format): create a new filter then choose the type  time intelligence  connection formula as per screenshot below.

calender picker sharepoint

To pass the date parameter value correctly to an SSRS report, the parameter value must be in US format. Even though it is displayed as AUS format, the value is still being passed as US format. So in the SSRS report, the date parameter must be set to text data type and the Language property set to use en-us. I know this is very annoying!.. I reckon it is a bug within PerformancePoint and it has not been fixed in PPS 2013.

8) How to turn the interactive functionality such as  “drillup”, “drilldown”, “actions”, “decomposition tree”?

The calculation must be set to use “data value”.

scorecard data value calculation

Read Full Post »

I want to show gross profit traffic lights on a map report for Australia and there are two ways to create such map report.

aus gross profit

1) Microsoft  Office Visio (must be at least Visio 2007 and 64 bits installed on SharePoint 2010 or above)

2) SSRS (at least SSRS 2008 and the map report is using ESRI shapefile format map. The map gallery only contains the USA maps)

Microsoft Visio map is not as attractive as SSRS hence I decided to use SSRS instead.

To download the Australian States ESRI shapefile for free, go to the Australian Bureau of Statistics website then download the “State and Territory ASGC Ed 2009 Digital Boundaries in ESRI Shapefile Format ” zip file.

Once unzipped, create a map report in SSRS. You will be prompted to choose the source of the spatial data. At first attempt, I chose the second option i.e. ESRI shapefile, leave everything else as per default then pick the Colour Analytical map. When I ran the report, I received the error – Map exceeds the maximum number of spatial elements.

Capture

According to Microsoft, by default, a map can have 20,000 map elements or 1,000,000 points. If your map exceeds these limits, you can use one of the following approaches:

1. Remove a layer

2. Decrease the map resolution.

3. Decrease the map viewport coordinates to view a smaller area.

4. If the spatial data comes from a report dataset, set a filter to limit the data from the dataset. The filter must be set on a field that is not a spatial data type.

5. If the spatial data comes from a SQL Server database, change the query to use spatial functions to limit the data to a smaller area.

My map has got the least amount of layer hence approach 1 could not be done. My viewport coordinates are already quite small hence approach 3 stays as it is. Same applies for approach 4. So it leaves me with approach 2 and 5.  Since I had no idea at first how to decrease the map resolution so I resolved to do approach 5 first then 2.

With approach 5, I needed to import the ESRI shapefile spatial data into SQL Server database. So how to do that? Google came back with this blog (thanks to Pinal Dave). I needed to download a tool called Shape2SQL or SqlSpatialTools (free to download at the time of the writing).

http://blog.sqlauthority.com/2010/03/30/sql-server-world-shapefile-download-and-upload-to-database-spatial-database/

Download SqlSpatialTools from the link below:

http://www.sharpgis.net/file.axd?file=SqlSpatialTools_build3759.zip

So I did all that  and set the source of the spatial data to obtain from SQL Server spatial query but the error still would not go away.

I was then left with approach 2. After many trials of recreating  the map report, I finally noticed that I could reduce the map resolution in the wizard. By default, it is set to “best quality”. Changing it to smallest size has made the error gone away at last 😀

map resolution

So now the report could be executed with no problems.  But I noticed that it took a long time to render(my VM is on 4 GB RAM, one processor) and even longer if it was embedded in SharePoint 2010/2013. I wondered what went wrong again! Google came up with one user mentioning about embedding the ESRI shapefile would speed up the report. So indeed, this is the clue! Approach 5 has impacted on the performance of the report.

So my final working attempt is below:

1) Set the spatial data to obtain from SQL Server spatial query. I needed to use this query because the spatial data contains the state_code in numbers ( such as 1 for NSW, 2 forVIC and so on).

SELECT STATE_CODE
,CASE
WHEN STATE_CODE = 1 THEN ‘NSW’
WHEN STATE_CODE = 2 THEN ‘VIC’
WHEN STATE_CODE = 3 THEN ‘QLD’
WHEN STATE_CODE = 4 THEN ‘SA’
WHEN STATE_CODE = 5 THEN ‘WA’
WHEN STATE_CODE = 6 THEN ‘TAS’
WHEN STATE_CODE = 7 THEN ‘NT’
WHEN STATE_CODE = 8 THEN ‘ACT’
WHEN STATE_CODE = 9 THEN ‘Other’
END AS STATE_CAPTION
,STATE_NAME
,geom
FROM STE09aAust

2) Set the map resolution to “Smallest Size”

3) Embed map data to this report. Ensure that the option to embed the map data is ticked.

4) Done!

 

Read Full Post »

Recently, I built a VM that has got SQL Server 2012 Developer edition and SharePoint 2010 Server Enterprise edition in it. However, I have forgotten to rename the VM before the installation. So I thought, should be alright as it is just a server rename. Boom! SharePoint failed to work. SQL Server name has not been updated!

 

First attempt to recover SharePoint:

1. Uninstall SharePoint 2010 Server . Go to Control Panel > Uninstall programs > SharePoint 2010 Server

2. Install SharePoint 2010 Server from the media.

3. Run the SharePoint Configuration Tool. This step failed.

4. Run SharePoint 2010 Central Administration. Failed to load.

5. Restart the VM. SharePoint still failed to load.

I thought, gosh, do I really need to build this VM from scratch ??!!  I already spent one day building it.

 

Second atttemp to recover SharePoint:

So I decided to try again.. this time I checked if the SharePoint created databases or sites in the IIS.

1. Uninstall SharePoint 2010 Server . Go to Control Panel > Uninstall programs > SharePoint 2010 Server

2. Check SQL Server 2012 database to see if there are any SharePoint related databases created. If there are, delete all of them. In my case, there is none.

3. Open IIS. There are 2 sites created by SharePoint.  My IIS pretty much has Default website installed with no custom sites. Do not delete this default website. Delete all SharePoint sites.

4. Install SharePoint 2010 Server from the media. When prompted to overwrite the existing SharePoint sites, choose Yes.

5. SharePoint works successfully 🙂

 

To rename SQL Server 2012, follow the steps in the following link:

http://msdn.microsoft.com/en-us/library/ms143799(v=sql.105).aspx

In my case, it is a stand-alone server, so the steps are below:

For a renamed computer that hosts a default instance of SQL Server, run the following procedures:

sp_dropserver <old_name>
GO
sp_addserver <new_name>, local
GO

Read Full Post »

Older Posts »

%d bloggers like this: