Question
· Aug 30, 2018

How to dynamically change ROWSPEC

Hello,

    When working with class queries you have to define the ROWSPEC at the beginning (See below).

Instead of using Year1 or Year2 as the column name, how can I use the passed in fiscal year (xFiscalYear)

so Instead of using "Year1" as the column name I can call it something like:  "Jul"_xFiscalYear

Query GLReportYearToYearTrend(xFiscalYear As %String) As %Query(CONTAINID = 0, ROWSPEC = "GLCode:%String,Description:%String,Year1:%String,Year2:%String,JulChange:%String") [ SqlProc ]
{
}

Discussion (10)1
Log in or sign up to continue

This is not quite an answer to your question. Just some clarifications:

  • Your example is a %Query class query, where you write ObjectScript code for the query rather than using SQL. I haven't written one of those for a while. I'm assuming that ROWSPEC and CONTAINID are required for these types of class queries, but I'm not 100% sure.
  • A "newer" way to write a custom ObjectScript-based class query, using the class %SQL.CustomResultSet, doesn't require ROWSPEC either. Sample.CustomResultSet is an example of this technique.
  • For a %SQLQuery class query, written using SQL, ROWSPEC and CONTAINID have not been required for a loooong time. They are dynamically created from the query itself.

I'm not sure %SQL.CustomResultSet is going to be a true solution here, as it also requires you to statically define properties that represent the columns being returned upfront, while Lyle would like to be able to let those column names depend on a runtime argument. You might be able to just do this at runtime and have dynamic dispatch take care of things, but that won't help the column metadata get set up.

Basically we are using the class queries as stored procedures to be called by Logi reports. All the developer has to do is perform a call method within the reports and get back a record set of data for the report.

Example:

Call SAT.ReportQueries_GLReportYearToYearTrend('2017') From SAT.ReportQueries

If there is a better way of doing this that would be great but this was the only way I knew how to do it at the time.

I tried Benjamin's suggestion (Select within a Select) but it doesn't work with a call Statement.

I just realized you're only on Caché 2012, which doesn't support table-valued functions, in which you can just SELECT from a function rather than having to use CALL, sorry.

On the other hand, I'd expect a BI tool like Logi to be capable of providing exactly the sort of UI-side labelling of columns, if not drive the entire YoY calculation. Not that I want to fend off the question, but if there's a full-fledged BI tool sitting on top of these results anyhow, let's make sure to use its full set of fledges :-)