SSRS reports on the IPad or IPhone has been improved since SQL 2012 SP1

Almost 2 years ago I wrote a blogpost about SQL Server Reporting Service (SSRS) Reports on the IPad or IPhone. I was the first time you could display SSRS reports on the IPad. Unfortunatly not everything was rendered in a correct way. In SQL Server 2012 SP1 this has been improved.

Here is an example of a report in SQL 2008 R2



 Here is an example of the same report in SQL 2012 SP1

As you can see, the strange Blue Question marks are gone.

Starting with SQL 2012 Service Pack 1 (SP1), Reporting Services supports viewing and basic interactivity with reports on Apple iOS devices like IPad en Iphone, with the Apple Safari browser.
Viewing reports in Report Manager (http://myserver/reportserver) is not suppported. You need to start the reports from the report server via http://myserver.reports. Here you can browse to the report and tape the report name to open the report. After opening the report yiu can see that the Export to PDF and TIFF file is supported. More information about the support for Apple iOS devices on SSRS can be found here.
For a video of using SSRS on your Ipad please see:

PowerPivot fieldlist is grey and not selectable

Sometimes my PowerPivot field list is grey and can't be selected. It can happen when you are busy in an existing PowerPivot sheet. It looks like a bug because a restart of Excel solved my situation.

Solution:

• Save your Power Pivot Sheet
• Close all other Excel sheets
• Close Excel
• Check in task manager if Excel.exe is not running
• If Excel.exe is still running, kill this task
• Start Excel
• Open your Power Pivot sheet
• Click on a graph and you should be able to select the Field list.

Unfortunatly, is is not clear when and how this happens. I'm using Power Pivot version 11.0.3000.0

Power Pivot: The PivotTable report will not fit on sheet.

During the refresh of data in an Excel Power Pivot next error can occur:

The PivotTable report will not fit on the sheet. Do you want to show as much as possible?

In the past I created an Excel Power Pivot file with multiple sheets. On every sheet one or more pivots or pivot charts are defined. Big question for me: On which sheet does this error occur? Current error message is to general. It does not tell on which sheet the error occurs. After a while I found the root cause of this problem. In the past I made a Pivot chart on some data. The datasheet of this Power Pivot Chart was hidden. Later on I deleted the sheet with the Pivot Chart. The datasheet is not automatically deleted. In this situation the datasheet was still availabel as hidden sheet. I unhide the sheet. When I looked to the data, it contains a big amount of columns.  (Over 256 columns). This sheet with more than 256 column is the root cause of this error. Because the initial Pivot chart sheet was already deleled, I deleted the datasheet. After deleting the datasheet, I was able to refresh all data in my Power Pivot sheet.

Solution: check all datasheets in your Power Pivot sheet for pivots with more than 256 columns.

The alternative for Zipping your SQL backups. Save download and restore time.

Sometimes you need a SQL database backup to analyze. To minimize the download time people compress the SQL database backup with tools like WINZIP, WINRAR, 7ZIP, ARJ etc...... This is nice but there is a more efficient way. First of all, I will explain the download and restore process of a WINZip backup.
You need to uncompress the database backup file before you can start the restore process it self. Example: you receive a SQL database backup of 50 Gb which is compressed to 5 GB. To restore this database, you need much more diskspace:

• 5 Gb for the zip file
• 50 Gb for the backup file
• 50 Gb for the restored database. (assume their is no empty space in the database)

In total 105 Gb of diskspace is needed.


The more efficient way. Use the Backup compression feature. Backup time is much faster because less disk IO is needed to write the backup file. A compressed backup file can be restored without a seperate uncompress proces. This will save a lot of disk space. In the previous example 50 Gb because you do not need to uncompress the WINZIP file. You will receive a 5Gb database which you can directly restore to the 50 GB database file(s).

In the option tab of the Backup database window you will find at the buttom the Set backup compression option. By default it is set to Use the default server setting. You can change this to Compress backup.

On server level you can change the default compression setting to compressed. Retrieve the server properties of the SQL server. Select the Database Settings property. Check the Compress backup checkbox.

As of now every SQL backup will be compressed.

By default, compression significantly increases CPU usage, and the additional CPU consumed by the compression process might adversely impact concurrent operations. Backup compression was introduced in SQL Server 2008 Enterprise. Beginning in SQL Server 2008 R2, backup compression is supported by SQL Server 2008 R2 Standard and all higher editions. Every edition of SQL Server 2008 and later can restore a compressed backup.

More information can be found here.

SQl 2008 R2 Setup fails: ExecuteStandardTimingsWorkflow

I tried to install SQL 2008 R2 Enterprise Edition X64 on my laptop which has a brand new image of Windows 7. All Windows updates are installed. I started the setup of SQl 2008 R2 from the root of my installation DVD. I run the setup with the option 'Run as Administrator'. During the Setup Support Files, the setup progress hangs on: ExecuteStandardTimingsWorkflow.

After a while next error while occur: Error writing to file: X86_Microsoft.VC80.CRT_1fc8b3b9a1e18e3b_8.0.50727.4027_xww_e69378d0.cat Verify that you have access to that directory.

Big question: Which directory?

Solution: Your installation DVD is corrupt. Take another installation DVD to complete succesfully the setup of SQL Server 2008 R2.

Script to generate a time dimension table to use in Power Pivot.

In SQL Server 2012 Power Pivot a new feature is introduced. You can Mark as Date Table. This will enable you to leverage date filtering in Excel. For instance, you can see the revenue totals grouped by different date groupings. For instance per week number, per month, week day number etc. To use this feature you need to have a Time Dimension table.

• Create a time dimension table.  (See later in this blog how to do)
• Mark this table as Date table.
• Link from your Revenue table the column invoicedate to the key of the Time Dimension table.

Now you are ready to use this feature.

To create a Time dimension table you can a script (GlobeBI_DimTime.SQL) which can be downloaded from here. At the end of the script you can specify the start date and end date of the Time dimension entries. 


INSERT INTO Dimtime
SELECT * FROM dbo.F_TABLE_DATE ('20000101','20201231')

In the script Date entries are created from 1 Januari 2000 up to 31 December 2020.

How to use Dynamic data sources in your SSRS report.

You can have situations in which your report should be executed on multiple databases. The user should be able to select the desired database on which the report should run. In this blog post I will explain how you can do this.
To be able to select to different database you need to make use of a dynamic data source. A data source makes use of a connection string. It is possible to pass the connection string of a data source as an expression. By using an expression, you can make use of parameter values to pass the servername and database name to the connection string. There is only one restriction of a dynamic data source. The data source should be embedded within the report. It can not be implemented with a shared data source. In this blogpost I will use a second database in which I retrieve the available SQL server\databases on which my reports should be executed.

• Open your report
• Add 2 report parameters
• ServerName
• DatabaseName
• Add datasource named: DynamicDataSource. Use the a 'hard coded' connection string. For instance:  Data Source=MySQLServer1;Initial Catalog=MyDatabase1.

• Add datasource to the database with all SQL Server\databases. In my example named: Synergy
• Add a embedded dataset to retrieve SQL Server and Database information.
  
  
• Configure the available values for the report parameters: ServerName and DatabaseName.
  
  
• Add all datasets and report items to your report.
• Test your report using the 'hard coded' connection string.
• If everything works fine, change the 'hard coded' connection string with next expression
  ="data source=" & Parameters!ServerName.Value & ";initial catalog=" & Parameters!DatabaseName.Value
  
  
• Run the report and select a value for the report parameters ServerName and DatabaseName 

Enjoy it.

Bluescreen caused by CIPCDDDP.SYS after rebooting Windows 7 Enterprise X64

After rebooting my laptop which runs Windows 7 Enterprise edition (64 bits) I got blue screens (BSOD) caused by driver CIPCDDDP.SYS. I could only boot in safe mode. On another computer I found that the CIPCDDDP.SYS driver is part of the Cisco IP Communicator V7. In Safe mode it was not possible to unistall this driver. This driver is loaded when network drivers are loaded. To avoid this process

• I undocked my laptop
• Disabled my wireless network using the wireless network swith on my Dell Latitude E6410.
• Now I was able to boot without a bluescreen. 
• After booting I enabled the wireless network card.
• Update my Cisco IP Communicator client to version 8.6.2.

After booting I did not get a BSOD but after a while my laptop did not respond to any keyboard input. Therefor I disabled again my wireless network. After booting successfully, I enabled my wireless network card and started Cisco IP Communicator 8.6.2. successfully and was able to make a phone call.

So everything is working again after 90 minutes. I hope this workaround will also help for you if you experience this Blue Screen of Death (BSOD).

Microsoft® SQL Server® 2008 R2 Service Pack 2 available for download

Service Pack 2 for SQL Server 2008 R2 is available for download, it includes product improvements based on requests from the SQL Server community and hotfix solutions provided in SQL Server 2008 R2 SP1 Cumulative Updates 1 to 5. A few highlights are as follows:

• Reporting Services Charts Maybe Zoomed & Cropped Customers using Reporting Services on Windows 7 may sometime find charts are zoomed in and cropped. To work around the issue some customers set ImageConsolidation to false. 
• Collapsing Cells or Rows, If Hidden Render Incorrectly Some customers who have hidden rows in their Reporting Services reports may have noticed rendering issues when cells or rows are collapsed. When writing a hidden row, the Style attribute is opened to write a height attribute. If the attribute is empty and the width should not be zero.

You can download SQL Server 2008 R2 Service Pack 2 from here.
Succes with upgrading your SQL Server with this Service Pack.

Which visualizations should I use in my dashboard or cockpits ?

With SSRS you can build very nice reports. You can make use of a lot of different report types like:

• Table
• Tablix
• Chart
• Gauge
• Map
• Data Bar
• Sparkline
• Indicator

Every report type has it's own purpose. To make your reports a success, the report should directly tell you what you need to know. One aspects of this, is to apply the dashboard design rules to your reports.
Another aspects is to choose the best report type. I will use a tree stucture you can use to select the best report type.  For your report you should answer yourself some questions about the report:

1. Comparison
1. Among Items
1. Two variables per Item: Variable width column chart
2. One variable per Item
1. Many categories: Table
2. Few categories
1. Many Items: Bar Chart
2. Few Items: Column Chart
2. Over Time
1. Many Periods
1. Cyclical data: Polar Chart
2. Non Cyclical data: Line Chart
2. Few Periods
1. Single or Few categories: Column Chart
2. Many categories: Line Chart
2. Relationship
1. Two variables: Scatter chart
2. Three variables: Bubble chart
3. Distribution
1. Single variable
1. Few Data Points: Column Histogram
2. Many Data Points: Line Histogram
2. Two variable: Scatter Chart
3. Three variable: 3D Area Chart
4. Composition
1. Changing over time
1. Few Periods
1. Only relative difference matters: Stacked 100% Column Chart
2. Relative and Absolute difference matters: Stacked Column Chart
2. Many Periods
1. Only relative Difference matters: Stacked 100% Line Chart
2. Relative and Absolute difference matters: Stacked Line Chart
2. Static
1. Single Share of Total: Pie Chart
2. Accumulation to total: Waterfall Chart
3. Components of Components: Stacked 100% Column Chart with Sub components.

A nice picture of this tree structure can be found on : http://extremepresentation.typepad.com/photos/uncategorized/choosing_a_good_chart.jpg

Click on the picture to enlarge.

Some observations:

• Pie Charts are ONLY used to show Simple Share of Total.
• Over Time Difference between few periods (Column charts)  and many periods (Line Charts).
• 3D is ONLY used to display distribution of 3 variables. 
• Few variables (Column Histogram) versus Many variables (Line Histogram)

Select the report type which is the best to show your data, therefor do NOT select a report type because it looks nice. If you want to have something which is nice, please buy a painting.

Enjoy using this tree structure, to select the best report type for you report.

How to upload a big PDF to your IPad

Today I received a new book to read. In the book, a DVD was included with a PDF version of the book. I really like this because now i'm able to read the book on my IPad. In past I send an email to myself and opened this email on my IPad. This gives me the opportunity to open the PDF with IBooks. However, the PDF of this new book is 25 Mb. This is too big to attach to my email, so I have to look to another way get the PDF on my IPad.

How to do this?

• Upload the PDF file to your Skydrive account.(https://skydrive.live.com/)
• Install the Skydrive app on your Ipad\Iphone. (http://itunes.apple.com/us/app/skydrive/id477537958?mt=8)
• Open the Skydrive app and login with you skydrive credentials.
• Open the PDF.
• Click on the reply icon in the left bottom.
• Select Open in other app.
• Accept warning that it can take a while to open files bigger than 2 Mb.
• Select IBooks

That's all you need to do. Happy reading on your IPad.

SQL Azure connection termination causes.

Microsoft SQL Azure database is a clud based relational database service that's hosted and maintained by Microsoft. Multiple databases from different customers are hosted on the same server. To garantuee performance for all these different customers, the SQL server should not come in an overload state. SQL Azure has the ability to monitor and rebalance active and online user databases. To achieve this, SQL Azure continuously gathers and analyze database usage statistics and will terminate connections when necessary. Throttling is a mechanism used by SQL Azure to prevent machines from becoming overloaded and unresponsive. Throttling can be divided in:

1. Soft throttling. This applies to the database consuming the most resources on the box. Soft throttling happens when a physical machine seems to be on the way of beig overloaded, unless its workload is reduced.
2. Hard throttling. This is the final stage of throttling. It happens when the machine is critically impacted due to overload. It terminates existing operations and prevents new ones until the metric returns below expected thershold.

This blog will summarize all connection terminations which can happen:

• Worker threads. When soft throttling limit for worker threads on a machine is exceeded, the database with the highest requests per second is throttled. Existing connections to that database are terminated and new connections to the database are denied, until number of workers drops below soft limit
• Database size. When the database space allocatted to user db is full, the user gets a db full error.
• Physical database space. When total database size on a machine exceeds 90% of total space available on machine, all databases become read-only. Load balancer ensures the situation is resolved by balancing databases across machines.
• Log bytes used. SQL Azure supports transactions generating log of up to 2 GB in size.
• Transaction log length. Uncommitted transactions can block the truncation of log files. To prevent this, the distance from the oldest active transaction log sequence number (LSN) to the tail of the log (current LSN) cannot exceed 20% of the size of the log file. When violated, the offending transaction is terminated and rolled back so that the log can be truncated.
• Transaction Lock Count. Sessions consuming greater than one million locks are terminated.
• Blocking System tasks. Transactions request locks on resources like rows, pages, or tables, on which the transaction is dependent and then free the locks when they no longer have a dependency on the locked resources. Due to these locks, some transactions might block resources required by system sessions. If a transaction locks a resource required by an underlying system operation for more than 20 seconds, it is terminated. In addition, any transaction that runs for more than 24 hours is terminated.
• Tempdb usage. When a session uses more than 5 GB of tempdb space, the session is terminated.
• Memory use. When there are sessions waiting on memory for 20 seconds or more, sessions consuming greater than 16 MB for more than 20 seconds are terminated in the descending order of time the resource has been held, so that the oldest session is terminated first. Termination of sessions stops as soon as the required memory becomes available.
• Too many requests. If number of concurrent requests made to a database exceed 400, all transactions that have been running for 1 minute or more are terminated.
• Idle connections. Connections to SQL Azure database that are idle for 30 minutes or longer will be terminated.
• Denial of service. When there are a high number of login failures from a particular source internet protocol (IP) address, SQL Azure will block the connections from that IP address for a period of time. The connection is terminated and no error is returned.
• Fail over issues. SQL Azure is flexible to cope with any variations in usage and load. The service replicates multiple redundant copies of data to multiple physical servers to maintain data availability and business continuity. In case of a hardware failure, SQL Azure provides automatic failover to optimize availability for your application. Currently, some failover actions may result in an abrupt termination of a session.

More details about all these termination causes and returned error messages can be found here.

How to create a timeline in a SSRS report.

Timeline reports can be very usefull to display what is happening during a specific time period. For instance, you have a server on which a lot of background tasks are running. In the beginning, you know which jobs are running at which moment. However, when the number of jobs are increasing, you will lose the overview. Especially when the execution times of the background jobs are taking longer after a while. A timeline report will help you to visualize all running background jobs. In SQL Server Reporting Services (SSRS) their is not a standard reporting item to make a timeline. However, there is a way to do this with the current report items. In this blogpost, I will explain how you can make a timeline report. I will use the example to display which backgroundjobs are running on which moment.

First of all we need to have a dataset which the execution times and durations of the backgroundjobs.
I have table named: Activitylog. In this table the starttime of the background job is registered including the execution time (duration).

SELECT StartTime, APP, Duration FROM ActivityLog

The report will have 4 filters to specify a date range. This date range is divided in a date and hour of thedate on which the backgroundjobs have been executed. (@MeasureDateStart, @MeasureDateEnd, @MeasureHourStart, @MeasureHourEnd)

This will results in a dataset which calculates the EndTime of the backgroundjob based on the StartTime and duration of the backgroundjob. It will use the parameters: @MeasureDateStart, @MeasureDateEnd, @MeasureHourStart, @MeasureHourEnd to select the data you need. All backgroundjobs will be displayed which have a start time and or end time within the selected time period.
SELECT AL.App, AL.StartTime,
DATEADD(ms, AL.Duration, AL.StartTime) AS EndTime, AL.Duration
FROM ActivityLog AS AL
WHERE (AL.StartTime >= @MeasureDateStart) AND
(DATEPART(hh, AL.StartTime) >= @MeasureHourStart) AND  
(DATEADD(dd, 0, DATEDIFF(dd, 0,DATEADD(ms, AL.Duration, AL.StartTime))) <= @MeasureDateEnd) AND
(DATEPART(hh, AL.StartTime) <= @MeasureHourEnd) 


The dataset is ready for use.

• Start Business Intelligence Development Studio(BIDS) and create a new report with a datasource to the database and define the dataset.
• All defined parameters in the dataset will be added automatically to the report.
• Add the report item Chart and select the Range Bar.
  
  
• Define the chart, Values: Startime ;Series Groups: App ;Category Groups Details.

• Configure the StartTime by opening the series properties.
  
  
• if desired you can configure the tooltip to show additional information.
• Configure the horizontal Axis to display the hours by opening the properties and select custom format: hh
  
   

• Your report is ready for use and should look like the first picture in this blogpost.

Auto refresh of SSRS reports.

In some situations you want to use a SSRS report to monitor activity on your solution which requires some action of people. For instance your internal helpdesk has made an overview of the number of logged errors in the last 10 minutes. This overview will help the helpdesk in their daily operation. In SSRS you can configure your report to refresh automatically. Of course you should be very carefully in implementing this feature. It can result in a lot of recurring datbase activity which can have a big impact on the overall performance of your solution.

To configure auto refresh of your report, open the property windows of your Report. In the Other section you will find a property AutoRefresh. Sets its value to 60 and the report will auto-refresh every 60 seconds.

Picture: Milford Sound, New Zealand Dec 2011.

The difference between drill down and drill through reports.

During the design of reports and dashboards I have seen that a lot of people do not know the difference between drill down and drill through reports. In most situations people uses the term: Drill down for both situations. However, there is a difference between these reports.

Drill through reports:
A drill through report allows you to go from summary to detail. For instance: You have a financial application in which you can show the balance and profit and loss for a specific time frame. Every general ledger displays the total amount of all transactions for this specific period. By clicking (Drill through) on one of the general ledgers you will see the underlying transactions for this specific period.

Drill down reports:
A drill down reports allows you to look to the data in different levels. For instance, a report displays information on Country level if we drill down the report displays information on state level. Next drill down will display information on city level. So Countries contains States, States contains Cities. So we can view all levels through drill down.
Another examples is Year contains Months, Months contains Weeks, Weeks contains Days, Days contains Hours, Hours contains Minutes.

Picture: My drill down from the Kawarau Bridge, New Zealand Dec 2011

How to display a multi value parameter in a text box in SSRS.

In my previous blog I described how you can pass a multi-value parameter to another SSRS report via drill through. After doing this, sometimes you want to show the used multi-value parameters in a text box. The solution for this is very simple. This can be achieved by using the JOIN function. I will explain with an example @Hostname as multi-value parameter.
Open the text box and define next expression:

="My own text: " + Join(Parameters!Hostname.Label,", ")


Other blog post about multi-value parameters: How to pass NULL value to a multi-value parameter in SSRS?

Picture: Milford Sound, New Zealand Dec 2011.

Passing Multi-value parameter to drill through SSRS report.

Last week I was building Report A with a multi value parameter called @Hostname. The values selected for the @Hostname parameter should be passed to Report B with the same multi value parameter. This failed because the dataset expects a valid IN clause. I will explain what happens with an example:
Multi value Parameter: @Hostname
Dataset: Select col1, col2 col3 FROM TableX WHERE Col4 IN (@Hostname)

In this case reporting server tries to add multi values like:
Select col1, col2 col3 FROM TableX WHERE Col4 IN (Server1,Server2,Server3)

This query syntaxt is wrong. It should be
Select col1, col2 col3 FROM TableX WHERE Col4 IN ('Server1','Server2','Server3')

This can be achieved by using next expression in the action for the parameter field:

=SPLIT(JOIN(Parameters!Hostname.Value,","),",")

With this expression the correct TSql syntax will be generated.

Happy building your reports. Enjoy It.

Picture: Mount Cook, New Zealand Dec 2011.

How to pass NULL value to a Multi value parameter in SSRS?

This week I tried to build a SSRS report with a parameter which can have multiple values including the NULL value. This is not possible by default in SSRS. When you configure the parameter with option: Allow null value and Allow multiple values. You will receive next error message:

A multi-value parameter cannot include null values.

The reason for this is the TSql syntax of the underlying dataset.

SELECT Column1, Column3
FROM TableX
WHERE Column2 IN (@MyParameter)

This query will fail for the NULL value because IN syntax is in conflict with NULL value. TSql syntax for NULL values is

SELECT Column1, Column3
FROM TableX
WHERE Column2 IS NULL

Solution:
We are going to use the ISNULL function to change parameter values to a blank value (''). Assume you have a dataset to retrieve parameter values for @MyParameter.

SELECT DISTINCT(Col1) FROM TableB

Change this query to:

SELECT DISTINCT ISNULL(Col1, '') AS Col1, ISNULL(Col1, 'NULL') AS Col1Description
FROM TableB
Change the Report Parameter Properties to: Allow blank values ('') and Allow multiple values.
Change the available values: Value field: Col1   , Label field: Col1Description.


Now we need to change the query of the dataset.
-- Original query
SELECT Column1, Column3
FROM TableX
WHERE Column2 IN (@MyParameter)

-- Changed query
SELECT Column1, Column3
FROM TableX
WHERE  ISNULL(Column2,'')  IN (@MyParameter)

That's all, enjoy it to make your report more user friendly.

Register your hours spent really simple and fast in SaaS solution Exact Online.

In the past I blogged about 8 reasons why I uses a Saas accounting solution for my financial administration. Up till today, I still uses Exact Online with a lot of pleasure for my financial administration. With the introduction of Exact Online Time & Billing in november 2011, we started to use this module in my company for the hour registration of different development teams. As you can see in the title of my blog, I like solutions which are Simple And Fast. The Time and Billing iPhone app is one one of these solutions I like because it is Simple and Fast. Registering my hours is a matter of seconds. Start the app and press the hours symbol.

Now I got a list of all days. The days for which I need to register my hours can be recognized by the plus symbol. Press on the plus symbol.

For the selected day, I can specify the Project, Relation, hour classification and the hours I have spent.

Press on the green submit (Indienen) button and you are done.

Within 1 minute I have entered the hours I spent for the whole week on my iPhone. Hour registration is now fun instead of a boring activity. This app is also available for Android.

The iPhone app can be download via this link or scan this QR tag. Of course this app will also work on the iPad.

More information about the Time and Billing solution can be found here (unfortunatly Dutch only).
Have fun with the registration of the hours you have spent.

Argument data type nvarchar is invalid for argument 2 of dateadd function.

Situation:
I have build a query, using the DATEADD function, which executed without any error in SQL Server Management Studion (SSMS). I want to use this query as a dataset in my SQL Server Reporting Service Report (SSRS). I paste the query in the query designer window of my dataset. After I press the OK button of the Query designer, I got next error message:

Argument data type nvarchar is invalid for argument 2 of dateadd function.

This is the part of the query with the DATEADD function:
(SELECT DATEADD(HOUR, @MeasureHourStart, @MeasureDateStart) AS XXXX)

In my report I have report parameters @MeasureHourStart (INT) and @MeasureDateStart (DateTime)

Solution:
SSRS still sees @MeasureHourStart as a Varchar value.

As a workaround I forced to set the value of @MeasureHourStart as an Integer by using the CAST function.
(SELECT DATEADD(HOUR, CAST(@MeasureHourStart AS INT), @MeasureDateStart) AS XXXX)

Now the query will be accepted by SSRS as a valid dataset.

My first experience with Power View of SQL Server 2012 (febr 2012)

In the past I blogged about SQL Server Project "Crescent" Denali. This project will come general available in SQL 2012 named Power View. Last week I installed SQL 2012 RC0 to experience the Power View capabilities. First of all I will give some characteristics of Power View which will give you an idea what you can expect from Power View.

• Power View is a browser-based Silverlight application launched from SharePoint Server 2010 that enables users to present and share insights with others in their organization through interactive presentations.
• Web-based report designer. So, this will be the fifth report designer after BIDS, Report Builder 1.0 (not sure if RB 1.0 will survive SQL 11), Report Builder 3.0, Visual Studio Report Designer.
• Reporting experience will become more interactive and similar to Excel PivotTable. The end user can quickly create an interactive report by dragging metadata, and then with a few mouse clicks change the report layout without switching to design mode. This is completely different in comparison to a Reporting Services report which is always static.
• See next blog post for a perfect demo of Power View.

 To install Power View you need to have 2 installation sets:

• SQL 2012
• SharePoint 2012 SP1

Use next blog post to install Power View: Getting up and running with SQL Server Denali for Business Intelligence (Crescent and PowerPivot in CTP3). It will explain the sequence to install which components. After completing the installation, I installed the Power View and PowerPivot HelloWorldPicnic Samples for SQL Server 2012 Release Candidate 0 (RC0). The HelloWorldPicnic samples are very useful to explore the new capabilities of Power View. After this I used the Power Pivot sheet on a Globe database I created in the past. This sheet enables me to play with data I know.

Having done this, it is now time to talk about my experiences with Power View. I will start with a list of GREAT user experience examples:

• You can really 'play' with the data without changing and redeploying your report.
• Highlight of selected values, see next example
  
  
  
  
  
  
  
  
  
  
  
  Left chart is the revenue and margin per year of the company. Right chart is the revenu per employee over the years. Select one employee in the right chart and you will get:

Now you will see what the amount of Revenue and Margin this employee has made in comparison with the total amount of revenue made by the company.
• Pop out. Every visualization has an pop out option. This will show the visualization full screen so you can better look to the details. Press on the Pop In button to go back to the report.
• Tiles. Another way of selecting data. Selected data in the tile applies only to the visualization the tile belongs.
  
  
• Play Axis on a bubble chart. Press on the Play button so the bubbles move, grow, and shrink as their values change over the months. Note that the months display as watermarks in the upper-right corner, and the bubbles move to the right as the months pass and the year-to-date quantity increases.
  
  
  Click on the city Assen and you will see how the city 'Assen' moved over the years.
  
  
• Interact in Power Point. You can export the Power View report to Power Point 2010. Each Power View view has been added as a separate slide. In PowerPoint design mode, each view is static. After you open the Power Point presentation in presenter view, you will get a button in the right bottom on your presentation called: Click to interact
  
  
  As long you have connection to the sharepoint server on which report is deployed, you can interact with the view in the same way that you can in reading and full screen modes. So you can play with the data in the Power Point presentation itself.

This is the first release of Power View with a lot of interesting interactive reporting features. Next to this nice features I will give a list of possible future improvements:

• Installation is complex. It is not 'out of the box' like a one click installation.
• Additional knowledge about Sharepoint 2010 SP1 is needed. A big part of the SQL community do not use Sharepoint 2010. The installation of SQL Server itself is already complex enough for them.
• Silverlight is needed. This means Power View will not run on the IPad. Power View is, for instance, made for the people in the board room. However a lot of them are using IPad's as of today.
• Visualizations can not be customized like you can in SSRS. For instance, Fonts, Colors, Borders  etc. It is not possible to apply your style guide.
• Default colors are sometimes in conflict with dashboard design rules. One example: The use of the color Red. The first color in charts is blue, the second color is red. According to the dashboard design rules, red is used to get attention for something. In next example, it is not intended to give extra attention to the Amount of Margin. Using red it looks like something is going wrong with the Amount of Margin.
  
  
• Automatic alignment of chart horizontal or vertical is not supported.
• Report is based on a metadata layer. You do not have datasets. To use Power View on a database you first need to develop a metadata layer.
• Existing SQL 2008 R2 reports (RDL) can't be converted to the Power View format RDLX. You need to build them from scratch. Of course you can use your SQL 2008 R2 RDL's in the traditional reporting service SSRS of SQL 2012.
• Power View is not available in SQL Azure Reporting. When Power View is available in SQL Azure Reporting we can get rid of the Share Point knowledge need.

Enjoy your Power View journey.

How to show a section in a SSRS report based on a parameter?

Today, I got a question how you can display a section in a SQL Server Reporting Services (SSRS) report based on a parameter value. In this blog I will describe how you can achieve this. I will make use of a general ledger card as example. This general card will show financial entry information. Depending on the parameter value: CostAnalysis it will show costcenter information.

Photo credit: Nisha A

Example of general ledger card without costcenter information:

Example of general ledger card with cost center information:

How to achieve this?

• Add a extra detail line in the report for the costcenter columns.

• Add the costcenter columns to this detail line.
• Add a parameter CostAnalysis to your report. In this example I will make the posibility to choose to display the costcenter code or the costcenter code + costcenter description.
• Select Available Values, Specify values and Add 3 labels

• Specify default value 0. So costcenters will not be displayed by default.
• Select the costcenter detail line of the left. Right mouse click and select row visibility.
• Select Show or Hide based on expression. Add next expression:
  =IIF((Parameters!CostAnalytics.Value=0) True,False)
  This will display the costcenter line if parameter value = 1 OR 2
• In the costcenter columns we need to add logic to display only the costcenter code or the costcenter code + description. Select the costcenter column and add next expression to this column: =Fields!Costcenter.Value + IIF (Parameters!CostAnalytics.Value = 2, " - " + Fields!CostcenterDescription.Value, "")
  This will print the costcenter code and only the description in case the CostAnalytics parameter value = 2

In this way you can do the same for currency information etc.
Enjoy it to build nice customer experience reports.