sql server planet - Page 529

BIP 11g Dynamic SQL

- by Tim Dexter

Back in the 10g release, if you wanted something beyond the standard query for your report extract; you needed to break out your favorite text editor. You gotta love 'vi' and hate emacs, am I right? And get to building a data template, they were/are lovely to write, such fun ... not! Its not fun writing them by hand but, you do get to do some cool stuff around the data extract including dynamic SQL. By that I mean the ability to add content dynamically to your your query at runtime. With 11g, we spoiled you with a visual builder, no more vi or notepad sessions, a friendly drag and drop interface allowing you to build hierarchical data sets, calculated columns, summary columns, etc. You can still create the dynamic SQL statements, its not so well documented right now, in lieu of doc updates here's the skinny. If you check out the 10g process to create dynamic sql in the docs. You need to create a data trigger function where you assign the dynamic sql to a global variable that's matched in your report SQL. In 11g, the process is really the same, BI Publisher just provides a bit more help to define what trigger code needs to be called. You still need to create the function and place it inside a package in the db. Here's a simple plsql package with the 'beforedata' function trigger. Spec create or replace PACKAGE BIREPORTS AS whereCols varchar2(2000); FUNCTION beforeReportTrig return boolean; end BIREPORTS; Body create or replace PACKAGE BODY BIREPORTS AS FUNCTION beforeReportTrig return boolean AS BEGIN whereCols := ' and d.department_id = 100'; RETURN true; END beforeReportTrig; END BIREPORTS; you'll notice the additional where clause (whereCols - declared as a public variable) is hard coded. I'll cover parameterizing that in my next post. If you can not wait, check the 10g docs for an example. I have my package compiling successfully in the db. Now, onto the BIP data model definition. 1. Create a new data model and go ahead and create your query(s) as you would normally. 2. In the query dialog box, add in the variables you want replaced at runtime using an ampersand rather than a colon e.g. &whereCols. select d.DEPARTMENT_NAME, ... from "OE"."EMPLOYEES" e, "OE"."DEPARTMENTS" d where d."DEPARTMENT_ID"= e."DEPARTMENT_ID" &whereCols Note that 'whereCols' matches the global variable name in our package. When you click OK to clear the dialog, you'll be asked for a default value for the variable, just use ' and 1=1' That leading space is important to keep the SQL valid ie required whitespace. This value will be used for the where clause if case its not set by the function code. 3. Now click on the Event Triggers tree node and create a new trigger of the type Before Data. Type in the default package name, in my example, 'BIREPORTS'. Then hit the update button to get BIP to fetch the valid functions.In my case I get to see the following: Select the BEFOREREPORTTRIG function (or your name) and shuttle it across. 4. Save your data model and now test it. For now, you can update the where clause via the plsql package. Next time ... parametrizing the dynamic clause.

Search Results

Search found 98684 results on 3948 pages for 'sql server planet'.

Page 529/3948 | < Previous Page | 525 526 527 528 529 530 531 532 533 534 535 536 | Next Page >

- by Tim Dexter

- by Jagira

- by guitar-

- by zecougar

- by pmr

- by morpheous

- by Eva

- by Sid

- by Khou

- by spike07

- by Chris Farmer

- by Nick

- by Camran

- by immuner

- by vsync

- by Pydev UA

- by jendy

- by James

- by rima

- by Filip Kierzek

- by Rayzor78

- by WebDevHobo

- by Guillaume

- by Ice

- by josh

< Previous Page | 525 526 527 528 529 530 531 532 533 534 535 536 | Next Page >