Calculated member - Count function

Hello
I have te following problem:
I have made a cube with quotations. But a quotation consist of one or more
lines. On each line a product is selected (with price, amount, etc.).
The records in my fact table are quotationlines (not quotation-headers),
because the producthierarchy is linked to the articles (in quotationlines).
I would like to count the quotations (not the lines).
The lines linked to a certain quotation have the same document number.
How can I count the number of quotations, e.g. by using the function
calculated member or...? I have tried something but without a good result.
Can you help me?
Thanks in advance
HansIs the relatoinship between the document number and quotation is one to one?
If then, it's possible to aggregate the document number with aggregation
function of "distinct count". You can set the property of measure in Cube
Editor.
Ohjoo Kwon
"Hans" <Hans@.discussions.microsoft.com> wrote in message
news:C28A061E-1975-47A9-8DE2-7489FDED0C3B@.microsoft.com...
> Hello
> I have te following problem:
> I have made a cube with quotations. But a quotation consist of one or more
> lines. On each line a product is selected (with price, amount, etc.).
> The records in my fact table are quotationlines (not quotation-headers),
> because the producthierarchy is linked to the articles (in
quotationlines).
> I would like to count the quotations (not the lines).
> The lines linked to a certain quotation have the same document number.
> How can I count the number of quotations, e.g. by using the function
> calculated member or...? I have tried something but without a good result.
> Can you help me?
> Thanks in advance
> Hans|||Thank you,
But I still have a problem. At the Aggregate Function the option 'count
distinct only appears when the source field is an Numeric field type. The
documentnumber I would like to count (distinct count) is a Text-field (e.g.
VF05001234).
Do you have any ideas to solve this problem?
Or is the problem not the field type, but something else I don't know?
Hans
"Ohjoo Kwon" wrote:

> Is the relatoinship between the document number and quotation is one to on
e?
> If then, it's possible to aggregate the document number with aggregation
> function of "distinct count". You can set the property of measure in Cube
> Editor.
> Ohjoo Kwon
>
> "Hans" <Hans@.discussions.microsoft.com> wrote in message
> news:C28A061E-1975-47A9-8DE2-7489FDED0C3B@.microsoft.com...
> quotationlines).
>
>|||Yes, you are right. It must be numeric.
I recommend you use surrogate key mapped to document number.
Ohjoo Kwon
"hans.de.korte@.verder.nl"
<hans.de.korte@.verder.nl@.discussions.microsoft.com> wrote in message
news:BB9AA35C-6772-4026-B656-BCC31FF21533@.microsoft.com...
> Thank you,
> But I still have a problem. At the Aggregate Function the option 'count
> distinct only appears when the source field is an Numeric field type. The
> documentnumber I would like to count (distinct count) is a Text-field
(e.g.[vbcol=seagreen]
> VF05001234).
> Do you have any ideas to solve this problem?
> Or is the problem not the field type, but something else I don't know?
> Hans
> "Ohjoo Kwon" wrote:
>
one?[vbcol=seagreen]
Cube[vbcol=seagreen]
more[vbcol=seagreen]
quotation-headers),[vbcol=seagreen]
result.[vbcol=seagreen]|||Thank you!!
"Ohjoo Kwon" wrote:

> Yes, you are right. It must be numeric.
> I recommend you use surrogate key mapped to document number.
> Ohjoo Kwon
> "hans.de.korte@.verder.nl"
> <hans.de.korte@.verder.nl@.discussions.microsoft.com> wrote in message
> news:BB9AA35C-6772-4026-B656-BCC31FF21533@.microsoft.com...
> (e.g.
> one?
> Cube
> more
> quotation-headers),
> result.
>
>

Calculate Unit % by Category

I have a report that is grouped by Category and on each detail line I need to show the % Units Sold per Category. I can't figure out how to get the total Units Sold per Category before I write the individual lines. Can someone, please, walk me through this process?

Thank you very much!!!

ChrisRight click on the filed select summary and summary type as sum and Check the button "show as Percentage" and select again the filed

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.

