Framework - Dashboard Datatype

[jasonmacpei]

We have a client id field, that we need to count distinct id's to get the number of clients we have. The client id contains alpha-numeric values and is stored in the database as a "Character Length 16".

I have set the usage as a fact and have created a dimension as a fact.  When I drop the field on a dashboard, I am not able to then change that count into different formats, as in, different number types, remove the abbreviation, etc.

I can set the default format in framework, which works, but I want to be able to go into the visualization in Cognos Dashboard and change how this is formatted.  The only option is "auto" and "text".  This is obviously because it is stored in the database as a string, but there must be some sort of work around that will allow me to change the counting format inside a dashboard.

Thanks!

[bus_pass_man]

I don't know what you mean by "different formats, as in, different number types, remove the abbreviation, etc."  Because of that and my uncertainty about what you are actually doing makes it difficult to suggest further actions.


You could create a calculation (either in the query subject or as a stand alone calculation) with the expression count (client ID).  That would give you a count of the number of client IDs.  The data type of the calculation would be numeric. That doesn't seem to be what you are trying, which confuses me which, admittedly, is an easily accomplished feat.

[cognostechie]

Client_ID , if a character field in the DB, should be marked as an attribute in FM, not as a Fact. You can create a calculated column in the Business Later with count distinct function and that will be a numeric which Cognos will automatically do. Set the aggregation property to calculated.

I don't know what you mean by changing formats in the Dashboard so can't advise. Is it a Dashboard or a report made to look like a Dashboard? 

[jasonmacpei]

Hi, thank you for the replies.

To be clearer, when creating a dashboard, and while a visualization is selected, and while looking at the fields, you can click on the ellipsis (three dots next to the field name) and from there select format.  If Cognos is recognizing the field as a number, you will have the option to set format to number, percent, etc.  When Cognos sees this is a string, the only options are Text and Auto.

My issue is that Framework is seeing this a string field because of the letters in the Client Id.

In the database layer, I do have a separate count in my dimensional model, I do have this set as a measure, with a Regular Aggregate as a count distinct.

This still comes up as a "text" field in the dashboard.

[cognostechie]

Framework Manager does not determine data type based on the data in the field. It determines by the data type specified in he table. Since the data type is character in the table so FM will automatically set that as a character data type in the Database Layer.

Dimensional Model (DMR) does not have it's data type. It will inherit the data type from the Model Query Subject in the Business Layer. This is where you have to change it. In your Model Query Subject, create a column with the count distinct expression and FM will set that to numeric after which you can change that to calculated. Then the DMR and the Dashboard will see that as a numeric. Use 'count (distinct Client_ID)' in the expression, not in the regular aggregate.