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; } } }

show 2 different summary field in Crosstab

bossyang

Hi,
I need to present 2 summary fields in a cross table. Refer to the attachment please. The column header represent a date range. There are 2 values in the data cell. The left is the count of SR on the schedule date. The right is the number of SR on the actual date. Even some date without SR data, it still show in the header. For the summery field of crosstab, I add a computed column "Cnt" in the data set. Now i can show either "count of schedule" or "count of actual", not both of them. At the buttom of crosstab, it shows grand total of "count of actual" per week. I might not express accurately. Any suggestion?. Thx.

Sample Data Set:
Customer | Product Type | Schedule | Actual | Cnt |
Hitachi | H920B | 2010-03-03 | 2010-03-04 | 1 |
Asus | H916C | 2010-02-11 | 2010-02-10 | 1 |
Acer | H920A | 2010-03-01 | x | 1 |
RPS Co., Ltd | F20B | 2010-02-20 | 2010-02-24 | 1 |
Sun Micro | M200K | 2010-02-16 | 2010-02-16| 1 |

Find more posts tagged with

Comments

mwilliams

Hi bossyang,

You'll probably need to manipulate your data to get a structure more like the following to be able to do this with a crosstab.

Customer | Product | Date | Actual/Schedule
Hitachi | H920B | 2010-03-03 | Schedule
Hitachi | H920B | 2010-03-04 | Actual
Asus | H916A | 2010-02-11 | Schedule
Asus | H916A | 2010-02-10 | Actual
Acer | H920A | 2010-03-01 | Schedule
RPS Co. Ltd | F20B | 2010-02-20 | Schedule
RPS Co. Ltd | F20B | 2010-02-24 | Actual
Sun | M200K | 2010-02-16 | Schedule
Sun | M200K | 2010-02-16 | Actual

Then, you'd have Customer/Product as your row dimension, Date/Actual/Schedule as your column dimensions, and the count of a field for your measure.

I was able to achieve this dataSet by creating a dataSet of all the dates in the range of the report, creating a joint dataSet between that dataSet and the Schedule dates of the original dataSet, creating a joint dataSet between the date dataSet and the Actual dates of the original dataSet, and then joining those two new dataSets on two fields that definitely wouldn't have anything in common. Then, I created computed columns to pull the data that I wanted into single columns like the data above and used those in the crosstab. So, if you have the ability to edit your database to look like this to start with, it'll help with your crosstabs. If not, a series of joins like I did should work, but may be slow if you have lots and lots of data. Let me know if you have questions.

bossyang

Thank you Michael for replying so quickly. I try your suggestion this morning. But I don't know how to generate such data structure by Data Set feature in BIRT. So I ask my colleague to write a complicated SQL script. And the result looks like your version. Every SR has 2 data row even. If no date data, "VDATE" column will be blank. Please refer to the image attachement. 
 
original table schema 
SR # | Customer| Product Type | Schedule Date| Actual Date 
 
For the missing dates, I also create a scripted data set referying to <a class='bbc_url' href='http://www.birt-exchange.org/devshare/birt-report-designers/1006-add-missing-dates-to-chart-category-table/'>Add missing dates to chart category / table - Designs & Code - BIRT Exchange</a>. Then I create a new Joint Data Set with these two with join column "VTYPE". According to your tips, I assigned Customer/Product as row dimension, Date/Type(Actual|Schedule) as column dimensions. Unfortunately, the crosstab did not show "Schedule" or "Acutal" on some date. What I missed? Thx.

mwilliams

bossyang,

Are you saying you're not having days that are in the range that have no data don't show up and you'd like them to? If so, try selecting your crosstab, going down to the empty columns/rows section of the property editor and select the "show empty columns" check box and choose date from the list (it should be there if there are unshown dates). If you're talking about something else, let me know.

bossyang

I checked the "Show empty columns" with "TYPE", but no dates list. The crosstab shows 3 types because no SR data associated on some dates in joint data set. So TYPE here becomes "Schedule", "Actual" and null. I think I need to make the records with 2 TYPEs on those dates? But how..

