Anoying bug...

Hello,
I have a report which used AS as a source. I have a report parameter and a
dataset with the following query:
="with
Member [Measures].[BGBNLUniqueName] as
'[Product].[Concern].currentmember.UniqueName'
member [Measures].[BGBNLDisplayName] as 'IIF(
[Product].[Concern].Currentmember.level is [Product].[Concern].[PD], ""All
Business Groups"", [Product].[Concern].Currentmember.Name)'
set [Products] as ' {[Product].[Concern].[Sub Region].&[BNL].&[3400],
[Product].[Concern].[Sub
Region].&[BNL].&[3400].&[6918],[Product].[Concern].[Sub
Region].&[BNL].&[3400].&[6919],[Product].[Concern].[Sub
Region].&[BNL].&[3400].&[6920]} ' "
& IIF( Parameters!Product_BG.Label = "All Business Groups",
"Set [ChosenProducts] as ' Filter( [Products],
[Product].[Concern].currentmember.name = ""3400 Consumer Electronics"")'",
"Set [ChosenProducts] as ' Filter( [Products],
[Product].[Concern].currentmember.name = " & Parameters!Product_BG.Label &
")' ") & "
select { [Measures].[BGBNLUniqueName] , [Measures].[BGBNLDisplayName] } on
columns,
{ [ChosenProducts] } on rows
from [EDWH_SMRT_VIRT_SALESOUT]"
I have tested this in the mdx sample application and in order to work the
Parameter has to be enclosed by double quotes. When I do that I always get
the errormessage from AS that the value from the parameter is not enclosed
with double quotes.
Any Ideas? How can I escape the double quote?
Regards,
QI think the third part of the expression should be this:
& IIF( Parameters!Product_BG.Label = "All Business Groups", "Set
[ChosenProducts] as ' Filter( [Products],
[Product].[Concern].currentmember.name = ""3400 Consumer Electronics"")'",
"Set [ChosenProducts] as ' Filter( [Products],
[Product].[Concern].currentmember.name = """ & Parameters!Product_BG.Label &
""")' ") & "
Note: I have added "" around your parameter string concatenation.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Qbee" <Qbee@.discussions.microsoft.com> wrote in message
news:8DBC307C-6823-46CE-9835-31EAF552B941@.microsoft.com...
> Hello,
> I have a report which used AS as a source. I have a report parameter and a
> dataset with the following query:
> ="with
> Member [Measures].[BGBNLUniqueName] as
> '[Product].[Concern].currentmember.UniqueName'
> member [Measures].[BGBNLDisplayName] as 'IIF(
> [Product].[Concern].Currentmember.level is [Product].[Concern].[PD], ""All
> Business Groups"", [Product].[Concern].Currentmember.Name)'
> set [Products] as ' {[Product].[Concern].[Sub Region].&[BNL].&[3400],
> [Product].[Concern].[Sub
> Region].&[BNL].&[3400].&[6918],[Product].[Concern].[Sub
> Region].&[BNL].&[3400].&[6919],[Product].[Concern].[Sub
> Region].&[BNL].&[3400].&[6920]} ' "
> & IIF( Parameters!Product_BG.Label = "All Business Groups",
> "Set [ChosenProducts] as ' Filter( [Products],
> [Product].[Concern].currentmember.name = ""3400 Consumer Electronics"")'",
> "Set [ChosenProducts] as ' Filter( [Products],
> [Product].[Concern].currentmember.name = " & Parameters!Product_BG.Label &
> ")' ") & "
> select { [Measures].[BGBNLUniqueName] , [Measures].[BGBNLDisplayName] }
> on
> columns,
> { [ChosenProducts] } on rows
> from [EDWH_SMRT_VIRT_SALESOUT]"
>
> I have tested this in the mdx sample application and in order to work the
> Parameter has to be enclosed by double quotes. When I do that I always get
> the errormessage from AS that the value from the parameter is not enclosed
> with double quotes.
> Any Ideas? How can I escape the double quote?
> Regards,
> Q|||Hi Robert,
I have tried this but still get the error.
Q
"Robert Bruckner [MSFT]" wrote:
> I think the third part of the expression should be this:
> & IIF( Parameters!Product_BG.Label = "All Business Groups", "Set
> [ChosenProducts] as ' Filter( [Products],
> [Product].[Concern].currentmember.name = ""3400 Consumer Electronics"")'",
> "Set [ChosenProducts] as ' Filter( [Products],
> [Product].[Concern].currentmember.name = """ & Parameters!Product_BG.Label &
> """)' ") & "
> Note: I have added "" around your parameter string concatenation.
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Qbee" <Qbee@.discussions.microsoft.com> wrote in message
> news:8DBC307C-6823-46CE-9835-31EAF552B941@.microsoft.com...
> > Hello,
> >
> > I have a report which used AS as a source. I have a report parameter and a
> > dataset with the following query:
> >
> > ="with
> > Member [Measures].[BGBNLUniqueName] as
> > '[Product].[Concern].currentmember.UniqueName'
> > member [Measures].[BGBNLDisplayName] as 'IIF(
> > [Product].[Concern].Currentmember.level is [Product].[Concern].[PD], ""All
> > Business Groups"", [Product].[Concern].Currentmember.Name)'
> >
> > set [Products] as ' {[Product].[Concern].[Sub Region].&[BNL].&[3400],
> > [Product].[Concern].[Sub
> > Region].&[BNL].&[3400].&[6918],[Product].[Concern].[Sub
> > Region].&[BNL].&[3400].&[6919],[Product].[Concern].[Sub
> > Region].&[BNL].&[3400].&[6920]} ' "
> >
> > & IIF( Parameters!Product_BG.Label = "All Business Groups",
> > "Set [ChosenProducts] as ' Filter( [Products],
> > [Product].[Concern].currentmember.name = ""3400 Consumer Electronics"")'",
> > "Set [ChosenProducts] as ' Filter( [Products],
> > [Product].[Concern].currentmember.name = " & Parameters!Product_BG.Label &
> > ")' ") & "
> >
> > select { [Measures].[BGBNLUniqueName] , [Measures].[BGBNLDisplayName] }
> > on
> > columns,
> > { [ChosenProducts] } on rows
> >
> > from [EDWH_SMRT_VIRT_SALESOUT]"
> >
> >
> > I have tested this in the mdx sample application and in order to work the
> > Parameter has to be enclosed by double quotes. When I do that I always get
> > the errormessage from AS that the value from the parameter is not enclosed
> > with double quotes.
> >
> > Any Ideas? How can I escape the double quote?
> >
> > Regards,
> > Q
>
>

Another Row After SubTotal in Matrix

I have a Matrix in Report, with SubTotals of rows and columns.

I want to add another row below the subtotal (as well as another column to the left) in order to display percentage. Could anyone please tell me how?

There is a work around. you need to add one more column and display percentage in that column, this would add a column after total column and would display the sum of percentage column.

Then you reduce the width of the column (the column that you have manually added) to 0.

|||

First, Thanks for your reply.

I Tried it, but it doesn't work.

When I set the width of the added column to 0, the total row becomes 0 too.

?

another question about bind object to Report

hi,all
I download the Sample from http://www.gotreportviewer.com
in the "InvoiceMaker" sample,
the object like Custome can be bind to the Report directly,
but in the "Object Data Source" Sample ,I Modify the
" reportViewer.LocalReport.DataSources.Add(
new ReportDataSource("Product", merchant.GetProducts()));"
to "reportViewer.LocalReport.DataSources.Add(
new ReportDataSource("Product", new new Product("Pencil", 30)))"
there was an Exception with infomation:"Value does not fall within the expected range "

what's wrong with it?

LocalReport.DataSources takes a collection of objects, it doesn't allow a single element - that's why you get the error.

The reason why the InvoiceMaker works with a single element is because it uses a System.Windows.Forms.BindingSource - which can take either a list, an object or a type.

Here's the "magic" that makes it work in InvoiceMaker

Me.CustomerBindingSource = New System.Windows.Forms.BindingSource(Me.components)

...

Me.CustomerBindingSource.DataSource = GetType(ReportFromGrid.Customer)

...

ReportDataSource1.Value = Me.CustomerBindingSource

...

Me.CustomerBindingSource.DataSource = New Customer()

The ObjectDataSource sample takes a shortcut, and shows you how to use a collection directly, w/o going through the binding. That makes things simpler but removes some of the syntactic sugar that makes it easier to use.

Hope that helps,

Tudor Trufinescu

|||I know,thanks！

Another nested SQL question

I am trying to get a nested SQL statement to work in my main SQL report code below. I can successfully run the nested code by itself and the main code by itself, however, I am having some trouble getting them to work together. [Note: the chart_components table and the episodes tables can be linked via the episode_key field ]

SELECT

srm.EPISODES.MEDREC_NO,

srm.EPISODES.ACCOUNT_NUMBER,

srm.EPISODES.EPISODE_TYPE AS Visit_Type,

Isnull(ltrim(rtrim(srm.patients.patient_lname)) + ', ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_fname)) + ' ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_mname)) + ' ','')

+

