CrossTab with multiple Columns

Hii,
Attached along with is post is my report structure and database resultset. What would be appropriate element to use to display this report. I tried to create this report with crosstab but only able to display it till current month data
Kindly provide me suggestion regarding this report.

Thanks & Regards,
Monica Dayaramani

Find more posts tagged with

Comments

Monica D

mwilliams

After looking at your xls file, my thought is that you'll need to create a column in each individual dataSet that is either, "This Month", "This Year", or "Last Year". Then, you'll join all the dataSets into one dataSet and merge the appropriate columns with computed columns, including the "This Month", "this year", and "Last Year" values into a single column. Now, you'll have a field you can use as a dimension in your dataCube that will separate the crosstab data into these three categories before it separates into the different "Types". Hope this helps. Let me know if you have questions!

Monica D

Hi,
Thanks for the reply.

As per my above mentioned report I will have to join the DataSets based on four columns country,state,gender,product type but birt is allowing to create a join based on only one column.

Regards,
Monica Dayaramani

mwilliams

If your data looks as it does in your attached file, you should only need to join on the computed column that I said you'd need to make for each dataSet. If you're using SQL, creating a single dataSet with unions and a static field that shows "This Month", "This Year", or "Last Year" depending on which section of the query the result comes from would also do the trick. If I'm understanding correctly, the crosstab would do the rest of the work once you join the dataSets and then merge the columns with computed columns (if using BIRT to do the join). If using SQL to do the union, you should be able to create your dataCube without creating a computed column in BIRT, as long as you create the new field that separates whether the row comes from this month, this year, or last year.

Monica D

<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="85838" data-time="1322666714" data-date="30 November 2011 - 08:25 AM"><p>
If your data looks as it does in your attached file, you should only need to join on the computed column that I said you'd need to make for each dataSet. If you're using SQL, creating a single dataSet with unions and a static field that shows "This Month", "This Year", or "Last Year" depending on which section of the query the result comes from would also do the trick. If I'm understanding correctly, the crosstab would do the rest of the work once you join the dataSets and then merge the columns with computed columns (if using BIRT to do the join). If using SQL to do the union, you should be able to create your dataCube without creating a computed column in BIRT, as long as you create the new field that separates whether the row comes from this month, this year, or last year.<br /></p></blockquote>

mwilliams

Not sure if you meant to write something in your reply or not. Let me know!

Monica D

Hi,

Thanks for ur reply got my report working by changing my query using unions & static text fields

Regards,
Monica Dayaramani

mwilliams

Wonderful! Let us know whenever you have questions!