nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Cross tab or table ?

nnish

Hi Guys, 
 
Am building a school report. And what to know what is easy to use. I am stuck in both the options Cross tab and the table / Grid . 
 
I have the following data . 
 
Subjects : These are dynamic there are 8-10 subjects which are added or deleted periodically . these come from the db. 
Number of classes : If the teacher goes to a class a entry is made .this is saved in the db. 
Classrooms : This is a list from the database

History - Geography - Algebra - Geometry - Physics - Chemistry - Total Classes 
Class room1 - 2 - 1 - 2 - 1 - 1 - 1 - 9 
Class room2 - 1 - 0 - 0 - 1 - 0 - 0 - 2 
Class room3 - 1 - 1 - 2 - 1 - 1 - 0 - 6 
Class room4 - 2 - 0 - 0 - 1 - 0 - 1 - 4 
Class room5 - 1 - 1 - 2 - 1 - 1 - 0 - 6 
Class room6 - 0 - 0 - 0 - 0 - 1 - 0 - 1 
 
 
Total - 8 - 3 - 6 - 5 - 5 - 2 - 28 
 
Can some one give me an example. 
 
I had pm the admin . But i guess he was too busy . It would be great if some one can post an example. 
 
All the data will be loaded from the data base. Is a cross tab better here or a grid ? 
 
Thanks in advance .

Find more posts tagged with

Comments

mwilliams

Hi nnish,

It sounds like a crosstab would be the best for this. How is your data set up? Is it like:

Classroom, Subject, Classes
Classroom1, History, 2
Classroom1, Geography, 1
etc...

If so, a crosstab with dimensions for classroom and subject, and then use classes as the measure should get you what you're wanting. The row and column dimensions would expand to meet the amount of classrooms and subjects. You can even add a total row and column. Let me know if you have questions.

nnish

<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="67673" data-time="1282652077" data-date="24 August 2010 - 05:14 AM">
Hi nnish, 
 
It sounds like a crosstab would be the best for this. How is your data set up? Is it like: 
 
Classroom, Subject, Classes 
Classroom1, History, 2 
Classroom1, Geography, 1 
etc... 
 
If so, a crosstab with dimensions for classroom and subject, and then use classes as the measure should get you what you're wanting. The row and column dimensions would expand to meet the amount of classrooms and subjects. You can even add a total row and column. Let me know if you have questions. </blockquote>
 
Thanks for the info. 
 
I tried cross but it got me confused. 
 
I have 3 tables from where data is coming in all the values are dynamic and can change periodically. 
 
Table no.1 saves class room names 
Table no.2 saves the subject names 
Table no.3 saves the classes what the teachers have attended or taken. 
 
is there a similar example. I got one example but many header values are hard coded.

nnish

I figured this out . It was simple.
Got it working . Thanks .

But i wanted to know how can i add one more column for total at the end of the cross tab ?
and want to add one row to give the total count of all the number of classes.

mwilliams

In your design window, you'll see a little icon next to the dimension. If you click on that, you'll get the option to add grand totals.

nnish

<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="67751" data-time="1282747312" data-date="25 August 2010 - 07:41 AM">
In your design window, you'll see a little icon next to the dimension. If you click on that, you'll get the option to add grand totals. </blockquote>
 
I figured this too. It worked flawlessly . 
 
Now the last hurdle. 
I want to make this a weekly report . Can you guide me how this can be done ? 
 
I got the report parameter part Added a "from" and "to". Is there a way i can get all the weekdays dynamically in the cross tab Say sunday to Friday ?

nnish

<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="67751" data-time="1282747312" data-date="25 August 2010 - 07:41 AM">
In your design window, you'll see a little icon next to the dimension. If you click on that, you'll get the option to add grand totals. </blockquote>
 
 
I got the report parameter part Added a "from" and "to". Is there a way i can get all the weekdays dynamically in the cross tab Say sunday to Friday ? and filter it . i am trying the inbuilt functions but cant figure it out . 
 
If i choose 2 dates i want to get all the weekdays between those dates. Is there any example of this kind .

mwilliams

So, you have a "from" and a "to" parameter value? If so, you can just limit your data that you bring in with these values. Since a crosstab is dynamic, it will adjust its rows/columns based on the data available.

nnish

<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="67848" data-time="1282918520" data-date="27 August 2010 - 07:15 AM">
So, you have a "from" and a "to" parameter value? If so, you can just limit your data that you bring in with these values. Since a crosstab is dynamic, it will adjust its rows/columns based on the data available. </blockquote>
 
This is working . 
 
How do i get the weekdays dynamically . Say Sunday to Friday or Monday to Sunday in the cross tab ?

mwilliams

If one of your dimensions is a date, you should be able to use the auto grouping to do group by year/month/day/etc. The default is going to be Sunday - Saturday. Hope this helps.

nnish

<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="67903" data-time="1283180110" data-date="30 August 2010 - 07:55 AM">
If one of your dimensions is a date, you should be able to use the auto grouping to do group by year/month/day/etc. The default is going to be Sunday - Saturday. Hope this helps. </blockquote>
 
Cool. It worked . 
The cross tabs are actually very simple . just need to spend some time on it . 
 
mwilliams i am not able to add text in the cross tab is it possible ? 
 
I am trying to create a cross tab like the one below

saturday - sunday - monday - tuesday - wednesday - thursday 
number of classes 10-21-31-3-14-51-23 
number of subjects 2-4-2-3-5-12-3 
 
