Calculate 3rd column from subtotaled columns

[Former Member]

I have an Xacute query that returns data in a somewhat detail level. I have applied the SubtotalTransform.xsl Stylesheet to subtotal the data to a daily total. I now wish to create a 3rd column based on the subtotaled data.

Is there are process that I can insert into the xsl document or execute through xMII that will allow this transformation?

Thank you,

Larry

[Former Member · Accepted solution]

Hi, Larry.

Did you try using the Crosstab action instead of the SubtotalTransform?

- Rick

[Former Member · Accepted solution]

Larry - I sent you an overall solution to your application "off-list".

The great news is that I was able to do it using just the XML transformation actions – no repeaters, no weird expressions!

The steps are as follows:

1) Load your two datasets

2) Do a crosstab action on each dataset, crosstabbing on Resource

3) Do an <b>outer join</b> on the results of the two crosstabs - <i><b>the other magic is to assign a value of "0" in the link editor to the "NullNumberValue" property</b></i> - the default is "NA"

4) Do a calculated columns action on the results of the join. I used 100 * (Production - OffQuality) / Production to calculate yield.

5) Assign the output of the calculated columns action to the transaction output parameter.

This way, you not only get a yield for each resource, you also get an overall yield (you'll see drilldown levels in your resultset). You can filter these out if you don't want grand totals, group totals, etc...

Best regards,

Rick

[Former Member]

You can use the "Calculated Columns" action block in Business Logic Services.

Also for your subtotals you can also use the "Crosstab" action block to get your subtotals

You could accomplish all of this with a few steps by mapping in the data from your query into Business Logic.

Basically you would drop in an action for the type of query you are using (SQL, TAG, etc). Use the Crosstab action block on the query's dataset being returned to calculate the subtotals. Then use a Calculated Columns on the dataset of the Crosstab action block to get your third column.

Hope this help!