Formula Error - Cannot find dimension member

When I run a MDX query for a specific date, where I donot have any data in the fact table, I get the error - "Formula Error - Cannot find dimension member "--" - in a name binding function.
Query 1:
select {[Measures].[BlockOrders]} ON COLUMNS
from USAGEDETAILS
WHERE [Trdate].[All Trdate].[2002].[July].[30]
This works fine, but if I run the following query, I get the above error message, because there is no data for July 1st, in the fact table.

select {[Measures].[BlockOrders]} ON COLUMNS
from USAGEDETAILS
WHERE [Trdate].[All Trdate].[2002].[July].[1]

Can some one help me?
Thanks,
Sophia.We have two tables Loans (Parent) & LoanIndicators (Child).

LoanIndicators stores the Loan status (ex Outstanding Amount) date wise. This table is updated with a new record only when the Outstanding Amount Changes , until that time the last record holds the status.

Ex :
Loan Table
Id LoanId LoanAmount
1 L1 1000
2 L2 2000

LoanIndicator Table

Id LoanId IndicatorDate Outstanding Amount
1 L1 1/1/2005 100
2 L1 1/13/2005 90

So in the above case if i want to know the Outstanding amount on 1/10/2005 of L1 i.e the record dated 1/1/2005 gives me the outstanding amount of 100.

To Create a Cube on this I create a View linking Loan & LoanIndicators i.e the view holds all the Parent + Child Records of each of the Loans.
Also the cube has a dimension on IndicatorDate.

Now in the Cube the Total AMount of Loan disbursed is 4000 but it shouold be 3000.
It is 4000 because it is summing L1 twice as the view has two records for L1.
1) How do we prevent this summing of L1 twice ?

2) If I want to know the Outstanding Amount on 1/10/2005 for L1 , how do i get it (It should be 100 as the position only changes on 1/13/2005).

TIA

Shuchi Agarwalsql

Formatting query/view in SQL Server Management Studio (2005)

This may be a stupid question but...
When designing a query or view, I write the SQL visually formatted the way I
like it. But when I run the query in Management Studio, the software
reformats the query into a mess (even though it still runs).
Is there an option or configuration which would allow me to keep the
formatting that I like?
Should I post this in another newsgroup?Yes, use a query window (Ctrl + N), not the query designer?
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:9563908A-1E74-4606-AEB8-7099BF9C6BB6@.microsoft.com...
> This may be a stupid question but...
> When designing a query or view, I write the SQL visually formatted the way
> I
> like it. But when I run the query in Management Studio, the software
> reformats the query into a mess (even though it still runs).
> Is there an option or configuration which would allow me to keep the
> formatting that I like?
> Should I post this in another newsgroup?

Formatting query/view in SQL Server Management Studio (2005)

This may be a stupid question but...
When designing a query or view, I write the SQL visually formatted the way I
like it. But when I run the query in Management Studio, the software
reformats the query into a mess (even though it still runs).
Is there an option or configuration which would allow me to keep the
formatting that I like?
Should I post this in another newsgroup?
Yes, use a query window (Ctrl + N), not the query designer?
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:9563908A-1E74-4606-AEB8-7099BF9C6BB6@.microsoft.com...
> This may be a stupid question but...
> When designing a query or view, I write the SQL visually formatted the way
> I
> like it. But when I run the query in Management Studio, the software
> reformats the query into a mess (even though it still runs).
> Is there an option or configuration which would allow me to keep the
> formatting that I like?
> Should I post this in another newsgroup?

Formatting query/view in SQL Server Management Studio (2005)

This may be a stupid question but...
When designing a query or view, I write the SQL visually formatted the way I
like it. But when I run the query in Management Studio, the software
reformats the query into a mess (even though it still runs).
Is there an option or configuration which would allow me to keep the
formatting that I like?
Should I post this in another newsgroup?Yes, use a query window (Ctrl + N), not the query designer?
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:9563908A-1E74-4606-AEB8-7099BF9C6BB6@.microsoft.com...
> This may be a stupid question but...
> When designing a query or view, I write the SQL visually formatted the way
> I
> like it. But when I run the query in Management Studio, the software
> reformats the query into a mess (even though it still runs).
> Is there an option or configuration which would allow me to keep the
> formatting that I like?
> Should I post this in another newsgroup?

Formatting Query Data

When I run queries against tables in MSSQL2000 it returns dates with a time component and has way too many zeros after the decimal point.

Is there any functions or something I can put in the query so my data comes back with only a date for the date and only two decimal points for the numbers?You are generally best off leaving the formatting to the code that receives the data from the database. But you can use the Convert function to format a date and the round function for numeric values.

