Wednesday, October 5, 2011

SSRS Embedded VB Script

In my case I am needing a UserName function to bind to the parameters. The goal is to limit the location parameters based on the UserName. So I added a UserName vb function in the embedded code section of the report. 

Go to Report > Properties > Code

Add the following function:
Public Function UserName()
          Return Report.User!UserID
          Return "System"
      End Try
End Function

Now go to Parameters and set default value of UserName to: =code.UserName()

This will bind your userName function to the actual UserName parameter.

Good luck

Friday, July 29, 2011

Fix Slow WebDAV Performance in Windows 7

Today, finally, I got so sick and tired of the Slow WebDav Performance so I searched for a solution. I found the answer in just a minute or two, illustrating quite succinctly just how much time I had been wasting just “putting up with it.” Oddly, the culprit is none other than Internet Explorer 8. Quite simply, all you have to do is turn off IE’s automatic proxy settings detection. Here’s how to do that:
  1. In Internet Explorer, open the Tools menu, then click Internet Options.
  2. Select the Connections tab.
  3. Click the LAN Settings button.
  4. Uncheck the “Automatically detect settings” box.
  5. Click OK until you’re out of dialog hell.
I found the answer on this Microsoft Technet discussion, incidentally. The solution was posted by “Daniel CD” about two-thirds of the way down the page.


Monday, January 10, 2011

SSAS Different Dimension Granularity

In an SSAS cube you sometimes need to connect a dimension at a different granularity than other dimensions. For example, if you have budget measures and actual numbers. If you are trying to budget your expenses you budget at a higher level than the actual amounts. You budget money for groceries, not for Eggs, Milk, and bread. But your actual amounts will be down to the individual items.

In the cube browser image below you can see this in use. Notice the budget numbers only show on the category level and not on the individual items.
SSAS Different Dimension Granularity
Here are the tables being used in this cube.
SSAS Different Dimension Granularity
SSAS Different Dimension Granularity
SSAS Different Dimension Granularity
To set up this you will need to open the dimension usage tab in the cube editor. Click on the connection between dim account and fact budget and set up the category code as the key between the tables. The category code will be the relationship between the dimension and the fact table. A foreign key between the tables does not need to exist.
SSAS Different Dimension Granularity
SSAS Different Dimension Granularity
One other change is to set the budget measure group to ignore unrelated dimensions. This is in the Cube Structure tab, click on the budget measure group and change this property. It will need to be false, I know this seems backwards.
SSAS Different Dimension Granularity
That is all you need. Let me know if you have any questions.

Ref link:

SharePoint View and Filter techniques

Advanced SharePoint View and Filter techniques


We discussed in a previous article how to customise SharePoint’s built in Task List and briefly looked at the power of Views with Filters. In this article we will build upon that by demonstrating some advanced use of Filters.
This article assumes that you are already familiar with Views and Filters – if not please review the previous article.

Overdue Tasks

To show a task lists Overdue tasks use the following Filter :-

  • Status – is not equal to – Completed
  • Due Date – is less than – [Today]
Note – [Today] is a special marker that SharePoint understands as the current date.
Overdue Tasks

My Overdue Tasks

Add the following to the Overdue Tasks filter

  • Assigned To – is equal to – [Me]
Note – [Me] is a special marker that SharePoint understands as the current user.

Items added in Last 7 Days

It would be nice to be able to enter something like
  • Created – is less than – [Today] – 7
