Dear all, I am working with a problem:There are three list boxes to select the financial year range, quarter and months respectively in our application each of which can serve as a filter.We are trying to make a report showing the customer-wise total sales of the previous financial year(first measure column), that of the current financial year(second measure column), the corresponding indicator (next column) and the corresponding percentage.The indicator(for each rows in the report) will be calculated as: if the total sales of the current year is greater than that of the previous then 'Growth', if it's less the 'De-growth' and if it's equal then 'No-growth'.The percentage(for each rows in the report) will be calculated as For e.g. 'Growth': (Total number of rows with indicator='Growth'/Total number of rows displayed in the report)*100. The same logic will be followed for the other two indicators also.We need to create another summary report based on the outcome of the above report considering the indicators as the dimension and their percentage values and total values. Thus this report will contain three columns: 1. Indicator: showing one of the aforesaid value (i.e. 'Growth'/'De-growth'/'No-growth') 2. Corresponding percentage value. 3. Corresponding total values.The logic for calculating the total value for each indicator will be as:For 'Growth': The sum total of all the (Total sale value or previous year-That of the current year) for which indicator='Growth' in the first report. The same logic will be followed for the other indicators too in order to get the corresponding total value.This is our urgent requirement.We are attaching the application herewith. The first report heading is given as the "Main Report" and the next one as "Summary Report".Please do reply if you need any more information.
Thank you very much for looking into our problem and sending the application. The "Summary Report" is almost OK.
However a little bit more effort is required. Please address the problem in the "Main Report" first where the percentage values for each indicator ('Growth'/'Degrowth'/'Nogrowth' depending on the values of the first and second measure columns) need to be displayed. Naturally these values will repeat many times. Once this is done then the next report (Summary) can be done based on it where the indicators will act as dimension.
Please note some points:
1. The inline data for "S" in the script is given for example only. In reality there will not be such thing. Those percentage values will be obtained on-the-fly from the first report (i.e. with the caption: 'Main Report').
2. While calculating the percentage value, please don't divide by "count(DISTINCT Customer)" which is a static value. It should be the total number of displayed customer records in the "Main Report".
Please do reply us if you need any more information regarding this.