Isnull(ltrim(rtrim(srm.patients.patient_sname)), '') AS Patient_Name,

srm.EPISODES.EPISODE_DATE AS DISCHDT,

MAX(srm.EVENT_HISTORY.EVENT_DATE) AS ABSCOMPDT,

--Begin nested query

(SELECT et1.episode_key, MAX(et1.status_date)

FROM srm.chart_components et1, srm.chart_components et2

WHERE et1.episode_key = et2.episode_key

AND et1.chart_component_ke = et2.chart_component_ke

AND et1.deficiency_type = et2.deficiency_type

AND et1.deficiency_status = et2.deficiency_status

AND et1.status_date = et2.status_date AND et2.deficiency_status = 'C'

GROUP by et1.episode_key

HAVING COUNT(et1.episode_key) = COUNT(et2.episode_key)) AS Chart_Comp_Date

-- End nested query

FROM srm.cdmab_base_info INNER JOIN

srm.EPISODES INNER JOIN

srm.PATIENTS INNER JOIN

srm.ITEM_HEADER ON srm.PATIENTS.PATIENT_KEY = srm.ITEM_HEADER.LOGICAL_PARENT_KEY ON

srm.EPISODES.EPISODE_KEY = srm.ITEM_HEADER.ITEM_KEY INNER JOIN

srm.PATIENT_VISIT ON srm.EPISODES.EPISODE_KEY = srm.PATIENT_VISIT.EPISODE_KEY ON

srm.cdmab_base_info.EPISODE_KEY = srm.EPISODES.EPISODE_KEY INNER JOIN

srm.EVENT_HISTORY ON srm.EPISODES.EPISODE_KEY = srm.EVENT_HISTORY.ITEM_KEY INNER JOIN

srm.CHART_COMPONENTS ON srm.CHART_COMPONENTS.EPISODE_KEY = srm.EPISODES.EPISODE_KEY

WHERE srm.CHART_COMPONENTS.DEFICIENCY_STATUS = 'C'

AND srm.EPISODES.EPISODE_DATE Between '08/06/2007' and '08/13/2007'

Group by

srm.EPISODES.MEDREC_NO,

srm.EPISODES.ACCOUNT_NUMBER,

srm.EPISODES.EPISODE_TYPE,

Isnull(ltrim(rtrim(srm.patients.patient_lname)) + ', ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_fname)) + ' ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_mname)) + ' ','')

+

Isnull(ltrim(rtrim(srm.patients.patient_sname)), '') ,

srm.EPISODES.EPISODE_DATE,

srm.EVENT_HISTORY.EVENT_DATE,

srm.CHART_COMPONENTS.STATUS_DATE

See the much simpler version of the query that gets the Chart_Comp_Date from my reply in your other thread. If you want just the date in the SELECT list then you need to select only that column. You are selecting the episode_key and the date. This will raise errors. So fix it like:

Code Snippet

-- Begin nested query:

(select max(c1.status_date)

from srm.chart_components as c1

group by c1.episode_key

having count(*) = sum(case c1.deficiency_status when 'C' then 1 end)) as Chart_Comp_Date

|||

Thanks. The code below reflects your code snippet. However, I am receiving the following error when I run it.

"The multi-part identifier "c.deficiency_status" could not be bound."

SELECT

srm.EPISODES.MEDREC_NO,

srm.EPISODES.ACCOUNT_NUMBER,

srm.EPISODES.EPISODE_TYPE AS Visit_Type,

Isnull(ltrim(rtrim(srm.patients.patient_lname)) + ', ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_fname)) + ' ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_mname)) + ' ','')

+

Isnull(ltrim(rtrim(srm.patients.patient_sname)), '') AS Patient_Name,

srm.EPISODES.EPISODE_DATE AS DISCHDT,

MAX(srm.EVENT_HISTORY.EVENT_DATE) AS ABSCOMPDT,

-- begin nested query

(select max(c1.status_date)

from srm.chart_components as c1

group by c1.episode_key

having count(*) = sum(case c.deficiency_status when 'C' then 1 end)) as Chart_Comp_Date

--end nested query

FROM srm.cdmab_base_info INNER JOIN

srm.EPISODES INNER JOIN

srm.PATIENTS INNER JOIN

srm.ITEM_HEADER ON srm.PATIENTS.PATIENT_KEY = srm.ITEM_HEADER.LOGICAL_PARENT_KEY ON

srm.EPISODES.EPISODE_KEY = srm.ITEM_HEADER.ITEM_KEY INNER JOIN

srm.PATIENT_VISIT ON srm.EPISODES.EPISODE_KEY = srm.PATIENT_VISIT.EPISODE_KEY ON

srm.cdmab_base_info.EPISODE_KEY = srm.EPISODES.EPISODE_KEY INNER JOIN

srm.EVENT_HISTORY ON srm.EPISODES.EPISODE_KEY = srm.EVENT_HISTORY.ITEM_KEY INNER JOIN

srm.CHART_COMPONENTS ON srm.CHART_COMPONENTS.EPISODE_KEY = srm.EPISODES.EPISODE_KEY

WHERE srm.CHART_COMPONENTS.DEFICIENCY_STATUS = 'C'

AND srm.EPISODES.EPISODE_DATE Between '08/06/2007' and '08/13/2007'

Group by

srm.EPISODES.MEDREC_NO,

srm.EPISODES.ACCOUNT_NUMBER,

srm.EPISODES.EPISODE_TYPE,

Isnull(ltrim(rtrim(srm.patients.patient_lname)) + ', ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_fname)) + ' ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_mname)) + ' ','')

+

Isnull(ltrim(rtrim(srm.patients.patient_sname)), '') ,

srm.EPISODES.EPISODE_DATE,

srm.EVENT_HISTORY.EVENT_DATE,

srm.CHART_COMPONENTS.STATUS_DATE

|||

I had a mistake in the alias inside the case expression. Change c.deficiency_status to c1.deficiency_status.

|||

I just noticed that I get the same error is I run your code snippet (see below) just by itself.

(select max(c1.status_date)

from srm.chart_components as c1

group by c1.episode_key

having count(*) = sum(case c.deficiency_status when 'C' then 1 end)) as Chart_Comp_Date

|||

I made the change above and and now receiving this error.

Msg 512, Level 16, State 1, Line 1

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Warning: Null value is eliminated by an aggregate or other SET operation.

My code follows:

SELECT

srm.EPISODES.MEDREC_NO,

srm.EPISODES.ACCOUNT_NUMBER,

srm.EPISODES.EPISODE_TYPE AS Visit_Type,

Isnull(ltrim(rtrim(srm.patients.patient_lname)) + ', ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_fname)) + ' ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_mname)) + ' ','')

+

Isnull(ltrim(rtrim(srm.patients.patient_sname)), '') AS Patient_Name,

srm.EPISODES.EPISODE_DATE AS DISCHDT,

MAX(srm.EVENT_HISTORY.EVENT_DATE) AS ABSCOMPDT,

--begin nested query

(select max(c1.status_date)

from srm.chart_components as c1

group by c1.episode_key

having count(*) = sum(case c1.deficiency_status when 'C' then 1 end)) as Chart_Comp_Date

-- end nested query

FROM srm.cdmab_base_info INNER JOIN

srm.EPISODES INNER JOIN

srm.PATIENTS INNER JOIN

srm.ITEM_HEADER ON srm.PATIENTS.PATIENT_KEY = srm.ITEM_HEADER.LOGICAL_PARENT_KEY ON

srm.EPISODES.EPISODE_KEY = srm.ITEM_HEADER.ITEM_KEY INNER JOIN

srm.PATIENT_VISIT ON srm.EPISODES.EPISODE_KEY = srm.PATIENT_VISIT.EPISODE_KEY ON

srm.cdmab_base_info.EPISODE_KEY = srm.EPISODES.EPISODE_KEY INNER JOIN

srm.EVENT_HISTORY ON srm.EPISODES.EPISODE_KEY = srm.EVENT_HISTORY.ITEM_KEY INNER JOIN

srm.CHART_COMPONENTS ON srm.CHART_COMPONENTS.EPISODE_KEY = srm.EPISODES.EPISODE_KEY

WHERE srm.CHART_COMPONENTS.DEFICIENCY_STATUS = 'C'

AND srm.EPISODES.EPISODE_DATE Between '08/06/2007' and '08/13/2007'

Group by

srm.EPISODES.MEDREC_NO,

srm.EPISODES.ACCOUNT_NUMBER,

srm.EPISODES.EPISODE_TYPE,

Isnull(ltrim(rtrim(srm.patients.patient_lname)) + ', ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_fname)) + ' ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_mname)) + ' ','')

+

Isnull(ltrim(rtrim(srm.patients.patient_sname)), '') ,

srm.EPISODES.EPISODE_DATE,

srm.EVENT_HISTORY.EVENT_DATE,

srm.CHART_COMPONENTS.STATUS_DATE

|||

You have no correlation between the query in the SELECT list and the tables in the FROM clause. You need to reference the EPISODE_KEY from one of the outer tables also like below. Otherwise, you will get errors depending on the data.

Code Snippet

