VB function to convert a list of items into an MDX set

Say you have a  report parameter called @Scenario, which can have multiple values listed as: Actual, Budget, Forecast, which is an array. This array needs to be converted to an MDX set before it’s passed into the main report query. The following function does that conversion to {Actual, Budget, Forecast}.

 

Public Shared Function ListToSet(sList As Array, Brackets as Boolean) as String
  Dim sSet As String
  Dim sVal As String
  Dim i As Integer

  sSet = ""

  For i = 0 To sList.Length – 1

    if Brackets = true then
 sVal = "["
    else
        sVal = ""
    end if

    sVal = sVal + sList(i)

    if Brackets = true then
 sVal = sVal +  "]"
    end if

    sSet += Iif( sSet = "", "{", ", " )

    sSet += sVal

  Next i

  sSet += "}"

  Return sSet
End Function

 

 

To use this in the report RDL:

=Code.ListToSet(Parameters!ReportMeasure.Value, true) & " * " & Code.ListToSet(Parameters!Scenario.Value, false)

 

Read Full Post »

How to write an MDX query to return a list of fields in the cube dimension required for a report parameter? (MDX)

I have a report parameter named @Scenario and I would like to get its available values from a query. In SQL, this is simple and easy, however, not the case if you would like to write it in MDX.

 

To write it in MDX, use UNIQUENAME and NAME built-in MDX functions (see below). The UNIQUENAME function returns the unique name of Dimensions, Hierarchies, Levels, and Members.

 

WITH MEMBER

[ScenarioUniqueName] AS [Scenarios].CURRENTMEMBER.UNIQUENAME

          MEMBER

[ScenarioEnglishCaption] AS [Scenarios].CURRENTMEMBER.NAME

          SET

CalcScenarios AS [Scenarios].[Scenario].ALLMEMBERS

 

SELECT

{[ScenarioUniqueName], [ScenarioEnglishCaption] } ON COLUMNS

,CalcScenarios 

ON ROWS

FROM

InSiteCube

 

 

Read Full Post »

How to hide an empty point in a line chart of Dundas? (SSRS 2008)

The answer is in the following website:

 

 http://support2.dundas.com/Default.aspx?article=1059

 

Read Full Post »

How to code Dundas chart so that users can select the chart type with or without 3D using report parameters?

Even though Microsoft has included Dundas into Reporting Services 2008, but it is not a full version. Coding dundas objects requires the installation of the full version of Dundas.

 

 

How to code Dundas chart so that users can select the chart type  with or without 3D using report parameter:

1. Create a report parameter called @ChartType with AvailableValues of the different chart types and @Enable3D with True and False as the available values.

2. Add Dundas Chart then right click and "View Code".

3. Select "Code Parameters" tab then click on the plus button to add a new parameter.

 

 

4). Go to "Source Code" tab then select PostApplySeriesData event. Copy and paste the following code in C#. Compile code.

 

if( ! codeParams.ContainsKey("ChartType") )
    return;

//Set the Chart TYpe based on parameter ChartType
try
{
    series.Type = (Dundas.Charting.WebControl.SeriesChartType)Enum.Parse(typeof(Dundas.Charting.WebControl.SeriesChartType), codeParams["ChartType"].ToString());
}

// do nothing if the value of the parameter is not a valid boolean
catch
{
    series.Type = Dundas.Charting.WebControl.SeriesChartType.Line;
}

 

5). Go to "PostApplyData" event then copy and paste the following code. Compile code.

 

// check if the parameter exist
if( ! codeParams.ContainsKey("Enable3D") )
    return;

//Set the Enable3D flag
try
{
    chartObj.ChartAreas[0].Area3DStyle.Enable3D = bool.Parse(codeParams["Enable3D"].ToString());
}

// do nothing if the value of the parameter is not a valid boolean
catch
{

}

 

 

 

 

 

 

Read Full Post »

Cube Deployment error: unable to connect to the data source

I have been working on this cube on my local machine on Analysis Server 2008. and able to deploy and process the cube without any problems.

 

Whilst deploying a cube over a VM server, I had issue with connection. All credentials were set up properly. I could explore data from the data source view. However, I  could not deploy the cube successfully.

 

Resolution:

The server that I am deploying to is SQL/Analysis Server 2008, same as my local server.

The Data Source type on the project file has to be set to Native OLE DB\SQL Server Native Client 10.0 (for SQL Server 2008). It was previously set to Native OLE DB\SQL Server Native Client. As soon as I changed the data source type, the deployment went successfully.

 

The strangest thing is the deployment to my local machine has always been successful with data source type of Native OLE DB\SQL Server Native Client. What the .. ?

 

Read Full Post »