SELECT Convert(varchar,DateField,101),Round(NumericField,2)
FROM YourTable

However, Round may not work quite as you expect. It will round the value but not necessarily format it for display as you might expect. Other date formats are available, check Sql Server Books OnLine under the convert function for the options.|||I agree with McMurdo, however, that the best place to handle this is outside of the database, in your code that is displaying the data.|||I agree, this is what I wanted - the ability to have the data formatted correctly from SQL. I tried for ages to find a way to do that on the basis of what I new about MySQL (which has a lot of functions for this).

But I'll look into it, as ideally I want my data coming in right from the SQL Query, rather than .Net doing it. Indeed, there is another thread in another forum arguing that as people kept giving me the whole Eval solution.|||Can you strip trailing spaces as well - I have a CUSTNMBR that is 5 characters long put it pads it with four spaces to to make it 9 (the length of the field).

I'll investigate the day, so I may have the answer this morning (UK Time).|||Use RTRIM or LTRIM to strip spaces.sql

Format for Parameters

Simple question - I have a date as a parameter. When I enter 12/1/2004 for
example and run the report, after the data is displayed, the parameter
displays "12/1/2004 12:00:00AM". Is there any way to pass the typical date
format like "MM/dd/yyyy" to the parameter so that it formats the date only?
thanks
billHi Bill,
Based on my knowledge, we are unable to format the
parameters. But I would like to perform further research
to confirm if this is possible. I will update you once I
have more information.
BTW: If you display the date in a text box in the Report
body, you can set the format via the text box Properties
dialog box.
Sincerely,
William Wang
Microsoft Online Partner 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.
--
>Reply-To: <sutphinwb@.nospam.nospam>
>From: <sutphinwb@.nospam.nospam>
>Subject: Format for Parameters
>Date: Wed, 2 Feb 2005 00:37:39 -0500
>Lines: 10
>X-Priority: 3
>X-MSMail-Priority: Normal
>X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
>X-RFC2646: Format=Flowed; Original
>Message-ID: <es9bQlOCFHA.1424@.TK2MSFTNGP09.phx.gbl>
>Newsgroups: microsoft.public.sqlserver.reportingsvcs
>NNTP-Posting-Host: ip24-250-32-203.ri.ri.cox.net
24.250.32.203
>Path:
cpmsftngxa10.phx.gbl!TK2MSFTFEED01.phx.gbl!TK2MSFTNGP08.p
hx.gbl!TK2MSFTNGP09.phx.gbl
>Xref: cpmsftngxa10.phx.gbl
microsoft.public.sqlserver.reportingsvcs:41614
>X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
>Simple question - I have a date as a parameter. When I
enter 12/1/2004 for
>example and run the report, after the data is
displayed, the parameter
>displays "12/1/2004 12:00:00AM". Is there any way to
pass the typical date
>format like "MM/dd/yyyy" to the parameter so that it
formats the date only?
>thanks
>bill
>
>|||William is correct to the best of my knowledge as well... If you need pretty
formatting either use a string, check that it is a valid date, and format it
as you wish... You could also use an HTML page as a front end to the report,
formating date strings etc using html techniques, then passing the params to
the URL... ( I prefer the simpler string method if possible.)
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
<sutphinwb@.nospam.nospam> wrote in message
news:es9bQlOCFHA.1424@.TK2MSFTNGP09.phx.gbl...
> Simple question - I have a date as a parameter. When I enter 12/1/2004
for
> example and run the report, after the data is displayed, the parameter
> displays "12/1/2004 12:00:00AM". Is there any way to pass the typical
date
> format like "MM/dd/yyyy" to the parameter so that it formats the date
only?
> thanks
> bill
>|||You can use a stored procedure to retrieve the data from the database. And
then you can format the parameter inside the stored procedure.
"Wayne Snyder" wrote:
> William is correct to the best of my knowledge as well... If you need pretty
> formatting either use a string, check that it is a valid date, and format it
> as you wish... You could also use an HTML page as a front end to the report,
> formating date strings etc using html techniques, then passing the params to
> the URL... ( I prefer the simpler string method if possible.)
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> <sutphinwb@.nospam.nospam> wrote in message
> news:es9bQlOCFHA.1424@.TK2MSFTNGP09.phx.gbl...
> > Simple question - I have a date as a parameter. When I enter 12/1/2004
> for
> > example and run the report, after the data is displayed, the parameter
> > displays "12/1/2004 12:00:00AM". Is there any way to pass the typical
> date
> > format like "MM/dd/yyyy" to the parameter so that it formats the date
> only?
> >
> > thanks
> >
> > bill
> >
> >
>
>