in the filter, but alas its not quite that simple!
[EDIT - This work around was written in early 2006 and WSS 2 / SPS 2003 wouldn't accept this - in WSS 3 / MOSS 2007 you can now use a FILTER formula like [Today]-7 as long as you keep the spaces out!]
The workaround is to create a Calculated column and then filter on that column.
Select Modify Settings and Columns, then Add a new columnGive the column name something like “Filter 7 Days” and select a Calculated column
Name the view "Filter 7 Days"
Enter a suitable DescriptionIn the Formula box enter “=Created+7″Choose Date and Time format and Date Only

Clear Add to default view as we don’t want this field to appear on any of the views
Set the calculated columns formula
Create a new viewSetup a filter to show only :-

  • Filter 7 Days – is greater than or equal to – [Today]
Set the views filter

Items Due in the next 7 Days

[EDIT - This work around was written in early 2006 and WSS 2 / SPS 2003 wouldn't accept this - in WSS 3 / MOSS 2007 you can now use a FILTER formula like [Due Date]-7 as long as you keep the spaces out!]
Select Modify Settings and Columns, then Add a new columnGive the column name something like “Filter Coming Due” and select a Calculated columnSet the new columns name and choose "calculated column"
Enter a suitable DescriptionIn the Formula box enter “=Due Date-7″Choose Date and Time format and Date Only

Clear Add to default view as we don’t want this field to appear on any of the views
Set the calculated columns formula
Create a new viewSetup a filter to show only :-

  • Status – is not equal to – Completed
  • Filter Coming Due – is less than or equal to – [Today]
  • Due Date – is greater than or equal to – [Today]
This will ensure that the view only displays Uncompleted items that are due in the next 7 Days but are not Overdue.
Set the filter

Friday, January 7, 2011

Integrating SQL Server 2008 R2 Reporting Services with SharePoint 2010

I'm ready to install SQL Server 2008 R2 and SharePoint 2010 in a test environment.  I'm trying to decide whether to run SQL Server Reporting Services in SharePoint Integrated mode or native mode.   What are the advantages of running Reporting Services in SharePoint Integrated mode?  Can you provide an overview of what I need to do to get Reporting Services installed and running in SharePoint Integrated mode?
The biggest advantage to running Reporting Services in SharePoint Integrated mode is that you can deploy data sources, reports, etc. to SharePoint document libraries instead of the Report Manager web application that Reporting Services creates for you.  Since your users are probably familiar with SharePoint this makes sense.  In addition your users can take advantage of the new version of Report Builder that came with SQL Server 2008 R2 and deploy their reports to SharePoint document libraries, leveraging SharePoint for security.  This is easier for them than using the Report Manager.  The users can be much more self-sufficient with SharePoint.
In this tip I will highlight the steps you will want to follow to get Reporting Services installed and integrated with SharePoint.  I'll be using SQL Server 2008 R2 and SharePoint 2010 Foundation.  SharePoint 2010 Foundation is the new name for what was formerly Windows SharePoint Services.  It is the version of SharePoint that is included with your Windows 2008 server license.  The steps for SharePoint 2010 Server are essentially the same.  In order to get a test environment up and running as quickly as possible, I will install everything on a single server.  I'm using Windows Server 2008 R2 64 bit; 64 bit is a requirement for SharePoint 2010.
Installing SQL Server 2008 R2
At a minimum, install the database engine and reporting services.  By installing SQL Server first you have the option of specifying the database engine that SharePoint will use.  Alternatively you can install SharePoint and allow it to install its own instance of SQL Server Express (now called SQL Server Embedded Edition).  There are two steps I want to highlight in the SQL Server 2008 R2 installation: Setup Role and Reporting Services Configuration.
In the Setup Role step, choose SQL Server Feature Installation as shown below:
In the Reporting Services Configuration step, choose Install the SharePoint Integrated mode default configuration as shown below:
This allows the install to do the majority of the work, limiting the steps that you will need to perform to get Reporting Services integrated with SharePoint.
Installing SharePoint 2010
In order to successfully install SharePoint, there are a number of prerequisites that must be installed.  When you launch the SharePoint 2010 setup, make sure to first click Install software prerequisites as shown below:
After clicking Install software prerequisites the following screen is displayed:
Note that this step will automatically install anything that you need from the list of required products and updates.  In order to run Reporting Services in SharePoint Integrated mode, you need the SQL 2008 R2 Reporting Services SharePoint 2010 Add-in; this will be done for you.
Now we are ready to install SharePoint 2010 Foundation.  Click Install SharePoint Foundation on the splash screen (shown at the beginning of this section).  Since we are interested in a test environment choose Standalone as shown below:
The Standalone option is perfect for getting going quickly; it installs the software with all default settings.  While this approach requires the least amount of effort on your part, it does install an instance of the SQL Server Embedded Edition that can only be used by SharePoint; i.e. you can't leverage this SQL Server instance for the Reporting Services database.  In addition you truly get a standalone instance of SharePoint; you cannot add any other SharePoint servers.  Despite these limitations, I'm going to proceed with the Standalone option.
When the SharePoint 2010 Foundation installation is completed, you will be prompted to run the SharePoint Products Configuration Wizard.  You need to do that to in order to complete the configuration steps required to have a working installation of SharePoint 2010 Foundation.  When the SharePoint Products Configuration Wizard is finished, your browser will open to the SharePoint Team site that has been created for you.
Configuring Reporting Services SharePoint Integration
At this point we have Reporting Services 2008 R2 and SharePoint 2010 Foundation installed.  The next step is to configure Reporting Services SharePoint integration using SharePoint Central Administration.  Launch SharePoint Central Administration from the Start menu (it should be pinned to the Start menu; if not then select it from All Programs, Microsoft SharePoint 2010 Products group).  Click General Application Settings as shown below:
Click Reporting Services Integration as shown below:
Note that the Reporting Services options shown above will only appear after the SQL 2008 R2 Reporting Services SharePoint 2010 Add-in is installed.  I installed the add-in by running the Install software prerequisites option before installing SharePoint.
There are just a couple of things that you need to enter to complete the integration as shown below:
The following are the main points about the integration settings:
  • You can get the Report Server Web Service URL from the Reporting Services Configuration Manager (in the Microsoft SQL Server 2008 R2, Configuration Tools program group).  Click on Web Service URL in the menu on the left.
  • When you have everything running on a single server as I do in this example, choose Trusted Account for Authentication Mode.
  • For Credentials you do need an account that is in the local Administrators group.  With everything on a single server it can be a local account, but I'm using a domain account.
  • Choose Activate feature in all existing site collections.
After entering the required information and clicking OK, you should see the Reporting Services Integration Summary as shown below:
At this point Reporting Services is configured in SharePoint Integration mode.
Preparing a Document Library
There is one last step that needs to be performed in order to create Reporting Services artifacts (e.g. shared data sources, report models, reports) in a SharePoint document library.  We need to add the appropriate content types to the document library and customize the New Document menu options.  For this example I created a brand new document library called Reports.
Navigate to the document library then click Library Settings as shown below:
SharePoint 2010 has a new ribbon menu.   You have to click on the Library tab in order to see the above (the other option is the Documents tab).  Click Advanced Settings on the page as shown below:
Click the Yes radio button under Allow management of content types then click OK:
You will be returned to the library settings page; click Add from existing site content types:
Select the Report Builder Model, Report Builder Report, and Report Data Source content types from the Available Site Content Types List, then click the Add button:
Click OK to return to the library settings page; click Change new button order and default content type:
Change the Visible property and Position from Top as shown below:
Click OK, then return to the document tab for the document library.  Click New Document and you will now see the menu options as we have configured them:
Click Report Builder Report to launch Report Builder.  You should see Report Build 3.0 launch and you can now author reports, save them to a SharePoint document library, and of course run reports by simply clicking on them in the SharePoint document library.

Ref link: