Tree prompts are very useful in Cognos report prompt page, but it needs dmr packages to works.

Problem with dmr packages is that report performance is awful when source database tables contain millions of lines.

Does someone know if a custom tree prompt based on relational package exists ? 

Hi Pierre-Frank MOUZON,

That phrase could mean a few different things depending on your context.

For example

If you're referring to Python or R packages: 

• Are you talking about a decision tree or tree based model.
• And by "relational package," do you mean a relational database interface Ie., using tree prompts or build from relational data

2If you mean prompt engineering or LLM use:

• Are you describing a structured prompt  that represents hierarchical relationships.
• "And  is relational package " referring to something LangChain , LlamaIndex or a similar framework.

What language/environment you're using and what you mean by" custom tree point prompt"

Tree prompts are very useful in Cognos report prompt page, but it needs dmr packages to works.

Problem with dmr packages is that report performance is awful when source database tables contain millions of lines.

Does someone know if a custom tree prompt based on relational package exists ? 

Hi Azhagumuthu,

To explain my request, I'm talking about Hierarchical prompt in Cognos reports, like this :

Which we can use only if source package for the report has this kind of DMR objects :

My request is : could it be possible to have an equivalent (custom ?) prompt with relational objects in package :

Hi Pierre-Frank MOUZON,

That phrase could mean a few different things depending on your context.

For example

If you're referring to Python or R packages: 

• Are you talking about a decision tree or tree based model.
• And by "relational package," do you mean a relational database interface Ie., using tree prompts or build from relational data

2If you mean prompt engineering or LLM use:

• Are you describing a structured prompt  that represents hierarchical relationships.
• "And  is relational package " referring to something LangChain , LlamaIndex or a similar framework.

What language/environment you're using and what you mean by" custom tree point prompt"

Tree prompts are very useful in Cognos report prompt page, but it needs dmr packages to works.

Problem with dmr packages is that report performance is awful when source database tables contain millions of lines.

Does someone know if a custom tree prompt based on relational package exists ? 

You do not need to have a "fully enabled" DMR package in order to use a tree prompt.  Back in the day (think Cognos 8.2/3), the company I worked for had an accounting ledger that was 22 levels deep with thousands of rows of data.  Prior to the tree prompt, the only way was to create a cascading prompt that had 22 pages -- talk about painful from the user's perspective.  When the tree prompt was introduced, we did not want to create a DMR for the whole package because of performance reasons as well as the development time to switch things over.  Our solution was to create a stand-alone query subject that was DMR and had no joins to any of the fact tables.  We placed it into folder with a name like "To be used only for Tree Prompts".  We were able to replace the 22 pages with the tree prompt and life was good for us and for the user base (they were thrilled to not have to go through 22 prompt pages.   By not having any joins on the query subject, it forced the report developers to only use it for tree prompts.   The use of the parameter remained the same as if it wasn't a DMR.  So you would just do something like "Date in ?pTreeDatePrompt?" and it worked.  I have only used this technique once since (v10.x timeframe).  I have not attempted this technique in v11 or 12 - but I can't see a reason why it wouldn't still work. 

