Distinct count in case of Custom Rollup formula in a dimension very Urgent.........

how we can calculate the dictinct customer ID in case
of custom rollup formula
BrhanCreate a separate cube for the distinct count measure, and remove any custom
rollup formula in cube editor.
Ohjoo Kwon
"Burhan" <burhanuddin.ahmed@.gonetbpo.com> wrote in message
news:%233m4VeSMFHA.436@.TK2MSFTNGP09.phx.gbl...
> how we can calculate the dictinct customer ID in case
> of custom rollup formula
>
> Brhan
>
>

Displaying selected rows from a Fact table

I have a fact table which stores data ( customer name, document type, editing start time, editing end time, editor, revision id etc) for each revision of a document.

While displaying data however i need to take into account only the last revision of each document.

What is the best way of doing this? Do I need to create a separate dimension table with the document id and max revision id as fields or is there a better way of doing it?

One idea would be to mark Revision dimension as of type Time, and use semiadditive measure LastNonEmptyChild - this will show data for the last revision only.|||

I also need to create calculated members based on the lastnonemptychild. How do I do that?

Eg: for last nonemptychild ie. last revision I need to count the number of records that are of type 'S'

I also need to calculate percentage of records of last revision that are greater than target time and less than target time.....

|||This is very easy to do. Assuming you have attribute called RecordType, you can create calculated measure with|||

In the previous post you mentioned mark Revision dimension of type time. How do I do tht?

Does this also mean tht I should hv a separate dimension for revisions with attributes being documentid and revid and the hierarchy being documentid -> revid ? For the lastnonemptychild aggregation to work? That would mean tht the dimension table would contain as many records as the fact table isnt it?

|||

You don't need to change anything about your revision dimension. I imagine, that it has key attribute having values of 1,2,3,... up to whatever largest revision you think you will have in few years. I don't see the reason to include document id into this dimension - different documents can have same revision - there is no problem with it.

In the dimension editor, simply go to the properties of dimension, and choose the value Time for the property Type.

|||After changing revision dimension's property type to time, how do I use the semiadditive measure last child to sum only the records with the last revision id for the Measure InTAT ( where InTAT is either 1 or 0) ?|||You need to change Aggregation Function for this measure from Sum to LastNonEmptyChild.|||For a calculated measure how do I use the LastNonempty measure and get the sum of records with last revid?|||It is not a calculated measure. It is a real measure. Marking it as LastNonEmpty will cause returning sum of records with last revid.|||I have some calculated measures called TAT Factor, Half TAT etc for which too I need to be able to sum on the lastrevid. How do I do that?|||Make them a real measures, and move whatever expressions you use for them to the Leaves(Revision) inside MDX Script.|||I am new to analysis services. Could you explain what you mean by moving the expressions to the leaves? Should I make calculated columns in the view?

Displaying selected rows from a Fact table

I have a fact table which stores data ( customer name, document type, editing start time, editing end time, editor, revision id etc) for each revision of a document.

While displaying data however i need to take into account only the last revision of each document.

What is the best way of doing this? Do I need to create a separate dimension table with the document id and max revision id as fields or is there a better way of doing it?

One idea would be to mark Revision dimension as of type Time, and use semiadditive measure LastNonEmptyChild - this will show data for the last revision only.|||

I also need to create calculated members based on the lastnonemptychild. How do I do that?

Eg: for last nonemptychild ie. last revision I need to count the number of records that are of type 'S'

I also need to calculate percentage of records of last revision that are greater than target time and less than target time.....

|||This is very easy to do. Assuming you have attribute called RecordType, you can create calculated measure with|||

In the previous post you mentioned mark Revision dimension of type time. How do I do tht?

Does this also mean tht I should hv a separate dimension for revisions with attributes being documentid and revid and the hierarchy being documentid -> revid ? For the lastnonemptychild aggregation to work? That would mean tht the dimension table would contain as many records as the fact table isnt it?

