Clarity

As a Clarity/Portlet/NSQL n00b I want to learn.

I have downloaded the CA PDF document titled Clarity Studio Developers Guide (m001081e). I have read the NSQL section and have been successful in creating my first semi-complex NSQL query that parsed fine into SQL Server 2005.

Now I am tasked to create my first portlet to display a grid with a summary query then drill down into a detailed grid and then drill some more.

Before I get into the displaying data part of the portlet, I have to learn NSQL. According to the document mentioned above (the only reference I could find),:

"Only SELECT statements that specify which rows and columns to fetch from one or more tables are permitted. NSQL statements fail with an error message if a statement does not start with SELECT."

The query I need is complex. It is actually a series of queries, declaring variables to be filled by queries and used in subsequent queries. A very simplified excerpt in T-SQL follows:

Declare @ProjectFinishDate datetime
Declare @TotalBillableProjects as float
set @ProjectFinishDate = '06-30-2010' /* End of Q1-2010 */
select

@TotalBillableProjects = count(p.Name)
from

srm_projects p

inner join prj_projects pr ON p.ID=pr.prID

inner join odf_ca_project po ON p.ID=po.ID
where

p.is_active = 1 and po.pr_project_billable = 1 and p.is_template = 0 and p.is_program = 0

.. then the next query in the set uses the filled in variable @TotalBillableProjects as input and so on with many more queries and more declared variables (not shown in this example).

Q: How do I declare and save variables in NSQL so I can make complex queries dependent on results of previous query?
This is for temporary variables declared internal to the query, besides the user-input vars (which is another issue on how to read user input), like the @ProjectFinishDate shown above.

I guess one easy solution would be to have NSQL execute a SQL Server Stored Procedure so the NSQL used is minimal, and the complex query logic resides in SQL Server (as a SP). Is this possible?

Thanks,

Alex

OK : you are jumping into the hard stuff for a n00b thread! :grin:

--

I think you have 2 alternatives to how to build this sort of functionality;

#1 - you have a "summary" portlet - you click onto a value in that portlet and it "calls" another portlet (on a different (or the same) portlet page) which shows the "detail". I tend to call this a "drill down" portlet - if you search for "drill down" on the board you should find a number of threads talking about this technique (which boils down to the "link" in the summary portlet passing details to the detail portlet via the URL on the portlet page upon which the detail portlet exists) .

i.e. you end up building a series of separate (but connected by the parameters) portlets.

#2 - you use "hierarchy portlets "- i.e. where you "expand" a node in a summary portlet and it displays the underlying detail in a "navigator" like manner. These are a bit interesting to "code" (and I must admit to never having used these myself in anger, but I am keen to find a business-case that allows me to build one!). Have a search on the boards for "hierarchy portlets" for that.

i.e. you end up with one portlet that "expands" to show you the details.

--

My gut feeling would be #1 (mostly since I have built lots of these in the past and they sound much easier to code than #2!).

I think its important for you to understand what you can (and more importantly can NOT) do in the GUI with portlets before you start to try to design a solution (or promise anything to the users! 8o )

--

In terms of learning NSQL - IMHO NSQL is really just a matter of "wrapping" your SQL in the NSQL stuff. So I'll always build a portlet by starting in pure-SQL and only stick the NSQL bits in at the end - the NSQL bits just being the "wrapper" that Clarity needs to present the details back to the Clarity GUI and the NSQL-ification of any parameters that the query uses.

--

Hope that points you in some interesting directions to learn from!

Dave wrote:

OK : you are jumping into the hard stuff for a n00b thread! :grin:

Oh, it's more fun that way!

I understand your #1 option. Seems the way to go about drilling down (with my limited knowledge at this time). But my question above was more basic than that as I have not yet gotten into the "creating portlets experience [size=1]tm[size]"

The question was how to define and use variables in queries or alternatively how to run stored procedures. I have the query in T-SQL already and it runs fine. I could easily make it a stored procedure if I know it's the best way to run it from SQL Server (if not then I need to create/fill/save vars in the portlet - don't know if this is the correct terminology).

I have not promised to the users :grin: just the boss that runs the Clarity dept. here asks to plug this query into a portlet. :blink:

Thanks for the input!

(BTW: the way I am learning NSQL is by looking at existing queries in our system but i have not yet found the answer)

Alex

OK, getting back to "basics" a bit then....

There are 2 (no hang on make that 3, no 4) ways to "use a variable" in your NSQL.

#1 - The "simple way" - you write your query to select all the data as a big dump - don't try to include your "variable" in the query logic, but make sure that it is a returned colum value in the data-dump.

When you build your portlet, you put the "variable column" into the portlet FILTER section (rather than the LIST section, although it can be in the LIST as well).

Now when you run the portlet you get a filter field for your "variable" in the portlet header.

( And if you want to associate a lookup with that filter filed, you do this in the QUERY / attribute properties )

--

