Setting up an "All" Parameter
This short tutorial is fairly similar to the last one, let's say it's an improved version:
We work again with Pentaho Report Designer 3.5. Imagine you want to give your users the possibility to choose either one of the parameter values or all. So how can we implement this with a MySQL query?
The approach is as follows:
Set up the query to for the parameter values like this:
SELECT
"All Countries" AS country_name
UNION
SELECT
country_name
FROM
table
;
We do the same for the channels query. Please keep in mind that UNION will remove any duplicates. If you are sure that you have no duplicates, you can use UNION ALL to improve the performance.
Set up a new parameter called "country_name", tick "mandatory", choose the above query as the source, set the type to string.
Now change the main query that feeds the report like this:
SELECT
[...]
WHERE
(cc.channel_name = ${channel_name} OR "All Channels" = ${channel_name}) AND
(country_name = ${country_name} OR "All Countries" = ${country_name}) AND
the_date >= ${start_date} AND
the_date <= ${end_date}
GROUP BY 1,2,3,4
;
Save everything and run the report ... you should see the all options now in your pull down menu:
As some of you might use Oracle as well, a user named "zulu" from the pentaho forum pointed out that:
"Not sure if this helps you now, but depending on your SQL dialect, a
NULL (meaning nothing) is treated differently to a "NULL" string.
In Oracle your predicate could be:
WHERE (${media} IS NULL OR media=${media}).
Oracle applies "lazy" logic, so if your Media parameter is not
completed by the user (meaning it "IS NULL"), the condition will not
even check the "media=${media}" part."
Just a remark from my side: My original post included a query like this one "(media = ${media} OR 'All Media' = ${media})"
You can find the original post here.
Update 2012-05-30: Somebody asked me how to achieve the same with mulitselect parameters. Here is the approach that works for me (It's a different dataset, but you get the idea):
SELECT
`city_sales`.`date`,
`city_sales`.`continent`,
`city_sales`.`country`,
`city_sales`.`city`,
`city_sales`.`revenue`
FROM
`city_sales`
WHERE
(city IN (${CITY}) OR "All cities" IN (${CITY}))
Note: I defined the CITY parameter as type STRING.
转自:http://diethardsteiner.blogspot.jp/2009/10/setting-up-parameter.html
相关推荐
Fixed Lua toAddress when the 2nd parameter is an address Fixed assembling xmm,m32 Fixed issue when disassembling AVX instructions Fixed rightclicking r8-r9 in the registers window Fixed the plugin ...
- You can compress up to 2147483647 files into an archive. This is compatible with PKZip's Zip64 format. - If a file does not extend beyond any of the original limitations (filesizes of 4 gig or ...
Table of Contents Preface 1 Chapter 1: Shell Something Out 7 ...Setting up an Ethernet and wireless LAN with script 250 Password-less auto-login with SSH 253 Running commands on remote host with SSH 255
-a 所有(all)。 -e 所有(every),比a更详细。 -f 取消保护。 -i 添加提示。 -p 强制执行。 -r 目录管理。 分屏显示的中途操作 空格<space> 继续打开下一屏; 回车<return> 继续打开下一行; b 另外开上...
The all new auto-hide child windows give you The all new auto-hide child windows allow you to maximize your editing space by hiding the child windows against the edge of the editor. Customizing ...
Setting Up a Database 260 Try It Out: Creating a Gadfly Database 261 Using the Python Database APIs 262 Downloading Modules 263 Creating Connections 263 Working with Cursors 264 Try It Out: ...
- Added an operation watchdog timer for all tests. In rare cases, a single test can stop in the operating system - i.e. there is a problem in the operating system/ device driver that prevents ...
You can redirect all of the output of your scripts to a function. For ; example, if you set output_handler to "mb_output_handler", character ; encoding will be transparently converted to the ...
Applications interact with the interface by setting up tablet contexts and consuming event packets. Applications may assume interface and hardware control functions by be¬coming tablet managers. The...
Q517792 - Two layout groups containing controls in AutoSize mode hang up an application if the alignment settings of these groups are set to occupy the entire client area ExpressNavBar B238354 - ...
Q517792 - Two layout groups containing controls in AutoSize mode hang up an application if the alignment settings of these groups are set to occupy the entire client area ExpressNavBar B238354 - ...
- ADD: Added the method TEnumProp.SetEnumType, setting all enumerable type items in compliance with the Delphi type: SetEnumType(TypeInfo('TMyDelphiEnumType')); - ADD: Added the functions PathLength...
not all of the features mentioned in this file are available inall editions of the product.This update resolves the following issues:IDE* Using the up/down arrow keys to navigate and select items from...
Processing All of a List's Items in Random Order Recipe 5.7. Keeping a Sequence Ordered as Items Are Added Recipe 5.8. Getting the First Few Smallest Items of a Sequence Recipe 5.9. Looking for...
CurrPorts displays the list of all currently opened TCP/IP and UDP ports on your local computer. For each port in the list, information about the process that opened the port is also displayed, ...
Setting an intent lock at the table level prevents another transaction from subsequently acquiring an exclusive (X) lock on the table containing that page. Intent locks improve performance because ...
Alternatively, there's an "expand all" at the top of this document. Background C++ is the main development language used by many of Google's open-source projects. As every C++ programmer knows, the...
IMAP, POP, SMTP, MIME, and all those other Internet-related messaging protocols. With the help of the JavaBeans Activation Framework (JAF), your applications can now be mail-enabled through the ...
ARM processors and coprocessors up to and including v8. Contents: 1. ARM assembly compatibility 2. UAL and pre-UAL syntaxes 3. IT block handling 4. Alternate encodings 5. Output formats ...
versionInfo - Used to repopulate the version information in AndroidManifest.xml since newer aapt requires version information to be passed via parameter compressionType - Used to determine the ...