|||

You don't need to change anything about your revision dimension. I imagine, that it has key attribute having values of 1,2,3,... up to whatever largest revision you think you will have in few years. I don't see the reason to include document id into this dimension - different documents can have same revision - there is no problem with it.

In the dimension editor, simply go to the properties of dimension, and choose the value Time for the property Type.

|||After changing revision dimension's property type to time, how do I use the semiadditive measure last child to sum only the records with the last revision id for the Measure InTAT ( where InTAT is either 1 or 0) ?|||You need to change Aggregation Function for this measure from Sum to LastNonEmptyChild.|||For a calculated measure how do I use the LastNonempty measure and get the sum of records with last revid?|||It is not a calculated measure. It is a real measure. Marking it as LastNonEmpty will cause returning sum of records with last revid.|||I have some calculated measures called TAT Factor, Half TAT etc for which too I need to be able to sum on the lastrevid. How do I do that?|||Make them a real measures, and move whatever expressions you use for them to the Leaves(Revision) inside MDX Script.|||I am new to analysis services. Could you explain what you mean by moving the expressions to the leaves? Should I make calculated columns in the view?

Displaying Picture In Crystal Report

I want to display picture of customers in crystal reports.

For that purpose I have created table customer.

When new custiomer comes to me I take their picture using PC camera and save that picture in one of the folders.

At the same time I am sending path of that picture to database.

Now when I display record of customer in Crystal report. I want to diaplay their pictures tooo.

Can you help me??Place the OLE Object -> Bitmap Image on the report and right click the OLE Object ->Format Graphics -> Picture Tab and Graphic Location enter the path which is stored in the database in the formula. This will show the image in runtime.

Keep this in mind this functionality is available after Crystal Reports 10.

In the earlier versions you have to store the image as BLOB in the database and then show that field on the report.

Displaying customer data

Hi all,
I need to design a solution that will display customer data from Sql
Server tables in an Internet Explorer tab of a third party app, e.g. the
third party app has a tab that can display web pages.
The third party app display one customer record at a time. I need to
display Payment Transaction records for the current customer(linked by
CustomerNo field) in the Internet Explorer tab. There are six types of
Payment Transaction records. I need to display all Payment transaction
records for the current customer, the subtotal for each transaction type and
the total ampont for all transactions.
What are the best tools for implementing this solution? Shoule I use
DbNetGrid, Reporting Services, Crystal Reports or any other tool? Please
also point me to any examples on the web or any tutorial.
Thanks in advance.Hi,
Take a look at this page
http://office.microsoft.com/en-gb/a...1153831033.aspx
"helpful sql" <nospam@.stopspam.com> wrote in message
news:uVMSYfaXGHA.3972@.TK2MSFTNGP04.phx.gbl...
> Hi all,
> I need to design a solution that will display customer data from Sql
> Server tables in an Internet Explorer tab of a third party app, e.g. the
> third party app has a tab that can display web pages.
> The third party app display one customer record at a time. I need to
> display Payment Transaction records for the current customer(linked by
> CustomerNo field) in the Internet Explorer tab. There are six types of
> Payment Transaction records. I need to display all Payment transaction
> records for the current customer, the subtotal for each transaction type
> and the total ampont for all transactions.
> What are the best tools for implementing this solution? Shoule I use
> DbNetGrid, Reporting Services, Crystal Reports or any other tool? Please
> also point me to any examples on the web or any tutorial.
> Thanks in advance.
>

Displaying customer data

