custom sum in crosstab

[avuong6]

trying to add custom total of a specific category in a crosstab report.

i created a data item:
total(currentmeasure within set [BECAT_ID])

which totals the column but i need it to total only a member within [BECAT_ID]

how to do? any help would be appreciated.

this is a crosstab on a relational db.

https://photos.app.goo.gl/N6Yim7LJ4hid9wmM7

[MFGF]

trying to add custom total of a specific category in a crosstab report.

i created a data item:
total(currentmeasure within set [BECAT_ID])

which totals the column but i need it to total only a member within [BECAT_ID]

how to do? any help would be appreciated.

this is a crosstab on a relational db.

i would upload a picture but not sure how to do.

Hi,

I'm assuming that this is a DMR (Dimensionally Modelled Relational) package? You're using dimensional syntax, so I'm basing my answer on that. It sounds like you just want the measure value for a specific member in the set? I think a tuple() function will give you what you need here.

tuple([The member you are interested in],[your measure])

Cheers!

MF.

[avuong6]

how to load an image? would help to explain if you could see an image.

[avuong6]

its crosstab report using a relational package.

[MFGF]

how to load an image? would help to explain if you could see an image.

You'd need to upload the image to a site that supports sharing via a URL. Then use an IMG tag in the post to embed it into your post.

Cheers!

MF.

[avuong6]

ok i uploaded some pics.
1st pic is structure of crosstab.
2nd pic is output of crosstab.
i need to sum up foreign affiliates separately from the year total.

[MFGF]

ok i uploaded some pics.
1st pic is structure of crosstab.
2nd pic is output of crosstab.
i need to sum up foreign affiliates separately from the year total.

I don't see any pictures?

[avuong6]

here is the link:

https://photos.app.goo.gl/N6Yim7LJ4hid9wmM7

[MFGF]

ok i uploaded some pics.
1st pic is structure of crosstab.
2nd pic is output of crosstab.
i need to sum up foreign affiliates separately from the year total.

So Foreign Affiliates is one of the Categories in the CATEGORY_NAME item? And this is a pure relational package?

What results do you get if you create a query calculation like this:

if ([CATEGORY_NAME] = 'Foreign Affiliates') then ([Your measure value]) else (0)

Cheers!

MF.

[avuong6]

woot! that worked. its strange because i tried a case statement doing the same thing and that didnt work.

now next issue, i want to put a total column on the right side of the table to summarize each row. when i hit column heading and total, it gives me a total for each column and not a total across all columns ie a grand total.

any ideas?

[MFGF]

woot! that worked. its strange because i tried a case statement doing the same thing and that didnt work.

now next issue, i want to put a total column on the right side of the table to summarize each row. when i hit column heading and total, it gives me a total for each column and not a total across all columns ie a grand total.

any ideas?

What is in the column heading(s) - just a single TRANS_NAME item, or stacked/nested items?

If you're just using a single item for TRANS_NAME, you should be able to click on it (it should say "Crosstab node member" above the Properties pane on the right) then press the Summary button on the toolbar and select the summary you want.

Cheers!

MF.

[avuong6]

yes, trans_name only. tried that. summary button give just sum of each separate column.
so i made query item like this:

total([Trans].[TRANS_VALUE] for [TRN_TYPE_SCH],[QUARTER],[CATEGORY_NAME])

and this works. it sums each column and then in the end i get the total for the whole row i want. but i only need that last column and not the other 5o columns that come with it.

can i conditionally hide the others by instructing it to show only last column?

[MFGF]

yes, trans_name only. tried that. summary button give just sum of each separate column.
so i made query item like this:

total([Trans].[TRANS_VALUE] for [TRN_TYPE_SCH],[QUARTER],[CATEGORY_NAME])

and this works. it sums each column and then in the end i get the total for the whole row i want. but i only need that last column and not the other 5o columns that come with it.

can i conditionally hide the others by instructing it to show only last column?

Just to be clear, you're adding the summary across the columns and not down the rows? And you just have a single measure in your crosstab? What happens if you just drag the measure from your list of query items (a second time) and stack it to the right of the last column? Does this give you the correct total?

Cheers!

MF.

[avuong6]

yes adding across columns
yes single measure
stacking measure again just returns multiple blank columns nested in everything

[MFGF]

yes adding across columns
yes single measure
stacking measure again just returns multiple blank columns nested in everything

It sounds like there's something funky going on in your crosstab. If the columns are a single item, you should get a single summary at the end.

What happens if you remove the column headings and drag in the item again? Can you then summarize it properly?

Cheers!

MF.

[avuong6]

i get the same thing. any summary adds a sum column for each existing category column.

[MFGF]

i get the same thing. any summary adds a sum column for each existing category column.

I'm not sure what to suggest here - I don't see that behaviour when I build a crosstab with a similar structure.

What are you using for the columns - is it a data item from the source or a calculation of some sort? Does your crosstab have a default measure?

