Nonempty + Hide empty rows does not work on dashboard (#common)

ANM · December 8, 2020, 1:20pm

I have a dashboard using some reports where I used the functions “Nonempty” and “Hide empty rows”. Nevertheless, since I have changed the dimension settings to “common” within the dashboard, “(none)” reappears in the implemented reports. What can I do to hide empty rows within the dashboard?

My idea as an alternative option for one of the reports was to replace empty values by a string via a calculated member. But since I am a rookie I do not really know how.

CASE WHEN IsEmpty([Measures].[Issue Customer Request Type])
THEN Format([Measures].[Issue Customer Request Type],‘Sub-task’)
ELSE [Measures].[Issues created]
END

Thank you!

martins.vanags · December 14, 2020, 11:07am

Hi @ANM

Please export and share the definition of the report you created and let me know the name of the dimension which you are then using with the common page filter on the dashboard.
Here is how to export the definition:

Martins / eazyBI

ANM · December 14, 2020, 11:50am

common page filter

export -> need to filter or replace “none” within the dashboard
{
“cube_name”: “Issues”,
“cube_reports”: [ {
“name”: “[MGMT] Issues created by Request Type”,
“result_view”: “pie_chart”,
“definition”: {“columns”:{“dimensions”:[{“name”:“Measures”,“selected_set”:["[Measures].[Issues created]"],“members”:[]}]},“rows”:{“dimensions”:[{“name”:“Customer Request Type”,“selected_set”:["[Customer Request Type].[Artifactory Repository Access Request]","[Customer Request Type].[Bitbucket Repository Access Request]","[Customer Request Type].[Change Or Feature]","[Customer Request Type].[Dev. Environment]","[Customer Request Type].[Mail Request]","[Customer Request Type].[Emushape Request]","[Customer Request Type].[Customer Feedback]","[Customer Request Type].[IT Support Request]","[Customer Request Type].[IdefX Request]","[Customer Request Type].[Integration Test Env. Request]","[Customer Request Type].[New E3-Team Request]","[Customer Request Type].[Parasoft DTP Request]","[Customer Request Type].[Parasoft DTP Project Request]","[Customer Request Type].[Remove Access Request]","[Customer Request Type].[Archive Ressources Request]","[Customer Request Type].[E3SWPAC Components Request]","[Customer Request Type].[Bug Or Malfunction]","[Customer Request Type].[SDK Eng. Support Request]","[Customer Request Type].[Something Else Request]","[Customer Request Type].[Toolchain Support Request]","[Customer Request Type].[VWOS User Access Request]","[Customer Request Type].[codeBeamer Access Request]","[Customer Request Type].[codeBeamer Project Request]","[Customer Request Type].[Sub-tasks]"],“members”:[{“depth”:1,“name”:"(none)",“full_name”:"[Customer Request Type].[(none)]",“removed”:true}],“bookmarked_members”:[]}],“extreme_by”:{“expression”:["[Measures].[Issues created]"],“extreme”:“top_count”,“limit”:10,“others”:true},“nonempty_crossjoin”:true},“pages”:{“dimensions”:[{“name”:“Project”,“selected_set”:["[Project].DefaultMember"],“members”:[{“depth”:0,“name”:“All Projects”,“full_name”:"[Project].[All Projects]",“drillable”:true,“type”:“all”}],“bookmarked_members”:[],“current_page_members”:["[Project].[All Projects]"]},{“name”:“Status”,“selected_set”:["[Status.Category].[All Statuses by category]"],“members”:[{“depth”:0,“name”:“All Statuses by category”,“full_name”:"[Status.Category].[All Statuses by category]",“drillable”:true,“type”:“all”,“expanded”:true,“drilled_into”:false}],“bookmarked_members”:[],“current_page_members”:["[Status.Category].[All Statuses by category]"]},{“name”:“Issue Type”,“selected_set”:["[Issue Type].DefaultMember"],“members”:[{“depth”:0,“name”:“All Issue Types”,“full_name”:"[Issue Type].[All Issue Types]",“drillable”:true,“type”:“all”}],“bookmarked_members”:[],“current_page_members”:["[Issue Type].[All Issue Types]"]},{“name”:“Priority”,“selected_set”:["[Priority].DefaultMember"],“members”:[{“depth”:0,“name”:“All Priorities”,“full_name”:"[Priority].[All Priorities]",“drillable”:true,“type”:“all”}],“bookmarked_members”:[],“current_page_members”:["[Priority].[All Priorities]"]},{“name”:“Time”,“selected_set”:["[Time].[All Times]","[Time].[Current month]","[Time].[Previous month]","[Time].[Last 12 months]","[Time].[Last 30 days]","[Time].[Current year]","[Time].[Previous year]","[Time].[21 days ago]","[Time].[S2044.1]","[Time].[S2044.2]","[Time].[S2044.3]","[Time].[S2044.4]","[Time].[S2105.1]","[Time].[S2105.2]","[Time].[S2105.3]","[Time].[S2105.4]"],“members”:[{“depth”:0,“name”:“S2105.2”,“full_name”:"[Time].[S2105.2]",“calculated”:true,“drillable”:true}],“bookmarked_members”:[],“current_page_members”:["[Time].[S2105.2]"]}]},“options”:{“nonempty”:“rows”},“view”:{“current”:“pie_chart”,“maximized”:false,“pie_chart”:{“swap_axes”:false,“donut”:false,“show_legend”:false,“show_labels”:true,“data_labels”:“values”,“relative_size”:false,“series_options”:{},“font_size”:“large”},“table”:{}},“calculated_members”:[{“dimension”:“Measures”,“name”:“IssuesbyReqType”,“formula”:“CASE WHEN IsEmpty([Measures].[Issue Customer Request Type])\n THEN Format([Measures].[Issue Customer Request Type],‘Sub-task’)\nELSE [Measures].[Issues created]\nEND”,“format_string”:""}]}
} ],
“calculated_members”: [{“dimension”:“Time”,“name”:“Current month”,“format_string”:"",“formula”:“Aggregate({\n [Time].[Month].CurrentDateMember\n})”},{“dimension”:“Time”,“name”:“Previous month”,“format_string”:"",“formula”:“Aggregate({\n [Time].[Month].CurrentDateMember.PrevMember\n})”},{“dimension”:“Time”,“name”:“Last 12 months”,“format_string”:"",“formula”:“Aggregate(\n [Time].[Month].DateMembersBetween(‘12 months ago’, ‘today’)\n)”},{“dimension”:“Time”,“name”:“Last 30 days”,“format_string”:"",“formula”:“Aggregate(\n [Time].[Day].DateMembersBetween(‘30 days ago’, ‘today’)\n)”},{“dimension”:“Time”,“name”:“Current year”,“format_string”:"",“formula”:“Aggregate({\n [Time].[Year].CurrentDateMember\n})”},{“dimension”:“Time”,“name”:“Previous year”,“format_string”:"",“formula”:“Aggregate({\n [Time].[Year].CurrentDateMember.PrevMember\n})”},{“name”:“Issue Customer Request Type”,“dimension”:“Measures”,“formula”:"[Issue].CurrentHierarchyMember.get(‘Customer Request Type’)",“format_string”:""},{“name”:“21 days ago”,“dimension”:“Time”,“formula”:“Aggregate(\n [Time].[Day].DateMembersBetween(‘21 days ago’, ‘today’)\n)”,“format_string”:""},{“name”:“S2044.2”,“dimension”:“Time”,“formula”:“Aggregate(\n [Time].[Day].DateMembersBetween(‘2020-08-31’, ‘2020-09-18’)\n)”,“format_string”:""},{“name”:“S2044.1”,“dimension”:“Time”,“formula”:“Aggregate(\n [Time].[Day].DateMembersBetween(‘2020-08-10’, ‘2020-08-28’)\n)”,“format_string”:""},{“name”:“S2044.3”,“dimension”:“Time”,“formula”:“Aggregate(\n [Time].[Day].DateMembersBetween(‘2020-09-21’, ‘2020-10-09’)\n)”,“format_string”:""},{“name”:“S2044.4”,“dimension”:“Time”,“formula”:“Aggregate(\n [Time].[Day].DateMembersBetween(‘2020-10-12’, ‘2020-10-30’)\n)”,“format_string”:""},{“name”:“S2105.1”,“dimension”:“Time”,“formula”:“Aggregate(\n [Time].[Day].DateMembersBetween(‘2020-11-02’, ‘2020-11-20’)\n)”,“format_string”:""},{“name”:“S2105.2”,“dimension”:“Time”,“formula”:“Aggregate(\n [Time].[Day].DateMembersBetween(‘2020-11-23’, ‘2020-12-11’)\n)”,“format_string”:""},{“name”:“S2105.3”,“dimension”:“Time”,“formula”:“Aggregate(\n [Time].[Day].DateMembersBetween(‘2020-12-14’, ‘2021-01-15’)\n)”,“format_string”:""},{“name”:“S2105.4”,“dimension”:“Time”,“formula”:“Aggregate(\n [Time].[Day].DateMembersBetween(‘2021-01-18’, ‘2021-02-05’)\n)”,“format_string”:""},{“name”:“Artifactory Repository Access Request”,“dimension”:“Customer Request Type”,“formula”:“Aggregate(\n [Customer Request Type].[Artifactory Repository and Access Request]\n )”,“format_string”:""},{“name”:“Bitbucket Repository Access Request”,“dimension”:“Customer Request Type”,“formula”:“Aggregate(\n [Customer Request Type].[Bitbucket Repository and Access Request]\n )”,“format_string”:""},{“name”:“Change Or Feature”,“dimension”:“Customer Request Type”,“formula”:“Aggregate(\n [Customer Request Type].[Change or Feature Request]\n )”,“format_string”:""},{“name”:“Dev. Environment”,“dimension”:“Customer Request Type”,“formula”:“Aggregate(\n [Customer Request Type].[Development Environment]\n )”,“format_string”:""},{“name”:“Mail Request”,“dimension”:“Customer Request Type”,“formula”:“Aggregate(\n [Customer Request Type].[E-Mail Request]\n )”,“format_string”:""},{“name”:“Emushape Request”,“dimension”:“Customer Request Type”,“formula”:“Aggregate(\n [Customer Request Type].[Emushape]\n )”,“format_string”:""},{“name”:“Customer Feedback”,“dimension”:“Customer Request Type”,“formula”:“Aggregate(\n [Customer Request Type].[Feedback]\n )”,“format_string”:""},{“name”:“IT Support Request”,“dimension”:“Customer Request Type”,“formula”:“Aggregate(\n [Customer Request Type].[IT Support]\n )”,“format_string”:""},{“name”:“IdefX Request”,“dimension”:“Customer Request Type”,“formula”:“Aggregate(\n [Customer Request Type].[IdefX]\n )”,“format_string”:""},{“name”:“Integration Test Env. Request”,“dimension”:“Customer Request Type”,“formula”:“Aggregate(\n [Customer Request Type].[Integration test environment]\n )”,“format_string”:""},{“name”:“New E3-Team Request”,“dimension”:“Customer Request Type”,“formula”:“Aggregate(\n [Customer Request Type].[New E3 Team]\n )”,“format_string”:""},{“name”:“Parasoft DTP Request”,“dimension”:“Customer Request Type”,“formula”:“Aggregate(\n [Customer Request Type].[Parasoft DTP]\n )”,“format_string”:""},{“name”:“Parasoft DTP Project Request”,“dimension”:“Customer Request Type”,“formula”:“Aggregate(\n [Customer Request Type].[Parasoft DTP Project]\n )”,“format_string”:""},{“name”:“Remove Access Request”,“dimension”:“Customer Request Type”,“formula”:“Aggregate(\n [Customer Request Type].[Remove Access]\n )”,“format_string”:""},{“name”:“Archive Ressources Request”,“dimension”:“Customer Request Type”,“formula”:“Aggregate(\n [Customer Request Type].[\tArchive Ressources]\n )”,“format_string”:""},{“name”:“E3SWPAC Components Request”,“dimension”:“Customer Request Type”,“formula”:“Aggregate(\n [Customer Request Type].[\tE³ SW PAC Components]\n )”,“format_string”:""},{“name”:“Bug Or Malfunction”,“dimension”:“Customer Request Type”,“formula”:“Aggregate(\n [Customer Request Type].[Report a bug or malfunction]\n )”,“format_string”:""},{“name”:“SDK Eng. Support Request”,“dimension”:“Customer Request Type”,“formula”:“Aggregate(\n [Customer Request Type].[SDK Engineering Support]\n )”,“format_string”:""},{“name”:“Something Else Request”,“dimension”:“Customer Request Type”,“formula”:“Aggregate(\n [Customer Request Type].[Something else]\n )”,“format_string”:""},{“name”:“Toolchain Support Request”,“dimension”:“Customer Request Type”,“formula”:“Aggregate(\n [Customer Request Type].[Toolchain Support]\n )”,“format_string”:""},{“name”:“VWOS User Access Request”,“dimension”:“Customer Request Type”,“formula”:“Aggregate(\n [Customer Request Type].[VW.OS User Access]\n )”,“format_string”:""},{“name”:“codeBeamer Access Request”,“dimension”:“Customer Request Type”,“formula”:“Aggregate(\n [Customer Request Type].[codeBeamer Access]\n )”,“format_string”:""},{“name”:“codeBeamer Project Request”,“dimension”:“Customer Request Type”,“formula”:“Aggregate(\n [Customer Request Type].[codeBeamer Project]\n )”,“format_string”:""},{“name”:“Sub-tasks”,“dimension”:“Customer Request Type”,“formula”:“Aggregate(\n Filter([Customer Request Type].Members,\n [Customer Request Type].CurrentMember.Name MATCHES “\(none\)”\n ))”,“format_string”:""}]
}

–> I already managed to replace “none” with “Sub-taks” by using the following calculated member:
Aggregate(
Filter([Customer Request Type].Members,
[Customer Request Type].CurrentMember.Name MATCHES “(none)”
))

BUT I found out, that “none” is not only all created Sub-tasks, but other issues without a Request type too. Now I need to add the Issue type to my calculated member, but I do not know how.

martins.vanags · December 14, 2020, 2:27pm

In which of page dimensions the “none” member appears?

martins.vanags · December 14, 2020, 2:38pm

I think I understand the challenge here.
You can’t use “Issue type” dimension memberes in formula when creating calculated members for “Customer Request Type” dimension.
You could instead create a new calculated measure in “Measures” dimension that would limit the results depending on the member which is selected in the “Customer Request type” dimension.
Try this formula:

CASE WHEN
[Customer Request type].CurrentMember.Name = "Sub-tasks"
THEN
Nonzero((
[Measures].[Issues created],
[Issue Type].[Sub-task]
))
ELSE
Nonzero([Measures].[Issues created])
END

Then you use it in your report intead of “Issues created”

Martins

ANM · December 14, 2020, 9:26pm

Thank you very much !!! That helped a lot!