Skip to content

Decrease the margin in a chart report

Sometimes you want the chart are to stretch all the way to the left edge of the report. Today I wanted that! It took some time for me to figure out how to do this and here is´the solution.

image

Select the Chart and in the Properties window click the ellipse button for the property ChartAreas.

image

Expand the property CustomPosition and change Enabled to True and set the property Left to 0

image

 

This will make the chart area move to the left of the chart as shown in the image below.

image

Advertisements

Create a “Show details” table in Reporting Services 2008

Sometimes it can be a good idea to show users an overall view of data. But sometimes this isn’t enough and the user wants to se more information, i.e. columns.

If you have a tablix with static columns and want to be able to show more columns when a user click “Show details” your first approach might be to select the column and set an expression of the hidden property as below.

1

2

When you select a column like this it is the contents of the column that is selected (i.e. textboxes) and not the column itself. Therefor when you run the report the columns content will be hidden but not the column itself.

3

 

The solution

To hide the actual table column you must first put the Column Groups section on the design surface to advanced mode by first clicking the little triangle and then select Advanced mode as the picture below shows.

4

In the list select the column you want to hide and set the Hidden property to True and the ToggleItem to the item that should toggle visibility.

5

As a result the table will now “shrink” the entire table width and make the table look like we wanted it to.

6

Query a cube in T-Sql

To query a cube in T-Sql you will have to use OPENROWSET, or OPENQUERY. The query returns a “flattened rowset” and not a multi dimensional result set.

With OPENROWSET it looks like this.

SELECT a.*
FROM OpenRowset(‘MSOLAP.4′,’DATASOURCE=TESTDEV; Initial Catalog=Adventure Works DW 2008;’,
‘SELECT Measures.[Internet Sales Amount] ON COLUMNS,
[Product].[Category].members ON ROWS
FROM [Adventure Works]’) as a

If you want to use OPENQUERY you must first add a linked server and then use it in the query.

EXEC sp_addlinkedserver
     @server=’OLAP_LINKED_SERVER’,
     @srvproduct=”,
     @provider=’MSOLAP’,

     @datasrc=’TESTDEV’,
     @catalog=’Adventure Works DW 2008′

go

— MDX in OPENQUERY —

SELECT * FROM OPENQUERY(OLAP_LINKED_SERVER,
‘SELECT Measures.[Internet Sales Amount] ON COLUMNS,
[Product].[Category].members ON ROWS
FROM [Adventure Works]’ )

If you want to use dynamic MDX you must execute the whole query (not just the OPENROWSET) as a string as described in this blog post

Free text parameter in Reporting Services

Let’s say you have a report getting its data from a SSAS cube. In the cube you have a date dimension.

Now. Imagine that you want a parameter pointing to the date dimension and the parameter cannot be a “select” parameter and it must be NON-QUERIED, i.e. the parameter is a free text field.

 

To make this work you first have to create a parameter, lets call it Year. The data type is set to “Text”, available values is set to “None” and Default values is set to “No default value”.

From here there are to alternatives:

  1. Build the parameter string manually.
  2. Send the string directly to the cube.

 

Solution 1

Build your dataset using the query designer. Drag the Year attribute to the filter pane in query designer. Check the box in Parameters column and you should see a query similar to this when you switch over to “Query mode”.

SELECT NON EMPTY { [Measures].[Internet Sales Amount] } ON COLUMNS, NON EMPTY { ([Date].[Calendar Year].[Calendar Year].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOSET(@DateCalendarYear, CONSTRAINED) ) ON COLUMNS FROM [Adventure Works]) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

To resolve the error “Could not create a list of fields…” with the detailed error “Query (1, 215) The restrictions imposed by the CONSTRAINED flag in the STRTOSET function were violated.” that occurs when you click Ok, you must edit the query string manually.

image

STRTOSET(@DateCalendarYear, CONSTRAINED) should be edited to STRTOSET(@DateCalendarYear).

When the query is done go to the Parameters tab. For the Parameter DateCalendarYear set the value to ="[Date].[Calendar Year].&[" & Parameters!Year.Value & "]".

This statement returns a string pointing to a member in the Calendar Year hierarchy in the date dimension.

 

Solution 2

This solution is more clean and a little bit simpler. I haven’t done any performance comparisons so I don’t know which one is the fastest.

Create a new dataset using “Query Mode” and use the following query.

Select
[Measures].[Internet Sales Amount] on columns,
Filter(
    {[Date].[Calendar Year].Members},
     [Date].[Calendar Year].member_value = @Year)
      on rows
