In our last tip we showed you how to use a Code Description pair to make your lookups more descriptive. The same principle can be applied when using a Pass Through Variable in your reports.
Although standard Expressions and Pass Through Variables include some of the same lookup types, the Pass Through variable also has a Multiselect option. This allows you to select more than one item at a time to filter your reports when they are run out. However problems can arise when using it.
In this tip we’ll look at what these problems are and how you can work around them.
Lookup Types of a standard Expression:
Lookup Types of a Pass Through Variable:
I have set up an example where I want to view a summary of product sales by store. I have created a container which joins my Stores table with my StoresSummary table and included the necessary Expressions. I have also added a Pass Through Variable (Product Selection) which uses a SQL Statement Defined Multiselect lookup and referenced it in the source of my container. Find more information on using a lookup with a Pass Through Variable in previous tips.
In the Report Manager I have added a standard report which is based off of my container and added a parameter which makes use of the variable – Product Selection is equal to an empty field.
Now if I run out my report I am given the option to enter a value for my product selection and if I click on the ellipses I have the ability to choose more than one item at a time.
Let’s look at two scenarios.
Scenario 1 – Selecting a single item:
I have selected ARCH02 and continued to run out my report but received the below error.
To try and diagnose the problem I am going to use the SQL Debug mode. This shows the SQL that is performed when a report is run and may help to identify where the problem lies. To switch to the mode, right click on the Home object in the Report Manager, select Switch Output Mode, select SQL Debug and then click OK.
Now when I run the report again I am presented with the following and if I look at where the variable is applied I can identify the fault. The problem is that ARCH02 is a text string and in SQL, text strings should be surrounded by single quotes.
Going back to my container I can change it to look as follows:
If I run out my report again in the Excel output mode, the report succeeds.
Scenario 2 – Selecting multiple items:
Now, even though I have corrected the single quotes in my container, if I select multiple items when I run out the report, I get the following error.
Looking at the SQL again in the SQL Debug mode, I see the following.
The problem is that even though single quotes are being applied to the variable, they are not being applied individually to each selection. It should read:
WHERE [StoreSummary].[ProductID] IN (‘ARCH02’,’BB0001’,’BIGM03’)
Solution
The solution to this is outlined in the below three steps:
1. Remove the reference to the variable in the container source:
2. Make sure your container type is not a SQL Query. This prevents us from applying the next step – If the container type is SQL Query then the option to apply filters is removed. If you are using a SQL Query then it will need to be converted to a Join type.
3. Apply the variable using a filter in the Report Manager.
Doing this still gives you the Multiselect option and results in your report running out correctly into Excel.
In this example the field that we filtered on was ProductID and was a text string of type WChar. One last thing to note is that if the field you are filtering on is a number, for example type Integer, then you do not need to apply the solution above. You can still reference your Pass Through Variable in the source of your container and do not need to apply single quotes. This is because SQL does not require quotes around a number, even when multiple items are selected. You can identify the type of an Expression by looking its Data Type field in the Connector.
