Dynamic Template

[vance_duke]

I wanted to ask the Community for some direction or guidance on Pocketquery and a dynamic template layout.

We are using Pocketquery for Confluence, and I have some query parameters that I am passing into a SQL statement, and they are working fine to filter the data as expected.  I have a template that I am then displaying the results of the query, with about 10-12 columns.

What I am wondering is how can I create or pass in parameters to the Pocketquery macro, that are then available for reference in the Template to potentially show or hide certain columns from the query results.

As I mentioned, there are 10-12 columns, and most are relevant, but different teams using the same query might want to see more or less data.  I did not want to put a lot of conditional logic into the query, and just allow the query to return the expected data, but then dynamically show different subset of the data based on parameters.

i.e. (simple example)

Query returns Product Name, Price, Quantity Sold, Total Price sold, Cost of Product, Profit.

Some teams only need/want to see Product Name and Price
Some teams only need/want to see Product Name, Quantity Sold and Total Price
Some teams only need/want to see Product Name, Price, Quantity Sold, Total Price, Cost of Product and Profit.

Hope that helps.  I have looked at the documentation, and maybe I am just not interpreting them correctly so any direction or guidance would be helpful.

Thanks

[Sven Schatter _Lively Apps_ · Accepted solution]

Hi @vance_duke,

that's a a great question/use case and I have two answers for you. A clean one and a dirty/hacky one. Let's start with the clean and easy one:

In your case it definitely sounds like you should be splitting this up into different Queries! You said that different teams need different versions of this Query, so I would simply go ahead and create a Query for each team. Something like "Products_TEAM_A", "Products_TEAM_B", etc. This keeps everything organized and you can change things for teams independently, instead of trying to organize a big Query in a generic way.

Now for the hacky approach: Indeed, you do have the ability to access parameter values inside of PocketQuery templates, using the PocketQuery Velocity API You could use something like $queryParameters.get("parameter") to read out parameter values and base your rendering off of that. You could add a few "useless" parameters to your Query so that PocketQuery will generate fields for them in the Macro Editor. This could be done by defining parameters in comments (e.g. depending on your SQL dialect "// :parameter") or adding lines like "AND :parameter = :parameter". However, this is a bit of a hack and probably not the way to go.

Parameters will always be text fields in the macro browser and your users will have no way of knowing what to type in these fields. Also, if you want to make modifications to the Query/Template it's quite easy to break stuff with a setup like this. So even though it would be possible I strongly recommend that you go with the clean option and simply make a Query for each team. :)

Hope this helps!

Best regards,
Sven

[vance_duke]

Sven, thanks for the reply and I had thought about separate queries. However, that becomes more of a management nightmare when trying to maintain a report for different levels of management, and one that could change.  I am trying to give the teams control of the data that they would like to see, without limiting their ability to see data. The query runs well, and I am not worried about that, and I am not "mixing" dis-similar data elements, like sales and profit data with something like customer data.

Regarding your second suggestion, I was thinking about that as well. Pass is some query parameters that would then be returned in the query that in turn would guide the output. This would allow the parameter to get from the query parameter settings through the query to the template.

i.e. "select Price, :ShowPrice as ShowPrice".

Thanks for the feedback.  I wonder if anyone else has any other ideas.

[Sven Schatter _Lively Apps_]

Oh yes, the "select Price, :ShowPrice as ShowPrice" is also a very smart hack! Nice one! ;)

Your users would still need to know that they have to type "true" or "false" (or whatever you decide to use) into that. But if that's easier for you, that's certainly a way to do it.

[vance_duke]

This worked for me.

Variable passed into the query and then set as a returned value in the query. Then, in the template, I look for the value of the "show" variable, and if it is 1, then show the column, otherwise, don't.

Thanks for the direction.