(select max()

...

where c1.EPISODE_KEY = srm.EPISODES.EPISODE_KEY

having count(*) .... ) as Chart_Comp_Date

|||

Sometimes you can look at a problem for too long and not see the answer right in front of you. I can't believe I missed this! Thank you Umachandar for all your help with this query. I appreciate it.

|||

My thanks to Umachandar, Arnie and Shawn for their help on this problem. Below is the code I ended up using in the event someone else finds themselves in a similar situation.

select max(c1.status_date)as Chart_Comp_Dt,
c1.chart_component_ke,
c2.episode_type as Visit_Type,
c1.deficiency_type,
c1.deficiency_status,
c1.episode_key,
c2.account_number as Account_No,
c2.medrec_no as MRN,
c2.episode_date as Disch_Date,
c4.patientname as Patient_Name,
MAX(c3.event_date) as ABSCOMPDT
from srm.chart_components c1, srm.episodes c2, srm.event_history c3, dbo.PtMstr c4
where c1.EPISODE_KEY = c2.EPISODE_KEY
and c2.EPISODE_KEY = c3.ITEM_KEY
and c2.ACCOUNT_NUMBER = c4.accountnumber
and c2.episode_date between @.StartDate and @.EndDate
and c2.episode_type IN(@.visittype)
group by c1.episode_key,c1.chart_component_ke,c1.deficiency_type,
c1.deficiency_status,c1.episode_key,c2.account_number,c2.episode_type,
c2.medrec_no,c2.episode_date,c4.patientname
having (c2.episode_date < max(c1.status_date)) and
count(*) = sum(case c1.deficiency_status when 'C' then 1 end)
order by c2.episode_date desc

Another nested SQL question

I am trying to get a nested SQL statement to work in my main SQL report code below. I can successfully run the nested code by itself and the main code by itself, however, I am having some trouble getting them to work together. [Note: the chart_components table and the episodes tables can be linked via the episode_key field ]

SELECT

srm.EPISODES.MEDREC_NO,

srm.EPISODES.ACCOUNT_NUMBER,

srm.EPISODES.EPISODE_TYPE AS Visit_Type,

Isnull(ltrim(rtrim(srm.patients.patient_lname)) + ', ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_fname)) + ' ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_mname)) + ' ','')

+

Isnull(ltrim(rtrim(srm.patients.patient_sname)), '') AS Patient_Name,

srm.EPISODES.EPISODE_DATE AS DISCHDT,

MAX(srm.EVENT_HISTORY.EVENT_DATE) AS ABSCOMPDT,

--Begin nested query

(SELECT et1.episode_key, MAX(et1.status_date)

FROM srm.chart_components et1, srm.chart_components et2

WHERE et1.episode_key = et2.episode_key

AND et1.chart_component_ke = et2.chart_component_ke

AND et1.deficiency_type = et2.deficiency_type

AND et1.deficiency_status = et2.deficiency_status

AND et1.status_date = et2.status_date AND et2.deficiency_status = 'C'

GROUP by et1.episode_key

HAVING COUNT(et1.episode_key) = COUNT(et2.episode_key)) AS Chart_Comp_Date

-- End nested query

FROM srm.cdmab_base_info INNER JOIN

srm.EPISODES INNER JOIN

srm.PATIENTS INNER JOIN

srm.ITEM_HEADER ON srm.PATIENTS.PATIENT_KEY = srm.ITEM_HEADER.LOGICAL_PARENT_KEY ON

srm.EPISODES.EPISODE_KEY = srm.ITEM_HEADER.ITEM_KEY INNER JOIN

srm.PATIENT_VISIT ON srm.EPISODES.EPISODE_KEY = srm.PATIENT_VISIT.EPISODE_KEY ON

srm.cdmab_base_info.EPISODE_KEY = srm.EPISODES.EPISODE_KEY INNER JOIN

srm.EVENT_HISTORY ON srm.EPISODES.EPISODE_KEY = srm.EVENT_HISTORY.ITEM_KEY INNER JOIN

srm.CHART_COMPONENTS ON srm.CHART_COMPONENTS.EPISODE_KEY = srm.EPISODES.EPISODE_KEY

WHERE srm.CHART_COMPONENTS.DEFICIENCY_STATUS = 'C'

AND srm.EPISODES.EPISODE_DATE Between '08/06/2007' and '08/13/2007'

Group by

srm.EPISODES.MEDREC_NO,

srm.EPISODES.ACCOUNT_NUMBER,

srm.EPISODES.EPISODE_TYPE,

Isnull(ltrim(rtrim(srm.patients.patient_lname)) + ', ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_fname)) + ' ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_mname)) + ' ','')

+

Isnull(ltrim(rtrim(srm.patients.patient_sname)), '') ,

srm.EPISODES.EPISODE_DATE,

srm.EVENT_HISTORY.EVENT_DATE,

srm.CHART_COMPONENTS.STATUS_DATE

See the much simpler version of the query that gets the Chart_Comp_Date from my reply in your other thread. If you want just the date in the SELECT list then you need to select only that column. You are selecting the episode_key and the date. This will raise errors. So fix it like:

Code Snippet

-- Begin nested query:

(select max(c1.status_date)

from srm.chart_components as c1

group by c1.episode_key

having count(*) = sum(case c1.deficiency_status when 'C' then 1 end)) as Chart_Comp_Date

|||

Thanks. The code below reflects your code snippet. However, I am receiving the following error when I run it.

"The multi-part identifier "c.deficiency_status" could not be bound."

SELECT

srm.EPISODES.MEDREC_NO,

srm.EPISODES.ACCOUNT_NUMBER,

srm.EPISODES.EPISODE_TYPE AS Visit_Type,

Isnull(ltrim(rtrim(srm.patients.patient_lname)) + ', ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_fname)) + ' ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_mname)) + ' ','')

+

Isnull(ltrim(rtrim(srm.patients.patient_sname)), '') AS Patient_Name,

srm.EPISODES.EPISODE_DATE AS DISCHDT,

MAX(srm.EVENT_HISTORY.EVENT_DATE) AS ABSCOMPDT,

-- begin nested query

(select max(c1.status_date)

from srm.chart_components as c1

group by c1.episode_key

having count(*) = sum(case c.deficiency_status when 'C' then 1 end)) as Chart_Comp_Date

--end nested query

FROM srm.cdmab_base_info INNER JOIN

srm.EPISODES INNER JOIN

srm.PATIENTS INNER JOIN

srm.ITEM_HEADER ON srm.PATIENTS.PATIENT_KEY = srm.ITEM_HEADER.LOGICAL_PARENT_KEY ON

srm.EPISODES.EPISODE_KEY = srm.ITEM_HEADER.ITEM_KEY INNER JOIN

srm.PATIENT_VISIT ON srm.EPISODES.EPISODE_KEY = srm.PATIENT_VISIT.EPISODE_KEY ON

srm.cdmab_base_info.EPISODE_KEY = srm.EPISODES.EPISODE_KEY INNER JOIN

srm.EVENT_HISTORY ON srm.EPISODES.EPISODE_KEY = srm.EVENT_HISTORY.ITEM_KEY INNER JOIN

srm.CHART_COMPONENTS ON srm.CHART_COMPONENTS.EPISODE_KEY = srm.EPISODES.EPISODE_KEY

WHERE srm.CHART_COMPONENTS.DEFICIENCY_STATUS = 'C'

AND srm.EPISODES.EPISODE_DATE Between '08/06/2007' and '08/13/2007'

Group by

srm.EPISODES.MEDREC_NO,

srm.EPISODES.ACCOUNT_NUMBER,

srm.EPISODES.EPISODE_TYPE,

Isnull(ltrim(rtrim(srm.patients.patient_lname)) + ', ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_fname)) + ' ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_mname)) + ' ','')

+

Isnull(ltrim(rtrim(srm.patients.patient_sname)), '') ,

srm.EPISODES.EPISODE_DATE,

srm.EVENT_HISTORY.EVENT_DATE,

srm.CHART_COMPONENTS.STATUS_DATE

|||

I had a mistake in the alias inside the case expression. Change c.deficiency_status to c1.deficiency_status.

|||

I just noticed that I get the same error is I run your code snippet (see below) just by itself.

(select max(c1.status_date)

from srm.chart_components as c1

group by c1.episode_key

having count(*) = sum(case c.deficiency_status when 'C' then 1 end)) as Chart_Comp_Date

|||

I made the change above and and now receiving this error.

Msg 512, Level 16, State 1, Line 1

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Warning: Null value is eliminated by an aggregate or other SET operation.

My code follows:

SELECT

srm.EPISODES.MEDREC_NO,

srm.EPISODES.ACCOUNT_NUMBER,

srm.EPISODES.EPISODE_TYPE AS Visit_Type,

Isnull(ltrim(rtrim(srm.patients.patient_lname)) + ', ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_fname)) + ' ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_mname)) + ' ','')

+

Isnull(ltrim(rtrim(srm.patients.patient_sname)), '') AS Patient_Name,

srm.EPISODES.EPISODE_DATE AS DISCHDT,

MAX(srm.EVENT_HISTORY.EVENT_DATE) AS ABSCOMPDT,