from [Adventure Works]

image

This query fetches the correct Years with the MDX filter function, just returning the year corresponding to the parameter.

Go to the Parameters tab and for the parameter Year, select @Year, created earlier in this example.

image

 

The final result look like this.

image

Description on calculated measures

To get a description on a calculated measure you either install BIDS helper and follow this little guide http://bidshelper.codeplex.com or you do the following.

In the XML code for the cube find the tag <CalculationProperties>. Inside this tag it should look something like:

        <CalculationProperty dwd:design-time-name="b85f38de-e526-4e67-89ba-8a399e0572b1">
          <CalculationReference>[Measures].[MeasureName]</CalculationReference>
          <CalculationType>Member</CalculationType>
          <Translations>
            <Translation dwd:design-time-name="a2d45a9d-8213-4d44-9db6-511a97efb363">
              <Language>3082</Language>
              <Caption>Porcentaje de Descuento</Caption>
            </Translation>
          </Translations>
          <Description>Measure Description Here</Description>
        </CalculationProperty>

 

If the measure isn’t there you will have to do some magic. Create a translation for the measure; this puts the measure in the XML then when you have made the XML to look like above you remove the translation again.

Wrong decimals in SSAS

Calculated measures can sometimes freak out regarding decimals. For example, if you have a calculated measure from a division of two measures the formatting can be the format string you set on the calculation or it can be inherited from either the numerator or the denominator.

To solve this problem put the following code snippet somewhere under the create script of the calculation.

Scope
(
[Measures].[MeasureName]
);
Format_String(This) = "#,#.0000";
End Scope;

Successful BI project – Step 3 – Architecture

The model below is a schematic description of the refining process steps that source data pass through on its way to the end user. The name of the steps indicates their main purpose and responsibility of the components which are active in each step.

Solution overview

Source

To make it easier to read source data there has to be rules defining how and when data will be sent to the Data Warehouse. There also has to be rules about how the information itself should look, column names, formats etc. Those rules must be set based on the needs and requirements present.

A person responsible for the source system should also be selected and that person should see to that the rules are followed and that the developers get the information they need. To get the correct data delivered a source system analysis must be performed with a person having good knowledge about the source system.

My recommendation is to let the source system “deliver” data as opposed to letting the BI-system fetch data directly from the source system. I say this because the responsibility that the correct data is delivered should be on the source system owner. This also requires the source system to maintain the data extractions when changes in the source system occur.

ETL

To get a scalable and functional BI system there has to be an architecture supporting this. The solution I use consists of four different areas that in turn contain tables for storage of information for different information and purposes. I use the term areas because the implementation can look different depending on the platform you are on. The areas can for example be schemas or databases.

The picture below shows what it could look like.

Dragspel 

Source

This is the area where data should be delivered. It could be delivered as a text file on the file system or in a table in a database. Data in this area is stored exactly as the source system delivers it and isn’t changed.

Stage

Stage area is a temporary storage area used by the ETL process. For data to enter the stage area it has to pass defined rules. The rules can be that a field can’t be null, that a value must be in a certain span or that a date should be in a certain month. If the row is approved it can enter the stage area. Otherwise the row will be rejected and put in an error table along with a date and an error message.

Data Warehouse (Storage)

The Data Warehouse must be built to be scalable and flexible. Data from different source systems will be brought together in this area into a common structure. Fact (transactions) shall be stored at lowest level and the source systems natural key should be stored in a column as a unique key. This way you get a link back to the source system if needed.

My recommendation is not to let users access this area but to create a data mart for that purpose.

Star (Access)

Data in this area is stored in a star schema with dimensions and fact tables to make a foundation for reports and OLAP cubes and to be easily understandable for end users.

Dimensions have a simple primary key whereas fact tables have a composite key including the dimension keys. Data in fact tables should be aggregated to the level needed.

Configuration

Mappings and translations should be saved in a separate database. Mappings can be that one product from one system must be mapped to a product from another system. A translation can be from one product “P01” to a more understandable name like “Bicycle”. The reason for this area not being part of another area is that configuration data shall be persistent and that end users might change data in this area (i.e. Mappings and translation might change over time).

Function

The function area refers to the task of enabling different types of interactions with information from the Data Warehouse. In this area there are a bunch of suppliers, standards and tools available. Generally you can say that OLAP and standard reports are typical tools used but AdHoc query, data mining, simulation and Geographic information system (GIS) can be part of the function area.