Help needed with Data Cube and crosstab

Hi,
I don't have an sample xml since i am new and still in process of building one. I am attaching word document that explains what I am trying to ask.
Please assists.
Thanks,
Jay

Find more posts tagged with

Comments

mwilliams

Is your issue with creating the XML dataSet, i.e. not knowing how to map the rows and columns? Or is it with creating a crosstab?

Jay1238

Hi mwilliams,
I can create DataSet. But i am not sure about data cube and crosstab in this case.
Right now, i am trying to come up with mocked up xml file which i can put in BIRT.

Thanks,
Jay

mwilliams

Once you have your dataSet, you just create a new cube with the dataSet. Whatever fields you want to have as row and column dimensions, you drag into the dimensions area. Whatever field you want to be aggregated in the cube, you put in the measure area of the data cube. Once done with the cube, you can drag a crosstab to your report from the palette, then, expand the groups in your cube, in the data explorer. Finally, drag the measure to the measures area, the fields for the row dimensions to the row dimension area, and the column dimension to the column dimension area.

In your example case, you'd have country as your row dimension, the field that contains s1, s2, etc as your column dimension, and the RC1, RC2, etc field as your measure field. If the measure field is a string, be sure to change the function of the cube aggregation to "FIRST" or something, by double clicking on the measure in the cube editor and changing the agg function.

Jay1238

Hi mwillaims,
Thanks for the clear instructions. I am still having some issues. I am attaching Steps.docx and DataSection.docx. The data is not my entire input. It is just the portion that i am interested in. However, the rptdesign takes entire input xml as Data source.

Thanks,
Jay

mwilliams

Take a look at this. Be sure to change the location of the XML file, in the dataSource, to where you save this sample xml file to.

Jay1238

Thanks mwillaims,
It looks fine but why are other countries from each service not displayed. Like Belgium, India

The xml has two countries (Australia and Belgium) for 1I but only Australia is displayed in table
The xml has three countries (Australia, Beligum and India) for 1E but only Australia is displayed in table.

Thanks for taking the time.
Jay

mwilliams

That's a dataSet issue. I just followed your setup. You'll probably just need to change the XPath Expression for the rows to be at the detail level, instead of where you currently have it.

mwilliams

Take a look at the mappings in the dataSet in this report.

Jay1238

Thanks mwilliams,
It is fine now. But i have another follow-up question.
Let's say it has 200 countries instead of 2 or 3. Then i want to break it at 50 countries at a time. I know i can use Row Area-Pagebreak under that crosstab. But i think this would give me 4 tables one under another.
What i want is 4 tables side-by-side.

Thanks,
Jay

mwilliams

To do side by side, you'd have to use a multi column grid, multiple crosstabs, and filters to get the result you want.

Jay1238

<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="109985" data-time="1348848101" data-date="28 September 2012 - 09:01 AM">
To do side by side, you'd have to use a multi column grid, multiple crosstabs, and filters to get the result you want. </blockquote>
 
ok i will play around with that idea and see how it goes .. thanks again!

mwilliams

Yep. Let me know if you get stuck.

Jay1238

Hi mwilliams,

I am attaching the sample data. When the pdf is generated it is sorted based on the country name. I don't want it that way. i want it in the way it was received in the file. Like it should be Australia-India-Belgium. But it is shows Australia-Belgium-India.

mwilliams

Create a computed column in your dataSet with a runningcount aggregation. In your data cube, double click on the row dimension for country and add this new field as an attribute. Then, in your layout, select your crosstab, go to the sorting tab of the property editor, and sort on this attribute.

http://www.birt-exchange.org/org/devshare/designing-birt-reports/1172-sorting-a-crosstab-by-a-field-not-in-the-crosstab/

Jay1238

cool man .. thanks!

mwilliams

Not a problem!

Jay1238

Hi,
I ran into an issue where it is not displaying duplicate countries. Please see the attached design and input xml. Service 1I has Australia twice but it only displays the first instance of it.

Thanks,
Jay

mwilliams

What are you wanting it to do? Do you want the appropriate values in a comma separated list in the cell? Or do you want two rows?

Jay1238

<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="114513" data-time="1361866176" data-date="26 February 2013 - 01:09 AM">
What are you wanting it to do? Do you want the appropriate values in a comma separated list in the cell? Or do you want two rows? </blockquote>
 
Hi mwilliams, 
Can you provide me solution for both, please? 
 
Thanks, 
Jay

Jay1238

Hi mwilliams,
I also need to put a note below the crosstab if duplicates row are found. Something like
"Australia-Duplicate rows found"

Thanks,
Jay

mwilliams

Take a look at this example. It shows the multiple rows way and the comma separated list way. Both use a computed column. The multiple row way, I replace the row dimension element with a new column binding to display the name. Let me know if you have questions.

Jay1238

Hi,
This looks good but it messes the order. For example, 1E has Australia-India-Belgium but it shows Australia-Belgium-India. Any fix for that?

Thanks,
Jay

mwilliams

But, if you switch that order, it wouldn't match the order of the row dimension. Maybe I'm not following. If wanting to order the entire crosstab by the order in which the countries appear for that group, how do you determine which groups order to use?

Jay1238

Hi mwilliams,
Let me attach a file with real data. Please see the data around Country U.S.A!! U.S.A. has Locations Ids and Postal Codes. But the Postal Codes with "***" for 70E and 86E is not coming in order. It should come after "Rest of the Country" Location Id but it is coming after Country "Zimbabwe".

Interesting thing is order of Location Ids for U.S.A remains in correct order.

The order in Preview Results of the DataSet looks correct.

This is also existing issue in rptdesign that displays only 1 row for "***"

Attaching the new rptdesign, old rptdesign and real data file.

Jay1238

Hi mwilliams,
Please see my latest rptdesign. I hid the 1st and 3rd crosstab. Only the middle - multiplerow crosstab is visible. To its data cube, i added Attribute 'Order' and 'CountryName' to Group2. Also i added sort on the crosstab by Group2-KeepCountrySeparate-Order. In its Data Set i added filter "70E" and "86E". This gives the correct order of Location Ids and Postal Codes for Country U.S.A. But as soon as i remove the filter it goes haywire on the Postal Codes and brings it down after Zimbabwe.

Thanks,
Jay

mwilliams

There is not data for USA or Zimbabwe. Can you attach the new data file, so I can see what you're looking at?

Jay1238

Sorry about that! Please see the attached. Also, please see my change in definition for KeepCountrySeparate computed column.

mwilliams

This is happening because the postal codes section doesn't appear in the first several sets of data. Since the postal codes lines are only for USA, you could create your own custom order field that assigns an appropriate number for these rows, when they finally do appear. Like, you could store the USA order in a global variable, then when you encounter the postal codes, you could have a script that recognizes this and gives the USA order number. Let me know if this would be a doable workaround. I can modify the example to do it, if it is. Without any way, besides original order, to tie the values to USA, there isn't much other way to do this, I don't think.

Jay1238

Thanks Mike - I am checking with the data owner if any other countries besides U.S.A could also possibly have Location Ids and Postal Codes. I have emailed them but they haven't gotten back to me yet. Will it complicate the issue more if indeed other countries also have Location Id and Postal Codes break up? Also, the same issue could have occured if Location Ids under U.S.A was not present in the first few data set, right?

Please email me your modification and i would understand better by looking at the changes. Thanks for all your help!

Jay

mwilliams

Yes, the location would be incorrect if it wasn't there for all sets. If there are other countries with these possibilities, it will definitely complicate the issue, but we might be able to figure something out.