Hi all,
I need to design a solution that will display customer data from Sql
Server tables in an Internet Explorer tab of a third party app, e.g. the
third party app has a tab that can display web pages.
The third party app display one customer record at a time. I need to
display Payment Transaction records for the current customer(linked by
CustomerNo field) in the Internet Explorer tab. There are six types of
Payment Transaction records. I need to display all Payment transaction
records for the current customer, the subtotal for each transaction type and
the total ampont for all transactions.
What are the best tools for implementing this solution? Shoule I use
DbNetGrid, Reporting Services, Crystal Reports or any other tool? Please
also point me to any examples on the web or any tutorial.
Thanks in advance.Hi,
Take a look at this page
http://office.microsoft.com/en-gb/assistance/ha011153831033.aspx
"helpful sql" <nospam@.stopspam.com> wrote in message
news:uVMSYfaXGHA.3972@.TK2MSFTNGP04.phx.gbl...
> Hi all,
> I need to design a solution that will display customer data from Sql
> Server tables in an Internet Explorer tab of a third party app, e.g. the
> third party app has a tab that can display web pages.
> The third party app display one customer record at a time. I need to
> display Payment Transaction records for the current customer(linked by
> CustomerNo field) in the Internet Explorer tab. There are six types of
> Payment Transaction records. I need to display all Payment transaction
> records for the current customer, the subtotal for each transaction type
> and the total ampont for all transactions.
> What are the best tools for implementing this solution? Shoule I use
> DbNetGrid, Reporting Services, Crystal Reports or any other tool? Please
> also point me to any examples on the web or any tutorial.
> Thanks in advance.
>

Displaying customer data

Hi all,
I need to design a solution that will display customer data from Sql
Server tables in an Internet Explorer tab of a third party app, e.g. the
third party app has a tab that can display web pages.
The third party app display one customer record at a time. I need to
display Payment Transaction records for the current customer(linked by
CustomerNo field) in the Internet Explorer tab. There are six types of
Payment Transaction records. I need to display all Payment transaction
records for the current customer, the subtotal for each transaction type and
the total ampont for all transactions.
What are the best tools for implementing this solution? Shoule I use
DbNetGrid, Reporting Services, Crystal Reports or any other tool? Please
also point me to any examples on the web or any tutorial.
Thanks in advance.Hi,
Take a look at this page
http://office.microsoft.com/en-gb/a...1153831033.aspx
"helpful sql" <nospam@.stopspam.com> wrote in message
news:uVMSYfaXGHA.3972@.TK2MSFTNGP04.phx.gbl...
> Hi all,
> I need to design a solution that will display customer data from Sql
> Server tables in an Internet Explorer tab of a third party app, e.g. the
> third party app has a tab that can display web pages.
> The third party app display one customer record at a time. I need to
> display Payment Transaction records for the current customer(linked by
> CustomerNo field) in the Internet Explorer tab. There are six types of
> Payment Transaction records. I need to display all Payment transaction
> records for the current customer, the subtotal for each transaction type
> and the total ampont for all transactions.
> What are the best tools for implementing this solution? Shoule I use
> DbNetGrid, Reporting Services, Crystal Reports or any other tool? Please
> also point me to any examples on the web or any tutorial.
> Thanks in advance.
>

Displaying customer data

Hi all,
I need to design a solution that will display customer data from Sql
Server tables in an Internet Explorer tab of a third party app, e.g. the
third party app has a tab that can display web pages.
The third party app display one customer record at a time. I need to
display Payment Transaction records for the current customer(linked by
CustomerNo field) in the Internet Explorer tab. There are six types of
Payment Transaction records. I need to display all Payment transaction
records for the current customer, the subtotal for each transaction type and
the total ampont for all transactions.
What are the best tools for implementing this solution? Shoule I use
DbNetGrid, Reporting Services, Crystal Reports or any other tool? Please
also point me to any examples on the web or any tutorial.
Thanks in advance.Hi,
Take a look at this page
http://office.microsoft.com/en-gb/assistance/ha011153831033.aspx
"helpful sql" <nospam@.stopspam.com> wrote in message
news:uVMSYfaXGHA.3972@.TK2MSFTNGP04.phx.gbl...
> Hi all,
> I need to design a solution that will display customer data from Sql
> Server tables in an Internet Explorer tab of a third party app, e.g. the
> third party app has a tab that can display web pages.
> The third party app display one customer record at a time. I need to
> display Payment Transaction records for the current customer(linked by
> CustomerNo field) in the Internet Explorer tab. There are six types of
> Payment Transaction records. I need to display all Payment transaction
> records for the current customer, the subtotal for each transaction type
> and the total ampont for all transactions.
> What are the best tools for implementing this solution? Shoule I use
> DbNetGrid, Reporting Services, Crystal Reports or any other tool? Please
> also point me to any examples on the web or any tutorial.
> Thanks in advance.
>

