Analytics Software

Hello,

I am attempting to build a dashboard (10g) which draws in a number of tables (queries) from Answers. These tables are governed by dashboard-level prompts.

One of the tables needs to exhibit a cost profile of the various types of work undertaken by an individual contractor (prompted) over a given time period. It also, for comparison purposes, has to show the equivalent cost profile at national level in a separate column.

Each of the columns/measures is created using an SQL script which makes reference to the appropriate dashboard prompt. So, to display the individual contractor's profile, the SQL is as follows:

SUM(filter ("FACT Submissions Outcomes"."Profit Costs_Sum" using Office."Account Number"='@{p_provider}' AND Submission."Submission Date" BETWEEN TIMESTAMPADD(SQL_TSI_MONTH, -5, date '@{ p_Latest_sub}') AND date '@{ p_Latest_sub}') BY "Case Stage/Level"."Case Stage/Level")/sum(filter ("FACT Submissions Outcomes"."No. Claims" using Office."Account Number"='@{p_provider}' AND Submission."Submission Date" BETWEEN TIMESTAMPADD(SQL_TSI_MONTH, -5, date '@{ p_Latest_sub}') AND date '@{ p_Latest_sub}') by "Case Stage/Level"."Case Stage/Level")

The results generated by this statement are correct. However when I then try to use SQL in the next column to generate the national profile (i.e. without making any reference to the contractor prompt {p_provider}), as follows:

SUM(filter ("FACT Submissions Outcomes"."Profit Costs_Sum" using Submission."Submission Date" BETWEEN TIMESTAMPADD(SQL_TSI_MONTH, -5, date '@{ p_Latest_sub}') AND date '@{ p_Latest_sub}') BY "Case Stage/Level"."Case Stage/Level")/sum(filter ("FACT Submissions Outcomes"."No. Claims" using Submission."Submission Date" BETWEEN TIMESTAMPADD(SQL_TSI_MONTH, -5, date '@{ p_Latest_sub}') AND date '@{ p_Latest_sub}') by "Case Stage/Level"."Case Stage/Level")

the results given remain identical to those shown at individual contractor level. Can anyone tell me why the prompt value appears to be carried forward to the next column when the SQL does not mandate this, and advise of a solution?

Many thanks

EM