c# UI Escape character problem to execute command line

Hi!

Thanks For your reply!

but this is very urgent please help!!!!!!!

I need one more help in this issue. what if i do not need any \ "

for e.g: i am trying to set conn string and varaible value

jobCommand = new SqlCommand("xp_cmdshell 'dtexec /f \"" + path + "\" /Conn \"" + Packconn + "\" \"" + connect + "\" '",cconn);

i am getting some value like this :

CommandText "xp_cmdshell 'dtexec /f \"D:\\SSISProject\\Integration Services Project1\\ArchiveMainMultiTables.dtsx\" /Conn \"SE413695\\AASQL2005.TestDB;\" \"Provider=SQLNCLI.1;Data Source=SE413695\\AASQL2005;Initial Catalog=TestDB;Provider=SQLNCLI.1;Integrated Security=SSPI;\" '" string

I do not need the highlighted escape characters in it. what should i do?

i need some thing like this :

xp_cmdshell 'dtexec /f "D:\SSISProject\Integration Services Project1\ArchiveMainMultiTables.dtsx" /Conn SE413695\AASQL2005.TestDB;"Provider=SQLNCLI.1;Data Source=SE413695\AASQL2005;Initial Catalog=TestDB;Provider=SQLNCLI.1;Integrated Security=SSPI;"'

Thanks,

Jas

C# has support for unescaped strings. Anything you put in them will appear as is. The only thing to escape is the " and you do that by using two ("")

You must append an @. to the front of the string to tell the compiler that is is unescaped.

So your code would look like this:

