InvoiceNumber as a degenerated dimension in AS 2000?

Hello,

We have a couple of cubes in AS 2000 where the users would like to be able to search/view by #Invoicenumber and #POnumber. Instead of creating a separate Invoicedimension I would like to implement it as a 'degerated dimension' - just as a textfield in the facttable.

Some questions: Does AS2000 support this? Are there any special demands on the client tool to be able to support degenerated dimensions? At the moment InvoiceNumber is defined as varchar(16), is that OK?

Looking forward to your responses and advices ....

As far as I can recall this shouldnt be any problem.

Degenerate dimensions are handled just like any other dimension by ssas so there are no problems with client tools using that dimension.

BUT (there is a big but): Unless you are developing your own custom front end for this, the functionality you are looking for will be hard to implement in the front-ends i know of (proclarity, pivottables in excel) because there will typically be a humongous number of invoices. Im guessing that you want users to be able to punch in an invoice number and display invoice lines for that invoice, not show every invoice in a dropdown. My advice would be to look at reporting services to create this kind of report.

|||

I agree with Peter K that Reporting Services is a good platform for detailed low level analysis. On the other hand, if you are required to build this is in AS2000 you simply build a dimension from the invoice number in the fact table. The way you do it is by building fake levels in this dimension like the two first positions for the invoice number, followed by three and four and so on. You can use the TSQL Left-function to do this in the dimension editor for the key and name column.

Regards

Thomas

|||

Thanks Peter and Thomas,

Since we need to stick to AS2000 for the moment I have created a Invoicedimension. This dimension will be huge an flat but hopefully we could migrate to AS2005 within a year!

sql

Invoice numbering system