As a sanity-check, do you see the same weirdness if you build a crosstab the same way in a new report?

MF.

[avuong6]

agree, i have other crosstabs that dont behave like this.

report is based off a main table which is created from a join of 2 other tables. one is the measure with the category ids. second is the text of the categories joined to the first table via id so the report can display the description of the categories.

columns are straight from source, no calc. yes default measure is trans_value, the only measure in whole report.

[avuong6]

ok there was something corrupt about crosstab, i rebuilt and it works except for 1 calculation.

i loaded a new image:

https://photos.app.goo.gl/N6Yim7LJ4hid9wmM7

in the last column everything calculated except for the foreign affiliates and unaffiliated foreign persons row.

total calculates total for each row.
merchserv calculates total for each row for 1 specific trans_name
total (move 22...) calculates total - merchserv

foreign affiliates and unaffiliated foreign persons calculates the specific category names for the column.

you can see in image, for the foreign rows, total column and the merchserv column has values. but the total (move 22...) column returns zero. why is the calculation not right for these 2 rows.

[MFGF]

ok there was something corrupt about crosstab, i rebuilt and it works except for 1 calculation.

i loaded a new image:

https://photos.app.goo.gl/N6Yim7LJ4hid9wmM7

in the last column everything calculated except for the foreign affiliates and unaffiliated foreign persons row.

total calculates total for each row.
merchserv calculates total for each row for 1 specific trans_name
total (move 22...) calculates total - merchserv

foreign affiliates and unaffiliated foreign persons calculates the specific category names for the column.

you can see in image, for the foreign rows, total column and the merchserv column has values. but the total (move 22...) column returns zero. why is the calculation not right for these 2 rows.

It's hard to know unless you tell us how these calculations are being done?

MF.

[avuong6]

foreign affilitiates:  if ([CATEGORY_NAME] = 'Foreign Affiliates') then ([Trans].[TRANS_VALUE]) else (0)

unaffiliated foreign persons:  if ([CATEGORY_NAME] = 'Unaffiliated Foreign Persons') then ([Trans].[TRANS_VALUE]) else (0)

total:  total([Trans].[TRANS_VALUE] for [TRN_TYPE_SCH],[QUARTER],[CATEGORY_NAME])

merchserv:  if ([TRANS_NAME] contains '%Merchanting%') then (([Trans].[TRANS_VALUE] )) else (0)

total (move 22...):  [Total]-[MerchServ]

[MFGF]

foreign affilitiates:  if ([CATEGORY_NAME] = 'Foreign Affiliates') then ([Trans].[TRANS_VALUE]) else (0)

unaffiliated foreign persons:  if ([CATEGORY_NAME] = 'Unaffiliated Foreign Persons') then ([Trans].[TRANS_VALUE]) else (0)

total:  total([Trans].[TRANS_VALUE] for [TRN_TYPE_SCH],[QUARTER],[CATEGORY_NAME])

merchserv:  if ([TRANS_NAME] contains '%Merchanting%') then (([Trans].[TRANS_VALUE] )) else (0)

total (move 22...):  [Total]-[MerchServ]

Hi,

Is there a specific reason your measure is being referred to as [Trans].[TRANS_VALUE] in your calculations? Have you tried adding it into your crosstab as the default measure (by dragging it into the home cell in the top left corner) and changing your calculations to refer to is just as [TRANS_VALUE]?

I just put together a similar crosstab using the sample "GO Sales (query)" package, and it works fine for me using a default measure.

Cheers!

MF.

[avuong6]

the measure comes from a join. i posted another image.

https://photos.app.goo.gl/N6Yim7LJ4hid9wmM7

trans.value comes up from the trans query

[avuong6]

i just realized the the sum in the total column for foreign affiliates and unaffiliated foreign persons is not correct.
the value 479,083 is wrong. it should be 3,669,143.

see next to last image.

why would this calculation not work in the total column (a custom data item) but be ok in the main tab body?

[MFGF]

i just realized the the sum in the total column for foreign affiliates and unaffiliated foreign persons is not correct.
the value 479,083 is wrong. it should be 3,669,143.

see next to last image.

why would this calculation not work in the total column (a custom data item) but be ok in the main tab body?

It's probably to do with the timing of the calculation? Do you see the same result if you just drag in the measure as a column next to it?

You could change the calculations for Foreign Affiliates and Unaffiliated Foreign Persons to be before the default aggregation occurs. You can do this by switching the items in the calculation to be directly from the package rather than being from the query, ie

instead of if ([CATEGORY_NAME] = 'Foreign Affiliates') then ([Trans].[TRANS_VALUE]) else (0)
drag in the items from the package instead, so the expression would look like if ([your package].[query subject].[CATEGORY_NAME] = 'Foreign Affiliates') then ([your package].[query subject].[TRANS_VALUE]) else (0)

Does this make a difference?

Cheers!

MF.