Crosstab

How do I create a crosstab that uses the following data sources in a data cube. I want to show all the options (from data source 1) in the report even when the data source 2 does not contain an entry for that outlet ie ABC Radio?

When the two data sources are linked in the data cube on the Outlet attribute then only if the same value appears in both data sources does it appear in the report - is there anyway to do an outer join type linking?

Thanks.

Data Source 1

MediaOutletCategory, Outlet
Metro, 702 ABC
Metro, ABC Radio

Data Source 2

CallTime, Outlet
7:10, 702 ABC

Find more posts tagged with

Comments

mwilliams

Hi stuart,

You should be able to create a joint dataset with those two datasets using an outer join. Have you tried this yet? If you can provide a larger sample of data for both datasets and what you want the output to look like, I'll see if I can put together a little example.

stuart

Hi,

Thanks for your reply. So you are saying that I should be able to produce something like the attached by joining the datasets and then using a crosstab?

thanks.

mwilliams

stuart,

I was able to create what you were looking for with an outer join in a joint dataset using your small sample of data as you can see in the attached image. It would take a larger sample of data to figure out for sure if it will do what you want, but I think the outer join should do it. It may require a computed column or two as well, but I would guess it can be done.

stuart

Hi Michael,

Thanks so much for your assistance. Below are the larger data sets:

DataSet 1:

MEDIACALLID,STARTSHIFTDATECALLTIME,CALLDIRECTION,CALLNAME,POSITION,CONTACTNUMBER,ISTAFFICREPORT,ISMEDIAUNITINFORMED,ISONAIRINTERVIEW,OTHERENQUIRY,MEDIACALLOUTLETCAT,MEDIACALLOUTLET

1,2009-03-01 00:50:00,I,Manish,News Reader,63256892,T,T,T,no other info,Metropolitan,ABC Radio

2,2009-03-01 00:50:00,O,Elena,News Reader,63256892,T,T,T,no other info,Metropolitan,Today Show

3,2009-03-01 00:50:00,O,Stuart,News Reader,63256892,T,T,T,no other info,Community,2SSR

DataSet 2:

MEDIACALLOUTLETCAT,MEDIACALLOUTLET

Metropolitan,702 ABC
Metropolitan,ABC Radio
Metropolitan,Today Show
Metropolitan,2UE
Metropolitan,2SM
Metropolitan,NOVA
Metropolitan,Vega FM
Metropolitan,Daily Telegraph
Metropolitan,SMH
Metropolitan,Channel 9
Metropolitan,Channel 10
Metropolitan,Channel 7
Metropolitan,Other
Community,2SSR
Community,2ME
Community,PVR
Community,WOW FM
Community,Radio 50+
Community,2NBC
Community,Other

Thanks,

Stuart.

stuart

Hi Michael,

I have been playing around with some of the things you have suggested and I'm getting close to what I want but I have a mysterious row included in the report which has a date 01-01-0001 (see attached image). Do you have any ideas?

Thanks,

Stuart.

stuart

Hi Michael,

Can you offer any thoughts on why I would be getting a blank row at the top of my cross tab report.

Thanks.

mwilliams

Stuart,

That row shows up because it wants to include the blank rows somehow in the crosstab. You can hide this row by going to the property editor of each "row" dimension and measure and setting a visibility statement similar to the following:

if ( data["FirstRowDimension"] == ""){
true
}
else{
false
}

This will hide the data items in that row if you do it on all "row" elements and that row will be compressed.

Hope this helps. Let me know if you have any questions.

stuart

Hi Michael,

Thanks again for the prompt feedback. Is there anyway I can determine why the crosstab is inserting this blank row?

Ta.

mwilliams

Stuart,

It happens because of the 'outlets' that don't have corresponding data. The empty strings still want to show up for some reason. I'll run it in 2.3.2 and see if it still happens in there. I'll also try something with a computed column after the join and see if I can get it to go away. I'll let you know.

mwilliams

Stuart,<br />
<br />
It seems to do this for 2.3.2 as well. I did a quick search of the bugs and didn't find anything similar reported. You may log a bug for this at <a class='bbc_url' href='http://www.eclipse.org/birt/phoenix/reportabug.php'>BIRT: Reporting Bugs and Requesting Enhancements</a> and see what they say. Be sure to include a good description and screenshots. Please post any bug info in here as well for future reference. Thanks.

stuart

Hi Michael,

I'm trying your suggestion to remove the blank row in the cross tab but now I'm getting the attached error. Any insights?

Stuart.

mwilliams

Stuart,

Can you attach your report design?