--begin nested query

(select max(c1.status_date)

from srm.chart_components as c1

group by c1.episode_key

having count(*) = sum(case c1.deficiency_status when 'C' then 1 end)) as Chart_Comp_Date

-- end nested query

FROM srm.cdmab_base_info INNER JOIN

srm.EPISODES INNER JOIN

srm.PATIENTS INNER JOIN

srm.ITEM_HEADER ON srm.PATIENTS.PATIENT_KEY = srm.ITEM_HEADER.LOGICAL_PARENT_KEY ON

srm.EPISODES.EPISODE_KEY = srm.ITEM_HEADER.ITEM_KEY INNER JOIN

srm.PATIENT_VISIT ON srm.EPISODES.EPISODE_KEY = srm.PATIENT_VISIT.EPISODE_KEY ON

srm.cdmab_base_info.EPISODE_KEY = srm.EPISODES.EPISODE_KEY INNER JOIN

srm.EVENT_HISTORY ON srm.EPISODES.EPISODE_KEY = srm.EVENT_HISTORY.ITEM_KEY INNER JOIN

srm.CHART_COMPONENTS ON srm.CHART_COMPONENTS.EPISODE_KEY = srm.EPISODES.EPISODE_KEY

WHERE srm.CHART_COMPONENTS.DEFICIENCY_STATUS = 'C'

AND srm.EPISODES.EPISODE_DATE Between '08/06/2007' and '08/13/2007'

Group by

srm.EPISODES.MEDREC_NO,

srm.EPISODES.ACCOUNT_NUMBER,

srm.EPISODES.EPISODE_TYPE,

Isnull(ltrim(rtrim(srm.patients.patient_lname)) + ', ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_fname)) + ' ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_mname)) + ' ','')

+

Isnull(ltrim(rtrim(srm.patients.patient_sname)), '') ,

srm.EPISODES.EPISODE_DATE,

srm.EVENT_HISTORY.EVENT_DATE,

srm.CHART_COMPONENTS.STATUS_DATE

|||

You have no correlation between the query in the SELECT list and the tables in the FROM clause. You need to reference the EPISODE_KEY from one of the outer tables also like below. Otherwise, you will get errors depending on the data.

Code Snippet

(select max()

...

where c1.EPISODE_KEY = srm.EPISODES.EPISODE_KEY

having count(*) .... ) as Chart_Comp_Date

|||

Sometimes you can look at a problem for too long and not see the answer right in front of you. I can't believe I missed this! Thank you Umachandar for all your help with this query. I appreciate it.

|||

My thanks to Umachandar, Arnie and Shawn for their help on this problem. Below is the code I ended up using in the event someone else finds themselves in a similar situation.

select max(c1.status_date)as Chart_Comp_Dt,
c1.chart_component_ke,
c2.episode_type as Visit_Type,
c1.deficiency_type,
c1.deficiency_status,
c1.episode_key,
c2.account_number as Account_No,
c2.medrec_no as MRN,
c2.episode_date as Disch_Date,
c4.patientname as Patient_Name,
MAX(c3.event_date) as ABSCOMPDT
from srm.chart_components c1, srm.episodes c2, srm.event_history c3, dbo.PtMstr c4
where c1.EPISODE_KEY = c2.EPISODE_KEY
and c2.EPISODE_KEY = c3.ITEM_KEY
and c2.ACCOUNT_NUMBER = c4.accountnumber
and c2.episode_date between @.StartDate and @.EndDate
and c2.episode_type IN(@.visittype)
group by c1.episode_key,c1.chart_component_ke,c1.deficiency_type,
c1.deficiency_status,c1.episode_key,c2.account_number,c2.episode_type,
c2.medrec_no,c2.episode_date,c4.patientname
having (c2.episode_date < max(c1.status_date)) and
count(*) = sum(case c1.deficiency_status when 'C' then 1 end)
order by c2.episode_date desc

Another nested SQL question

I am trying to get a nested SQL statement to work in my main SQL report code below. I can successfully run the nested code by itself and the main code by itself, however, I am having some trouble getting them to work together. [Note: the chart_components table and the episodes tables can be linked via the episode_key field ]

SELECT

srm.EPISODES.MEDREC_NO,

srm.EPISODES.ACCOUNT_NUMBER,

srm.EPISODES.EPISODE_TYPE AS Visit_Type,

Isnull(ltrim(rtrim(srm.patients.patient_lname)) + ', ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_fname)) + ' ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_mname)) + ' ','')

+

Isnull(ltrim(rtrim(srm.patients.patient_sname)), '') AS Patient_Name,

srm.EPISODES.EPISODE_DATE AS DISCHDT,

MAX(srm.EVENT_HISTORY.EVENT_DATE) AS ABSCOMPDT,

--Begin nested query

(SELECT et1.episode_key, MAX(et1.status_date)

FROM srm.chart_components et1, srm.chart_components et2

WHERE et1.episode_key = et2.episode_key

AND et1.chart_component_ke = et2.chart_component_ke

AND et1.deficiency_type = et2.deficiency_type

AND et1.deficiency_status = et2.deficiency_status

AND et1.status_date = et2.status_date AND et2.deficiency_status = 'C'

GROUP by et1.episode_key

HAVING COUNT(et1.episode_key) = COUNT(et2.episode_key)) AS Chart_Comp_Date

-- End nested query

FROM srm.cdmab_base_info INNER JOIN

srm.EPISODES INNER JOIN

srm.PATIENTS INNER JOIN

srm.ITEM_HEADER ON srm.PATIENTS.PATIENT_KEY = srm.ITEM_HEADER.LOGICAL_PARENT_KEY ON

srm.EPISODES.EPISODE_KEY = srm.ITEM_HEADER.ITEM_KEY INNER JOIN

srm.PATIENT_VISIT ON srm.EPISODES.EPISODE_KEY = srm.PATIENT_VISIT.EPISODE_KEY ON

srm.cdmab_base_info.EPISODE_KEY = srm.EPISODES.EPISODE_KEY INNER JOIN

srm.EVENT_HISTORY ON srm.EPISODES.EPISODE_KEY = srm.EVENT_HISTORY.ITEM_KEY INNER JOIN

srm.CHART_COMPONENTS ON srm.CHART_COMPONENTS.EPISODE_KEY = srm.EPISODES.EPISODE_KEY

WHERE srm.CHART_COMPONENTS.DEFICIENCY_STATUS = 'C'

AND srm.EPISODES.EPISODE_DATE Between '08/06/2007' and '08/13/2007'

Group by

srm.EPISODES.MEDREC_NO,

srm.EPISODES.ACCOUNT_NUMBER,

srm.EPISODES.EPISODE_TYPE,

Isnull(ltrim(rtrim(srm.patients.patient_lname)) + ', ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_fname)) + ' ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_mname)) + ' ','')

+

Isnull(ltrim(rtrim(srm.patients.patient_sname)), '') ,

srm.EPISODES.EPISODE_DATE,

srm.EVENT_HISTORY.EVENT_DATE,

srm.CHART_COMPONENTS.STATUS_DATE

See the much simpler version of the query that gets the Chart_Comp_Date from my reply in your other thread. If you want just the date in the SELECT list then you need to select only that column. You are selecting the episode_key and the date. This will raise errors. So fix it like:

Code Snippet

-- Begin nested query:

(select max(c1.status_date)

from srm.chart_components as c1

group by c1.episode_key

having count(*) = sum(case c1.deficiency_status when 'C' then 1 end)) as Chart_Comp_Date

|||

Thanks. The code below reflects your code snippet. However, I am receiving the following error when I run it.

"The multi-part identifier "c.deficiency_status" could not be bound."

SELECT

srm.EPISODES.MEDREC_NO,

srm.EPISODES.ACCOUNT_NUMBER,

srm.EPISODES.EPISODE_TYPE AS Visit_Type,

Isnull(ltrim(rtrim(srm.patients.patient_lname)) + ', ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_fname)) + ' ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_mname)) + ' ','')

+

Isnull(ltrim(rtrim(srm.patients.patient_sname)), '') AS Patient_Name,

srm.EPISODES.EPISODE_DATE AS DISCHDT,

MAX(srm.EVENT_HISTORY.EVENT_DATE) AS ABSCOMPDT,

-- begin nested query

(select max(c1.status_date)

from srm.chart_components as c1

group by c1.episode_key

having count(*) = sum(case c.deficiency_status when 'C' then 1 end)) as Chart_Comp_Date

--end nested query

FROM srm.cdmab_base_info INNER JOIN

srm.EPISODES INNER JOIN

srm.PATIENTS INNER JOIN

srm.ITEM_HEADER ON srm.PATIENTS.PATIENT_KEY = srm.ITEM_HEADER.LOGICAL_PARENT_KEY ON

srm.EPISODES.EPISODE_KEY = srm.ITEM_HEADER.ITEM_KEY INNER JOIN

srm.PATIENT_VISIT ON srm.EPISODES.EPISODE_KEY = srm.PATIENT_VISIT.EPISODE_KEY ON

