.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
david stephan
Gaurav Pal
Post New Web Links

SSAS - MDX - need to generate a count for the life of a record

Posted By:      Posted Date: October 21, 2010    Points: 0   Category :Sql Server


I'm struggling trying to create a calculation which will show what I want.  My requirement is to show a count of 1 while a record is active, and 0 when it is not.  For example, in my data warehouse I have an enrollment record active from 1/1/2010 through 12/31/2010 (12 months).  If the user runs an excel cube report for 2010 by months, I want to show a 1 for all 12 months (if the report were run for quarters, then a 1 for each quarter etc.).  If the user runs a report from 2008 through 2010, the record would show 0 for 2008 and 2009.


Now I could haul off and create a daily record for that enrollment, and have 365 daily records in my DB.  I really don't want to do that. Conceptually here's what I want to accomplish:


IIF([Coverage Start Date].[Date].CurrentMember.FirstChild>=[Coverage Start Date].[Calendar Date] AND [Coverage Start Date].[Date].CurrentMember.FirstChild <= [Coverage End Date].[Calendar Date],1,0)


So if the current member in the example above is Q3 2010, then [Coverage Start Date].[Date].CurrentMember.FirstChild would be '7/1/2010' (first day of Q3).  Since 7/1 is >= 1/1 [Coverage Start Date].[Calendar Date] and <= 12/31 [Coverage End Date].[Calendar Date] this should evaluate to 1.


it doesn't. I ge

View Complete Post

More Related Resource Links

how ssas generate sql queries when processing dimensions and partitions?

hi all, how does ssas2008 generate sql queries to read data from the source, where processing dimensions and partitions? is there any reading meterial that clearly explains how the sql queries are constructed according to properties of dsv, dimensions and measures and partitions.   thanks in advance.Andrew Chen Interested in BI related technologies

SSAS 2008 Measure group Distinct count

Hi all, I have a data of as number of trasactions,DD, SO, BouncedDD, CancelledDD all are in count (number) while adding these measures manullay to a measure group I have selected usage as DistinctCount for one measure and for all the remaining measures as DiscinctCount.While deploying the cube it shows error as "Fact table canot have more than one distinct count"  

record count in sqldatasource

Hi,   I would like to know how many records are selected in sqldatasource after select command   Thanks

DataBase maximum table record count


Hello Friends,

My requirement is, we have more than 5 database servers, each server lot of databases, each database lot of tables.

 I want show the details


ServerName: abcServer

DatabaseName: xyzDatabase

MaximumRecordsTableName: empTable


In asp or Classic asp.

please anybody help for code and query.


how to split the connection string in asp ?

record count for subquery which is used for INSERT

My current code is like this

Insert into ReportDetail( Partcipantid, Reportid)

      select distinct ParticipantID , 3 from abc   where

        closed = 0 and ( isnull ( primaryphone, '' )

How to get total record count in an OUTPUT Parameter


I couldn't get the total record count in a, in my stored proc, in a given scenario. Please help


My SP looks like this:

create proc RecordCount

       @CustomerID varchar(10),

       @RecCnt int OUTPUT


begin tran

if(CustomerID = 'ABC')

@RecCnt = select count(*) from Customers where CustomerID='ABC'



begin tran


@RecCnt = select count(*) from Customers where CustomerID='XYZ' and TranDate > '05/01/2009'




-- This is how I called my SP

declare @rc int

exec RecordCount 'XYZ', @RecCnt = @rc OUTPUT

select @rc

sql count record problem


if i have a prodcut with product no. and each product is made by many parts with parts no.
now i would like to retrieve data to count how many parts to make a product.
in sql, can i count parts no. by each product no. like belows:

select product_no,parts_name,manufacture_date,(select count(*) from item where product_no=p.product_no) as [No. of items] from prodcut p left outer join item i on p.product_no=i.product_no

but i found that the execution time is too long, is it the right way to count no. of item in each product??

How to generate pivot tables from SSAS cube programmatically?


I am able to produce about 20 pivot tables from SSAS cube one by one. Is it possible to produce these tables programmatically with header and footer information?


Please note that the end users like to use excel.





Submitting Infopath forms to a Sharepoint Library - how can I generate a unique name for each record


I have published an Infopath form to a Sharepoint Library. What is the best way to ensure the following:

1. have a name per saved form (I do have a name field which has a set certain amount of available names in it which I am currently using as the form name - coming from an XML file)
2. users can edit already submitted forms without changing the name of the saved form
3. Ensure that users who may create a new form which happens to have more or less the same data as a previously saved form don't end up overwriting the existing one and hence I end up loosing saved data?

Using the name field + allow overwrite will allow me to have a unique name for the form and allow it to be overwritten but if I create a new form with the same name and maybe just a few small differences I will overwrite the existing form which is what I don't want - someone may not realise that an existing form with the relevant information already exists and we could loose already saved information

Any advice would be greatly appreciated.


IN SSAS 2005, total count is incorrect for a partial month



IN SSAS 2005, when I navigate Cube->browser and I drag my fields "Day of week" to rows and couple of measures namely "M1" and "M2" to columns

and applied a filter "hierarchydate" in the filter section(eg partial month Jan 10 to Jan31), I get the total count incorrect. I also have the following observations

1) for example "Instead of giving Sunday "Sub total" for that partial month i.e "Jan 10 to Jan31", it is displayed with all the Total Sunday's count for all the years

2) IN Cube-> browser , it is working fine, if I apply the filter in "select dimesion" section instead of applying filter in "drop filter fields section"

Is there any such kind of issue in SSAS 2005?

Please reply me ASAP if you have any information

Unable to Export to Spreadsheet in SharePoint if record Count increase more than 1100


Hi All,

When I am trying to export the SharePoint View from SharePoint site to Spreadsheet, sometimes I am able to export the the whole data and sometimes I only get Column headers but no data or records

I have Infopath form (2007) published on SharePoint Site and I am using MOSS 2007 and Excel 2003

I am not able reach the cause of this behaivour of Export to Spreadsheet functionality

Is their a Buffer size Issue with IIS or Session time out

I am not sure because when I export to Spreadsheet and if the record goes to Spreadsheet the file size is just 1.72 MB

Please suggest

Hitesh Duggal

display a record count from sql database connection


sometimes learning asp.net feels so heavy handed compared to some of the general purpose scripting languages like php.  for examples, all i want to do is create a sql connection that pulls a row count and displays that number.  I don't need a grid, list, detail page, etc., nothing this heavy... a simply query that will display a simple bit of text.  for the life of me I can't figure out how to do it.  Using these controls, I'm not in the code behind with the query, so I wouldn't attach this to a label (or would i), and there's nothing i see in the toolbox that does what i want.  Perhaps i'm too accustomed to php but it feels like with asp.net, major things are simple, and simple things are hard.  any help would be appreciated.  i'm trying to convert my career to c#.net

query - generate month, year (count) for blog archive


hi guys.

from my sql blogs table, I want to generate blog archive section on my master page. like;

january, 2010 (20)

fabruary, 2010 (35)

I cannot come up with a linq to sql query on this. do you have any idea how it can be done ?

Record Count on MERGE Statement on Insert,Update,Delete


HI All,

How can I get the numbers of records affected in the Merge statement, INSERT,UPDATE,DELETE  separately and store it in a variable so I can get it in the application side? 



khrizz tell

Same measure group for count and distinct count in SSAS


Hello there,

I need to make DistinctCount aggregation of few columns in the same fact table in SSAS. For each DistinctCount it creates a new measure group. I need to put all these under same measure group. Please let me know how to go ahead.



Sqldatasource record count



I have read a few threads on displaying the sqldatasource record count on a label, but not successfull yet.

I am working on Visual studio 2010 (asp.net 4.0 with vb) with sqlexpress for database.

I need to display a grid of record count results from a sql table. To start with i am trying with a simple sqldatasource control and a Label on the webpage.

Can anyone guide as to how can i get the desired result?


<%@ Page Language="VB" AutoEventWireup="false" CodeFile="SRreport.aspx.vb" Inherits="SR_2011_SRreport" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <style type="text/css">
            text-decoration: underline;
    <form id="form1" runat="server">
        <span class="style1"><strong>Record in DL-1</strong></span>:
        <asp:Label ID="Label1" runat="server" Text="Label"></asp:Label>
&nbsp;records<asp:SqlDataSource ID="SqlDataSource1" runat="server" 
            ConnectionString="<%$ ConnectionStrings:Ranjit WebConnectionString %>

GridView inline Master/Detail record display

This code drop is part of a smash and grab series. If you're in a rush, you can just grab this code and insert it into your application, no understanding is required. When you have some time (ya, right) you may want to review the source code.
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend