SSRS #Error in Report Cells

When you run a report, you see #Error in the cells of a particular column.  This can indicate a number of different issues as referenced below.

Mark Brown Blog

Some Random #Error

Formatting Error

In my case, the cell in error used an expression to add the values of two dataset fields together – i.e.

 =Fields!Field1.Value + Fields!Field2.Value

In this particular case, the workaround that resolved the issue was to edit the cell Expression to use IsNothing to test problematic values and IIf to set problematic values to 0.

 =Fields!Field1.Value + Iif(IsNothing(Fields!Field2.Value),0,Fields!Field2.Value)

Unfortunately, this particular error does not have any detailed diagnostic info to point you in the right direction.   If you are looking for something reusable to perform calculations more consistently check out this read

Reportingservicestips Function Example, otherwise, try putting the calcs back in the dataset query or database itself.

SSRS Error: Subreport could not be shown

You have included one or more Subreports in your main report, selected Preview, selected your parameters and then encounter the error “Error: Subreport could not be shown” before the report renders.  This is seldom a good sign, however with an understanding of the common causes and a planned approach can be easily fixed.

The following thread explains there could be a number causes that can be contributing to this error.

Variety of causes discussed on this thread

Permissions related thread

Basically, you need to run your Subreport independently and make sure the subreport is working.  If there is a problem within the dataset for the Subreport, you will encounter this error when previewing from the main report.

Once this is proved to work independently, but is still not working when previewed from the main report, work your way up checking parameter properties for multi select and nullability are the same between main and Subreports.  This is a good reference for specifying parameters for subreports Parameters for Subreports.

If still no luck, then check the numerous other items discussed in the threads above.

If you are now rocking in the corner of the room from the experience, and are now asking yourself “Why do I even need a Subreport?”, consider the pro’s and con’s of Subreports discussed here Mark Hudson Considers Subreports.

Happy Subreporting !

…Casting SSIS Data Types – Cheque Please !

Next time you run into Data Conversion errors within SSIS, be sure to read this concise reference on compatible SSIS Casting operations.  Note – Black = Legal, Red = Illegal.

Casting SSIS Data Types Conversion Matrix

Casting explicitly converts an expression from one data type to a different data type. The cast operator can also function as a truncation operator – for example, casting from a DT_WSTR to a DT_STR with the same charcount value may cause truncation of the final characters in the converted string.

Publishing Intuitive Top B’s for Every A for Excel users

One of the most common reasons why we would go to the trouble of modelling our data in a cube is performance.  However self service access to the data is one of the other huge benefits that allows us to enrich the user experience and free up our workloads.

When a list of dimension members heads into the hundreds or thousands, users naturally seek out the Top N members, or more specifically, give me the Top 10 B’s for every A.

In the following example we want to see what the Top 10 Selling Products are for Every Supplier.   If we attempt to create a dynamic set called “Top N Suppliers”, and then browse the model in Excel, we will find the cross join occurs between only 10 suppliers and our product members.

Not only is this not intuitive for the end user but does not meet the requirement adequately either.

Creating a more intuitive set called “Set1 – Top 10 Suppliers for every Product – Sales $” allows us to publish this is workable self service solution.


WITH

SET [Set0] as [Product].[Product Description].Children

SET [Set1] as generate([Set0], crossjoin([Set0].Current, TopCount(NonEmpty([Supplier].[Supplier Description].Children, [Measures].[Sales $]), 10, [Measures].[Sales $])))

SELECT

[Measures].[Sales $] on columns,

NON EMPTY [Set1] on rows

FROM [Product Sales Data]

How about the Top 5 Suppliers for the Top 5 Selling Products ?

SET [Set0] as Head(ORDER(NonEmpty([Product].[Product Description].Children, Measures.[Sales $]),Measures.[Sales $],BDESC),5)

SET [Set1] As generate([Set0], crossjoin([Set0].Current, TopCount(NonEmpty([Supplier].[Supplier Description].Children, Measures.[Sales $]), 5, Measures.[Sales $])))

We can make this even more intuitive by adding the Time Period, in this case, Last 5 years.   A set called “Set2 – Top 10 Suppliers for every Product – Sales $ (Top 2 Performing Years)” provides instant access to the top time periods as well.  This could work equally well for months, weeks or days.


WITH

SET [Set0] as [Product].[Product Description].Children

SET [Set1] as generate([Set0], crossjoin([Set0].Current, TopCount(NonEmpty([Supplier].[Supplier Description].Children, [Measures].[Sales $]), 10, [Measures].[Sales $])))

SET [Set2] as generate([Set1], crossjoin([Set1].Current, TopCount([Time].[Year].Children, 5, [Measures].[Sales $])))

SELECT

[Measures].[Sales $] on columns,

NON EMPTY [Set2] on rows

