Doubt in page break in crosstab

Hey

I want to have 6 measures in crosstab. But for this, I need to apply page break after 6 columns. My requirement is I want 6 columns but the next columns should not go in the new page, I want it in the same page. Is there any solution for this ? My requirement is very urgent.

I have attached a report to get a clear picture. "LICENSE INFORMATION AND NODE COUNTS" is a crosstab. In this there are 6 measure in each row. I want it the same way.

Thanks a lot in advance.

Find more posts tagged with

Comments

mwilliams

You could do this with a table that has the dimension of the 6 measures listed in it and embed a crosstab in it and filter it by the outer table value.

Happy_20

<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="77574" data-time="1306366482" data-date="25 May 2011 - 04:34 PM">
You could do this with a table that has the dimension of the 6 measures listed in it and embed a crosstab in it and filter it by the outer table value. </blockquote>
If you don't mind Can I get an example 
 
Thanks, 
Happy

mwilliams

What is your BIRT version? I'm going to make a crosstab that has 6 measures and a column dimension just to show the idea.

Happy_20

Birt 2.6

mwilliams

Well, I only made it with 3 measures, but this should give you the idea of what I'm talking about. It's a crosstab embedded in an outer table and filtered to only show the "Country" from the outer table so that it "wraps" lines. Let me know.

junaidm

<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="77694" data-time="1306529387" data-date="27 May 2011 - 01:49 PM">
Well, I only made it with 3 measures, but this should give you the idea of what I'm talking about. It's a crosstab embedded in an outer table and filtered to only show the "Country" from the outer table so that it "wraps" lines. Let me know. </blockquote>
 
Hello, 
 
On behalf of Happy_20 and myself, we thank you for your solution. Now I have the cross tab wrapping lines, but there is one slight problem. 
 
Right now your attached report shows 1 "Country" column per row, but how would I show multiple "Country" columns per row while at the same time "wrapping" lines? 
 
For example, if there were 9 "Country" data entries in total and the desired interval is 3 "Country" columns interval per row. Then there would be 3 columns and 3 rows. (3x3) 
 
Thanks

mwilliams

Take a look at this example. I added a new dataSet to be used for the outer table. I also used the COUNTRY value from the second dataSet for the dataCube COUNTRY and linked the two tables in the dataCube editor. I also added a computed column to my new dataSet to give a number value to each COUNTRY. I then added this value as a attribute to my COUNTRY dimension in the dataCube. I then grouped the outer table on an the number field on an interval of 3 and added an aggregation to concatenate the values. Finally, I added a filter to my embedded crosstab to only pull the COUNTRIES whose attribute number fell in the interval of the given group. Let me know if you have questions.

Happy_20

<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="77763" data-time="1306854048" data-date="31 May 2011 - 08:00 AM">
Take a look at this example. I added a new dataSet to be used for the outer table. I also used the COUNTRY value from the second dataSet for the dataCube COUNTRY and linked the two tables in the dataCube editor. I also added a computed column to my new dataSet to give a number value to each COUNTRY. I then added this value as a attribute to my COUNTRY dimension in the dataCube. I then grouped the outer table on an the number field on an interval of 3 and added an aggregation to concatenate the values. Finally, I added a filter to my embedded crosstab to only pull the COUNTRIES whose attribute number fell in the interval of the given group. Let me know if you have questions. </blockquote>
 
Thanks million. It worked awesome. This is what I want.

junaidm

mwilliams

You're very welcome! Let us know whenever you have questions! And, I guess I did pass the 8000 park yesterday, huh? I didn't even notice! This has been a busy first half of the year, that's for sure! Thanks!

junaidm

Hi mwilliams,

I came across a slight problem.
I will explain the problem using the context of the Country output column from the cross tab example that you provided.

Remember how we used a count field to assign each Country a number for the interval table group and cross tab filter.
The column interval without page break works fine until another Country data value with the exact same name arrives into the data set. Then the cross tab interval breaks, where the second identical country column disappears and does not get outputted.

So if for whatever reason, in the data base or mock generator there are multiple Countries with the same names. ex. "Canada", "Canada"

Do you know what is going on here? Is there an option somewhere to show identical column names because only the first instance shows up and the rest of the identical countries do not.

Thanks,
-junaidm

junaidm

Attached is a picture of the problem that I explained above.
So in the cross tab I have two measures with the same node name (7750/7710 SR CMA) and the second one does not get displayed.

Here is what I have researched and discovered so far. I tried to see if any of the filters, groups, and aggregation is causing the problem. It seems the data cube is not picking up the identical node names.

Let me know if you have any questions.

mwilliams

As a crosstab does, if the two have the same dimension values, they'll just be combined together.

junaidm