#2 - Using a variable that you DON'T want to use (or CAN'T use) in the query data dump.

In your query logic you code the "variable" like this ;
@WHERE:PARAM:USER_DEF:DATE:P_DATE@ ("P_DATE" is my invention- use whatever name you need)
or
@WHERE:PARAM:USER_DEF:INTEGER:P_ID@ ("P_ID" is my invention - use whatever name you need)
etc
(there is a similar construct for CHAR fields too)

This will create in your query attributes called "param_p_date" or "param_p_id" etc

You can then use these "param_" fields in your FILTER section of the portlet in a similar manner as #1

(this is what I was getting at by saying "NSQL-ification of any parameters" in my earlier post)

--

#3 - Using a "built-in" variable - like the @WHERE:PARAM:USER_ID@ for the USER_ID - there are a finite list of these built-ins in the documentation.

This will allow you to restrict query results based on (eg) the logged-in user.

--

#4 - Referencing a variable contained on the URL of the currently displayed Clarity "page";

You can read the URL variables using things like this;

@WHERE:PARAM:XML:STRING:/data/drill_from/@value@ ("drill_from" is my invention here)

This is the "drill-down" technique I am referring to in my earlier post - the URL must contain the text (eg) "drill_from=1234" and that construct allows you to use the 1234 value in the query. Its your job to build that URL from the "calling portlet" (ref the other threads on this subject to see how you do that!).

--

I wonder if I can (or anyone else can) think of any other ways of referencing variables in queries! :grin:


All the above are examples though, look in the documentation for better explanations/examples.

--

And the comment about basing queries on stored procedures is also possible - look up the thread on "table functions" on the boards for some information on that.

This is great info Dave. Very helpful.

#4 is basically a Request.QueryString in ASP.NET to scrape info from URL parameters

I will look into all these examples. Will keep me busy and off the streets for a while -_-

Coming back to this issue as I now have the time to work on this next stage of my first Clarity Studio project.

