calc totals for months question

I have the following records:
Unit Create_date Type
A 01/02/2004 Clock
A 01/15/2004 Car
A 01/20/2004 Truck
A 01/23/2004 Clock
A 01/24/2004 Clock
A 01/25/2004 Car
A 01/27/2004 Truck
A 02/01/2004 Clock
A 02/02/2004 Car
A 02/15/2004 Truck
A 02/20/2004 Car
A 02/27/2004 Car
A 06/03/2004 Truck
A 06/04/2004 Truck
A 06/15/2004 Clock
A 06/29/2004 Car
Report Output example
Unit Created Type
A Jan 04 Clock
Type Count = 3
A Jan 04 Car
Type Count = 2
A Jan 04 Truck
Type Count = 2
Total for January = 7
A Feb 04 Clock
Type Count = 1
A Feb 04 Car
Type Count = 3
A Feb 04 Truck
Type Count = 1
Total for February = 5
A June 04 Clock
Type Count = 1
A June 04 Car
Type Count = 1
A June 04 Truck
Type Count = 2
Total for June = 4
I am trying to determine how to count the totals for the months.
Any ideas?
Thanks.Just use a table, and add a table grouping "MonthGrouping" like e.g.:
=Year(Fields!TimeStamp.Value)*100 + Month(Fields!TimeStamp.Value)
Then add a second (inner) table grouping "TypeGrouping" which groups on the
type, e.g.: =Fields!Type.Value
These two groupings should give you the basic structure of the desired
output. Finally, to determine the counts for the months, just add an
expression like this to the "MonthGrouping" groop footer:
=CountRows("MonthGrouping")
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Mike" <Mike@.discussions.microsoft.com> wrote in message
news:83250B36-B1BC-4601-9002-87EDE106838E@.microsoft.com...
>I have the following records:
> Unit Create_date Type
> A 01/02/2004 Clock
> A 01/15/2004 Car
> A 01/20/2004 Truck
> A 01/23/2004 Clock
> A 01/24/2004 Clock
> A 01/25/2004 Car
> A 01/27/2004 Truck
> A 02/01/2004 Clock
> A 02/02/2004 Car
> A 02/15/2004 Truck
> A 02/20/2004 Car
> A 02/27/2004 Car
> A 06/03/2004 Truck
> A 06/04/2004 Truck
> A 06/15/2004 Clock
> A 06/29/2004 Car
> Report Output example
> Unit Created Type
> A Jan 04 Clock
> Type Count = 3
> A Jan 04 Car
> Type Count = 2
> A Jan 04 Truck
> Type Count = 2
> Total for January = 7
> A Feb 04 Clock
> Type Count = 1
> A Feb 04 Car
> Type Count = 3
> A Feb 04 Truck
> Type Count = 1
> Total for February = 5
> A June 04 Clock
> Type Count = 1
> A June 04 Car
> Type Count = 1
> A June 04 Truck
> Type Count = 2
> Total for June = 4
> I am trying to determine how to count the totals for the months.
> Any ideas?
>
> Thanks.
>

Calc Measure Immediate Viewing

This is probably obvious, but....

In MSAS 2000, I could immediately view the new calc measure in Analysis Manager without rebuilding the cube. Is this still possible in SSAS 2005? Can you list the steps?

Yes you can using the new MDX debugger.

When in BIDS, in the caluclaiton tab, create your calculated member and then select the Debug menu and start debuggging.

The cube browser will appear just beneath the calculaiton script.

You can not only step by step through your various caluclaiton, but you can also change them and see the impact immediately.

It is a full fledge VS debugger for MDX...

|||

Thierry,

Thanks for the answer. It was very helpful.

I've read that you can develop in Online Mode in the BI Studio to get the same affect as in Analysis Manager (AS 2000). What is the difference between using the debugger in project mode and seeing the change immediately online mode? Are there pros and cons to using online mode versus project mode?

Justin

Calc a % of a summary total

I have a report that requires 2 "tables". The first table summarizes total
lbs by a category and then provides a company total at the end:

cat 1 75
cat 2 100
cat 3 200
-
total 375

The second table needs to display the % of the total for each category:

cat 1 20%
cat 2 27%
cat 3 53%
-
total 100%

How can I reference the company total for doing the calculations for the
second table? I am working with Visual Studio 2003. My dataset pulls a
file from the AS400 using an sql statement.

Thank you, PB

You don't need to access the first table to pull the percent. Just build the first table and then copy it below or next to table_1. Then go into the expression of the sum of table_2 and change the formaula to formatpercent(.....) and you are done.

Since each table is pulling the same fields and calculating on the same set of data the tables they will always match. Just make sure to make equal changes on both tables.

Option 2:

I would recommend just adding another column next to the sum with the percent. This way you would only need one table.

|||

The problem is that I don't know the company total until the end, or is there an expression that will let me calc the percent of the overall total? I'm kind of new to this and I don't know all the expressions/functions. Essentially, I need each category total/company total.

Also, I do agree that it would be better to just include the % as another column in the first table. These were originally Excel pivot tables that I have been asked to program using Vusual Studio.

Thank you .... PB

|||

I figured out the answer. I needed to add company as the highest grouping level in my table. I then need to add a "scope" to my aggregate function. ex.

=sum( Fields!ASCURWK9.Value ) / sum( Fields!ASCURWK9.Value,"company_group" )

Calc % in crystal detail againt running total

Quick question.
I have a grand total being calculated in a formula, for each detail line I am trying to get a percentage of that number against a value in the detail. The problem is that the grand total is running and only calculates against the current running total not against the grand total when the report finishes. Any way around this?Hi!

1) Insert the Formula Field from Report Design Page

2) Named as Percentage and write the code to the formula field coding area

{table.numberfiled}/100

3) And Drag and Drop the Formula Filed on Details Section Where do u want place.