display values depending on a rule

hello,
i have a table with 3 fields
CustNr (int)
artikleNr (int)
pieces (int)
simple example - all customer have bueyed the article with the nr 11
101 11 8
102 11 3
101 11 4
102 11 20
103 11 3
104 11 15
104 11 25
i want to display a information in the following way
if customer has < 10 pieces display 0
if customer has 10 -20 display real value 1...20
if customer has more then 20 display 20
101 8 + 4 =12 display 12
102 3 + 20=23 display 20
103 3 display 0
104 15+25=40 display 20
thanksSomething like this?
SELECT CustNr,
'SomeColumn' = CASE WHEN SUM(pieces) < 10 THEN 0
WHEN SUM(pieces) BETWEEN 10 AND 20 THEN SUM(pieces)
WHEN SUM(pieces) > 20 THEN 20
ELSE NULL END
FROM YourTable
WHERE artikleNr = 11 /* I don't know if this is a param that would limit
the data returned, or if you want to group by this column as well (in
addition to CustNr) */
Keith Kratochvil
"Xavier" <Xavier@.discussions.microsoft.com> wrote in message
news:6A10DB32-0D1E-4C48-B657-C5C83A78DDF7@.microsoft.com...
> hello,
> i have a table with 3 fields
> CustNr (int)
> artikleNr (int)
> pieces (int)
> simple example - all customer have bueyed the article with the nr 11
> 101 11 8
> 102 11 3
> 101 11 4
> 102 11 20
> 103 11 3
> 104 11 15
> 104 11 25
> i want to display a information in the following way
> if customer has < 10 pieces display 0
> if customer has 10 -20 display real value 1...20
> if customer has more then 20 display 20
>
> 101 8 + 4 =12 display 12
> 102 3 + 20=23 display 20
> 103 3 display 0
> 104 15+25=40 display 20
> thanks|||thanks Keith, it works perfect.
best regards
"Keith Kratochvil" wrote:

> Something like this?
> SELECT CustNr,
> 'SomeColumn' = CASE WHEN SUM(pieces) < 10 THEN 0
> WHEN SUM(pieces) BETWEEN 10 AND 20 THEN SUM(pieces)
> WHEN SUM(pieces) > 20 THEN 20
> ELSE NULL END
> FROM YourTable
> WHERE artikleNr = 11 /* I don't know if this is a param that would limit
> the data returned, or if you want to group by this column as well (in
> addition to CustNr) */
> --
> Keith Kratochvil
>
> "Xavier" <Xavier@.discussions.microsoft.com> wrote in message
> news:6A10DB32-0D1E-4C48-B657-C5C83A78DDF7@.microsoft.com...
>
>

Display null values

Hello,

I'm facing a problem in my reporting.

I have a Customer table where is record various events like CustomerEventId, DateTime, StatusId, StatusTime, GroupId, ...

I also have a status table (Id, Description) and a group table (Id, Description).

I want to create a report where for a selected date range (From ... To ...) i can see (grouped by date) all status's the customer

went in. The possible status are :

Id Description

-

1 status 1

2 status 2

3 status 3

4 status 4

My query looks something like this :

SELECT CustomerEventId, DateTime, CONVERT(varchar, DateTime), 103) AS DATEVAL, StatusId,

status.description as StatusDescription, StatusTime, GroupId, group.Description as GroupDescription

From Customers inner join status on customers.StatusId = status.id

