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

How can I multiply DS row x value in another DS or global variable

Leom

I have a programming challenge I cannot solve. I am hoping to get help from the experts. I have one SQL Server dataset with sales results in which I use Case statements to get monthly column totals.I have a second dataset of sales objectives, also created with case statements. I join them together in a JOIN dataset and I get columns in one table: JAN Sales JAN Quota JAN % 90 100 90% The challenge is I need to pro-rate the quota, for example only apply 50% of the quota on day 11 of a month with 22 sales days. I have a third dataset that tells me the number of days elapsed .. and percentage to apply, but it is in a Flat File and I cannot use case statements. 
 
How can I do something like the following? JAN Sales JAN Quota JAN Elapsed Prorated Quota JAN %90 100 X 50% = 50 = 180% Since the Flat file cannot be queried with case statements, I cannot join this dataset to the results. I was thinking there must be a way to multiply datarows x the value in other datarows .. or perhaps set it to some global variable so it can be used in the aggregate in the table. Any help would be appreciated.

Find more posts tagged with

Comments

BRM

You're on the right track. Set a PersistentGlobalVariable in the onCreate event of the cell that contains the elapsed field. Then recall it in the prorated and % fields to do the calculations. Alternatively you could do a join of the datasets in BIRT and thus bypass that limitation of SQL Server. Then you woulld have one dataset and could do what you want with calculated columns.