the weekdays and the count are all coming from the cross tab how can i add my own value ie the number of classes and number of subjects ?? 
 
Birt reports is really awesome . Thanks a ton mwilliams and the team for adding good tutorials and information in the forum..

mwilliams

You are very welcome. Always let us know whenever you have questions!

I'm not completely sure what you're having a problem with. Is it with having the labels "number of..."? Or is it how to compute the data? Or is it all of the above? Let me know. Also, let me know what your data for this looks like so I can figure out how you'll have to go about this.

nnish

<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="67999" data-time="1283353199" data-date="01 September 2010 - 07:59 AM">
You are very welcome. Always let us know whenever you have questions! 
 
I'm not completely sure what you're having a problem with. Is it with having the labels "number of..."? Or is it how to compute the data? Or is it all of the above? Let me know. Also, let me know what your data for this looks like so I can figure out how you'll have to go about this.

</blockquote>
 
The computing is fine . I have a problem with getting the label. "number of classes" Can it be hardcoded in some or the other way . 
 
the weekdays are coming properly the data is coming properly . Just want to add the label in the first column.

mwilliams

I don't know how your data is, so I'm not sure exactly how to suggest to do this. Can you post a small, dummy sample of your data?

nnish

<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="68062" data-time="1283436370" data-date="02 September 2010 - 07:06 AM">
I don't know how your data is, so I'm not sure exactly how to suggest to do this. Can you post a small, dummy sample of your data? </blockquote>
 
Mwilliams this is a different issue . 
 
See the number of classes and number of students attended I want them to come in the cross tab . 
My data is as follows 
 
Saturday,Sunday,Monday,Tuesday,Wednesday,Thursday 
Number of classes, 18,12,16,25,22,11, 
Number of students attended,43,21,14,23,31,11, 
 
The weekdays i want it to be common for all the rows . how can i add the number of classes and number of 
students attended ? I have attached a screen shot its in excel thats exactly what i am trying for. 
 
Any suggestions.

mwilliams

nnish,

Sorry, I understand what you're trying to do, but I just want to know exactly what your data looks like so I can see if I can find a way to approach this. If you can show a small sample of your dataSet, that'd be great! Thanks!

joseflores

<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="68196" data-time="1283886372" data-date="07 September 2010 - 12:06 PM">
nnish, 
 
Sorry, I understand what you're trying to do, but I just want to know exactly what your data looks like so I can see if I can find a way to approach this. If you can show a small sample of your dataSet, that'd be great! Thanks! </blockquote>
 
HI Williams, What if i would like to add empty lines between details from the crosstab?

joseflores

<blockquote class='ipsBlockquote' data-author="'joseflores'" data-cid="71120" data-time="1291743904" data-date="07 December 2010 - 10:45 AM">
HI Williams, What if i would like to add empty lines between details from the crosstab? </blockquote>
 
I mean between grouped details

mwilliams

So, you'd like an empty line between each grouping? Like if you had:

Continent | Country || 2008 | 2009 | 2010
NA | USA | 1 | 2 | 4
___ | CA | 2 | 3 | 4
EU | FR | 0 | 1 | 1
__ | SP | 1 | 1 | 2

You'd like to see that as:

Continent | Country || 2008 | 2009 | 2010
NA | USA | 1 | 2 | 4
___ | CA | 2 | 3 | 4

EU | FR | 0 | 1 | 1
__ | SP | 1 | 1 | 2

??

If so, you could use an outer list or table to list the continents, then embed a crosstab into this outer element and filter it to the outer element's value. You could then add a dummy text/grid/label element to give you a space between the groupings that would be done by the outer element. Let me know if this doesn't make sense.

I'll try to explain better.

joseflores

Excelent Williams!! 
Now this leads me to second question: 
 
I have a report of sales per region/dates (Year, Month, etc). 
I did what you told. Now how can I make all crosstabs details to have the same columns no matter they are no data for a region/date and have only on header - no one per crosstab - that reflects the report options selected? 
 
Regards, 
Jose 
 
 
 
<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="71123" data-time="1291744852" data-date="07 December 2010 - 11:00 AM">
So, you'd like an empty line between each grouping? Like if you had: 
 
Continent | Country || 2008 | 2009 | 2010 
NA | USA | 1 | 2 | 4 
___ | CA | 2 | 3 | 4 
EU | FR | 0 | 1 | 1 
__ | SP | 1 | 1 | 2 
 
 
You'd like to see that as: 
 
Continent | Country || 2008 | 2009 | 2010 
NA | USA | 1 | 2 | 4 
___ | CA | 2 | 3 | 4 
 
EU | FR | 0 | 1 | 1 
__ | SP | 1 | 1 | 2 
 
 
?? 
 
If so, you could use an outer list or table to list the continents, then embed a crosstab into this outer element and filter it to the outer element's value. You could then add a dummy text/grid/label element to give you a space between the groupings that would be done by the outer element. Let me know if this doesn't make sense.

I'll try to explain better. </blockquote>

mwilliams

Jose,

For them to all have the same columns, you'd probably need to know all the possible column values and make a dataSet with these in them, then you'd need to join your dataSet with these values so that the column value was present for every group value. You'll want to make sure you have your crosstab set up so that null or empty cells are shown. As for hiding the headers, you should be able to make the "display" setting in the "section" section of the "advanced" properties in the property editor, to "no display" on the headers you don't want to show.

Hope this helps.