This project has moved. For the latest updates, please go here.

Setting up a Test Server

CrissCross needs an SSRS server to run (obviously) and so if you just want to test or experiment with CrissCross, you'll need an SSRS server.

This page describes how to set up a 'test' SSRS server using free versions of Sql Server and the sample AdventureWorks database and reports.

These notes are based on using an AWS server, but you could of course do this with other virtual server providers. The notes assume you are familiar with server and Sql Server config.

NB: If you already have an SSRS server and just want to get CrissCross going, see Getting Started instead

Initial server config

On AWS/EC2, create an instance of "Microsoft Windows Server 2008 R2 Base" (64 bit, 30 Gig)
Choose an m1.medium size instance (anything smaller will struggle with memory)

Initially the server only has 30Gig which quickly fills up. So create an extra volume in Elastic Block Store and attach it to your instance as xvdf.

When you log into the instance, use Disk Management to get windows to attach the volume and assign it the drive letter D:

When installing things, try and use D: whenever possible to preserve space on C:

(Alternatively, it may be possible to expand the size of the initial C: drive)

In Windows Server Manager, you'll need to install the Web Server and App Server Roles.
For the App Server choose the following Role Services: .net framework, web server support (IIS), http activation
For the Web Server role the default Role Services are sufficient.

Visual Studio

If you want to do CrissCross development, you'll need Visual Studio 2008 installed, and its a good idea to install it before installing Sql Server.

Visual Studio 2008 Pro Trial Edition can be downloaded from here http://www.microsoft.com/en-gb/download/details.aspx?id=3713
The Trial Edition is in the form of an ISO, you can use the freeware MagicDisk to mount the ISO to your virtual server: http://www.magiciso.com/tutorials/miso-magicdisc-overview.htm

For CrissCross you only need the C# and WebDev bits of VS2008. Make sure you install on the D: drive.

After installing, apply at least VS 2008 SP1 http://www.microsoft.com/en-us/download/details.aspx?id=10986

Sql Server

Microsoft SQL Server 2008 Express is the 'free to use' version of SQL Server, and there is a version called 'Microsoft SQL Server 2008 Express with Advanced Services' which also includes a version of SSRS. Its not the full SSRS but it is sufficient for CrissCross testing.

NB: There is also a 'Sql Server 2008 R2 Express with Advanced Services' but the AdventureWorks sample reports dont work with it (see below) and so its not so useful for testing. CrissCross will happily connect to it if you install it though. However these notes focus on the non-R2 version.

Microsoft SQL Server 2008 Express with Advanced Services can be downloaded from here: http://www.microsoft.com/en-us/download/details.aspx?id=1842

Before running the installer, use Computer Management to create a local user for the Sql services. A normal user will do - the Sql Installer will sort out the permissions.

A word of warning: The Sql 2008 Express installer is a little bit shaky on 64-bit - it displays incompatibility warnings and sometimes crashes early on the first time you try and run it, but then will run the second time. However it usually works in the end.

When running the Sql Installer:

- Let is use the 'SQLEXPRESS' name for the instance
- Make sure you set the Data Directory to the D: drive (however it wont let you change the shared features directory)
- Make sure you include the SSRS and Full Text Search components
- You'll also need BIDS and the Basic Tools to get the sample reports working
- On the page where security options are set, go to the Filestream tab and enable Filestream (just the first checkbox is enough)

After installing it, download and install SP1 http://www.microsoft.com/en-us/download/details.aspx?id=20302

Test SSRS Report Manager

You should now have a functioning SSRS server. To check it:

Start the 'SSRS Configuration Manager'. This will tell you what the URL for ReportManager is.

Because we're using SqlExpress, its likely to be something like:

http:\\localhost\Reports_SQLEXPRESS

Later on we'll also need to know the Web Service URL. Its likely to be something like:

http://localhost/ReportServer_SQLEXPRESS

Use Internet Explorer to browse to the Report Manager url
http:\\localhost\Reports_SQLEXPRESS and check that it responds.

You should see the Report Manager 'Home' folder with no reports listed.

NB: There seems to be a glitch with IE Windows Authentication with this setup, so sometimes you need to run IE as Administrator to get SSRS to behave properly. For example, if Report Manager is not showing you the 'Site Settings' link in the top right, close IE and right click on the IE icon and choose 'Run as Administrator', then go back into Report Manager.

AdventureWorks databases

Microsoft maintain a set of example databases for Sql Server known as AdventureWorks. There are various versions for the different editions of Sql Server.

Sample databases are actually held on CodePlex:

Sql Server Database Product Samples http://msftdbprodsamples.codeplex.com/

There are various downloads available, I'd recommend the Samples Refresh 4 one for Sql Server 2008: http://msftdbprodsamples.codeplex.com/releases/view/37109

Download the installer and run it. It should let you install all of the test databases except for the 4 OLAP ones (because our SqlExpress server does not have OLAP).

The main one that we will use for the SSRS samples is the one labelled 'AdventureWorks OLTP 2008' which generates the database called AdventureWorks2008

AdventureWorks Sample Reports

Microsoft also maintain sample SSRS reports for AdventureWorks, also on CodePlex.

Sql Server Reporting Services Product Samples http://msftrsprodsamples.codeplex.com/

There are various downloads, I'd recommend the Samples Refresh 3 one for Sql Server 2008: http://msftrsprodsamples.codeplex.com/releases/view/34047