srm.cdmab_base_info.EPISODE_KEY = srm.EPISODES.EPISODE_KEY INNER JOIN

srm.EVENT_HISTORY ON srm.EPISODES.EPISODE_KEY = srm.EVENT_HISTORY.ITEM_KEY INNER JOIN

srm.CHART_COMPONENTS ON srm.CHART_COMPONENTS.EPISODE_KEY = srm.EPISODES.EPISODE_KEY

WHERE srm.CHART_COMPONENTS.DEFICIENCY_STATUS = 'C'

AND srm.EPISODES.EPISODE_DATE Between '08/06/2007' and '08/13/2007'

Group by

srm.EPISODES.MEDREC_NO,

srm.EPISODES.ACCOUNT_NUMBER,

srm.EPISODES.EPISODE_TYPE,

Isnull(ltrim(rtrim(srm.patients.patient_lname)) + ', ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_fname)) + ' ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_mname)) + ' ','')

+

Isnull(ltrim(rtrim(srm.patients.patient_sname)), '') ,

srm.EPISODES.EPISODE_DATE,

srm.EVENT_HISTORY.EVENT_DATE,

srm.CHART_COMPONENTS.STATUS_DATE

|||

I had a mistake in the alias inside the case expression. Change c.deficiency_status to c1.deficiency_status.

|||

I just noticed that I get the same error is I run your code snippet (see below) just by itself.

(select max(c1.status_date)

from srm.chart_components as c1

group by c1.episode_key

having count(*) = sum(case c.deficiency_status when 'C' then 1 end)) as Chart_Comp_Date

|||

I made the change above and and now receiving this error.

Msg 512, Level 16, State 1, Line 1

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Warning: Null value is eliminated by an aggregate or other SET operation.

My code follows:

SELECT

srm.EPISODES.MEDREC_NO,

srm.EPISODES.ACCOUNT_NUMBER,

srm.EPISODES.EPISODE_TYPE AS Visit_Type,

Isnull(ltrim(rtrim(srm.patients.patient_lname)) + ', ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_fname)) + ' ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_mname)) + ' ','')

+

Isnull(ltrim(rtrim(srm.patients.patient_sname)), '') AS Patient_Name,

srm.EPISODES.EPISODE_DATE AS DISCHDT,

MAX(srm.EVENT_HISTORY.EVENT_DATE) AS ABSCOMPDT,

--begin nested query

(select max(c1.status_date)

from srm.chart_components as c1

group by c1.episode_key

having count(*) = sum(case c1.deficiency_status when 'C' then 1 end)) as Chart_Comp_Date

-- end nested query

FROM srm.cdmab_base_info INNER JOIN

srm.EPISODES INNER JOIN

srm.PATIENTS INNER JOIN

srm.ITEM_HEADER ON srm.PATIENTS.PATIENT_KEY = srm.ITEM_HEADER.LOGICAL_PARENT_KEY ON

srm.EPISODES.EPISODE_KEY = srm.ITEM_HEADER.ITEM_KEY INNER JOIN

srm.PATIENT_VISIT ON srm.EPISODES.EPISODE_KEY = srm.PATIENT_VISIT.EPISODE_KEY ON

srm.cdmab_base_info.EPISODE_KEY = srm.EPISODES.EPISODE_KEY INNER JOIN

srm.EVENT_HISTORY ON srm.EPISODES.EPISODE_KEY = srm.EVENT_HISTORY.ITEM_KEY INNER JOIN

srm.CHART_COMPONENTS ON srm.CHART_COMPONENTS.EPISODE_KEY = srm.EPISODES.EPISODE_KEY

WHERE srm.CHART_COMPONENTS.DEFICIENCY_STATUS = 'C'

AND srm.EPISODES.EPISODE_DATE Between '08/06/2007' and '08/13/2007'

Group by

srm.EPISODES.MEDREC_NO,

srm.EPISODES.ACCOUNT_NUMBER,

srm.EPISODES.EPISODE_TYPE,

Isnull(ltrim(rtrim(srm.patients.patient_lname)) + ', ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_fname)) + ' ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_mname)) + ' ','')

+

Isnull(ltrim(rtrim(srm.patients.patient_sname)), '') ,

srm.EPISODES.EPISODE_DATE,

srm.EVENT_HISTORY.EVENT_DATE,

srm.CHART_COMPONENTS.STATUS_DATE

|||

You have no correlation between the query in the SELECT list and the tables in the FROM clause. You need to reference the EPISODE_KEY from one of the outer tables also like below. Otherwise, you will get errors depending on the data.

Code Snippet

(select max()

...

where c1.EPISODE_KEY = srm.EPISODES.EPISODE_KEY

having count(*) .... ) as Chart_Comp_Date

|||

Sometimes you can look at a problem for too long and not see the answer right in front of you. I can't believe I missed this! Thank you Umachandar for all your help with this query. I appreciate it.

|||

My thanks to Umachandar, Arnie and Shawn for their help on this problem. Below is the code I ended up using in the event someone else finds themselves in a similar situation.

select max(c1.status_date)as Chart_Comp_Dt,
c1.chart_component_ke,
c2.episode_type as Visit_Type,
c1.deficiency_type,
c1.deficiency_status,
c1.episode_key,
c2.account_number as Account_No,
c2.medrec_no as MRN,
c2.episode_date as Disch_Date,
c4.patientname as Patient_Name,
MAX(c3.event_date) as ABSCOMPDT
from srm.chart_components c1, srm.episodes c2, srm.event_history c3, dbo.PtMstr c4
where c1.EPISODE_KEY = c2.EPISODE_KEY
and c2.EPISODE_KEY = c3.ITEM_KEY
and c2.ACCOUNT_NUMBER = c4.accountnumber
and c2.episode_date between @.StartDate and @.EndDate
and c2.episode_type IN(@.visittype)
group by c1.episode_key,c1.chart_component_ke,c1.deficiency_type,
c1.deficiency_status,c1.episode_key,c2.account_number,c2.episode_type,
c2.medrec_no,c2.episode_date,c4.patientname
having (c2.episode_date < max(c1.status_date)) and
count(*) = sum(case c1.deficiency_status when 'C' then 1 end)
order by c2.episode_date desc

Another Date Picker question

I created a report with two parameters, StartDate and EndDate.

They are specified as datetime type.

When I went to the Preview tab, there were date pickers for both values.

After a week or so of using the report, I thought it would be better if I gave the parameters default values. I spent some time reading what others had done in this forum and used the following as the expressions for the parameters:

Code Snippet

=IIF(Weekday(Today()) =1, FormatDateTime(Today(),2),
IIF(Weekday(Today()) =2, FormatDateTime(Today.AddDays(-1),2),
IIF(Weekday(Today()) =3, FormatDateTime(Today.AddDays(-2),2),
IIF(Weekday(Today()) =4, FormatDateTime(Today.AddDays(-3),2),
IIF(Weekday(Today()) =5, FormatDateTime(Today.AddDays(-4),2),
IIF(Weekday(Today()) =6, FormatDateTime(Today.AddDays(-5),2), FormatDateTime(Today.AddDays(-6),2)))))))

=IIF(Weekday(Today()) =1, FormatDateTime(Today.AddDays(6),2),
IIF(Weekday(Today()) =2, FormatDateTime(Today.AddDays(5),2),
IIF(Weekday(Today()) =3, FormatDateTime(Today.AddDays(4),2),
IIF(Weekday(Today()) =4, FormatDateTime(Today.AddDays(3),2),
IIF(Weekday(Today()) =5, FormatDateTime(Today.AddDays(2),2),
IIF(Weekday(Today()) =6, FormatDateTime(Today.AddDays(1),2), FormatDateTime(Today(),2)))))))

and now, there are no date pickers. I am curious. Why are they gone? Can I get them back and still have my default values?

Thanks,

IanO

Try removing the FormatDateTime function.|||

Thanks for your reply. Allow me to explain why the Format is there.

When I had not specified default values for the report parameters, the date pickers provided values that were dates only - no time values. After I put in the IIFs for the default values, the defaults included 12:00:00 AM with the date.

I hunted for quite a while and the solution that I chose was the Format. Perhaps you have another idea of how I can have a default Sunday and a default Saturday for the current week, without the time?

Thanks,

IanO

|||It's odd that the default values gave you a time. I used it here and had only date values. Hmmmm.|||If you want to keep the format text you should be able to add the cdate function.|||

Code Snippet

=CDATE(IIF(Weekday(Today()) =1, FormatDateTime(Today(),2),
IIF(Weekday(Today()) =2, FormatDateTime(Today.AddDays(-1),2),
IIF(Weekday(Today()) =3, FormatDateTime(Today.AddDays(-2),2),
IIF(Weekday(Today()) =4, FormatDateTime(Today.AddDays(-3),2),
IIF(Weekday(Today()) =5, FormatDateTime(Today.AddDays(-4),2),
IIF(Weekday(Today()) =6, FormatDateTime(Today.AddDays(-5),2), FormatDateTime(Today.AddDays(-6),2))))))))

Code Snippet