I think I understand your examples, but coming from procedural languages (e.g. C# or even T-SQL) where you declare a variable, then assign a value to it an keep it alive for later use, this is still confusing in Clarity Studio to me.

This is what I need to do. I have a set of complex queries to finally go into Grid Portlets. One of them requires a total value calculated, then this value used in a subsequent query.

Specifically, in MSSQL I declare a variable, then calculate a COUNT(*) and put the result into this variable. Simplifiying the queries for the example:

-------------- #1

Declare @TotalBillableProjects as float
select @TotalBillableProjects = count(p.Name)
from 
  srm_projects p
  (some joins here)
where (conditions here)

--------------

Now I have a temporary value in @TotalBillableProjects that I can later use in another query to calculate a percentage of incidences over the total as follows (simplified example):

-------------- #2

select  FunctionalArea,
count(p.Name) as [Number of Billable Projects],
CAST(((count(p.Name)/@TotalBillableProjects) * 100) as decimal(5,2)) as [% of Billable Projects]
from .....

--------------

As you can see, now I use @TotalBillableProjects to calculate the percentage (I do the same with several other variables).

In my very limited knowledge of Clarity at this stage, I figure I could create a separate NSQL query for example #1 here, save the total to some holding variable (this is what I don't know how to do yet), then create NSQL query #2 and fetch and use this calculated total from the first query.

Now I could create a portlet that would run #1, then run #2 using the calculated total from #1 and finally put #2 into a grid portlet.

Q: How/where do I create this holding variable (and many others I'll need) for this?

Alex

Right there is not real concept of doing it as you describe.

All NSQL and the portlets based on them is giving you is the ability to run a single SQL statement - i.e. no procedural langage (be it PL/SQL for Oracle, r euqvaluents) enter into the portlet itself.

--

HOWEVER, if you start to think "outside the box" a little you can do what you are describing...

In your last example, you could build a "summary portlet" that runs your query #1, returning your "TotalBillableProjects" as part of the data that the portlet displays.

Then you have another "detail portlet" that reads parameters from the page URL and you pass the "TotalBillableProjects" parameter as a link from portlet #1 to portlet #2.

All that is what I'm getting at with the "drill-down portlet" stuff I'm whittering on about.

--

HOWEVER, I'm making huge assumptions about how you want to implement this (portlets drilling to portlets etc). if all you are trying to do is write a really complicated bit of logic to use in portlet, then just do that on the database (as a database procedure) and call that from the portlet directly.

(Look up "TABLE FUNCTIONS" on this board for what I mean with that (for Oracle, I don't know what the MS equivalent is - but basically this is an Oracle technique to write a procedure that you can call as if it were a SQL statement, i.e. returning a dataset or "table" of data).

--

Ultimately you have to get the portlet to work from "just" a SQL call, but then within the SQL bit you can be as clever as you need to be (linking, based on a view, based on a procedure, reading data from different data-sources / web-services, etc etc etc)

Drilling down is perfect as I will be required to do that in one of these grid portlets to display summary/detail info.

Same as in Oracle, you are describing Stored Procedures (what it's called in MS-SQL).

The idea of the two portlets is what I'm looking for (I think). Are you saying I can create a portlet to hold the result of the first query (just a value), and don't display it , then pass and read params from the URL (as shown in your earlier messages)?

I'll look into this and see if I can make it work. Learning something new every day.

Thanks Dave.

Alex_Feldstein wrote:

Are you saying I can create a portlet to hold the result of the first query (just a value), and don't display it , then pass and read params from the URL

Yes - exactly this. :grin:

--

The "TABLE Functions" stuff is a special type of Stored Procedure though - "normal" SPs will just return a single value (or single set of values), these are returning sets of data.

Actually my memory wasn't as good as I thought it was, the thread on these boards I was thinking of WAS to do with this in MS-SQL rather than Oracle! I've used the same technique in oracle though.

2290223

I have a new query that returns the totals (NSQL doesn't allow a single returned value and insists on having a RESOURCE so I supplied a dummy one).

I created a new grid portlet to run this query and hold the returned value (is just one row with a COUNT(*) of course).

Q:How do I now run this portlet from the on eI created previously that will use this value? (I still have to modify that full query to account for this value, but first things first).

I am not sure how to call another page and pass the variable as a URL param that i can scrape layer in the receiving portlet/query?

I know I need some handolding for the first one (don't we all?), but all of this is a little alien coming from a procedural-experience.

OK...

In the QUERY for portlet #1 (summary portlet) you need to "code in" the paramater that you want to "pass down" into the query results.

In the QUERY for portlet #2 (detail portlet) you code the SQL to use the parameter value "scraped" from the URL using the syntax I posted earlier (we'll come back to how that all works in a moment).

You need to create a new portlet PAGE, make it LINKABLE and define a "Link Parameter" (this option will not appear until you have made the page linkable). What you call this parameter is what will appear in the URL. Then place portlet #2 on that page.

Now go back to the QUERY for portlet #1, and add a new entry in the "Linking" section. In the "Action" you should see the portlet PAGE that you have just created. When you select that, you should then be prompted for what you want to put into the "Link Parameter" you defined above - so you map that to the "pass down" parameter from the underlying query.

Finally you need to link the "Linking" action defined in Query #1 with a field which is displayed in Portlet #1.

Phew!

--

Note that you are NOT restricted to a single "Link Parameter" / "Linking" action - i.e. you can pass many parameters and also have many different drill-downs from the same portlet.

This post

2284225

also explains what I'm trying to say above (just in case I'm not making sense!)

:rolleyes:

Dave,

The explanation is great. I really appreciate what you are doing, helping me 9and others that might be reading this or will in the future).

The link you provided also has a clear explanation of the LINKING procedure.

What is not clear from the examples on passing parameters through the URL is that they all assume the parameter goes for the WHERE clause (a fair assumption when you want ot filter detail data).

In this case I need to plug in a calculated value for math into one of the columns. The @WHERE doesn't belong there and NSQL doesn't parse.

What I have in the @SELECT for one of the columns is:

@SELECT:METRIC:USER_DEF:IMPLIED:CAST(((COUNT(p.Name)/<calculated value goes here>* 100) as decimal(5,2)):PctofBillableProjects@,

My value is linked as: totalbillableprojects, so this needs to be converted to:

@SELECT:METRIC:USER_DEF:IMPLIED:CAST(((COUNT(p.Name)/<totalbillableprojects goes here>* 100) as decimal(5,2)):PctofBillableProjects@,

What is the proper syntax to scrape from the URL then?

The example provided says something like:
"@WHERE:PARAM:XML:INTEGER:/data/totalbillableprojects/@value@"

which doesn't work here.

I would never try to push the "NSQL parameters" into the "NSQL return" constructs...

So where you have;

@SELECT:METRIC:USER_DEF:IMPLIED:CAST(((COUNT(p.Name)/<calculated value goes here>* 100) as decimal(5,2)):PctofBillableProjects@,
FROM <<some tables and stuff>>

(I'm just going to rewrite this to illustrate what I am trying to get at)

i.e. you have

@SELECT:METRIC:USER_DEF:IMPLIED:<< something really complex>>:PctofBillableProjects@,
FROM <<some tables and stuff>>

I'd just write that as

@SELECT:METRIC:USER_DEF:IMPLIED:my_calc_column:PctofBillableProjects@,
FROM
(
SELECT << something really complex>> AS my_calc_column
FROM <<some tables and stuff>>
)

and the @WHERE/URL@ syntax will work now since it is in the "inner select" rather than in the NSQL "wrapper" bit.

--

Big fan of only ever having all that "outer" NSQL @SELECT@ stuff returning simple columns and doing ALL the data manipulation (CASE statements/calls to procedures/datatype casting/etc) done in an "inner select".