ReportViewer control in MVC

Continuing from my previous post, I wanted to make my reports available in my ASP.NET MVC site. I read about the ReportViewer control that it works only on webformst (ASPX pages), due to viewstate requirements. Luckily, webforms ges can be added to an MVC project. But when I started passing values to the report parameters, the page started some kind of inifite loop. That was because I had forgotten about webforms development and that you must often have a check for postback in code-behind. Like this:

    public partial class ReportWebForm : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                ReportViewer1.ServerReport.ReportServerUrl = new Uri(Properties.Settings.Default.ReportServerUrl);
                var userUnit = Session[LoginModel.EnhetSessionKey] as Enhet;
                if (userUnit == null)
                {
                    ReportViewer1.ServerReport.SetParameters(new Microsoft.Reporting.WebForms.ReportParameter("Unit", "[Organisation].[Organisation-Enhet].[All]"));
                }
                else
                {
                    ReportViewer1.ServerReport.SetParameters(new Microsoft.Reporting.WebForms.ReportParameter("Unit", string.Format("[Organisation].[Organisation-Enhet].[Enhet].&[{0}]", userUnit.Id)));
                }
            }
        }
    }

Date Ranges in SSRS and SSAS

I have taken my first steps in Reporting Services (SSRS) land. The reports use data from a multidimensional cube in Analysis Services (SSAS). Of course, one requirement was to filter the reports on a date range. Should be simple, right? Well, at least not for me.

When designing the reports, I created a new dataset, used the MDX query designer and added my date dimension as a filter. I choose Range (Inclusive) and checked the two parameter checkboxes. Visual Studio automatically added these parameters both to the dataset and the report.

The problem was that the key of my dimension was a date column, but the tool added text parameters and choose available values from a query. That wasn’t what I wanted. I wanted a calendar drop down. So I changed the parameters to date and as available values I choose None. OK, now I had the calendar. But the report didn’t work. I got “The restrictions imposed by the CONSTRAINED flag in the STRTOMEMBER function were violated”.

It turned out I had to use a completely different expression for the dataset parameter expression. The expression should return a string that contains a dimension filter. I tried:

="[Datum].[Datum].&[" + Format((Parameters!StartDate.Value), "yyyy-MM-dd") + "]"

(My dimension is called “Datum” and so is my key attribute.)

It didn’t work. I got an error saying 2014-05-01 couldn’t be converted to the type date. Now I thought that SSAS was using US formatted dates internally, so I tried “MM-dd-yyyy”. Didn’t work either. After some Bing research I finally managed to find the solution: include time in the string as well:

="[Datum].[Datum].&[" + Format((Parameters!StartDate.Value), "yyyy-MM-ddTHH:mm:ss") + "]"

By the way, here is how to set the default for the report parameters to start and end of previous month:

=DateAdd(DateInterval.Month, -1, DateSerial(Year(Now()), Month(Now()), 1))
=DateAdd(DateInterval.Day, -1, DateSerial(Year(Now()), Month(Now()), 1))

Microsoft Azure Automation

Microsoft recently added a very useful feature to automate things in Azure. It is in preview at the time of writing, but I decided to try it for starting and stopping our virtual machines used for testing, and it works really well. Here is a link to a step-to-step guide: http://blogs.technet.com/b/keithmayer/archive/2014/04/06/step-by-step-getting-started-with-windows-azure-automation.aspx

Here is my “workflow” for starting:

workflow Start-nnn
{
    # Specify Azure Subscription Name
    $subName = 'nnn'
    # Connect to Azure Subscription
    Connect-Azure -AzureConnectionName $subName
    Select-AzureSubscription -SubscriptionName $subName

    # Start VMs
    $vmList = ('vm1','vm2')
    For ( $vmCount = 0; $vmCount -lt $vmList.Count; $vmCount++) {
        Write-Output ("Getting virtual machine status for {0}..." -f $vmList[$vmCount])
        $vm = Get-AzureVM -ServiceName $vmList[$vmCount] -Name $vmList[$vmCount]
        if ( $vm.InstanceStatus -eq 'StoppedDeallocated' ) {
            Write-Output ("Starting {0}..." -f $vm.Name)
            Start-AzureVM -ServiceName $vm.ServiceName -Name $vm.Name
        }
    }
}

And for stopping:

workflow Stop-nnn
{
    # Specify Azure Subscription Name
    $subName = 'nnn'
    # Connect to Azure Subscription
    Connect-Azure -AzureConnectionName $subName
    Select-AzureSubscription -SubscriptionName $subName

    # Shutdown VMs
    $vmList = ('vm1','vm2')
    For ( $vmCount = 0; $vmCount -lt $vmList.Count; $vmCount++) {
        Write-Output ("Getting status for {0}..." -f $vmList[$vmCount])
        $vm = Get-AzureVM -ServiceName $vmList[$vmCount] -Name $vmList[$vmCount]
        if ( $vm.InstanceStatus -eq 'ReadyRole' ) {
            Write-Output ("Stopping {0}..." -f $vm.Name)
            Stop-AzureVM -ServiceName $vm.ServiceName -Name $vm.Name -Force
        }
    }
}