=CDATE(IIF(Weekday(Today()) =1, FormatDateTime(Today.AddDays(6),2),
IIF(Weekday(Today()) =2, FormatDateTime(Today.AddDays(5),2),
IIF(Weekday(Today()) =3, FormatDateTime(Today.AddDays(4),2),
IIF(Weekday(Today()) =4, FormatDateTime(Today.AddDays(3),2),
IIF(Weekday(Today()) =5, FormatDateTime(Today.AddDays(2),2),
IIF(Weekday(Today()) =6, FormatDateTime(Today.AddDays(1),2), FormatDateTime(Today(),2))))))))

Then make sure your datatype is date because using just your original expression you get an error if the datatype is not a string.

Simone

Another "InternalCatalogException: An internal error occurred on the report server"

We have uploaded a report that works fine in development and works OK most
of the time on the production server but produces this internal error from
time to time.
Any advice would be REALLY appreciated.
cheers,
Paul.
w3wp!library!6!12/04/2007-19:15:56:: i INFO: Call to RenderNext( '/CRMTemp_MSCRM/Employee
Payslip' )
w3wp!processing!6!12/04/2007-19:15:56:: a ASSERT: Assertion failed! Call
stack:
Microsoft.ReportingServices.ReportProcessing.Persistence.IntermediateFormatReader.Assert(Boolean
condition)
Microsoft.ReportingServices.ReportProcessing.Persistence.IntermediateFormatReader.RegisterDefinitionObject(IDOwner
idOwner)
Microsoft.ReportingServices.ReportProcessing.Persistence.IntermediateFormatReader.ReadReportItemBase(ReportItem
reportItem)
Microsoft.ReportingServices.ReportProcessing.Persistence.IntermediateFormatReader.ReadReport(ReportItem
parent)
Microsoft.ReportingServices.ReportProcessing.Persistence.IntermediateFormatReader.ReadSubReportInternals(ReportItem
parent)
Microsoft.ReportingServices.ReportProcessing.Persistence.IntermediateFormatReader.ReadReportItem(ReportItem
parent)
Microsoft.ReportingServices.ReportProcessing.Persistence.IntermediateFormatReader.ReadReportItemList(ReportItem
parent)
Microsoft.ReportingServices.ReportProcessing.Persistence.IntermediateFormatReader.ReadReportItemCollection(ReportItem
parent)
Microsoft.ReportingServices.ReportProcessing.Persistence.IntermediateFormatReader.ReadListInternals(ReportItem
parent)
Microsoft.ReportingServices.ReportProcessing.Persistence.IntermediateFormatReader.ReadDataRegionInternals(ReportItem
parent)
Microsoft.ReportingServices.ReportProcessing.Persistence.IntermediateFormatReader.ReadReportItem(ReportItem
parent)
Microsoft.ReportingServices.ReportProcessing.Persistence.IntermediateFormatReader.ReadReportItemList(ReportItem
parent)
Microsoft.ReportingServices.ReportProcessing.Persistence.IntermediateFormatReader.ReadReportItemCollection(ReportItem
parent)
Microsoft.ReportingServices.ReportProcessing.Persistence.IntermediateFormatReader.ReadReport(ReportItem
parent)
Microsoft.ReportingServices.ReportProcessing.Persistence.IntermediateFormatReader.ReadReportSnapshot()
Microsoft.ReportingServices.ReportProcessing.ReportProcessing.DeserializeReportSnapshot(GetReportChunk
getChunkCallback, CreateReportChunk createChunkCallback, GetResource getResourceCallback,
RenderingContext renderingContext, Hashtable& instanceObjects, Hashtable&
definitionObjects, State& declarationsRead, Boolean& isOldSnapshot)
Microsoft.ReportingServices.ReportProcessing.ReportProcessing.RenderSnapshot(IRenderingExtension
renderer, CreateReportChunk createChunkCallback, RenderingContext rc, GetResource
getResourceCallback)
Microsoft.ReportingServices.ReportProcessing.ReportProcessing.RenderSnapshot(CreateReportChunk
createChunkCallback, RenderingContext rc, GetResource getResourceCallback)
Microsoft.ReportingServices.Library.RenderSnapshotAction.Render()
Microsoft.ReportingServices.Library.RSService.RenderFromSessionNoCache(CatalogItemContext
reportContext, ClientRequest session, RenderingResult& result)
Microsoft.ReportingServices.Library.RSService.RenderFromSession(CatalogItemContext
reportContext, ClientRequest session, Warning[]& warnings, ParameterInfoCollection&
effectiveParameters)
Microsoft.ReportingServices.Library.RSService.RenderNext(CatalogItemContext
reportContext, ClientRequest session, Warning[]& warnings, ParameterInfoCollection&
effecectiveParameters, String[]& secondaryStreamNames)
Microsoft.ReportingServices.Library.RenderNextCancelableStep.Execute()
Microsoft.ReportingServices.Diagnostics.CancelablePhaseBase.ExecuteWrapper()
Microsoft.ReportingServices.Library.RenderNextCancelableStep.RenderNext(RSService
rs, CatalogItemContext reportContext, ClientRequest session, JobType type,
Warning[]& warnings, ParameterInfoCollection& effectiveParameters, String[]&
secondaryStreamNames)
Microsoft.ReportingServices.Library.RenderReportAction.Render(String format,
String deviceInfo)
Microsoft.ReportingServices.WebServer.ReportExecutionService.InternalRender(String
Format, String DeviceInfo, Stream& Result, String& Extension, String& MimeType,
String& Encoding, Warning[]& Warnings, String[]& StreamIds)
Microsoft.Reporting.WebForms.ServerReport.InternalRender(String format,
String deviceInfo, String& extension, String& mimeType, String& encoding,
String[]& streams, Warning[]& warnings)
Microsoft.Reporting.WebForms.ServerReport.Render(String format, String deviceInfo,
NameValueCollection additionalParams, String& mimeType, String& extension)
Microsoft.Reporting.WebForms.ServerReportControlSource.RenderReport(String
format, String deviceInfo, NameValueCollection additionalParams, String&
mimeType, String& fileExtension)
Microsoft.Reporting.WebForms.ReportControlSource.RenderReportHTML4(String
deviceInfo)
Microsoft.Reporting.WebForms.ReportControl.RenderReport(ReportControlSource
controlSource, String instanceIdentifier, String navigationID, ZoomMode zoomMode,
Int32 zoomPercent, Int32 pageNumber, String prefix, String searchText, String
linkTarget, String replacementRoot, String alertMessage, Boolean enableFindNext,
Boolean sizeToContent)
Microsoft.Reporting.WebForms.ReportAreaPage.OnPreRender(EventArgs e)
System.Web.UI.Control.PreRenderRecursiveInternal()
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint,
Boolean includeStagesAfterAsyncPoint)
System.Web.UI.Page.ProcessRequest(Boolean includeStagesBeforeAsyncPoint,
Boolean includeStagesAfterAsyncPoint)
System.Web.UI.Page.ProcessRequest()
System.Web.UI.Page.ProcessRequestWithNoAssert(HttpContext context)
System.Web.UI.Page.ProcessRequest(HttpContext context)
System.Web.HttpServerUtility.ExecuteInternal(IHttpHandler handler, TextWriter
writer, Boolean preserveForm, Boolean setPreviousPage, VirtualPath path,
VirtualPath filePath, String physPath, Exception error, String queryStringOverride)
System.Web.HttpServerUtility.Transfer(IHttpHandler handler, Boolean preserveForm)
Microsoft.Reporting.WebForms.ReportAreaPageOperation.PerformOperation(NameValueCollection
urlQuery, HttpResponse response)
Microsoft.Reporting.WebForms.HttpHandler.ProcessRequest(HttpContext context)
System.Web.HttpApplication+CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()
System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)
System.Web.HttpApplication.ResumeSteps(Exception error)
System.Web.HttpApplication.System.Web.IHttpAsyncHandler.BeginProcessRequest(HttpContext
context, AsyncCallback cb, Object extraData)
System.Web.HttpRuntime.ProcessRequestInternal(HttpWorkerRequest wr)
System.Web.HttpRuntime.ProcessRequestNoDemand(HttpWorkerRequest wr)
System.Web.Hosting.ISAPIRuntime.ProcessRequest(IntPtr ecb, Int32 iWRType)
w3wp!library!6!12/04/2007-19:15:56:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException:
An internal error occurred on the report server. See the error log for more
details., un-named assertion fired for component processing;
Info: Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException:
An internal error occurred on the report server. See the error log for more
details.On Apr 12, 4:32 am, Paul Ritchie <REMOVEpritc...@.xtraREMOVE.co.nz>
wrote:
> We have uploaded a report that works fine in development and works OK most
> of the time on the production server but produces this internal error from
> time to time.
> Any advice would be REALLY appreciated.
> cheers,
> Paul.
> w3wp!library!6!12/04/2007-19:15:56:: i INFO: Call to RenderNext( '/CRMTemp_MSCRM/Employee
> Payslip' )
> w3wp!processing!6!12/04/2007-19:15:56:: a ASSERT: Assertion failed! Call
> stack:
> Microsoft.ReportingServices.ReportProcessing.Persistence.IntermediateFormatReader.Assert(Boolean
> condition)
> Microsoft.ReportingServices.ReportProcessing.Persistence.IntermediateFormatReader.RegisterDefinitionObject(IDOwner
> idOwner)
> Microsoft.ReportingServices.ReportProcessing.Persistence.IntermediateFormatReader.ReadReportItemBase(ReportItem
> reportItem)
> Microsoft.ReportingServices.ReportProcessing.Persistence.IntermediateFormatReader.ReadReport(ReportItem
> parent)
> Microsoft.ReportingServices.ReportProcessing.Persistence.IntermediateFormatReader.ReadSubReportInternals(ReportItem
> parent)
> Microsoft.ReportingServices.ReportProcessing.Persistence.IntermediateFormatReader.ReadReportItem(ReportItem
> parent)
> Microsoft.ReportingServices.ReportProcessing.Persistence.IntermediateFormatReader.ReadReportItemList(ReportItem
> parent)
> Microsoft.ReportingServices.ReportProcessing.Persistence.IntermediateFormatReader.ReadReportItemCollection(ReportItem
> parent)
> Microsoft.ReportingServices.ReportProcessing.Persistence.IntermediateFormatReader.ReadListInternals(ReportItem
> parent)
> Microsoft.ReportingServices.ReportProcessing.Persistence.IntermediateFormatReader.ReadDataRegionInternals(ReportItem
> parent)
> Microsoft.ReportingServices.ReportProcessing.Persistence.IntermediateFormatReader.ReadReportItem(ReportItem
> parent)
> Microsoft.ReportingServices.ReportProcessing.Persistence.IntermediateFormatReader.ReadReportItemList(ReportItem
> parent)
> Microsoft.ReportingServices.ReportProcessing.Persistence.IntermediateFormatReader.ReadReportItemCollection(ReportItem
> parent)
> Microsoft.ReportingServices.ReportProcessing.Persistence.IntermediateFormatReader.ReadReport(ReportItem
> parent)
> Microsoft.ReportingServices.ReportProcessing.Persistence.IntermediateFormatReader.ReadReportSnapshot()
> Microsoft.ReportingServices.ReportProcessing.ReportProcessing.DeserializeReportSnapshot(GetReportChunk
> getChunkCallback, CreateReportChunk createChunkCallback, GetResource getResourceCallback,
> RenderingContext renderingContext, Hashtable& instanceObjects, Hashtable&
> definitionObjects, State& declarationsRead, Boolean& isOldSnapshot)
> Microsoft.ReportingServices.ReportProcessing.ReportProcessing.RenderSnapshot(IRenderingExtension
> renderer, CreateReportChunk createChunkCallback, RenderingContext rc, GetResource
> getResourceCallback)
> Microsoft.ReportingServices.ReportProcessing.ReportProcessing.RenderSnapshot(CreateReportChunk
> createChunkCallback, RenderingContext rc, GetResource getResourceCallback)
> Microsoft.ReportingServices.Library.RenderSnapshotAction.Render()
> Microsoft.ReportingServices.Library.RSService.RenderFromSessionNoCache(CatalogItemContext
> reportContext, ClientRequest session, RenderingResult& result)
> Microsoft.ReportingServices.Library.RSService.RenderFromSession(CatalogItemContext
> reportContext, ClientRequest session, Warning[]& warnings, ParameterInfoCollection&
> effectiveParameters)
> Microsoft.ReportingServices.Library.RSService.RenderNext(CatalogItemContext
> reportContext, ClientRequest session, Warning[]& warnings, ParameterInfoCollection&
> effecectiveParameters, String[]& secondaryStreamNames)
> Microsoft.ReportingServices.Library.RenderNextCancelableStep.Execute()
> Microsoft.ReportingServices.Diagnostics.CancelablePhaseBase.ExecuteWrapper()
> Microsoft.ReportingServices.Library.RenderNextCancelableStep.RenderNext(RSService
> rs, CatalogItemContext reportContext, ClientRequest session, JobType type,
> Warning[]& warnings, ParameterInfoCollection& effectiveParameters, String[]&
> secondaryStreamNames)
> Microsoft.ReportingServices.Library.RenderReportAction.Render(String format,
> String deviceInfo)
> Microsoft.ReportingServices.WebServer.ReportExecutionService.InternalRender(String
> Format, String DeviceInfo, Stream& Result, String& Extension, String& MimeType,
> String& Encoding, Warning[]& Warnings, String[]& StreamIds)
> Microsoft.Reporting.WebForms.ServerReport.InternalRender(String format,
> String deviceInfo, String& extension, String& mimeType, String& encoding,
> String[]& streams, Warning[]& warnings)
> Microsoft.Reporting.WebForms.ServerReport.Render(String format, String deviceInfo,
> NameValueCollection additionalParams, String& mimeType, String& extension)
> Microsoft.Reporting.WebForms.ServerReportControlSource.RenderReport(String
> format, String deviceInfo, NameValueCollection additionalParams, String&
> mimeType, String& fileExtension)
> Microsoft.Reporting.WebForms.ReportControlSource.RenderReportHTML4(String
> deviceInfo)
> Microsoft.Reporting.WebForms.ReportControl.RenderReport(ReportControlSource
> controlSource, String instanceIdentifier, String navigationID, ZoomMode zoomMode,
> Int32 zoomPercent, Int32 pageNumber, String prefix, String searchText, String
> linkTarget, String replacementRoot, String alertMessage, Boolean enableFindNext,
> Boolean sizeToContent)
> Microsoft.Reporting.WebForms.ReportAreaPage.OnPreRender(EventArgs e)
> System.Web.UI.Control.PreRenderRecursiveInternal()
> System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint,
> Boolean includeStagesAfterAsyncPoint)
> System.Web.UI.Page.ProcessRequest(Boolean includeStagesBeforeAsyncPoint,
> Boolean includeStagesAfterAsyncPoint)
> System.Web.UI.Page.ProcessRequest()
> System.Web.UI.Page.ProcessRequestWithNoAssert(HttpContext context)
> System.Web.UI.Page.ProcessRequest(HttpContext context)
> System.Web.HttpServerUtility.ExecuteInternal(IHttpHandler handler, TextWriter
> writer, Boolean preserveForm, Boolean setPreviousPage, VirtualPath path,
> VirtualPath filePath, String physPath, Exception error, String queryStringOverride)
> System.Web.HttpServerUtility.Transfer(IHttpHandler handler, Boolean preserveForm)
> Microsoft.Reporting.WebForms.ReportAreaPageOperation.PerformOperation(NameValueCollection
> urlQuery, HttpResponse response)
> Microsoft.Reporting.WebForms.HttpHandler.ProcessRequest(HttpContext context)
> System.Web.HttpApplication+CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()
> System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)
> System.Web.HttpApplication.ResumeSteps(Exception error)
> System.Web.HttpApplication.System.Web.IHttpAsyncHandler.BeginProcessRequest(HttpContext
> context, AsyncCallback cb, Object extraData)
> System.Web.HttpRuntime.ProcessRequestInternal(HttpWorkerRequest wr)
> System.Web.HttpRuntime.ProcessRequestNoDemand(HttpWorkerRequest wr)
> System.Web.Hosting.ISAPIRuntime.ProcessRequest(IntPtr ecb, Int32 iWRType)
> w3wp!library!6!12/04/2007-19:15:56:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException:
> An internal error occurred on the report server. See the error log for more
> details., un-named assertion fired for component processing;
> Info: Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException:
> An internal error occurred on the report server. See the error log for more
> details.
This link MS might be helpful:
http://support.microsoft.com/default.aspx/kb/895980/en-us
Regards,
Enrique Martinez
Sr. Software Consultant|||We have the same problem. This is really annoying. Have anyone found a solution for this problem?
Any - really any help would be useful.
EggHeadCafe.com - .NET Developer Portal of Choice
http://www.eggheadcafe.com