I am creating an invoicing structure in our database. Our system allows
multiple companies to be setup within the one database. Each company should
have seperate invoice number sequences. E.g.
Company One's last invoice number was 10000
Company Two generates a new invoice. They should be given either 10000 or a
number in a completely different range. But they should not be given 10001
(in company one's sequence).
Has anyone any suggestions on how to create a system for generating invoice
numbers. Two users should never be given the same invoice number (within
the same company). There should never be any gaps in invoice numbers (e.g.
Company One next invoice number after 10000 must be 10001 and then 10002
etc).
I need a guaranteed system that will generate an invoice number using the
rules above.
Thanks,
ChrisIf I was to generate my next number using something such as this...
INSERT INTO Invoices (InvoiceNo)
SELECT TOP 1 InvoiceNo + 1 FROM Invoices WHERE CompanySerialNo = 5 ORDER
BY InvoiceNo DESC
This approach seems to work, except for when I generate my first invoice
because the select clause returns nothing. So I tried this...
INSERT INTO Invoices (InvoiceNo)
SELECT TOP 1 InvoiceNo FROM (
SELECT InvoiceNo + 1 As InvoiceNo FROM Invoices WHERE CompanySerialNo
= 5
UNION
SELECT 1 AS InvoiceNo) SubQuery ORDER BY InvoiceNo DESC
This seems to work. Does this sound a good approach for generating invoice
numbers that are unique to a company? And does this guarantee two users
cannot be given the same number?
Thanks,
Chris
"Chris" <cw@.community.nospam> wrote in message
news:%23QF7DOqvFHA.3124@.TK2MSFTNGP12.phx.gbl...
>I am creating an invoicing structure in our database. Our system allows
>multiple companies to be setup within the one database. Each company
>should have seperate invoice number sequences. E.g.
> Company One's last invoice number was 10000
> Company Two generates a new invoice. They should be given either 10000 or
> a number in a completely different range. But they should not be given
> 10001 (in company one's sequence).
>
> Has anyone any suggestions on how to create a system for generating
> invoice numbers. Two users should never be given the same invoice number
> (within the same company). There should never be any gaps in invoice
> numbers (e.g. Company One next invoice number after 10000 must be 10001
> and then 10002 etc).
> I need a guaranteed system that will generate an invoice number using the
> rules above.
>
> Thanks,
> Chris
>|||put it on separate tables
--
thanks,
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787
"Chris" wrote:

> I am creating an invoicing structure in our database. Our system allows
> multiple companies to be setup within the one database. Each company shou
ld
> have seperate invoice number sequences. E.g.
> Company One's last invoice number was 10000
> Company Two generates a new invoice. They should be given either 10000 or
a
> number in a completely different range. But they should not be given 1000
1
> (in company one's sequence).
>
> Has anyone any suggestions on how to create a system for generating invoic
e
> numbers. Two users should never be given the same invoice number (within
> the same company). There should never be any gaps in invoice numbers (e.g
.
> Company One next invoice number after 10000 must be 10001 and then 10002
> etc).
> I need a guaranteed system that will generate an invoice number using the
> rules above.
>
> Thanks,
> Chris
>
>|||Chris ,
why dont u try describing the column as identity using newid function to
give to unque number everytime
"Chris" wrote:

> If I was to generate my next number using something such as this...
> INSERT INTO Invoices (InvoiceNo)
> SELECT TOP 1 InvoiceNo + 1 FROM Invoices WHERE CompanySerialNo = 5 ORDE
R
> BY InvoiceNo DESC
>
> This approach seems to work, except for when I generate my first invoice
> because the select clause returns nothing. So I tried this...
> INSERT INTO Invoices (InvoiceNo)
> SELECT TOP 1 InvoiceNo FROM (
> SELECT InvoiceNo + 1 As InvoiceNo FROM Invoices WHERE CompanySerial
No
> = 5
> UNION
> SELECT 1 AS InvoiceNo) SubQuery ORDER BY InvoiceNo DESC
>
> This seems to work. Does this sound a good approach for generating invoic
e
> numbers that are unique to a company? And does this guarantee two users
> cannot be given the same number?
> Thanks,
> Chris
>
> "Chris" <cw@.community.nospam> wrote in message
> news:%23QF7DOqvFHA.3124@.TK2MSFTNGP12.phx.gbl...
>
>|||On Wed, 21 Sep 2005 13:29:44 +0100, Chris wrote:

>If I was to generate my next number using something such as this...
>INSERT INTO Invoices (InvoiceNo)
> SELECT TOP 1 InvoiceNo + 1 FROM Invoices WHERE CompanySerialNo = 5 ORDER
>BY InvoiceNo DESC
>
>This approach seems to work, except for when I generate my first invoice
>because the select clause returns nothing. So I tried this...
>INSERT INTO Invoices (InvoiceNo)
> SELECT TOP 1 InvoiceNo FROM (
> SELECT InvoiceNo + 1 As InvoiceNo FROM Invoices WHERE CompanySerialN
o
>= 5
> UNION
> SELECT 1 AS InvoiceNo) SubQuery ORDER BY InvoiceNo DESC
>
>This seems to work. Does this sound a good approach for generating invoice
>numbers that are unique to a company? And does this guarantee two users
>cannot be given the same number?
Hi Chris,
This one is easier, and probably quicker as well:
INSERT INTO Invoices (InvoiceNo)
SELECT COALESCE(MAX(InvoiceNo),0) + 1
FROM Invoices
WHERE CompanySerialNo = 5
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks Hugo,
That's more readable!
Chris

> Hi Chris,
> This one is easier, and probably quicker as well:
> INSERT INTO Invoices (InvoiceNo)
> SELECT COALESCE(MAX(InvoiceNo),0) + 1
> FROM Invoices
> WHERE CompanySerialNo = 5
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)

Invicible Sub report for generating shared variables

Hi!

Im creating a report with a manual running total in a main report. As a condition to the running total, Im having a sub-report in the details section bringing a shared boolean variable back to the main report.

My problem is that I dont want the sub report to be visible. Have tried suppress it, but then the shared variable isnt brought to the main report correctly.

Please advice me!

Regards,
KarlSuppressing the report doesn't stop the execution of the report.
Show the code that you are using or screen shot.|||Suppress all sections of the subreport; the formulas etc. within it will still run. In the subreport's properties (right click it in the main report) choose suppress blank subreport. In the main report, choose suppress blank section.|||Hi!
Thanks for fast reply.

I've uploaded a screenshot of the report. When showing the sub-report, I'm getting the shared variables back. But if I suppress the section of the sub-report, the sub-report or un-select the can-grow flag, I'm not getting any share variable back (i.e. it's false).

Have anyone seen this behaviour before?

B.R
Karl|||If you suppress the subreport it doesn't run. That's why you suppress the sections within the subreport and suppress both a blank subreport and a blank section containing the subreport. It then still runs, but nothing is displayed.|||Yea, that works!
Thanks alot, you saved my day/week/job... :)