I also have created a custom tree prompt (similar to Paul's - just not searchable) - that does not require a DMR package.  It was developed in v11.1 and since then it has stood the test of time through v12.  Get in touch with me if you are interested in knowing more details on it.  I can't recall the details surrounding the performance but from what I recall, it does a pretty good job handling at least 1K rows of data (might be closer to 3K -- can't recall how many products that customer has off the top of my head).

Hope that is helpful. 

Hi Azhagumuthu,

To explain my request, I'm talking about Hierarchical prompt in Cognos reports, like this :

Which we can use only if source package for the report has this kind of DMR objects :

My request is : could it be possible to have an equivalent (custom ?) prompt with relational objects in package :

Hi Pierre-Frank MOUZON,

That phrase could mean a few different things depending on your context.

For example

If you're referring to Python or R packages: 

• Are you talking about a decision tree or tree based model.
• And by "relational package," do you mean a relational database interface Ie., using tree prompts or build from relational data

2If you mean prompt engineering or LLM use:

• Are you describing a structured prompt  that represents hierarchical relationships.
• "And  is relational package " referring to something LangChain , LlamaIndex or a similar framework.

What language/environment you're using and what you mean by" custom tree point prompt"

Tree prompts are very useful in Cognos report prompt page, but it needs dmr packages to works.

Problem with dmr packages is that report performance is awful when source database tables contain millions of lines.

Does someone know if a custom tree prompt based on relational package exists ? 

Thanks John for your idea.

A remaining goal concerns query relational filter based on user selection in the tree prompt, because in my case user can select several members on any levels.

In a time dimension, user can select year 2024 + quarter 1,2 + day 10, 20, .... so filter expression could be complex to write.

May be with macro functions ?

You do not need to have a "fully enabled" DMR package in order to use a tree prompt.  Back in the day (think Cognos 8.2/3), the company I worked for had an accounting ledger that was 22 levels deep with thousands of rows of data.  Prior to the tree prompt, the only way was to create a cascading prompt that had 22 pages -- talk about painful from the user's perspective.  When the tree prompt was introduced, we did not want to create a DMR for the whole package because of performance reasons as well as the development time to switch things over.  Our solution was to create a stand-alone query subject that was DMR and had no joins to any of the fact tables.  We placed it into folder with a name like "To be used only for Tree Prompts".  We were able to replace the 22 pages with the tree prompt and life was good for us and for the user base (they were thrilled to not have to go through 22 prompt pages.   By not having any joins on the query subject, it forced the report developers to only use it for tree prompts.   The use of the parameter remained the same as if it wasn't a DMR.  So you would just do something like "Date in ?pTreeDatePrompt?" and it worked.  I have only used this technique once since (v10.x timeframe).  I have not attempted this technique in v11 or 12 - but I can't see a reason why it wouldn't still work. 

I also have created a custom tree prompt (similar to Paul's - just not searchable) - that does not require a DMR package.  It was developed in v11.1 and since then it has stood the test of time through v12.  Get in touch with me if you are interested in knowing more details on it.  I can't recall the details surrounding the performance but from what I recall, it does a pretty good job handling at least 1K rows of data (might be closer to 3K -- can't recall how many products that customer has off the top of my head).

Hope that is helpful. 

Hi Azhagumuthu,

To explain my request, I'm talking about Hierarchical prompt in Cognos reports, like this :

Which we can use only if source package for the report has this kind of DMR objects :

My request is : could it be possible to have an equivalent (custom ?) prompt with relational objects in package :

Hi Pierre-Frank MOUZON,

That phrase could mean a few different things depending on your context.

For example

If you're referring to Python or R packages: 

• Are you talking about a decision tree or tree based model.
• And by "relational package," do you mean a relational database interface Ie., using tree prompts or build from relational data

2If you mean prompt engineering or LLM use:

• Are you describing a structured prompt  that represents hierarchical relationships.
• "And  is relational package " referring to something LangChain , LlamaIndex or a similar framework.

What language/environment you're using and what you mean by" custom tree point prompt"

Tree prompts are very useful in Cognos report prompt page, but it needs dmr packages to works.

Problem with dmr packages is that report performance is awful when source database tables contain millions of lines.

Does someone know if a custom tree prompt based on relational package exists ? 

The short answer is yes.  

Using the tree prompt - you could select 2024, 2025 Q1 and 2025 Oct and 2025 Nov 25.  The resultant filter should return back the full year of 2024, Jan 1 thru Mar 31 2025, plus the full month of October 2025 and the one day of Nov. 

Thanks John for your idea.

A remaining goal concerns query relational filter based on user selection in the tree prompt, because in my case user can select several members on any levels.

In a time dimension, user can select year 2024 + quarter 1,2 + day 10, 20, .... so filter expression could be complex to write.

May be with macro functions ?

You do not need to have a "fully enabled" DMR package in order to use a tree prompt.  Back in the day (think Cognos 8.2/3), the company I worked for had an accounting ledger that was 22 levels deep with thousands of rows of data.  Prior to the tree prompt, the only way was to create a cascading prompt that had 22 pages -- talk about painful from the user's perspective.  When the tree prompt was introduced, we did not want to create a DMR for the whole package because of performance reasons as well as the development time to switch things over.  Our solution was to create a stand-alone query subject that was DMR and had no joins to any of the fact tables.  We placed it into folder with a name like "To be used only for Tree Prompts".  We were able to replace the 22 pages with the tree prompt and life was good for us and for the user base (they were thrilled to not have to go through 22 prompt pages.   By not having any joins on the query subject, it forced the report developers to only use it for tree prompts.   The use of the parameter remained the same as if it wasn't a DMR.  So you would just do something like "Date in ?pTreeDatePrompt?" and it worked.  I have only used this technique once since (v10.x timeframe).  I have not attempted this technique in v11 or 12 - but I can't see a reason why it wouldn't still work. 

I also have created a custom tree prompt (similar to Paul's - just not searchable) - that does not require a DMR package.  It was developed in v11.1 and since then it has stood the test of time through v12.  Get in touch with me if you are interested in knowing more details on it.  I can't recall the details surrounding the performance but from what I recall, it does a pretty good job handling at least 1K rows of data (might be closer to 3K -- can't recall how many products that customer has off the top of my head).

Hope that is helpful. 

Hi Azhagumuthu,

To explain my request, I'm talking about Hierarchical prompt in Cognos reports, like this :

Which we can use only if source package for the report has this kind of DMR objects :

My request is : could it be possible to have an equivalent (custom ?) prompt with relational objects in package :

Hi Pierre-Frank MOUZON,

That phrase could mean a few different things depending on your context.

For example

If you're referring to Python or R packages: 

• Are you talking about a decision tree or tree based model.
• And by "relational package," do you mean a relational database interface Ie., using tree prompts or build from relational data

2If you mean prompt engineering or LLM use:

• Are you describing a structured prompt  that represents hierarchical relationships.
• "And  is relational package " referring to something LangChain , LlamaIndex or a similar framework.

What language/environment you're using and what you mean by" custom tree point prompt"

Tree prompts are very useful in Cognos report prompt page, but it needs dmr packages to works.

Problem with dmr packages is that report performance is awful when source database tables contain millions of lines.

Does someone know if a custom tree prompt based on relational package exists ? 

Do you have an example of query filter expression, transforming member into values to select relational items ?

The short answer is yes.  

Using the tree prompt - you could select 2024, 2025 Q1 and 2025 Oct and 2025 Nov 25.  The resultant filter should return back the full year of 2024, Jan 1 thru Mar 31 2025, plus the full month of October 2025 and the one day of Nov. 

Thanks John for your idea.

A remaining goal concerns query relational filter based on user selection in the tree prompt, because in my case user can select several members on any levels.

In a time dimension, user can select year 2024 + quarter 1,2 + day 10, 20, .... so filter expression could be complex to write.

May be with macro functions ?

You do not need to have a "fully enabled" DMR package in order to use a tree prompt.  Back in the day (think Cognos 8.2/3), the company I worked for had an accounting ledger that was 22 levels deep with thousands of rows of data.  Prior to the tree prompt, the only way was to create a cascading prompt that had 22 pages -- talk about painful from the user's perspective.  When the tree prompt was introduced, we did not want to create a DMR for the whole package because of performance reasons as well as the development time to switch things over.  Our solution was to create a stand-alone query subject that was DMR and had no joins to any of the fact tables.  We placed it into folder with a name like "To be used only for Tree Prompts".  We were able to replace the 22 pages with the tree prompt and life was good for us and for the user base (they were thrilled to not have to go through 22 prompt pages.   By not having any joins on the query subject, it forced the report developers to only use it for tree prompts.   The use of the parameter remained the same as if it wasn't a DMR.  So you would just do something like "Date in ?pTreeDatePrompt?" and it worked.  I have only used this technique once since (v10.x timeframe).  I have not attempted this technique in v11 or 12 - but I can't see a reason why it wouldn't still work. 

I also have created a custom tree prompt (similar to Paul's - just not searchable) - that does not require a DMR package.  It was developed in v11.1 and since then it has stood the test of time through v12.  Get in touch with me if you are interested in knowing more details on it.  I can't recall the details surrounding the performance but from what I recall, it does a pretty good job handling at least 1K rows of data (might be closer to 3K -- can't recall how many products that customer has off the top of my head).

Hope that is helpful. 

Hi Azhagumuthu,

To explain my request, I'm talking about Hierarchical prompt in Cognos reports, like this :

Which we can use only if source package for the report has this kind of DMR objects :

My request is : could it be possible to have an equivalent (custom ?) prompt with relational objects in package :

Hi Pierre-Frank MOUZON,

That phrase could mean a few different things depending on your context.

For example

If you're referring to Python or R packages: 

• Are you talking about a decision tree or tree based model.
• And by "relational package," do you mean a relational database interface Ie., using tree prompts or build from relational data

2If you mean prompt engineering or LLM use:

• Are you describing a structured prompt  that represents hierarchical relationships.
• "And  is relational package " referring to something LangChain , LlamaIndex or a similar framework.

What language/environment you're using and what you mean by" custom tree point prompt"

Tree prompts are very useful in Cognos report prompt page, but it needs dmr packages to works.

Problem with dmr packages is that report performance is awful when source database tables contain millions of lines.

Does someone know if a custom tree prompt based on relational package exists ? 

Tree prompts are very useful in Cognos report prompt page, but it needs dmr packages to works.

Problem with dmr packages is that report performance is awful when source database tables contain millions of lines.

Does someone know if a custom tree prompt based on relational package exists ?