inner join group on customers.GroupId = group.id

Group By CustomerEventId, DateTime, StatusId, status.description, StatusTime, GroupId, group.Description

My reports has 3 parameters (From date, To date, Group)

In my report i have a table with two groups : GroupByDate (grouped on DATEVAL) and GroupByStatus

now my problem : let's say i have values for statusid 1,2 and 4

then my report will only display those 3 status.

How can i display the status where there is no data for :

now it shows :

DATEVAL Occurrences Time

01/07/2007

Status 1 15 125

Status 2 25 366

Status 4 8 66

I would like it to show:

DATEVAL Occurrences Time

01/07/2007

Status 1 15 125

Status 2 25 366

Status 3 0 0

Status 4 8 66

Anybody (i hope i have provide enough details ...)

Vinnie

Hello Vinnie,

You're going to need to modify your dataset to return those records (in your example data, "Status 3") with NULL for the rest of the values. Then you can replace the NULL's with 0, either in your SQL query or in the table.

Use an outer join to get all the status records back whether or not there are matching customer records. Something like this:

SELECT CustomerEventId, DateTime, CONVERT(varchar, DateTime), 103) AS DATEVAL, StatusId,

status.description as StatusDescription, StatusTime, GroupId, group.Description as GroupDescription

From Customers

inner join group on customers.GroupId = group.id

right outer join status on customers.StatusId = status.id

Group By CustomerEventId, DateTime, StatusId, status.description, StatusTime, GroupId, group.Description

Hope this helps.

Jarret

Display hirearchy data using data region

I am trying to display hirearchical data using data region. For eg. the first level will be a list of customer names. The second level will be a list of Invoices under each customer. The third level will be a list of product names that were sold under each invoice.

I used a list for the data region and in each hireachy, there is a textbox in each list to display the data. I am trying to associate each data region with a datatable (I will provide the logic to populate the datatable with appropiate data). I do not want the ReportViewer to interact directly with a database because this is a 3 tier design and I am using the ReportViewer in local mode. I couldn't find any example of these. Can anybody help? Also I would like to see an example of the .rdlc file in these situation. Thanks very much.

Kam

Take a look at this article and post any follow-up questions to this post.|||

Hi Teo,

What my situation in is that I have a nested list data region. Specifically I have two list data region, one stack on top of each other. The list data region on the bottom has a textbox and another list data region. The textbox will display a list of customer name. The list data region on top also has a textbox. This textbox will display a list of invoice number with respect to each individual customer.

I read that not only can list data region be nested, each can associate with its own data table. So in my code I have two data table, the first one contain a list of customer name and its corresponding customer ID. The second one contain a list of invoice number and its corresponding customer ID that relates to the customer ID of the first table. I have trouble making this work because for some reason the textbox on the top list data region refuses to accept fields in the invoice data table. It only accepts fields in the customer data table.

I have seen example of nested list data region but it uses SQL statement and talks directly to a database. I am using this in local mode in a 3 tier situation so I have no access to a database.

Thanks very much on your last reply and I am looking forward on this one.

Thanks. Kam

|||

Take a look at the Sales Order sample report which ships with Reporting Services. Yes, data regions can be nested and each one can be bound to its own dataset.

Currently, SSRS doesn't support joining datasets at the report level. However, if all datasets will filter on a single customer you can synchronize them by using a customer query parameter. The Sales Order report demonstrates this technique although the list and table regions are not nested (they don't have to be because the datasets are pre-filtered already). An outer list can only reference the first or last record from an inner dataset as you can see by dragging a field onto it. That's because the inner list may have many records, e.g.; one customer has many invoices.

The report will work the same way in both remote and local modes. The only difference in local mode is that you have to bind the three application datasets to the report. The article I mentioned in my first reply demonstrates how dataset binding works in local mode.

|||Thanks for the tip. I haven't have a chance to look into it yet. I will let you know when I come back to this problem. Thanks.