jobCommand = new SqlCommand(@."xp_cmdshell 'dtexec /f """ + path + """ /Conn """ + Packconn + """ """ + connect + """ '",cconn);
|||

hi!

Need some urgent help

I am not using @. symbol cos i need to use \ character.

path = @."D:\SSISProject\Integration Services Project1\ArchiveMainMultiTables.dtsx";

string conn = @."Data Source=SE413695\AASQL2005;Initial Catalog=TestDB;Provider=SQLNCLI.1;Integrated Security=SSPI;";

jobCommand = new SqlCommand("xp_cmdshell 'dtexec /f \"" + path + "\" /Set \\package.Variables[User::connectst].Properties[Value]; \"" + conn + "\"'", cconn);

Path works correctly with ur previous answer but the conn as some thing liek this

Which is not working correclty i am getting this value

xp_cmdshell 'dtexec /f \"D:\\SSISProject\\Integration Services Project1\\ArchiveMainMultiTables.dtsx\"/Set \package.Variables[User::connectst].Properties[Value];\"Data Source=SE413695\\AASQL2005;Initial Catalog=TestDB;Provider=SQLNCLI.1;Integrated Security=SSPI;\"'

error near spaces when i execute SQL

Argument "\package.Variables[User::connectst].Properties[Value];"Data "Source=SE413695\\AASQL2005;Initial" "Catalog=TestDB;Provider=SQLNCLI.1;Integrated" Security=SSPI;"" for option "set" is not valid.

I need to get some thing like this through c#

xp_cmdshell 'dtexec /f \"D:\\SSISProject\\Integration Services Project1\\ArchiveMainMultiTables.dtsx\"/Set \package.Variables[User::connectst].Properties[Value];\""Data Source=SE413695\\AASQL2005;Initial Catalog=TestDB;Provider=SQLNCLI.1;Integrated Security=SSPI;"\"'

Please help me with this

thanks,

Jasmine

C# : failed due to the following error: 80040154

When i execute the following code provided by msdn, it shows error on the line Application app = newApplication().

But i got some comments that it works fine in 64-bit machine. Could you please help me out. Thanks in advance.

Executed Code

using System;

using System.Collections.Generic;

using System.Text;

using Microsoft.SqlServer.Dts.Runtime;

namespace Connections_Collection

{

classProgram

{

staticvoid Main(string[] args)

{

// The package is one of the SSIS Samples.

string mySample = @."C:\Program Files\Microsoft SQL Server\90\Samples\Integration Services\Package Samples\CalculatedColumns Sample\CalculatedColumns\CalculatedColumns.dtsx";

// Create an application and load the sample.

Application app = newApplication();

Package pkg = app.LoadPackage(mySample, null);

Connections myConns = pkg.Connections;

int connCount = myConns.Count;

Console.WriteLine("The number of connections in the package is: {0}", connCount);

// Enumerate over the collection, printing out

// the values for various properties.

foreach (ConnectionManager connMgr in myConns)

{

Console.WriteLine("ConnectionString: {0}", connMgr.ConnectionString);

Console.WriteLine("CreationName: {0}", connMgr.CreationName);

Console.WriteLine("DelayValidation: {0}", connMgr.DelayValidation);

Console.WriteLine("Description: {0}", connMgr.Description);

Console.WriteLine("HostType: {0}", connMgr.HostType);

Console.WriteLine("ID: {0}", connMgr.ID);

Console.WriteLine("InnerObject: {0}", connMgr.InnerObject);

Console.WriteLine("Name: {0}", connMgr.Name);

Console.WriteLine("ProtectionLevel: {0}", connMgr.ProtectionLevel);

Console.WriteLine("SupportsDTCTransactions: {0}", connMgr.SupportsDTCTransactions);

}

Console.WriteLine("");

}

}

}

Error message

Retrieving the COM class factory for component with CLSID {E44847F1-FD8C-4251-B5DA-B04BB22E236E} failed due to the following error: 80040154.

Thanks,
Kalam

I am also facing the same problem. Can anybody help on this?

the Code works fine in a 32 bit machine but shows this on a 64 bit machine.

Any idea?

Thanks in advance

|||Try to upload the DTS from a 32 bit machine onto the 64 bit and probably it should work...|||Could you elaborate on this solution please ("upload the DTS")? What is involved with uploading DTS onto Windows Vista Business 64-bit? I also get the same error message for a program running in 64-bit mode that is trying to call a 32-bit DLL. Thanks.

C# : failed due to the following error: 80040154

When i execute the following code provided by msdn, it shows error on the line Application app = new Application().

But i got some comments that it works fine in 64-bit machine. Could you please help me out. Thanks in advance.

Executed Code

using System;

using System.Collections.Generic;

using System.Text;

using Microsoft.SqlServer.Dts.Runtime;

namespace Connections_Collection

{

class Program

{

static void Main(string[] args)

{

// The package is one of the SSIS Samples.

string mySample = @."C:\Program Files\Microsoft SQL Server\90\Samples\Integration Services\Package Samples\CalculatedColumns Sample\CalculatedColumns\CalculatedColumns.dtsx";

// Create an application and load the sample.

Application app = new Application();

Package pkg = app.LoadPackage(mySample, null);

Connections myConns = pkg.Connections;

int connCount = myConns.Count;

Console.WriteLine("The number of connections in the package is: {0}", connCount);

// Enumerate over the collection, printing out

// the values for various properties.

foreach (ConnectionManager connMgr in myConns)

{

Console.WriteLine("ConnectionString: {0}", connMgr.ConnectionString);

Console.WriteLine("CreationName: {0}", connMgr.CreationName);

Console.WriteLine("DelayValidation: {0}", connMgr.DelayValidation);

Console.WriteLine("Description: {0}", connMgr.Description);

Console.WriteLine("HostType: {0}", connMgr.HostType);

Console.WriteLine("ID: {0}", connMgr.ID);

Console.WriteLine("InnerObject: {0}", connMgr.InnerObject);

Console.WriteLine("Name: {0}", connMgr.Name);

Console.WriteLine("ProtectionLevel: {0}", connMgr.ProtectionLevel);

Console.WriteLine("SupportsDTCTransactions: {0}", connMgr.SupportsDTCTransactions);

}

Console.WriteLine("");

}

}

}

Error message

Retrieving the COM class factory for component with CLSID {E44847F1-FD8C-4251-B5DA-B04BB22E236E} failed due to the following error: 80040154.

Thanks,
Kalam

I am also facing the same problem. Can anybody help on this?

the Code works fine in a 32 bit machine but shows this on a 64 bit machine.

Any idea?

Thanks in advance

|||Try to upload the DTS from a 32 bit machine onto the 64 bit and probably it should work...|||Could you elaborate on this solution please ("upload the DTS")? What is involved with uploading DTS onto Windows Vista Business 64-bit? I also get the same error message for a program running in 64-bit mode that is trying to call a 32-bit DLL. Thanks.

C# : failed due to the following error: 80040154

When i execute the following code provided by msdn, it shows error on the line Application app = new Application().

But i got some comments that it works fine in 64-bit machine. Could you please help me out. Thanks in advance.

Executed Code

using System;

using System.Collections.Generic;

using System.Text;

using Microsoft.SqlServer.Dts.Runtime;

namespace Connections_Collection

{

class Program

{

static void Main(string[] args)

{

// The package is one of the SSIS Samples.

string mySample = @."C:\Program Files\Microsoft SQL Server\90\Samples\Integration Services\Package Samples\CalculatedColumns Sample\CalculatedColumns\CalculatedColumns.dtsx";

// Create an application and load the sample.

Application app = new Application();

Package pkg = app.LoadPackage(mySample, null);

Connections myConns = pkg.Connections;

int connCount = myConns.Count;

Console.WriteLine("The number of connections in the package is: {0}", connCount);

// Enumerate over the collection, printing out

// the values for various properties.

foreach (ConnectionManager connMgr in myConns)

{

Console.WriteLine("ConnectionString: {0}", connMgr.ConnectionString);

Console.WriteLine("CreationName: {0}", connMgr.CreationName);

Console.WriteLine("DelayValidation: {0}", connMgr.DelayValidation);

Console.WriteLine("Description: {0}", connMgr.Description);

Console.WriteLine("HostType: {0}", connMgr.HostType);

Console.WriteLine("ID: {0}", connMgr.ID);

Console.WriteLine("InnerObject: {0}", connMgr.InnerObject);

Console.WriteLine("Name: {0}", connMgr.Name);

Console.WriteLine("ProtectionLevel: {0}", connMgr.ProtectionLevel);

Console.WriteLine("SupportsDTCTransactions: {0}", connMgr.SupportsDTCTransactions);

}

Console.WriteLine("");

}

}

}

Error message

Retrieving the COM class factory for component with CLSID {E44847F1-FD8C-4251-B5DA-B04BB22E236E} failed due to the following error: 80040154.

Thanks,
Kalam

I am also facing the same problem. Can anybody help on this?

the Code works fine in a 32 bit machine but shows this on a 64 bit machine.

Any idea?

Thanks in advance

|||Try to upload the DTS from a 32 bit machine onto the 64 bit and probably it should work...

Bullet Points in Textbox

I have some bullet-pointed text I need to add to a text box such as
* Line 1
* Line 2
* Line 3
I see the formatting toolbar has a bullet point icon, but it is always
disabled for me. How can I format text in a textbox so that it is bullet
pointed?
Thanks,
ChrisHi Chris,
Thank you for your posting.
You could not format the text with bullet point in the reporting services
design time directly.
One workaround is you type the text in the word document and then copy
paste the text into the textbox in reporting services.
Hope this will be helpful! Thank you!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.

BulkLoad Line Breaks

Hey,
Does anyone know if there is a way around XML Bulkload removing line breaks
that should be imported from xml to sql?
Thanks.
cd
Where are the linebreaks? The XML parsers have some specific rules about
line breaks that may remove them (inside attributes) or normalize them to
linefeed. If you want to not have this happen, you need to entitize them
(e.g., use for linefeed etc.)
Best regards
Michael
<CDONNICK@.wi.rr.com> wrote in message
news:mBSnd.164$%76.114@.twister.rdc-kc.rr.com...
> Hey,
> Does anyone know if there is a way around XML Bulkload removing line
> breaks that should be imported from xml to sql?
> Thanks.
> cd
>