How to create Dynamic Baseline for Chart based on a Parameter Value

[ThatKingslayer]

I am interesting in being able to allow a user to dynamically choose their baseline for a chart instead of hardcoding in a value such as 85% or 10,000 units. I've been trying to figure this out but just haven't been able to get there yet.

For the sake of example, I am interested in a baseline based around a percentage. I have a chart that shows a trend of rates and the user wants to be able to change the baseline from 80% to 85% or to 90%. My thought was to build a Value Prompt with a list of pre-defined values (.80, .85, .90, etc.) and have that create a parameter value named Benchmark.

I was hoping it would be possible within my chart to simply Query Calculation Baseline that references the parameter value of the parameter Benchmark which can be changed by the user in the prompt. I could not get this to work.

Is there a better way to do this or do any of you have suggestions?

[MFGF]

I am interesting in being able to allow a user to dynamically choose their baseline for a chart instead of hardcoding in a value such as 85% or 10,000 units. I've been trying to figure this out but just haven't been able to get there yet.

For the sake of example, I am interested in a baseline based around a percentage. I have a chart that shows a trend of rates and the user wants to be able to change the baseline from 80% to 85% or to 90%. My thought was to build a Value Prompt with a list of pre-defined values (.80, .85, .90, etc.) and have that create a parameter value named Benchmark.

I was hoping it would be possible within my chart to simply Query Calculation Baseline that references the parameter value of the parameter Benchmark which can be changed by the user in the prompt. I could not get this to work.

Is there a better way to do this or do any of you have suggestions?

Hi,

I just tried this using a combination chart (with the prompted value as the line) and it works fine for me. Don't forget to set the aggregation for your query item containing the parameter - Average worked for me.

Cheers!

MF.

[ThatKingslayer]

Hi,

I just tried this using a combination chart (with the prompted value as the line) and it works fine for me. Don't forget to set the aggregation for your query item containing the parameter - Average worked for me.

Cheers!

MF.

Thanks for the response, sir! I know there must be some small thing that I am just missing here as I am unable to make it work. If you don't mind, I'll describe my steps and I bet you'll quickly catch what piece I am missing.

• Value Prompt created with a list of static choices (.80, .85, .90) and a parameter called Benchmark
• Query calculation baseline added to my chart. For the expression definition, I used the parameter ?Benchmark?
• This created a data item in the query that is driving my chart. The data item is called DataItem1 (I did not rename it). Based on your suggestion, I adjusted the aggregation to be AVERAGE instead of DEFAULT.
• After running the report and making a selection of .80 from my Value Prompt, I receive the following error: RQP-DEF-0177 An error occurred while performing operation 'sqlPrepareWithOptions' status='-248'.
  UDA-SQL-0190 A local database name is required within a multi-database to locate a specific database.

Any thoughts?

[MFGF]

Thanks for the response, sir! I know there must be some small thing that I am just missing here as I am unable to make it work. If you don't mind, I'll describe my steps and I bet you'll quickly catch what piece I am missing.

• Value Prompt created with a list of static choices (.80, .85, .90) and a parameter called Benchmark
• Query calculation baseline added to my chart. For the expression definition, I used the parameter ?Benchmark?
• This created a data item in the query that is driving my chart. The data item is called DataItem1 (I did not rename it). Based on your suggestion, I adjusted the aggregation to be AVERAGE instead of DEFAULT.
• After running the report and making a selection of .80 from my Value Prompt, I receive the following error: RQP-DEF-0177 An error occurred while performing operation 'sqlPrepareWithOptions' status='-248'.
  UDA-SQL-0190 A local database name is required within a multi-database to locate a specific database.

Any thoughts?

Hi,

A quick sanity check. Are you using a combination chart for this? Do you have the columns driven from a measure item in your package that you know will work? Have you used a cast() function in your query calculation to convert the text parameter value to a numeric value? When you created the prompt, you didn't also create an invalid filter by any chance?

The error you are posting here is normally triggered where you have a package with a data source that does not specify the Catalog or Schema to find the database, and it doesn't sound to me like something your prompt would have anything to do with.

I am attaching a copy of an example report over the GO Sales (Query) package that effectively does what you need - albeit the measure is Quantity, so the prompt values reflect Quantity baselines). I created this in CA 11.1.1

Cheers!

MF.

[ThatKingslayer]

Hi,

A quick sanity check. Are you using a combination chart for this? Do you have the columns driven from a measure item in your package that you know will work? Have you used a cast() function in your query calculation to convert the text parameter value to a numeric value? When you created the prompt, you didn't also create an invalid filter by any chance?

The error you are posting here is normally triggered where you have a package with a data source that does not specify the Catalog or Schema to find the database, and it doesn't sound to me like something your prompt would have anything to do with.

I am attaching a copy of an example report over the GO Sales (Query) package that effectively does what you need - albeit the measure is Quantity, so the prompt values reflect Quantity baselines). I created this in CA 11.1.1

Cheers!

MF.

Good afternoon! I apologize for the delayed response. I had some other things come up that took me away from being able to look at this. I will review your attachment and see if it can be tweaked to suit my needs. Thanks again! I'll report back.