Regards,
Karl

Invalid Object Name Error

I'm trying to create a report model using a set of tables from two different servers. Creating the Data Sources and the Data Source View is no problem, however, while trying to create a Report model I run into an error that says,

An error occurred while executing a command.
Message: Invalid object name 'dbo.table_name.
Command:
SELECT COUNT(*) FROM [dbo].[table_name] t

I've checked the schemas for both these tables and they are correct. Why is this error occuring?

Any suggestions would be appreciated!

I'm not sure why the error occurs here but I have a found solution around the problem. You can create named queries that Union your tables from your different data sources into one query.sql

Invalid column name using BCP

Hi,

I am having trouble creating the data files. I received the error that there is an invalid column name 'Name' when using the BCP to create the files. How can I find out where this error is coming from?

Thanks

Quote:

Originally Posted by sarah21

Hi,

I am having trouble creating the data files. I received the error that there is an invalid column name 'Name' when using the BCP to create the files. How can I find out where this error is coming from?

Thanks

Sarah,

Name is a funny word in SQL Server as it is also a programming command and that is why you may be experiencing problems, try putting the column name in [] or change column name to be something more meaingfull like FullName.

Hope that helps

Invaild Object Name

Thanks in advance!

I am in the process of creating a means for users to access data via views using MS Excel. The views I have created "pull" from data in two different databases into a third. I also wanted to use the data as well in some web reporting that I do. The problem I ran into in the web app is that it giving me the following error.

[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'dbo.NVoltHSCLConsolProduction'

What did I miss in creating the views?

Thanks,
leeI assume NVoltHSCLConsolProduction is the name of your view? Can you access it from the QA and EM?

Try taking off the dbo in the reference.|||Yes, NVoltHSCLConsolProduction is the name of the view.

Yes, I can access it from both QA and EM as well as with MS Excel.

I use Dreamweaver UD for web apps and it's query building tool will access it as well.

I tried dropping the dbo, but did not help!|||I am assuming we have some kind of ownership \ permissions issue here. I am not familiar with Dreamweaver UD. What kind of web application is it? Is at ASP, PHP etc... If it is ASP I would check to see if the security context of the IIS web server (the IUSR_... account) has permissions to access the view.|||DW UD is for creating ASP. I have used views in the asp before. However they were views of data in that particular db.|||Sounds like you need to fully qualify your view with a three part name: Select thingamajig
From TheDatabase.dbo.NVoltHSCLConsolProduction|||The reason why you're not getting the error when using the view in EM and QA may be because of the default database setting for the account with which you registerd the server in EM and connected to in QA. Follow Max's advise.

Internet URL Access

I am creating a web appliction to access Reporting Service, but I got some
problem
1. User loges on web application via Forms Authentication
2. User types in report parameters.
3. Program gets all parameters, then call Reporting Server URL to get report.
It works fine with intranet, but it doesn't work with internet, because the
reporting service uses Windows Authentication. I don't want allow Anonymous
access Reporing service.
What should I do?Can you use the RS web service to render your reports? The user can log
into your app as normal, and in your code, you supply credentials (that will
authenticate) to the RS web service and return the report as PDF, etc.
Or you can set the RS up to use Forms Authentication and have your users go
directly there:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/ufairs.asp
--
Adrian M.
MCP
"pepcag" <pepcag@.discussions.microsoft.com> wrote in message
news:6F030717-3E37-40D2-B5D4-3AF49338DF27@.microsoft.com...
>I am creating a web appliction to access Reporting Service, but I got some
> problem
> 1. User loges on web application via Forms Authentication
> 2. User types in report parameters.
> 3. Program gets all parameters, then call Reporting Server URL to get
> report.
> It works fine with intranet, but it doesn't work with internet, because
> the
> reporting service uses Windows Authentication. I don't want allow
> Anonymous
> access Reporing service.
> What should I do?