FROM [Product Sales Data]

Richard Lees also has another good angle on this using Generate and order.

Thanks to Chris at Stackoverflow.

Reporting Actions – Its All About the Nutmeg !

Actions in Analysis Services are a fantastic way to bring the bling to your cube without overcomplicating the basic user interface.

Analysis Services supports several different types of actions however the scope of the following discussion will be on Reporting Actions and to a lesser extent Drillthrough Actions.

The classic case for a Reporting Action is where your cube rolls up to the day level, however you want to query at more detailed level to the cube’s granularity, going down to the line level.   The Reporting Action allows the user to investigate a lower level of detail by clicking the Reporting Action which will provide the additional data via a Reporting Services Report.   The Report will accept 1 or more parameters from the cube Action, query a relational (or multi-dimensional) dataset and return the results in the Reporting Services report.

There are several references available for creating a New Reporting Action.   The key point to get across here is if any of the fields used to define the Reporting Action are not correct, the action will not appear at the OLAP client.  Also make sure you give the Action a Caption by setting the Additional Properties section of the Action definition.  For the server name and path using similar naming to that of the Adventureworks solution will work i.e.

Server Name – needs only to be set to localhost (if SSRS and SSAS are on the same server) or the server’s actual name

Report Path – should look something like this –

ReportServer?/Folder Name/Report Name

Note the full path to the report is preceded by a / (following the ? character) and does not require the .rdl extension.

Caption – Before you get too excited with parameters, makes sure you go to the Additional Properties section and give the Action a caption.  This is the wording the user will see when they right click on the cube member and select Additional Actions.

Parameters – Make sure the Action parameters are correct otherwise this can cause the Action to effectively “break” which means it won’t appear on the OLAP client.  Using an expression such as

 UrlEscapeFragment( [Dimension].[Attribute].CurrentMember.Name ) 

is an ideal choice for passing a parameter to a relational dataset, whereas

 UrlEscapeFragment( [Dimension].[Attribute].CurrentMember.UniqueName ) 

is an ideal parameter expression for passing to a multidimensional dataset.

Some other examples here …

Bret Stateham Reporting Actions

Benny Austin Reporting Actions

The above examples work well for native mode installation of Reporting Services – hence be sure to read this integrated mode example if relevant.

Integrated Mode Samples

Test your Reporting Services action by deploying the Actions you have made using the Deployment wizard.

Benny Deploys Reporting Action

What to do when Actions don’t work ?  As outlined above, make sure you have a caption, the parameter expression is correctly formed and the Report Server and Report Path have been correctly defined.   This is reiterated here on MSDN.

IMPORTANT: It is important to note that the MDX expressions used in Conditional Expressions, Parameter Values and Caption must evaluate to valid values. If either one of them does not return a valid result, the Report Action will disappear when the cube is browsed. No errors are thrown, but the Report action just does not show up.”

Good luck and happy Action!

Member Does Not Exist in MDX Calculations

Granted if an MDX calculation involves a specific named member, then this member should ideally exist in the datamart and be processed in the cube.  Nonetheless, there may be the occasion where this isn’t the case and when browsing the calculated measure in your OLAP client will get a #Value! error.

The following code snippet provides an example for how to handle missing member. The approach uses a SET expression to look for the missing member followed by a simple IIF expression in the CREATE MEMBER to determine if this member is missing and how to handle it.

Noted, there are numerous more performance savvy and sometimes cumbersome ways to solve this problem in Analysis Services, however the simple approach is taken here to get you up and running.  This will buy you some time to come up with a  more sophisticated approach.

/* PreExempt Missing Member */
Create Set CurrentCube.[MissingMemberSet] As
iif(IsError(StrToMember("[Dimension].[Hierarchy].&[MEMBER]")),
{}, {[Dimension].[Hierarchy].&[MEMBER]});

Create Member CurrentCube.Measures.[Calculation on Missing Member]
AS
IIF ([MissingMemberSet].Count > 0,
([Dimension].[Hierarchy].&[MEMBER],Measures.[SomeMeasure]),
0
)
,
FORMAT_STRING = "Currency",
LANGUAGE = 1033,
NON_EMPTY_BEHAVIOR = { [SomeMeasure] },
VISIBLE = 1 ,  DISPLAY_FOLDER = 'Display Folder'  ;

Thanks to Dr. Nick and various posts by Deepak Puri for the inspiration.

Case Sensitive Search

Occasionally you may find it necessary to perform a case sensitive search in your data.  Using the COLLATE statement in the WHERE clause will allow you search for the exact case sensitive string you are looking for.

SELECT
AccountName
, AccountDescription
FROM
dbo.Accounts
WHERE AccountDescription COLLATE Latin1_General_CS_AS LIKE 'A Comp%'

Thanks to SQLMagRef139579