SSRS Error: “The value expression for the … contains an error. [BC30451] ‘code’ is not declared”

I was working on some enhancements on an existing SQL Server Reporting Services (SSRS) report & so before started adding enhancements to the report, I made a copy of the existing report. And after that, I tried running the report as-is to make sure everything was functioning as it should. But I got an error:

So, when you get an error like this, look for missing custom code in the SSRS report.

If you look at the expression which is throwing the error, you’ll see something like code.<custom function> in the expression.

To solve the error, you’ll have to define the custom function for your report. In my case, I had to copy the code from the existing report to the new version of report that I was working on.

To do that, follow the steps:

Design View > Right click on the surface outside the border of the report > Report Properties > Code:

I hope this helps if you run into this SSRS error.

Questions Power Users Ask about Excel: #3 of N

In this series, I intend to document common questions asked by Power users about Excel connected to SSAS cubes (or data warehouse) after they go beyond the basic stage of understanding & using Row labels, column labels, report filter & values in Pivot Tables. This post is #3 of N:

a. How to remove Grand Totals & Sub Totals?

1. Select the Pivot Table.

2. From the toolbar, go to Pivot Table Tools > Design > Sub Totals > Do not Show Sub Totals.

you can also choose to remove it just for rows or columns. it depends on your requirement on how to layout data.

Similarly, you can remove Grand Totals:

Alternatively, you can remove grant totals from Pivot Table options:

There’s also an alternate method for removing subtotals. Put your mouse over the hierarchy level for which you do not need subtotals. Right click > remove subtotals “field name”. But you’ll have to do that for all hierarchy levels if you need to remove all subtotals.

b. How to configure Automatic Data Refresh?

A common question asked by power user is how to make sure that the excel file is pulling the latest data from the cube? Good news for them is that Excel files that are configured to connect to a data source like SSAS cube can be configured to automatically refresh. Here’s how:

1. From the toolbar, Go to Data > Connections

2. Select the connection that interested for automatic data refresh > properties

3. From here, you can configure the file to do an automatic data refresh every xyz minute and/or configure the file to refresh data every time you open it.

click ok when you’re done and close the workbook connections after you’ve configured the data sources that you needed for automatic data refresh.

Conclusion:

In this post, we saw how to remove grand totals & subtotals and, how to configure the automatic data refresh.

In previous articles we saw:

#1: How to sort data? How to add slicers? How to change Pivot table Layout

#2: How to add calculated measures?

SSIS Error on opening a package: The connection “{GUID}” is not found. The error is thrown by connections collection when the specific connection element is not found

In my case, This error came up in SSIS after some copy-pasting happened in our TFS. I tried opening a package & it gave the “The connection {GUID} is not found…” error. So here’s how I was able to solve it:

1. After I got the latest version of files, I navigated to the Integration Services package file on my local machine.

2. Opened the file in notepad to look at XML.

3. After I was able to see the XML code, I searched for the connection GUID “xyz…” that was showing up in the error

4. Now, once you locate the GUID, figure out the package component that the connection is being used. In my case it was a “Execute SQL Task”

5. I then opened my package and fixed the connection in the Task.

That’s about it for this post. I hope this helps someone out there.

Questions Power Users Ask about Excel: #2 of N

How to add calculated measures (Excel 2010)?

Situation:

Power users in your organization/company are developing Excel 2010 reports against OLAP Cubes. They want to add their own calculations for analysis.

BUT Excel 2010 does not allow end-users to add their own private MDX calculations.

Solution:

A Free Community Excel add-in helps in this case. It’s called “OLAP Pivot Table Extensions”. Here are the steps to download, Install and use it:

1) Check Excel Version (32-bit/64-Bit)

Open Excel > File > Help> About Microsoft Excel

2) Download OLAP Pivot Table Extensions and Install it

Download URL: http://olappivottableextend.codeplex.com/ > Navigate to Downloads Tab & install the right version based on your local excel version.

In my case, I had 64-bit excel so I’ll download the 64-bit version of OLAP Pivot Table extensions.

Close Excel and Install add-in

3) Add simple measure

Open Excel.

Connect to Cube.

Let’s add a simple measure that calculates the difference between two measures.

So I created a simple Pivot Table that looks like:

Now let’s add a calculated measure:

Select the Pivot Table > Right Click > OLAP Pivot Table Extensions

On the next screen, please enter the name Difference. and the simple formula:

[Measures].[Meausre1]-[Measures].[Measure2]

(please replace measure1 & measure2 with the measure names from your cube)

And click on Add to Pivot Table

You can now see that the calculated measure Difference got added to the Pivot Table!

Note

1) OLAP Pivot Table extensions is not supported by Microsoft. It’s a community software.

2) To maintain the single version of truth (after all that’s why you create cubes/Data-warehouse’s!), it’s recommended that calculated measures that end users want in the cube.

Conclusion

In this post we saw a very common question “How to add calculated columns” asked by Power users about excel while they analyze data from SSAS cube.

Previous Article: http://parasdoshi.com/2013/12/10/questions-power-users-ask-about-excel-1-of-n/

Questions Power Users Ask about Excel: #1 of N

a. How to Sort Data?

There are two ways to do this & here they are:

#1: Select a cell that has a measure value > Right click > Sort > sort the values in ascending or descending from here:

#2: Alternatively, you can also do the same thing by: Select a value from the filed that needs sorting > go to Home Tab > Sort & Filter > from here you should be able to sort data in ascending for descending order:

b. How to add slicers?

They might not know what slicers are but I’ve been asked how can I add filters where users can see the filter values before picking the value. I’ve also been asked by a Power user for the capability for adding “global filters” who had couple of pivot tables in a single sheet. They also come in handy if you’re building Excel Dashboards. With that, here are the steps:

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:

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

5. Switch to Analyze Tab & click on insert slicer & Select the field that you want as slicer & click OK

6. you’ll see a slicer on your sheet now:

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

#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:

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

#2: Sliced by Accessories

c. How to change the layout of the Pivot Tables?

Without formatting and changing the layout, the excel pivot table looked like this:

Now, let’s work step by step to meet the requirement:

Step 1: click somewhere on the Pivot Table and from the Toolbar, Switch to the Design Tab under PivotTable Tools

Step 2: Now here, Go to Report Layout > Show in Tabular Form

Step 3: As you might have noticed the “hierarchical” structure is now broken up into multiple structure getting us closer to meet the requirement.

Step 4: In our requirement, you can see that it does not give the user to see the “+” or “-” (expand, collapse) buttons. So, let’s hide these buttons from the report from Step #3

Right click somewhere on the pivot table > PivotTable Options > Display tab > uncheck the box that says “Show expand/collapse buttons“:

Step 5: so now it looks as follows:

To recap, Here’s what we had to do: Change the Layout of the Pivot Table & we also hid the expand/collapse buttons too.