<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="78400" data-time="1307984542" data-date="13 June 2011 - 10:02 AM">
As a crosstab does, if the two have the same dimension values, they'll just be combined together. </blockquote>
 
Ok understood. 
 
Is there a way to make it so the cross tab removes the missing (blank) column? Or can that be achieved by assigning the same count value for the same dimension values for the column interval? (I am not sure if this is a safe solution). 
 
For example 
node = "a" 
count = 1 
numLicensed = 20 
 
node = "b" 
count = 2 
numLicensed = 300 
 
node = "b" 
count = 2 
numLicensed = 400 
 
node = "c" 
count = 3 
numLicensed = 10 
 
.. 
 
Regards, 
Junaid

mwilliams

Is that the first row that it's doing that in? If so, the issue could be in your outer table group. You need to select the "use fixed base...." checkbox under the interval value textbox and enter a value 1 more than your interval value in the textbox below the checkbox. In my example, it was 3 for the interval, but I had to put 4 for the base value to get the top row to fill out.

junaidm

<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="78535" data-time="1308152345" data-date="15 June 2011 - 08:39 AM">
Is that the first row that it's doing that in? If so, the issue could be in your outer table group. You need to select the "use fixed base...." checkbox under the interval value textbox and enter a value 1 more than your interval value in the textbox below the checkbox. In my example, it was 3 for the interval, but I had to put 4 for the base value to get the top row to fill out. </blockquote>
 
No, the disappearing thing occurs in every row. And I already have fixed base value to be 5.

mwilliams

Oh, gotcha. I think I understand what you mean now. For the dataSet that you use for the outer table, can you do like I did in the example above and select DISTINCT values. This would solve your issue, if I'm understanding correctly.

junaidm

<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="78546" data-time="1308163961" data-date="15 June 2011 - 11:52 AM">
Oh, gotcha. I think I understand what you mean now. For the dataSet that you use for the outer table, can you do like I did in the example above and select DISTINCT values. This would solve your issue, if I'm understanding correctly. </blockquote>
 
Are you referring to the SQL query in the outer table data set for selecting the DISTINCT values? Or is it some where else? Because right now I am using a mock generator (Java) to populate the data set. So how would I specify the DISTINCT property with the data set using the mock generator as opposed to the SQL query from the database? 
 
 
Is this what you are referring to? 
 
select distinct CLASSICMODELS.CUSTOMERS.COUNTRY 
from CLASSICMODELS.CUSTOMERS, 
CLASSICMODELS.ORDERS, 
CLASSICMODELS.PAYMENTS 
where CLASSICMODELS.CUSTOMERS.CUSTOMERNUMBER = CLASSICMODELS.ORDERS.CUSTOMERNUMBER 
and CLASSICMODELS.PAYMENTS.CUSTOMERNUMBER = CLASSICMODELS.CUSTOMERS.CUSTOMERNUMBER 
order by CLASSICMODELS.CUSTOMERS.COUNTRY asc

mwilliams

Yes, I was referring to the SQL distinct call. This is where I make my count field and used the distinct call to make sure I didn't have empty slots in my report. In a scripted dataSet, you'd have arrays of your values that you should be able to cycle through to make sure you haven't already added the current value to your dataSet. If you have you could just step to the next value in your array or whatever that you're pulling your data from and "skip" the current value because it's already been added. It would be essentially the same thing that a "distinct" call in SQL would do.

junaidm

<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="78558" data-time="1308169018" data-date="15 June 2011 - 01:16 PM">
Yes, I was referring to the SQL distinct call. This is where I make my count field and used the distinct call to make sure I didn't have empty slots in my report. In a scripted dataSet, you'd have arrays of your values that you should be able to cycle through to make sure you haven't already added the current value to your dataSet. If you have you could just step to the next value in your array or whatever that you're pulling your data from and "skip" the current value because it's already been added. It would be essentially the same thing that a "distinct" call in SQL would do. </blockquote>
 
 
Yeah that is feasible, but I was wondering if there would be an option somewhere in BIRT (but guess not). 
Thanks

junaidm

<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="78558" data-time="1308169018" data-date="15 June 2011 - 01:16 PM">
Yes, I was referring to the SQL distinct call. This is where I make my count field and used the distinct call to make sure I didn't have empty slots in my report. In a scripted dataSet, you'd have arrays of your values that you should be able to cycle through to make sure you haven't already added the current value to your dataSet. If you have you could just step to the next value in your array or whatever that you're pulling your data from and "skip" the current value because it's already been added. It would be essentially the same thing that a "distinct" call in SQL would do. </blockquote>
 
Yeah that is feasible, I was just hoping there would be an option to gather distinct values in BIRT directly but guess not. 
 
Thanks

mwilliams

There may be something simpler, but I'm just not thinking of it at this time. If I figure something else out, I'll let you know.