columns with null values are not visible in crosstab

[cognos4321]

Hi Experts,

I am running a crosstab report showing monthly Actual and Forecast values.
It looks like:

                Jan                 Feb         Mar   
         Actual    Forecast     ActualForecast        ActualForecast
Product 1                  
Product 2                  

Now if Jan doesn't have any actual or forecast values, the whole Jan column is invisible.
If Feb has only Actual and null forecast , then it shows like Feb Actual only(forecast col is invisible)

Please help.
                                                                                     

[dys]

This is normal behavior for a Relational data source - if the SQL query finds no data at your dimensional intersection cells, then nothing is returned and the empty column or row is not rendered. An OLAP source would render the empty column/row as long as you're not doing null/zero suppression. What type of data source are you using - Relational or OLAP/DMR?

[cognos4321]

Thanks dys for the reply.

I am using DMR model.
I tried to use nvl function also but it throws error. Actually nvl is used to have a '0' value instead of a blank space but in my case the whole column with null value disappears.

Any help is appreciated.

Thanks.

[chinnucognos]

try this..
1. Select the fact cells.
2. Go to Data format under Properties.
3. Change format to number
4. Give '0' as the input for "Missing value chars" property

or

Within each dimension create a case statement:
CASE WHEN Metric
is null THEN 0 ELSE Metric END

or

if the value is null then use coalesce() function to turn into a zero, e.g. coalesce(column,0)

Let me know if any above idea works for ur requirement

[cognos4321]

Thank you Chinnucognos. The first option worked.

But I have one more question if you can help.

They want if the report is run for 2015 it should show all actual and no forecast from Jan till Dec since the year is already passed.

And if ran for current year(2016) then Jan Feb March Actuals and rest from Apr to Dec as forecast.

Please help. I tried putting filter on current year but it didn't work.

Thanks again

[chinnucognos]

Try this...
prevmember(item(tail(filter(members( <Cube> .[Time].[Time].[Year]) , tuple( <Measure>, currentMember(<Cube>.[Time].[Time] )) is not null), 1), 0) )
- Returns the previous year member
there after, take that previous year member and filter values which is equal to Actual and not forecast.


For filtering values upto current month, try this
total( currentMeasure within set  periodsToDate ( <cube>.[Time].[Time].[Year], [Current Month] )  )

let me knw if it works and do some work around if u need any changes in logic

[cognos4321]

I have 3 data items
'Selected year' which has 2012,2013,2014,2015,2016.
Forecast
Actual
And Measure'Amount' in the crosstab below

                    Jan                      Feb                         Mar   
             Actual Forecast     ActualForecast        ActualForecast
Product 1                 
Product 2   

Now if selected year is from 2012 to 2015 the Forecast column should not be there in the crosstab
         if selected year is 2016 then  both Actual and Forecast should be there.

Please pardon my ignorance and help in letting understand the expression u mentioned in respect to the above problem.

[cognos4321]

Thanks Chinnucognos.

I wrote the below expression, saved it as 'Previous Year' and it shows no errors.

prevMember (item (tail (filter (members ([Line Cost].[Year].[Year].[L1]),tuple ([Amount],currentMember ([Line Cost].[Year].[Year])) is not null),1),0))

Now, as u mentioned this expression is not returning Previous year but Previous year member.

So for filtering only Actuals from previous year when I write the below expression, it throws errors.[A/F] is an attribute under Months Dimension which has Actual and Forecast

Case when [Previous Year] then [A/F]->'Actual'
End

Please help, I have not worked much on DMR model.
Thanks a lot.

[chinnucognos]

hello,

Please go thru some below links where u can get some idea on how to implement filters in dmr model.

https://www.ironsidegroup.com/2014/06/30/dimensional-reporting-leveraging-time-and-aggregate-functions-to-create-low-maintenance-reports/

http://www.ibm.com/developerworks/data/library/cognos/reporting/dimensional_queries/page561.html

http://www.cognoise.com/index.php?topic=24887.0


Initially u have to get members  which is equal to Actual
Pls try this inorder to get members in Month Dimension

members( <Cube> .[Time].[Month])
- Get the set of members from the Time dimension at the Year level