Month: August 2013

SQL Server Reporting Services: How to Add Tool Tips to charts?

Standard

Problem:

Consider a chart like the one shown below (Sales Amount VS. State region).  When a user moves their mouse over one the bars then you want them to show them the value (sales amount) of that bar as well as show them the category grouping values (State region)

SSRS Tool Tip

(The Visualization is just for demo purpose. It’s not presentation-ready)

Solution:

Here are the step by step instructions to set up Tool Tip based on the requirements:

1. You’ll have to open the series group properties to add the Tool Tip. There are a couple of ways to open the series property

1A Select the Chart. Right Click on any of the bar and select Series Properties

1B Select the Chart. Click on any of the bar. You should see chart data pane. you can click on down arrow button on the series to open series property:

SSRS Reporting SQL Series Properties

2. Once you’ve opened the Series Property, here’s what you’ll do.

Make sure you’re on Series Data tab. You can either select one the fields or write an expression.

To meet our requirement of showing State & Sales Amount, I am going to write an expression.

From Tool Tip. Click on Fx

3. I wrote an expression that meets the requirements I stated earlier.

Expression SSRS state and salesClick OK. Click OK on series property too. And return to design view

4. Preview the report. Move your mouse pointer over one of the bar chart, you should see a Tool Tip:

REPORTING TOOL TIP CHART

This makes the charts a little more easier to read. I hope this helped!

Related articles

SQL server reporting services: Expression to show current month name & year

Standard

Here’s a SSRS expressions code that I’ve using lately to show current month name & year in SSRS reports:

[code]

="REPORT NAME"&" – "&MonthName(Month(Today()))&"’"&Year(Today())[/code]

This is a nice little code that you can add to your expressions which will show current name & year. Small little things that you can do to make your business users happy! I hope this helps someone out there.

Related articles

SSRS Email subscription: “SSRS Subscriptions cannot be created because the credentials used to run the report are not stored…”

Standard

I recently ran into error message inside SSRS report manager: “SSRS Subscriptions cannot be created because the credentials used to run the report are not stored, or if a linked report, the link is no longer valid”.

My Setup:

Mode: Native

Authentication: Windows

So I changed the Report’s data sources properties to store the credential:

SSRS store credentials in the report server

Official resource: http://msdn.microsoft.com/en-us/library/ms159736.aspx

After doing this, I was able to set up email subscriptions.

Related articles

Gallery: Visualizing report execution logs data to understand what’s up with slow running reports.

Standard

At my current project, We faced an issue of slow running reports. SSRS was on SharePoint integrated mode. So before a meeting was scheduled between SharePoint Team & Report Development Team, I created a Data Visualization using SSRS Report Execution Logs like the one shown below so that we all had a starting point to discuss possible troubleshooting tasks for our team:

Here’s the data visualization:

Report Execution Logs SSRS data

can you guess – which Team took action items from the meeting? 🙂

How to Parametrize a SSRS report where data source is Analysis services cube?

Standard

In this blog post, we’ll see steps to Parametrize a SQL Server Reporting Services report where data source is Analysis services (SSAS) cube:

Step 1: Let’s say you have reached to a point with your SSRS report where you’ve configured your data source, data sets and data fields that you want to use for your report. For the purpose of this blog post, I’ll be starting with a SSRS report that shows Sales VS country names:

SQL Server reporting services 1

Step 2: Now, let’s say the requirement are such that you want to parametrize the report by a data field in the Analysis services cube: continents

Step 3: Switch to Design View. Now navigate to query designer: Select your Data-set > Right Click > DataSet Properties > Query > Query Designer

ssrs query designer SQLStep 4: Drag the field to the filter area. For the purpose of this blog post, I am going to select Continent Name and add it to Filter area.

To add a field to filter area, there are two options:

#1: Select the field > Right click > Add to Filter

#2: Select the field > use your mouse to drag it to filter area

Step 5: Once you’ve added your desired field to the filter area, we’ll have to add it as parameter.

Now chances are that you are not seeing the parameter check box for this field because the dialog box is minimized. You can either maximize the dialog box or scroll to the right side of the filter area.

Once you see it, check it > click ok

parameter query sql server reporting

Step 6: Once you’re back on Design View. Try “preview” report. you should be able to see the option to select parameter value before the report gets populated with data:

parameter in sql server reporting ssas

I selected Europe and then clicked on view report:

parameter in sql server reporting ssas 2

Step 7: One last thing, Let me also point out how you can change the properties of the parameters.

Go Back to design view > from the report data pane > Expand parameters folders > select the parameter > Parameter Properties

parameter properties report data ssrs ssas

I’ll leave you with exploring what you can do with parameter properties! And with that I conclude this blog post, Your comments are very welcome!

This was a beginner’s level post, I’ll encourage you to follow up by watching three videos here: http://sqlserverbiblog.wordpress.com/2013/12/03/reporting-services-mdx-queries-video-tutorials/

Related articles

Slicing/Dicing data in multiple Excel Pivot Tables with ONE slicer:

Standard

Background:

This week I created a couple of Excel Dashboard’s for a project that I am working on. As a part of the that, I decided to include slicers for these Excel Dashboards. And the reason I did that was because data in multiple pivot tables needed to be sliced with one click. In this blog post, I’ll show you steps to connect an Excel slicer to multiple pivot tables:

Steps for Excel 2010:

1. Based on your requirements, decide if multiple items on a dashboard (excel sheet) need to be affected with one click

2. Now, for the purpose of this blog post, let’s assume that you have two Pivot Tables like shown below:

two excel reports slicer 1

Note: the Two Excel Pivot Tables are created by connecting to same cube. And the slicer comes from the same cube.

3. And what if you need to slice data in both pivot tables by one common slicer? Let’s say we want to slice the data in the two pivot tables that we have by Product Category – here’s what you do (assuming that the tables are related in your data source and slicing makes sense)

4. click on any of the pivot table and you’ll see PivotChart Tools in the toolbar

pivot table charts tools5. Switch to Analyze Tab & click on insert slicer & Select the field that you want as slicer & click OK

pivot table insert slicers6. you’ll see a slicer on your sheet now:

slicer on pivot tables

7. Let’s format the report to make it look better:

#1: move the slicer such that it does not overlap any of the pivot tables.

#2: In this case, I want to move the slicer to the top of the sheet and change the slicer to have 5 columns. Here’s how you can do that:

select the slicer > from the toolbar > slicer tools > column

slicer tools option excel

#3 I also changed the slicer style from the slicer tools to match its color palette with that of the Pivot Tables:

8. Note that the slicer is connected to just ONE pivot table. Let’s connect it to both pivot tables

9. From Slicer Tools options > Pivot Table connections > check all pivot tables that you want the slicer to be connected to:

pivot table connections excel

10. Done! Test your slicers, with one click you should be able to see that the data gets sliced in both pivot tables:

#1: Sliced by Bikes

excel pivot analysis data 1

#2: Sliced by Accessories

excel pivot analysis data 2That’s about it for this post. Your comments are very welcome!

Related Posts:

Comparing Slicers in Excel 2010 to Standard PivotTable Filters