Create a constant calculated member

Hi everyone, my question is related to creating a calculated member that must not change when the user changes the selected filter in the dimensions.
I will try to explain it: I need the total number of rows of my cube for a concrete year (year is a dimension). I will use this value in a calculated formula to define the percentage of rows that contains a concrete value (selected in the filter dimension). It means that my calculated member has a factor that change with the filter (ie. number of rows with value 'XXX'), divide by a factor that not change (total number of rows per year).

I have tried in many different ways, but I did not succeed. I cannot believe this is not possible...Maybe some sample code or DDL will help...

I keep thinking cement for some reason...|||Hi Brett, I understand your question since this is quite strange, nevertheless, I will try to explain.

The main reason is that I need to detect the quality of the data (used to join with the dimensions) in the fact table . The formula to do that is getting the rate of rows with the correct information. One way to do it will be just dividing the total number of rows by the number of rows with dimension column equals null.

I can calculate the total number of rows creating a calculated member like "count([mydimension].AllMembers)", but this number of rows is true only for the dimension "mydimension". Other possibility is using a column with value 1 for every row, and doing a sum([mydimension].AllMembers,mycolumn1), but this is true only when I filter for mydimension.

So, the real question is, how can I get the real total number of rows, valid for any dimension?.

I hope this helps...
thx in advance
Oscar

Counting with a filter

Using SS2000, VS2003, RS2000.
I want to list all of the individual orders and how they were shipped. Then
I want to count the total number of orders and get a count for each method of
shipment. So, the data might look like this:
Order # Shipped
1 UPS
2 FedEx
3 USPS
4 FedEx
5 UPS
6 FedEx
Total Orders 6
FedEx 3
UPS 2
USPS 1
I tried adding a group and putting a filter in the group (FedEx) but it only
limited the detail part of the report to the rows that were shipped by FedEx.
Now I can't get rid of that filter. I even deleted all of the headers,
footers, groups and detail section and it still only gives me the rows with
FedEx.
Any ideas,
Thanks,
--
Dan D.I'm closer but not there yet. What I have now is this:
Order # Shipped
2 FedEx
4 FedEx
6 FedEx
Total Orders 3
FedEx 3
1 UPS
5 UPS
Total Orders 2
UPS 2
3 USPS
Total Orders 1
USPS 1
But what I need is what was in my original post
--
Dan D.
"Dan D." wrote:
> Using SS2000, VS2003, RS2000.
> I want to list all of the individual orders and how they were shipped. Then
> I want to count the total number of orders and get a count for each method of
> shipment. So, the data might look like this:
> Order # Shipped
> 1 UPS
> 2 FedEx
> 3 USPS
> 4 FedEx
> 5 UPS
> 6 FedEx
> Total Orders 6
> FedEx 3
> UPS 2
> USPS 1
> I tried adding a group and putting a filter in the group (FedEx) but it only
> limited the detail part of the report to the rows that were shipped by FedEx.
> Now I can't get rid of that filter. I even deleted all of the headers,
> footers, groups and detail section and it still only gives me the rows with
> FedEx.
> Any ideas,
> Thanks,
> --
> Dan D.|||Dan,
Can you do this with 2 datasets and 2 tables?
Dataset1:
SELECT orderID, carrier FROM orders ORDER BY orderID
Dataset2:
SELECT carrier, COUNT(orderID) AS carrierCount FROM orders GROUP BY
carrier ORDER BY COUNT(orderID) DESC
You can use a header/footer row for the grand total.
-Josh
Dan D. wrote:
> I'm closer but not there yet. What I have now is this:
> Order # Shipped
> 2 FedEx
> 4 FedEx
> 6 FedEx
> Total Orders 3
> FedEx 3
> 1 UPS
> 5 UPS
> Total Orders 2
> UPS 2
> 3 USPS
> Total Orders 1
> USPS 1
> But what I need is what was in my original post
> --
> Dan D.
>
> "Dan D." wrote:
> > Using SS2000, VS2003, RS2000.
> > I want to list all of the individual orders and how they were shipped. Then
> > I want to count the total number of orders and get a count for each method of
> > shipment. So, the data might look like this:
> >
> > Order # Shipped
> > 1 UPS
> > 2 FedEx
> > 3 USPS
> > 4 FedEx
> > 5 UPS
> > 6 FedEx
> >
> > Total Orders 6
> > FedEx 3
> > UPS 2
> > USPS 1
> >
> > I tried adding a group and putting a filter in the group (FedEx) but it only
> > limited the detail part of the report to the rows that were shipped by FedEx.
> > Now I can't get rid of that filter. I even deleted all of the headers,
> > footers, groups and detail section and it still only gives me the rows with
> > FedEx.
> >
> > Any ideas,
> >
> > Thanks,
> > --
> > Dan D.|||It's worth a try. I'm wondering RS will keep the two datasets in sync. BTW, I
also posted a different example this morning under the subject "is this
possible in RS".
For the time being, I've created a another group on the carrier. Even though
it's not in the format the client wanted, it will give the counts they want.
I'll keep experimenting, though and try your idea.
Thanks,
--
Dan D.
"Josh" wrote:
> Dan,
> Can you do this with 2 datasets and 2 tables?
> Dataset1:
> SELECT orderID, carrier FROM orders ORDER BY orderID
> Dataset2:
> SELECT carrier, COUNT(orderID) AS carrierCount FROM orders GROUP BY
> carrier ORDER BY COUNT(orderID) DESC
> You can use a header/footer row for the grand total.
> -Josh
>
> Dan D. wrote:
> > I'm closer but not there yet. What I have now is this:
> > Order # Shipped
> >
> > 2 FedEx
> > 4 FedEx
> > 6 FedEx
> > Total Orders 3
> > FedEx 3
> >
> > 1 UPS
> > 5 UPS
> > Total Orders 2
> > UPS 2
> >
> > 3 USPS
> > Total Orders 1
> > USPS 1
> >
> > But what I need is what was in my original post
> > --
> > Dan D.
> >
> >
> > "Dan D." wrote:
> >
> > > Using SS2000, VS2003, RS2000.
> > > I want to list all of the individual orders and how they were shipped. Then
> > > I want to count the total number of orders and get a count for each method of
> > > shipment. So, the data might look like this:
> > >
> > > Order # Shipped
> > > 1 UPS
> > > 2 FedEx
> > > 3 USPS
> > > 4 FedEx
> > > 5 UPS
> > > 6 FedEx
> > >
> > > Total Orders 6
> > > FedEx 3
> > > UPS 2
> > > USPS 1
> > >
> > > I tried adding a group and putting a filter in the group (FedEx) but it only
> > > limited the detail part of the report to the rows that were shipped by FedEx.
> > > Now I can't get rid of that filter. I even deleted all of the headers,
> > > footers, groups and detail section and it still only gives me the rows with
> > > FedEx.
> > >
> > > Any ideas,
> > >
> > > Thanks,
> > > --
> > > Dan D.
>