DateRenge: Date | SR Data:TYPE | SR Data:Customer|
2010-02-12 | Schedule | x | ...
2010-02-12 | Actual | x | ....

mwilliams

bossyang,

Can you post some data in a .csv file in here of your data how it looks in your final dataSet that you're using for your crosstab? This way I can play around with it to try to get what you're wanting from what you currently have? Thanks.

bossyang

The attachment contains 2 CSV files. One is generated by simple query and the other is by complex query. I don't know how to create the same result by joint data set (left outer join/full outer join). The joint data set seems to add more columns not union the data. Thx again!

Complex query like:

SELECT a.NO SR,
a.CMMS_DATE,
c.PARTY_NAME,
a.SPTYPE,
a_s.CDATE VDATE,
a_s.type
FROM SP_QUOTATION a,
(SELECT no,
party_id,
cdate,
'Actual' TYPE
FROM SP_QUOTATION
UNION ALL
SELECT no,
party_id,
qdate,
'Schedule' TYPE
FROM SP_QUOTATION) a_s,
SP_WORKREPORT b,
CUSTOMER c
WHERE a.NO = b.QU_NO(+)
AND a.PARTY_ID = c.PARTY_ID
AND a.no = a_s.no
AND a.party_id = a_s.party_id
AND a.CDATE BETWEEN ? AND ?
ORDER BY SR

mwilliams

bossyang,

Is one of the CSV files the data you use for your crosstab though? Or do they need to be joined?

bossyang

Yes, I use one of those joined with scripted data set (for missing dates) in the crosstab.

mwilliams

bossyang,

If you add a computed column with a value of 1 to your SQL dataSet. Then, in your joint dataSet, you can create a computed column to make sure there is a scheduled/actual value for all rows (it won't matter which you put in the empty rows because of the '1' computed column above). Then, create another computed column in your joint dataSet that checks whichever column you created in the first sentence of this post for a value of 1. If it's 1, put the same over in the new computed column, if it's null, put a 0. In your dataCube, you'll now use a SUM of this new 1/0 computed column as your measure. Let me know if this isn't explained well enough.

bossyang

Michael,
So I need to put 2 computed columns in the joint data set, right? It's very close to what I want. But the blank row makes it look oddly, referring to the snapshot. maybe i misunderstand your explaination.

mwilliams

bossyang,

You just need a value for all dimensions for all rows included in the crosstab, otherwise it will make a null row. By adding a value for all of your column dimensions, you got rid of the empty column row, now you need to do the same for the row dimensions. Again, it won't matter what value you put for them, as long as it's in your crosstab already, because the 0's associated with that value will just add 0 to the measure value. You can then put a mapping on your crosstab measure to replace all 0's with "" if you don't want to see them.

Hope this helps.

bossyang

Thank you for answering my questions patiently.

I added 2 another computed column "Customer" & "Product" in joint data set for row dimension. As you said, the dummy value should exist in the orginal dataset, otherwise the dummy customer/product row shows 0 associated with those missing dates in the crosstab. (The problem is similiar to another post by Bharath406.) But it's not direct. How to write the expression for choosing a dummy value in a computed column?

BTW, in the beginning of the post, another requirement is to aggregate measure weekly at the button of crosstab. How to make a aggregation with "regular group" dates dimension by date range condition?

ps. I found I can't put a field under "Date Group" dimension as sub dimension.
ps2. my rough expression for comupted column "Product"

if( row["SPTYPE"] ){
row["SPTYPE"];
} else {
"7ZTU";
}

mwilliams

bossyang,

You should be able to use your date as a string and use it as a group that way. As for choosing a value to fill in your blank values, you'll either need to know a value that will be in there, or bring your data in in a way that there will be a value in the first row that you can store in a temp variable to copy to all blank rows.

bossyang

Got it!! very helpful. Thanks Michael.

mwilliams

bossyang,

No problem. Glad to help. Let us know whenever you have questions!