Anoter Alternative

There is another alternative ... if you are using MSCRM 3.0

follow this steps

1. Navigate to your report manager --> //<<Server Name>>/reports

2. Navigate to your datasource --> HOME > <<Datasource>>

3. At the top tab select Properties

4. Make sure that there is a user role call "NT AUTHORITY\NETWORK SERVICE" thus apply the appropiate roles for it

Vula....!!!!!

Another alternative to what?

Anonymous access...

I want my report be accessible for everybody. So i enable anonymous access
(IUSR_MACHINE) on the virtual site ReportServer.
On my report, i allow IUSR_MACHINE the Browser Role.
Administrators have all Role on my report.
When i want to acces to the admin page (http://localhost/Reports), I have a
window authentification (like I want), I put my Admin login and pass but I
have not the Administrators rigth. I can only see what the IUSR_MACHINE as
right.
When I disable anonymous acces on ReportServer, I can see everything.
Can you help me 'Karine,
The Report Server itself does not authenticate the user. Instead, it obtains
the user identity from IIS. When Anonymous is enabled, all requests to the
Report Server will come in under the IUSR_<computer name> account or
whatever account you use for Anonymous access. Therefore, the Report Server
cannot differentiate the users.
Since you will need to manage the Report Server you will need to elevate
IUSR permissions in RS or assign it to a Windows group which has management
rights. Both approaches are equally bad from security standpoint and a sure
invitation for a security hack. Besides, Anonymous access is not officially
supported.
--
Hope this helps.
---
Teo Lachev, MVP [SQL Server], MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
---
"Karine" <Karine@.discussions.microsoft.com> wrote in message
news:49C55288-1B45-44BB-870D-CBDF84420D72@.microsoft.com...
> I want my report be accessible for everybody. So i enable anonymous access
> (IUSR_MACHINE) on the virtual site ReportServer.
> On my report, i allow IUSR_MACHINE the Browser Role.
> Administrators have all Role on my report.
> When i want to acces to the admin page (http://localhost/Reports), I have
a
> window authentification (like I want), I put my Admin login and pass but I
> have not the Administrators rigth. I can only see what the IUSR_MACHINE as
> right.
> When I disable anonymous acces on ReportServer, I can see everything.
> Can you help me '
>
>
>

Annoying feature - report service rewrite query automatically.

This feature is very annoying, report service 2k rewrites my query, even the
format of query is changed too. How to disable this feature?
Thanks really a lot.Use the generic query designer (two pane) instead of the graphical query
designer. The button to switch to it is one of the buttons to the right of
the ...
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"qye2020" <qye2020@.yahoo.com> wrote in message
news:uz0EglpJGHA.2088@.TK2MSFTNGP11.phx.gbl...
> This feature is very annoying, report service 2k rewrites my query, even
> the format of query is changed too. How to disable this feature?
> Thanks really a lot.
>|||Wonderful, problem is gone with your kind answer.
Thanks a lot.
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:ewOcV9pJGHA.3176@.TK2MSFTNGP12.phx.gbl...
> Use the generic query designer (two pane) instead of the graphical query
> designer. The button to switch to it is one of the buttons to the right of
> the ...
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "qye2020" <qye2020@.yahoo.com> wrote in message
> news:uz0EglpJGHA.2088@.TK2MSFTNGP11.phx.gbl...
>> This feature is very annoying, report service 2k rewrites my query, even
>> the format of query is changed too. How to disable this feature?
>> Thanks really a lot.
>

Annoying behavior in report designer

I have a report with a complex dataset query that involves a temp table. Anytime I make a change to that query and save or change tabs, I get this message:

Could not generate a list of fields for the query.
Invalid object name #[temp table name]. (Error 208)

Fair enough, but the real nuisance is that every time this happens it erases all of the dataset parameters I have previously set up. I have to redefine them every time.

I hope this behavior changes in the future.

I'm using technique like this:

IF (OBJECT_ID('#temp') IS NOT NULL)
DROP TABLE #temp
...
SELECT * from Table INTO #temp

|||

I assume you mean the SELECT INTO would avoid this behavior? Unfortunately I don't think that's an option for me as I'm retrieving the data set for the table from a stored procedure, i.e.

INSERT #temp
EXEC GetTempRecords @.Param1

|||As far as I know, INSERT does not create table, but SELECT INTO does

Annoying behavior in report designer

I have a report with a complex dataset query that involves a temp table. Anytime I make a change to that query and save or change tabs, I get this message:

Could not generate a list of fields for the query.
Invalid object name #[temp table name]. (Error 208)

Fair enough, but the real nuisance is that every time this happens it erases all of the dataset parameters I have previously set up. I have to redefine them every time.

I hope this behavior changes in the future.

I'm using technique like this:

IF (OBJECT_ID('#temp') IS NOT NULL)
DROP TABLE #temp
...
SELECT * from Table INTO #temp

|||

I assume you mean the SELECT INTO would avoid this behavior? Unfortunately I don't think that's an option for me as I'm retrieving the data set for the table from a stored procedure, i.e.

INSERT #temp
EXEC GetTempRecords @.Param1

|||As far as I know, INSERT does not create table, but SELECT INTO does

Animated Gif

Okay, I'm trying to be a bit flash.

Is it possible to add an animated gif to a Report or is this restricted to still images only?

Thanks!

Batty

You can add it but it will be presented as static not animated.

Anchoring textbox to a page.

Hi,

In our project I have a matrix report. In that I need to anchor a textbox to the right end of the page, so that the textbox will always be at the right end of the report/page.

I also need to place the textbox at the page header. (but I am also open to solution / workarounds which does not include page header!!)

Thanks in advance

Arun.

any ideas?|||

If you just place a new textbox to the right hand side of your matrix control this will always display at the right hand side of the information displayed, also that textbox can live in the page header.....

Unless you are after something more complex it should be that simple, please provide more info if this is not quite what you are looking for.

|||

thanks for the reply,that works..

but we need soemthing more!!

| textbox | <-- textbox alligned here.

--|

Matrix |

|

--|

we need the matix report and the textbox right aligned to the right margin of the page. ( the textbox's right edge and the matrix right edge edge should align).

thanks in advance...

Arun

|||within the properties page of the matrix control you have an option to grow content right to left or left to right, the default is left to right so change this and it may give you what you are looking for, Otherwise you would have to code the position based on screen width less size of control but this is another task all together, I would assume the property should fix you issues, then the textbox place will be pushed out by the matrix.|||

sorry Andy.. that too doesnt work.. it give a mirror image of the matix that is what we dont need!!..

any other ideas?

thanks

arun

|||

Hi

After much time trying to get this to happen unfortuantely there is no automated way to right align your matrix automatically, It is certianly something i would like to see MS put in as a feature as i would find it useful, The onyl thing you can do is to place the matrix as far right as possible within the designer.

Sorry I dont have a better solution for you here.

Andy

|||

thanks Andy. i would also like to see that in SSRS as a feature!!..

for now i will try to anchor the textbox using some expression..

mean while if anyone comeacross any other ideas please keep me posted!!

thanks

Arun

Analysis Services vs Report Models

What are the differences beetwen AS and RM?
KamelAS is much bigger and its a Datawarehousing tool. RM is just to create adhoc
queries. You can see RM as a tool you can give it to your top management so
that they can create reports on the fly. by giving user friendly columns even
top management can create reports they need. AS on the otherhand will arrange
data in such a way that viewing data pertaining to previous years are fairly
simple.
More search on the "sql server books 2005 online."
Amarnath
"kamel" wrote:
> What are the differences beetwen AS and RM?
> Kamel
>|||Thank you!
But still I can create Report Model from AS, so what's the difference?
Kamel

Analysis Services Rolling Date reports

I have a Time and Billing OLAP cube which I'm running RS against. I'm
stumped on a date issue.
How can I run a report which selects the last 7 days worth of data and rolls
forward each week? I've tried using an MDX query with the Tail funtion.
This pulls the last date which has data in it. (Effectively yesterday's
date) When I run it in MDX builder it gives me the desired results. However
when I use it in VS.Net to build my report, it pulls the last 7 days over,
but it's static and doesn't roll forward.
I can make a report with a date drop down parameter which is described in
the AS and RS article on Technet, but I'd really like the parameter to be
automatically selected based off of the date when the report is run.
My date formats in my cube are [Year].[Quarter].[Month].[Day]
Can anybody help me with this?
Thanks, MattTry setting defaults for your start and end date parameters. For
example, you can set the end date to
DateTime.Now.AddDays(-1).ToString("MM/dd/yyyy") and the start date to
DateTime.Now.AddDays(-7).ToString("MM/dd/yyyy"). When you see the
parameters in subscription creation, it may look like they are
hard-coded, but they aren't as long as the default checkbox is selected.|||Thanks for the suggestion... I think I'm close...
The problem is that my date needs to be in a [Time].[FY Calendar].[All
Time].[Year].[Quarter].[Month].[Day] format.
As you probably can tell, I'm a newbie to VB Scripting. How can I convert
your suggestion "DateTime.Now.AddDays(-1).ToString("MM/dd/yyyy")" to my cube
format? I think if I can do that, I can get it to work.
Thanks,
Matt
"Kenny" wrote:
> Try setting defaults for your start and end date parameters. For
> example, you can set the end date to
> DateTime.Now.AddDays(-1).ToString("MM/dd/yyyy") and the start date to
> DateTime.Now.AddDays(-7).ToString("MM/dd/yyyy"). When you see the
> parameters in subscription creation, it may look like they are
> hard-coded, but they aren't as long as the default checkbox is selected.
>

Analysis Services 2005 Cubes

Hi,

I had designed the DW database like

1. For each report one fact and some dimensions are created

2. Now i want to create a cube. while creating a cube i got some doubts. i want to know that what is the better way to create a cube

2.1 Whether i have to use one cube for one report

or

2.2 Whether i have to create only one cube and i have to create different measures

Doubts:

1. If i go to option 2.1 then it takes more memory? (because each cube stores the data in structres)

2. if i go to option 2.2 then it hits performance? (because several users access several reports using the same cube)

Can you please help me in this.

Thanks

Dinesh

If you have common structures between your reports you will probably get much better performance and memory useage from haveing multiple measures in a single cube rather than a cube per report. The indexes and caching could all be shared between the measures increasing the performance.|||Thanks.