Post reply

SSAS Cube calculation

  • November 19, 2009 at 10:35 am

    #385817

    Hi,

    I have built a data cube in SSAS 2008 that displays billing and payment information. There are two key date fields. One is the original invoice date. The other is for subsequent invoice dates. So, for example, we bill $100 to 10 different patients in January 2009. Therefore, my cube illustrates billed = $100 for 01/09 (original invoice date).

    Let's say we receive payments of $50 in Jan 09, $25 in Feb 09 and $25 in Mar 09 that all tie to invoices that were originally posted in Jan 09.

    In short, I have $100 billed in Jan 09 (original invoice date or month) and payments of $50 in Jan, $25 in Feb and $25 in Mar (subsequent invoice dates or months). I'm trying to compute payment percentages based on the billed amount from the orginal month. But SSAS only counts the billed amount of $100 for January. I need it to count $100 for Feb and March as well so I can get percentages.

    Basically, I want to divide 50/100, 25/100 and 25/100 and put them into periods 1, 2 and 3 so we will know that out of the 100 dollars billed in January, we got paid half in the same month, 25% in month 2 and 25% in month 3.

    I want the amount billed to be the same for every original invoice month and the payments should differ based on what was received each month (subsequent invoices).

    Can anyone please help me with the calculation?

    Thanks,

    Matt

  • November 19, 2009 at 2:21 pm

    #1081858

    Well, what does your data look like? Can you post the DDL for the the relevent tables?

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • November 19, 2009 at 2:36 pm

    #1081863

    Thanks for replying. I can't post any data due to HIPPA, but here is a sample of what it looks like:

    HistDateKey InvDateKey Billed Payments

    1 1 100 50

    1 2 0 25

    1 3 0 25

    What I need to figure out is how to take each one of those payments and divide it by 100. More simply, I need to figure out how to code 100 where each of those zeroes lie if I can without readjusting my dataset (it's pretty big and takes forever to load).

    The SQL is actually pretty easy (SELECT SUM(Billed) FROM Table1 WHERE MONTH(HistDate) = MONTH(FactDate) and YEAR(HistDate) = YEAR(FactDate). As I mentioned, if I can avoid adding this column to my dataset and instead coding it inside VS 2008, I would love to do so.

  • November 19, 2009 at 3:11 pm

    #1081872

    You could change you DSV to use a named query instead of your table. This would allow you to write the SQL to create the 'Billed' column value at the DSV layer to avoid updating your physical table. If you can get that value into your DSV, creating a new calculated measure to represent Paymnets / Billed would be easy.

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • November 23, 2009 at 9:31 am

    #1083012

    Thanks for your help with this....

    HistDateKey InvDateKeyBilledPayments

    1 1 100 50

    1 2 100 25

    1 3 100 25

    Once I get the billed fields to all equal the total amount billed per HistDateKey, how do I get it to not sum on the cube? In other words, I am able to populate each billed field with the $100, but when I place it in the cube, it sums it to $300. Let's say histdatekey of 1 = January 2009. I want the billed to appear on the cube as $100 for 01/09, not $300.

  • November 23, 2009 at 9:50 am

    #1083020

    OK, so you got the 100 amount to appear in the Billed column. Good start. If you are seeing that summed in your cube, that's because you've also created (or the cube wizard created it for you) a measure on that column using the SUM aggregate.

    You'll need to create a new Calculated Member. To do this, go to the Calculations tab, right-click in the 'Script Organizer' pane, and select 'New Calculated Member'. From the Metadata tab in the 'Calculation Tools' pane, drag the value for your Billed column and the Payments columns into the Expression for the Calculated member. Use the Expression builder to do your division to come up with your percentages. You can even set the format to use the Percent formatting.

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • November 23, 2009 at 12:22 pm

    #1083107

    Thanks again.

    Do you know what I need to choose in the "usage" drop down so that the cube returns the sum of the billed by month (per my SQL) instead of the total sum? I tried no aggregations and it returned nothing in my cube.

  • November 23, 2009 at 12:26 pm

    #1083108

    Nevermind. I used minimum and it worked.

    Thanks so much for your replies. This really helped me!!!

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply