Column Totals / Sum by Date

HI,
I am new to RS and I am running into some problems create reports. What I
would like to do is create a report that will count all distinct rows for a
"Users" column for every single date. I am able to get the total users from
the "Users" column but the problem is getting a running list of totals by
date. There is no date field in the database. There is a date field for
enrollment and unenroll but these are not the dates I am looking for. I
would like every single date to be totaled.
Do you know of any examples of this on the web or an example that you could
send me?
Any help would be great!
ThanksYou can't report on anything that's not in your data source. It sounds like
you're trying to get a count of users for each date. You'll have to solve
that in your source query first, then you can report on it. My suggestion
is to create a new reference date table with a record for each date in the
range you want to report on. That's only 365 records per year, so make as
many years as you want. Then join to the Users table on ReferenceDate
between EnrollDate and UnenrollDate.
A nice benefit of having a reference date table is that you can put other
data in each record as well, such as week number, quarter, fiscal year and
calendar year, for easy grouping. Yeah, it's denormalized, but makes
reporting a snap. It's easy to do this is in Excel, then import the data
into SQL.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"ACD" <ACD@.discussions.microsoft.com> wrote in message
news:66C00F58-9E89-456B-9931-5E2E5E964645@.microsoft.com...
> HI,
> I am new to RS and I am running into some problems create reports. What
> I
> would like to do is create a report that will count all distinct rows for
> a
> "Users" column for every single date. I am able to get the total users
> from
> the "Users" column but the problem is getting a running list of totals by
> date. There is no date field in the database. There is a date field for
> enrollment and unenroll but these are not the dates I am looking for. I
> would like every single date to be totaled.
> Do you know of any examples of this on the web or an example that you
> could
> send me?
> Any help would be great!
> Thanks

Column Totals

Hi,
I am trying to come up with column totals like,
Jan Feb Mar ...
P1 2000 2 5 6
2001 7 10 8
2003 10 5 9
P1 2000 4 12 10
2001 7 10 8
2003 10 5 9
TOTAL 40 47 50
How can I do that? I know it is related to the scope of sum function, but I
couldn't figure out.
Thanks a lot.
Regards,
DCIf this is a matrix, then just add a subtotal row (BOL has more information
on subtotals).
If it is a table, then add a group footer or a table footer. Then you just
need to drop the field into the according drop zone. The scope of the sum is
automatically determined as the "current" scope which would be the entire
table if the sum is in the table footer.
Alternatively, you can always explicitly specify a scope. Assuming a table
is named "Table1", an expression of =Sum(Fields!Sales.Value, "Table1")
anywhere within the table would evaluate to the total sales of the table's
data.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"DC" <DC@.yahoo.com> wrote in message
news:OrGA2xClEHA.2140@.TK2MSFTNGP15.phx.gbl...
>
> Hi,
> I am trying to come up with column totals like,
> Jan Feb Mar ...
> P1 2000 2 5 6
> 2001 7 10 8
> 2003 10 5 9
> P1 2000 4 12 10
> 2001 7 10 8
> 2003 10 5 9
> TOTAL 40 47 50
> How can I do that? I know it is related to the scope of sum function, but
I
> couldn't figure out.
> Thanks a lot.
> Regards,
> DC
>|||thank you for this as well.
tango
"Robert Bruckner [MSFT]" wrote:
> If this is a matrix, then just add a subtotal row (BOL has more information
> on subtotals).
> If it is a table, then add a group footer or a table footer. Then you just
> need to drop the field into the according drop zone. The scope of the sum is
> automatically determined as the "current" scope which would be the entire
> table if the sum is in the table footer.
> Alternatively, you can always explicitly specify a scope. Assuming a table
> is named "Table1", an expression of =Sum(Fields!Sales.Value, "Table1")
> anywhere within the table would evaluate to the total sales of the table's
> data.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "DC" <DC@.yahoo.com> wrote in message
> news:OrGA2xClEHA.2140@.TK2MSFTNGP15.phx.gbl...
> >
> >
> > Hi,
> >
> > I am trying to come up with column totals like,
> >
> > Jan Feb Mar ...
> > P1 2000 2 5 6
> > 2001 7 10 8
> > 2003 10 5 9
> > P1 2000 4 12 10
> > 2001 7 10 8
> > 2003 10 5 9
> > TOTAL 40 47 50
> >
> > How can I do that? I know it is related to the scope of sum function, but
> I
> > couldn't figure out.
> >
> > Thanks a lot.
> >
> > Regards,
> > DC
> >
> >
>
>

COLUMN GRAND TOTALS ARE INCORRECT FOR CALCULATED FIELDS IN VIRTUAL CUBE

Hello,

We have migrated some cubes from sqlserver 2000 to 2005 with success. The applicaticon contains one virtual cube made up from 6 other cubes. On the virtual cube level we specified calculated fields. The calculation contains elements from different cubes which shares dimensions.

while browsing the cube from within sql server management studio or from excel using OLEDb 9.0 Provider we discovered that the column grand totals for these calculated fields are incorrect. The row grand totals are fine.

Searching the internet (google) i have found a hotfix describing the problem SQL MSAS 2005 (http://support.microsoft.com/kb/918251). We have installed this hotfix on top of SP1 but without results.

Is somebody familiar with this problem or knows how to solve this !!!!

Thanks in advance.

Dirk

When you say "totals are incorrect", can you please provide the expressions of the calculated members, the expected values and the actual values. I.e. if you expected to see NULLs, but get values - it could be because of IgnoreUnrelatedDimensions setting etc.|||

Hi Mosha,

I expect values but the results are not correct ( so as an example i expect 82.0415,00 and i get 80.0345,53)

regards,

Dirk

|||

> please provide the expressions of the calculated members

The investigation will likely to go into the values on which these calculations are based. Another reason for disrepancies could be RI problems in the data.

|||

Hi Mosha,

I will provide details for you asap. At the moment i can't log in into the remote customer sql server (machine is down). I will provide you with full details tomorrow (same time).

Regards,

Dirk

|||

Hi Mosha,

Here are the details you asked for:

Virtual Cube: Cube_Omspanning

Calculated Measures:

[KostenquotientOmzet]

'[Measures].[Kostenquotient_OMZ]/[Measures].[Teller_OMZ]'

[KostprijsOmzet]

'[Measures].[EenhedenOmzet]*[Measures].[KostenquotientOmzet]'

[KostprijsOmzet]

'[Measures].[EenhedenOmzet]*[Measures].[KostenquotientOmzet]'

[RendementOmzet]

'[Measures].[BedragOmzet]-[Measures].[KostprijsOmzet]'

[RendementOmzet]

'[Measures].[BedragOmzet]-[Measures].[KostprijsOmzet]'

Cube_Omzet_Kostenquotient

Calculated measures:

[Gemiddelde Kostenquotient] (Not Used in virtual cube calculated fields)

'[Measures].[Kostenquotient_OMZ]/[Measures].[Teller_OMZ]'

Measures:

Kostenquotient_OMZ

Teller_OMZ

Cube_Omzet

Calculated measures:

None

Measures:

EenhedenOmzet

BedragOmzet

BedragBasis

A couple of general questions:

1.) Are Grand Totals calculated during load of the cube ?

2.) Could this be a Rounding (to integer) problem?

Regards,

Dirk

|||Based on these formulas - I can tell, that the differences must exist for the real measures. Please start comparing the real measures in AS2000 vs. AS2005, and start slicing by different dimensions. As soon as you identify dimension which causes the difference, I recommend checking whether the attribute relationships are defined correctly in that dimension - this seems to be the most probable cause of the difference. The difference is way too big to be explained by rounding here.

Column Grand Totals

Is there a slick way to add grand totals to my report? so far, in my report table I have the following sections

TableHeader1

TableHeader2

Group1

Footer1

I added one of the same fields from my Group1 to Footer1....but it's not summing correctly. I have this in my footer right below one of my Group Fields:

=SUM(Round(((Fields!FeeGoal_AZ.Value) / Fields!FeeSchedule.Value) * 100))

From the expression it looks like you are summing up percentage values? Is this really what you want?

Maybe you are actually looking for the average percentage value?
E.g.
=Avg(Fields!FeeGoal_AZ.Value / Fields!FeeSchedule.Value * 100)

-- Robert

|||

sorry, just ignore that one. Try this...this is actually numbers:

=SUM(Fields!PostedAmount.Value)

when I put that in my footer, it isn't summing my Group field above it in my Group1 correctly all the way down that colum...

|||

Rob, can you help me!! need your input since you're the only one who seems to have any knowledge on this. I am very desperate here! This can't be that hard:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=235554&SiteID=1

|||totals are accurate...I just needed to add SUM() to my group row fields and then I saw the light...My grand total was acting fine, it was just my row items that weren't showing currect Sums. I assumed that since the SSRS 2005 report had a group, that it would sum up it's rows automatically but that's not the case, you still need to use SUM() around your fields in your group.