CA 11.1.7 FP4
I have a Data Set (which is created by a user defined SQL) which contains dimensional attributes and numeric values (Amounts, etc..) which I want to total on the dashboard.
Unfortunately, the Amount columns are not being considered as measures when I try and use in a dashboard so the default aggregate when dragging on to the canvas is Count Distinct.
I know I can change the attributes of the column in the dashboard to be a measure - And, this works as it should.
However, I want to change the default in the data set to be an Measure so that I don't have to change it in every dashboard.
I presume this is easily done and I am missing something simple.
I already have dashboards that work this way, but I can't tell what is the difference in this case.
Any help would be greatly appreciated.
Thanks in advance,
Adam.
To achieve what you want, the aggregation property should be set correctly in the source. What's the source for the Dashboard? A package, data module, something else?
When you say the Data Set is created by user defined SQL - where is that SQL? In Framework Manager? If it is in Framework Manager and your Dashboard is using a package then check the properties of the query subject which has that SQL. The columns (called query items) of that query subject have aggregate properties and you can change it there but before that check the SQL to understand what that column is supposed to be for.
Ex:
Select
sum(Sales_amt) as Sales_Amt,
count (distinct Cust_ID) as Customer_Count,
Cust_ID as Cust_ID,
from DB.Sales
group by Cust_ID
In this case, the aggregation property of column 'Sales_Amt' can be set to 'Sum' or 'Total' but not the column for 'Customer_Count' because if you do that then the data will be wrong.
Quote from: adam_mc on 27 Oct 2023 03:23:53 PM
CA 11.1.7 FP4
I have a Data Set (which is created by a user defined SQL) which contains dimensional attributes and numeric values (Amounts, etc..) which I want to total on the dashboard.
Unfortunately, the Amount columns are not being considered as measures when I try and use in a dashboard so the default aggregate when dragging on to the canvas is Count Distinct.
I know I can change the attributes of the column in the dashboard to be a measure - And, this works as it should.
However, I want to change the default in the data set to be an Measure so that I don't have to change it in every dashboard.
I presume this is easily done and I am missing something simple.
I already have dashboards that work this way, but I can't tell what is the difference in this case.
Any help would be greatly appreciated.
Thanks in advance,
Adam.
Have you tried bringing the data set into a data module and defining how you want each column to be treated? You can change the usage, along with the default aggregation (for measures). YOu can then base your dashboards, reports etc on the data module.
Cheers!
MF.
I did indeed already solve by bringing into a Data Module.
Thanks,
Adam