Download and run the installer - it simply unpacks some files.

We're interested in the AdventureWorks Sample Reports which will get unpacked to:

C:\Program Files\Microsoft SQL Server\100\Samples\Reporting Services\Report Samples\AdventureWorks Sample Reports

Copy that folder to somewhere else (D: drive) and then open the Solution file in BIDS.

Before we can build and deploy, we need to change a few settings:

- Open up the Shared Data Source called AdventureWorks2008.rds
- The connection string needs to be tweaked to cope with SqlExpress - instead of 'local' point it to '.\SQLEXPRESS'
- Open up the Project Properties
- Change OverwriteDataSources option to True
- Change TargetServerURL to the SSRS Web Service URL (something like http:\\localhost\Reports_SQLEXPRESS )

Now try and run the Company Sales 2008.rdl report in the Preview tab of BIDS - it should work OK.

To deploy the reports:
- Switch from Debug to Release config
- You may need to change the Project Properties again as described above (OverwriteDataSources and TargetServerURL)
- Select Build -> Deploy from the menu

If you get a deploy error along the lines of 'Error rsAccessDenied : The permissions granted to user 'user' are insufficient for performing this operation.' then:
- close Visual Studio
- Run Visual Studio as administrator
- Load the solution and try the deploy again

Test Samples Reports in SSRS Report Manager

You should now have a functioning SSRS server with some Sample Reports.

Use Internet Explorer to browse to the Report Manager url
http:\\localhost\Reports_SQLEXPRESS and check that it responds.

If you just see the Report Manager Home page with none of the Sample Reports listed, try running Internet Explorer as Administrator (as described above).

If everything is working, Report Manager should show you a folder called 'AdventureWorks 2008 Sample Reports', and if you click through to it, the eight sample reports should be there.

Run a few of the sample reports to make sure they are working.

NB: I found that the SalesTrend2008 report didn't work in report manager, but the other 7 did.

Install CrissCross

Now that we have a working SSRS server with some reports, we should be able to add CrissCross to the server and have it run the report too.

General setup instructions for CrissCross are detailed in Getting Started, but for this specific server, the following steps are needed:

- Start IIS Manager
- Create a new AppPool called CrissCrossAppPool. The default Integrated Pipeline mode is OK. In the advanced settings, change the Identity to NetworkService
- Create a folder for CrissCross on the D: drive (e.g. D:\WebSites\CrissCross)
- Add an Application for CrissCross in IIS, mapping it to the folder you just created
- In IIS, click on the 'IIS - Authentication' icon for the CrissCross app. Set Anonymous Authentication to Disabled and set Windows Authentication to Enabled
NB: If Windows Authentication does not appear as an option, you need to install it - go to Server Manager and add the Windows Authentication Role Service for the Web Server Role.
- Download the latest CrissCross zip from here http://crisscross.codeplex.com/releases
- Unpack it and copy the contents of the 'PublishedWebApp' folder to your CrissCross folder on the D: drive
- Make the following changes to the web.config file:
  <add key="crisscross.ReportServerRootUrl" value="http://localhost/ReportServer_SQLEXPRESS" />
  <add key="crisscross.ReportServerWebServiceUrl" value="http://localhost/ReportServer_SQLEXPRESS/ReportService2005.asmx" />

  <!-- ... and a bit further down ... -->

  <add name="ReportServerDb" connectionString="Server=.\SQLEXPRESS;Database=ReportServer$SQLEXPRESS;Trusted_Connection=True;"/>

NB: These values (location of SSRS web service, name of database etc) can be checked in SSRS Configuration Manager

Now start IE - if you have had to run IE as Administrator to get Report Manager (above) to work, then you'll have to do the same to get CrissCross to work.

Then browse to http://localhost/CrissCross and see if CrissCross starts OK.

The home page should come up and then you should be able to run the reports just as you could in Report Manager.

A more interesting Sample Report

So CrissCross is now working, but most of the AdventureWorks sample reports are pretty boring - they just have one or two parameters, and there are no multipicks. CrissCross really shows it usefulness when its running reports with lots of parameters and reports that use multipicks. So here's a way to get some more interesting sample reports:

- Download this attached VS2008 solution: CrissCrossSampleReports.zip
- Unpack to a folder
- Follow the instructions in the ReadMe.txt file, which says:

- Run the script 'Create_SalesReportFlexi.sql' against the AdventureWorks2008 database to create the strored procedure and function needed for the report
- Copy the file ExtraConfig.xml over to your CrissCross\App_data folder (to replace the one already there). This gives CrissCross a few extra hints about how to handle the report parameters.
- Open the CrissCrossSampleReportSol in Visual Studio
- Ensure the project properties are set to deploy to the right SSRS server (as described above for the other sample reports)
- Ensure the AdventureWorks2008 Data Source is pointing to the right database (as described above for the other sample reports)
- Select Build -> Deploy

NB: As with the other sample reports, if you have problems deploying, try running VS2008 as Administrator.

In ReportManager and in CrissCross you will now see a new folder called 'CrissCrossSampleReports' that contains a report called 'SalesReportFlexi'.
This is a report with four (optional) date parameters and four (optional) multi-pick parameters. It demonstrates how CrissCross handles reports with lots of parameters and reports with multi-picks.

Last edited Aug 31, 2012 at 3:33 PM by codeulike, version 13

Comments

No comments yet.