topic Re: Information Map Prompts with empty values and Web Report summing those. in SAS Web Report Studio

Information Map Prompts with empty values and Web Report summing those.

metalray — Tue, 15 Mar 2011 14:55:53 GMT

Hi Guys,

Problem 1. When I create a prompt in an information map based on a two dimensional dataset that
is created by submitting MDX to an OLAP cube I later get in my selection also empty values.
Empty values are in the dataset thats just how it works when it comes from an OLAP cubes (summarized data).
How can I avoid displaying those empty value in my prompts?

Problem 2. Those empty values also show up on my OLAP report and cause the wrong summing of values:

--Month--Day--Sum of Fact--Total
____1_____________111______111
____1_____26______111______111

If I know put the --Day-- in the hidden area (web report studio)

--Month--Day--Sum of Fact--Total
____1_____26______111______222

You can see the total is wrong. I dont want that my calculation sum individual figures
and sub totals because the sub totals already are totals of individual figures.
I hope this relates to problem 1.

I hope my problem can be understoon.

Regards,
metalray

Re: Information Map Prompts with empty values and Web Report summing those.

AngelaHall — Tue, 15 Mar 2011 17:59:02 GMT

For problem 1 - look into adding NONEMPTYCROSSJOIN of something similar into your MDX code. Example: http://msdn.microsoft.com/en-us/library/ms144797.aspx

For problem 2 - in view mode, select the Data menu & uncheck the box 'Include members with only missing values' and see if that corrects the issue. Otherwise, in the OLAP cube itself, set up this dimension as ragged (with IGNORE_EMPTY option) so missing members are skipped. http://support.sas.com/documentation/cdl/en/olapug/59574/HTML/default/viewer.htm#a003212362.htm

~ Angela

Re: Information Map Prompts with empty values and Web Report summing those.

metalray — Thu, 17 Mar 2011 10:24:27 GMT

Hi Angela,
Thanks for that.
I used the NONEMPTYCROSSJOIN. Certainly helpfull and many thanks for the idea.
I took the issue a bit further and even for dynamic prompts based on relational data sources
I see an empty value in the selection list. So this problem is not limited to OLAP prompts!!

--Select Year--
------ -----
------2001-----
------2002-----

I dont know where that [ ] empty value comes from but I suspect it has to do with prompts.
Selecting this empty value in a prompt causes a different behaviour in EG compared to a Web Report.
My web report is based on an info map and the empty values are also selectable in my info map (probably means that the problem is not withint WRS). Message was edited by: metalray

Re: Information Map Prompts with empty values and Web Report summing those.

AngelaHall — Thu, 17 Mar 2011 14:41:22 GMT

I think this comes from the Default Value. If you are in Edit Prompt, at the bottom of the Prompt Type and Values screen you will see the Sort Order and Default Value boxes. Select a Default Value and see if that improves the prompt.

~ Angela

Re: Information Map Prompts with empty values and Web Report summing those.

metalray — Thu, 17 Mar 2011 15:17:10 GMT

Hello Angela,
it did not, a default value was selected but at the top we still had the white empty value. we dont want default values because for the period prompts that would mean chaning the default values every year, quarter, month, week , day.
thanks for the suggestion. In this case we need to pre/auto-select just the first one (it is sorted)

problem1:
SELECT
NON EMPTY { [Measures].[FACTSUM] } ON COLUMNS,
NON EMPTY CrossJoin
(NONEMPTYCROSSJOIN
(NONEMPTYCROSSJOIN
({ descendants([DIM_KPI].[All DIM_KPI]) },
{ descendants([DIM_PERIOD].[HRH_CALENDERPERIOD].[All HRH_CALENDERPERIOD]) }
),
{ [DIM_Customer].[Value].AllMembers }),
{ descendants([DIM_SZENARIO].[All DIM_SZENARIO]) }
) ON ROWS
FROM
[mysmallcube]

the output is
KPI_DESC---KPI---YEAR---...
_________________2005___...
Revenue__________2005___...
Revenue____RV____2004___...

You can see that I got in various variables empty values.
I specified a string for ragged hierarchies on cube and dimension level but
this "miss" string does not appear so that does not seem to be the proble. Message was edited by: metalray

Re: Information Map Prompts with empty values and Web Report summing those.

metalray — Fri, 18 Mar 2011 14:56:01 GMT

I solved my issue now by making it a SAs dataset and seeting some filters:

create view mytable as select * from connection to olap
(
SELECT
NON EMPTY { [Measures].[FACTSUM] } ON COLUMNS,
NON EMPTY CrossJoin
(NONEMPTYCROSSJOIN
(NONEMPTYCROSSJOIN
({ descendants([DIM_KPI].[All DIM_KPI]) },
{ descendants([DIM_PERIOD].[HRH_CALENDERPERIOD].[All HRH_CALENDERPERIOD]) }
),
{ [DIM_Customer].[Value].AllMembers }),
{ descendants([DIM_SZENARIO].[All DIM_SZENARIO]) }
) ON ROWS
FROM
[mysmallcube]
)
AS t1
WHERE t1.KPI_DESC NOT IS MISSING AND t1.KPI NOT IS MISSING AND t1.YEAR NOT IS MISSING;