Hi, If you are willing to change your query filter, the following is a filter I use to do the same thing you are seeking to do: "Contracts"."Revised Contract End Date" BETWEEN TIMESTAMPADD(SQL_TSI_MONTH,-1,TIMESTAMPADD(SQL_TSI_DAY,-(DAYOFMONTH(CURRENT_DATE)-1),CURRENT_DATE)) AND…

Have you tried bringing the sort column outside of the union? By that, I mean going to the header level and using the "Add a Result column" option. If it's the fourth column, refer to it as "saw_3" (first column is saw_0). Sort on that instead of the original column. If that doesn't work, add a function to it, like…

You can insert dummy lines right in the analysis using a union. In my case, I added a "Week of the Year" column, a zero value for the graphed column, and null values for all other columns. Filter on the previous year for weeks less than week-of-current date. Use external columns ("saw_x" calculated columns) that sum by…

My solution to this is to set a presentation variable in the employee ID prompt, say "varID". Then, in the filter of the query, insert: UPPER('@{varID}')=LOWER('@{varID}'). In the 'No Results' view, type the custom message "Invalid Employee ID - Numbers Only!" This only works as a test for unwanted alpha characters, (and…

My suggestion was really about the structure of your data model. Setting that aside, another approach would be to construct accumulating columns for each month of the report. I'm assuming you have an "Acquired Date" and a "Loss Date" for each customer. You would create 6 monthly columns of this general formula: September…

Can you pivot your data the other way? If so, the calculated columns become simple:

I don't have an immediate answer, but isn't your illustration incorrect? Should the losses in the Aug-16 and Sep-16 rows be shifted to the right? i.e., Customers gained in Sep-16 can't be lost in July or August of '16.

Are any of your analyses unions? I work in 11.1.1.9.5, but I've found that that can cause problems for master-detail linking. This may not be your problem, but if it is, the solution I've found is that the channel has to be established in the original "leg" of the union, before other queries are added in.

I had to get out of my Inbox view to see the "Correct Answer" button.

I would, but I don't see where I can do that. All I see is "helpful", and "No actions are available".

Thanks Robert. See my comment below, mistakenly posted to my original post. I appreciate the response!

It turned out that my original subtotal method worked just fine, but at some point, I had removed the "Measures Labels" from the column stack. When I replaced it, the dividers appeared as I wanted.

Robert, Nasty, hacky ideas are a specialty of mine. But, in this case, I think I'd have to create a union to get the null column to appear where I want it. Just beyond my tolerance level for a cosmetic treatment. I have tried conditional formatting, but that only affects populated cells. Thanks for your time, Jerry

Sounds like tough sledding, but well done. I did format with html when I used this narrative-view method, but I wasn't concerned with the export difficulties.

Sounds like tough sledding, but well done. I did format with html when I used this narrative-view method, but I wasn't concerned with the export difficulties.

Hi Darlene, If you've written a simple query that returns the values you want to see in the prompt, you can copy that SQL from the Advanced tab, and put it into your prompt. I usually delete the "s_0"- type references(highlighted), and in the example below, I'd place my "Contracts"."Fund Source Code" into the ORDER BY…

Glad I could help. I've found that I can reference repository variables in the headings, but not presentation variables. One way around that is to create a narrative view. If you include the values of your variables as columns, you can list them in the prefix, then list each record in the body. Formatting is a bear,…

Try adding "by 1" to the Max clause: MAX(saw_1 by 1)+RSUM(ifnull(saw_2,0)). I know it's a workaround, but the addition of a constant eliminates that error in most cases.

Are you familiar with the "Add Result Column" link on the combined analysis window? It allows you to create columns calculated outside the union. If your union looks like your example table, I would add a result column that looks like MAX(saw_1)+RSUM(ifnull(saw_2,0)).

Robert, I do understand that, but doing it more than once reinforces the need to get it done the right way. Jerry