"Slipt" rows based on datetime

Hello!

I have a table that, among other columns, has two datetime columns which indicate the initial and the final time. This would be an exemple of data in this table:

row1:

initial_time: 2006-05-24 8:00:00

final_time: 2006-05-24 8:30:00

row2:

initial_time: 2006-05-24 8:35:00

final_time: 2006-05-24 9:15:00

I would like to split a row in two new rows if final time's hour is different of initial time's hour, so I would like to split row2 into:

row2_a:

initial_time: 2006-05-24 8:35:00

initial_time: 2006-05-24 8:59:59

row2_b:

initial_time: 2006-05-24 9:00:00

initial_time: 2006-05-24 9:15:00

Is it possible to do it in a query, I mean, without using procedures?

Thank you!

? It gets a bit complex, but here's one way: create table #table ( initial_time datetime, final_time datetime) insert #table values( '2006-05-24 8:00:00', '2006-05-24 8:30:00') insert #table values( '2006-05-24 8:35:00', '2006-05-24 9:15:00') SELECT CASE x.N WHEN 0 THEN T.initial_time WHEN 1 THEN DATEADD(ms, ((DATEPART(minute, T.final_time) * 60000) + (DATEPART(second, T.final_time) * 1000) + DATEPART(millisecond, T.final_time)) * -1, T.final_time) END AS initial_time, CASE x.N WHEN 0 THEN CASE DATEDIFF(hour, T.initial_time, T.final_time) WHEN 0 THEN T.final_time WHEN 1 THEN DATEADD(millisecond, -3, DATEADD(millisecond, ((DATEPART(minute, T.final_time) * 60000) + (DATEPART(second, T.final_time) * 1000) + DATEPART(millisecond, T.final_time)) * -1, T.final_time)) END WHEN 1 THEN T.final_time END AS final_timeFROM #table TJOIN( SELECT 0 UNION ALL SELECT 1) x (N) ON x.N <= DATEDIFF(hour, T.initial_time, T.final_time) drop table #tablego -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <AnaC@.discussions.microsoft..com> wrote in message news:bdb330d8-5a34-409c-aaf3-57044628fc13@.discussions.microsoft.com... Hello! I have a table that, among other columns, has two datetime columns which indicate the initial and the final time. This would be an exemple of data in this table: row1: initial_time: 2006-05-24 8:00:00 final_time: 2006-05-24 8:30:00 row2: initial_time: 2006-05-24 8:35:00 final_time: 2006-05-24 9:15:00 I would like to split a row in two new rows if final time's hour is different of initial time's hour, so I would like to split row2 into: row2_a: initial_time: 2006-05-24 8:35:00 initial_time: 2006-05-24 8:59:59 row2_b: initial_time: 2006-05-24 9:00:00 initial_time: 2006-05-24 9:15:00 Is it possible to do it in a query, I mean, without using procedures? Thank you!|||Thank you!

"OnGroupChange" event?

I find that I regularly need to evaluate all the rows in "data groups" within the pipeline and execute script based transform logic for each grouping (aggregate transform does not provide script access to the data).

I do this in script by sorting the pipelined data on some key columns to define the groupings, caching the key values as the rows pass through the ProcessInputRow procedure, and running logic that compares the current row's key values as each row passes through the transform with the previous row's key values. When they differ I know I have a new group (special casing for the very first row in the pipleine), so I perform my transforms with cached data from the previous group .

This works but I am thinking this must be a common "pattern". Also, I have experience with a competing ETL tool which has "built in" support for script processing of user defined data groupings. That product provides an "OnChange" event, where the user defines the key in the GUI (can be composite), and the event provides a container to hold script (with access to the row data values) to run when the event "fires"(any values change in the key vs. the previous). Of course the data stream again has to be sorted for this to work.

Anyone else regularly have a need to do this type of processing? If so perhaps it is a good candidate for a custom component? Or a suggestion to Microsoft to enhance the aggregate component for the next SSIS version to provide a script "hook" in the aggregate transform? Anyone else regularly have this type of processing requirement?

Ken

Yeah, this would be a useful feature, but it looks like someone will have to write a custom component!

"Current day" MDX

Hi,

is there any "update" on how to do the "current day" as a set in MDX with SQL 2005 based on the actual system date?

If you don't want to base that on the system date is this still the "best practice" to introduce some "flags" in the time dimension to identifiy the actual day, last week, ... Just the way it was introduced with the BI Accelerator tool...

Thanks,

It's an interesting question...

I can't actually remember how SSABI did its current time periods, but from what you say I guess it created sets which filtered on a member property value such as 'Is Current Day'. That should still work, but my feeling is that in AS2005 it might be better to create relative time attributes. So, for example, if you had Year, Quarter and Month attributes you would add Relative Year, Relative Quarter and Relative Month attributes too. These would have members on them such as 'Current Month', 'Current Month - 1', 'Current Month - 2' etc. This would allow users to create much more sophisticated relative time period reports, and at the same time still see the actual dates involved if they crossjoined the actual time period attributes with the relative time period attributes because autoexists would automatically do filter out everything but the correct combinations.

Incidentally, using the NOW() function to find the current date is a bad idea in AS2005 because as far as I can see, server-defined sets are now evaluated at processing time. So for example, add the following set to Adventure Works:

create set currentcube.test as strtoset("{[Date].[Calendar Year].&[" + cstr(cint(mid(cstr(now()),7,4))-2) + "]}");

If you run a query which shows the contents of this set, you should see the CY2004 member returned. However if you change the system date of your machine to be 2005 instead of 2006, then rerun the same query, you'll see that CY2004 is still returned. You need to reprocess the cube ('Process Script Cache' seems to be sufficient) to get CY2003 to appear in your query.

Chris

|||

Chris, I think your suggestion is good. However, it does have some rather nasty processing implications, since you have to reprocess your time dimension each day (if the granularity of your time dimension is date). A "Process Update" is sufficient of course, but this method invalidates (and therefore removes) all aggregations that include attributes from the time dimension. You are therefore forced into doing a "Process Index" on all your measure groups (except those that might not contain the time dimension) to get the aggregations back online.

It puzzles me that named sets should be evaluated at processing time?! As far as I know they are evaluated the first time they are requested by a query in a given session. The set is then cached until the session expires or the set is removed by the DROP SET statement. I have not verified that this is true for server-defined sets, though...

Anyway, using named sets for dynamic time, should in my opinion be one of the best approaches. Using Chris' suggestion allows you to define a single named set "Current Day", which you can base a number of other sets on. For instance, having created the set "Current Day", you can easily create the set "Current Month" by using the Exists function:

CREATE SET CURRENTCUBE.[Current Month] AS Exists([Date].[Calendar].[Month],[Current Day])

... and so on...

If you are using a front-end tool, make sure that it supports the use of named sets. If not, you have to create a calculated member that aggregates across the named set, but this approach has quite a few disadvantages (one of which is that a calculated member does not establish current cube context).

|||

True, the processing overhead could be pretty nasty depending on your cube.

Re sets, you can test out the new behaviour on Adventure Works as follows:

Create the following server-side set

create set currentcube.test as topcount([Date].[Date].[Date].members,10, measures.[internet sales amount]);

|||

Yes, I can see that the two queries return the same set. I don't think the example proves your point, though, since the named set under no circumstances is evaluated in the context of the query, which is why the WHERE clause has no effect on the set of dates returned.

I have also tested the fact that the named set is evaluated at processing time. This is actually not the case. It is evaluated the first time it is requested in a query. This can be verified (following the example in a previous post) by doing a process, changing the system date and then running the query, in which case the returned set will accurately reflect the new system date. Anyway, you are absolutely right that the content of the named set does not change until the cube is reprocessed. I wonder if this behavior can be changed? For the sake of dynamic time, however, it doesn't really matter too much. If only you process a part of your cube database once a day (after midnight), you should be good to go.

|||

You're right about the sets - sorry, my mistake. I think I was getting confused with a slightly different issue which is that the same set can return different results depending on where you put it in the MDX Script. Here's an example in Adventure Works: if you add the following onto the end of your MDX Script -

create set currentcube.test1 as order([Customer].[Education].[Education].members, measures.[internet sales amount], bdesc);

(measures.[internet sales amount], [Customer].[Education].&[Bachelors])=0;

create set currentcube.test2 as order([Customer].[Education].[Education].members, measures.[internet sales amount], bdesc);

Then run queries showing the contents of test1 and test2, you can see that they return different results - test2 accurately reflects the change made by the assignment. This of course isn't inconsistent with the set being evaluated the first time it's queried (presumably the results are then stored in the 'script cache' I was processing), just that it's correctly evaluated in the context of the script.

Anyway, I've had an idea on how to have a relative time dimension of the type I've described without incurring any of the processing penalties. What might work (and I need to test this) would be to create a separate Relative Time dimension then add it to your cube with *no* relationship to any measure group; you could then use MDX Script assignments to map the members on it to the equivalent members on the real time dimension. Definitely worth investigating...

Chris

|||Good thinking! I would be very interested in knowing how you accomplish this, as I have already tried to implement your suggestion (without success). |||

Here's a 'proof of concept' version:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=348468&SiteID=1

I'd be interested to hear if anyone actually tries this, and if they have any suggestions for improvements.

Chris

|||That link appears to point back to this thread, but I would be extremely interested in seeing your proof of concept code. I didn't know it was possible to dynamically link dimensions at run time, but that would solve for me a number of very difficult issues I am struggling with.|||

I have seen two other ways of implementing the current day.

Never have members in the time dimension after the current date. Today it is Aug 22 so never let at time member enter the dimension after this date. With this solution you can look for the last time member in MDX by lastchild.lastchild and so on. In this case it is the ETL process and SSIS that manage the time dimension.

Another solution is to look for a measure in the cube that you know will reflect the current day or the day before the current day. Actual Sales is a good candidate if budget sales is entered for the full year in advance. In this case you can use Tail with Filter, in MDX, to look for the last non empty member. If this set works you can add Lag(MDX) och Lead(MDX) to your first named set, together with Lead(MDX).

If you buy "Fast track to MDX", Second edition, you can read a discussion about these methods and a third one, recursion.

Regards

Thomas Ivarsson

|||

I use this technique as it supports dates beyond today.

In ETL, I select the min and max trx date keys (a bit more involved if multiple fact tables which I have) from the fact table and store them in a one row table called DIM_DAY_RANGE. Then use the following where clause for your DAY_DIM view

create view v_active_day_dim as
select * from
FROM dbo.DAY_DIM
WHERE DAY_KEY BETWEEN (SELECT minDayKey FROM dbo.DAY_DIM_RANGE) AND
(SELECT maxDayKey FROM dbo.DAY_DIM_RANGE)

The same technique applies to active products and customers and other dimensions. Makes the cube smaller and only used dimension keys are diplayed.

|||

Perhaps it's good to know some background...

I need this information ("today") because I need to build up reports which show "todays" Orders, Revenue or whatever. The users should do nothing but open the report, so I need something like a set to use instead of a fixed day or something what a user has to select.

Using the approach to "just offer what you have" is OK in many cases, however what do you do if you not only have actual sales but also your planned sales? Then you have to be able to show all days (or months) of the current year or also the next year... So this will not help you much...

|||

Quote:"what do you do if you not only have actual sales but also your planned sales". Do you have a version dimension like Actual, Budget, Planned, Forecast ? Or is this in the measures like, ActualSales, BudgetSales, Planned Sales, ForecastSales?

/Thomas

|||

Have a look here: http://support.dspanel.com/help43/Web_Part/Examples/MDX_Examples.htm

Regards

Thomas Ivarsson

|||

Thomas,

that's basically what Chris posted before... I used that for my solution as well because I'll have daily reprocesses of the cube... But it's a nice page with quite some useful stuff...

"Current day" MDX

Hi,

is there any "update" on how to do the "current day" as a set in MDX with SQL 2005 based on the actual system date?

If you don't want to base that on the system date is this still the "best practice" to introduce some "flags" in the time dimension to identifiy the actual day, last week, ... Just the way it was introduced with the BI Accelerator tool...

Thanks,

It's an interesting question...

I can't actually remember how SSABI did its current time periods, but from what you say I guess it created sets which filtered on a member property value such as 'Is Current Day'. That should still work, but my feeling is that in AS2005 it might be better to create relative time attributes. So, for example, if you had Year, Quarter and Month attributes you would add Relative Year, Relative Quarter and Relative Month attributes too. These would have members on them such as 'Current Month', 'Current Month - 1', 'Current Month - 2' etc. This would allow users to create much more sophisticated relative time period reports, and at the same time still see the actual dates involved if they crossjoined the actual time period attributes with the relative time period attributes because autoexists would automatically do filter out everything but the correct combinations.

Incidentally, using the NOW() function to find the current date is a bad idea in AS2005 because as far as I can see, server-defined sets are now evaluated at processing time. So for example, add the following set to Adventure Works:

create set currentcube.test as strtoset("{[Date].[Calendar Year].&[" + cstr(cint(mid(cstr(now()),7,4))-2) + "]}");

If you run a query which shows the contents of this set, you should see the CY2004 member returned. However if you change the system date of your machine to be 2005 instead of 2006, then rerun the same query, you'll see that CY2004 is still returned. You need to reprocess the cube ('Process Script Cache' seems to be sufficient) to get CY2003 to appear in your query.

Chris

|||

Chris, I think your suggestion is good. However, it does have some rather nasty processing implications, since you have to reprocess your time dimension each day (if the granularity of your time dimension is date). A "Process Update" is sufficient of course, but this method invalidates (and therefore removes) all aggregations that include attributes from the time dimension. You are therefore forced into doing a "Process Index" on all your measure groups (except those that might not contain the time dimension) to get the aggregations back online.

It puzzles me that named sets should be evaluated at processing time?! As far as I know they are evaluated the first time they are requested by a query in a given session. The set is then cached until the session expires or the set is removed by the DROP SET statement. I have not verified that this is true for server-defined sets, though...

Anyway, using named sets for dynamic time, should in my opinion be one of the best approaches. Using Chris' suggestion allows you to define a single named set "Current Day", which you can base a number of other sets on. For instance, having created the set "Current Day", you can easily create the set "Current Month" by using the Exists function:

CREATE SET CURRENTCUBE.[Current Month] AS Exists([Date].[Calendar].[Month],[Current Day])

... and so on...

If you are using a front-end tool, make sure that it supports the use of named sets. If not, you have to create a calculated member that aggregates across the named set, but this approach has quite a few disadvantages (one of which is that a calculated member does not establish current cube context).

|||

True, the processing overhead could be pretty nasty depending on your cube.

Re sets, you can test out the new behaviour on Adventure Works as follows:

Create the following server-side set

create set currentcube.test as topcount([Date].[Date].[Date].members,10, measures.[internet sales amount]);

|||

Yes, I can see that the two queries return the same set. I don't think the example proves your point, though, since the named set under no circumstances is evaluated in the context of the query, which is why the WHERE clause has no effect on the set of dates returned.

I have also tested the fact that the named set is evaluated at processing time. This is actually not the case. It is evaluated the first time it is requested in a query. This can be verified (following the example in a previous post) by doing a process, changing the system date and then running the query, in which case the returned set will accurately reflect the new system date. Anyway, you are absolutely right that the content of the named set does not change until the cube is reprocessed. I wonder if this behavior can be changed? For the sake of dynamic time, however, it doesn't really matter too much. If only you process a part of your cube database once a day (after midnight), you should be good to go.

|||

You're right about the sets - sorry, my mistake. I think I was getting confused with a slightly different issue which is that the same set can return different results depending on where you put it in the MDX Script. Here's an example in Adventure Works: if you add the following onto the end of your MDX Script -

create set currentcube.test1 as order([Customer].[Education].[Education].members, measures.[internet sales amount], bdesc);

(measures.[internet sales amount], [Customer].[Education].&[Bachelors])=0;

create set currentcube.test2 as order([Customer].[Education].[Education].members, measures.[internet sales amount], bdesc);

Then run queries showing the contents of test1 and test2, you can see that they return different results - test2 accurately reflects the change made by the assignment. This of course isn't inconsistent with the set being evaluated the first time it's queried (presumably the results are then stored in the 'script cache' I was processing), just that it's correctly evaluated in the context of the script.

Anyway, I've had an idea on how to have a relative time dimension of the type I've described without incurring any of the processing penalties. What might work (and I need to test this) would be to create a separate Relative Time dimension then add it to your cube with *no* relationship to any measure group; you could then use MDX Script assignments to map the members on it to the equivalent members on the real time dimension. Definitely worth investigating...

Chris

|||Good thinking! I would be very interested in knowing how you accomplish this, as I have already tried to implement your suggestion (without success). |||

Here's a 'proof of concept' version:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=348468&SiteID=1

I'd be interested to hear if anyone actually tries this, and if they have any suggestions for improvements.

Chris

|||That link appears to point back to this thread, but I would be extremely interested in seeing your proof of concept code. I didn't know it was possible to dynamically link dimensions at run time, but that would solve for me a number of very difficult issues I am struggling with.|||

I have seen two other ways of implementing the current day.

Never have members in the time dimension after the current date. Today it is Aug 22 so never let at time member enter the dimension after this date. With this solution you can look for the last time member in MDX by lastchild.lastchild and so on. In this case it is the ETL process and SSIS that manage the time dimension.

Another solution is to look for a measure in the cube that you know will reflect the current day or the day before the current day. Actual Sales is a good candidate if budget sales is entered for the full year in advance. In this case you can use Tail with Filter, in MDX, to look for the last non empty member. If this set works you can add Lag(MDX) och Lead(MDX) to your first named set, together with Lead(MDX).

If you buy "Fast track to MDX", Second edition, you can read a discussion about these methods and a third one, recursion.

Regards

Thomas Ivarsson

|||

I use this technique as it supports dates beyond today.

In ETL, I select the min and max trx date keys (a bit more involved if multiple fact tables which I have) from the fact table and store them in a one row table called DIM_DAY_RANGE. Then use the following where clause for your DAY_DIM view

create view v_active_day_dim as
select * from
FROM dbo.DAY_DIM
WHERE DAY_KEY BETWEEN (SELECT minDayKey FROM dbo.DAY_DIM_RANGE) AND
(SELECT maxDayKey FROM dbo.DAY_DIM_RANGE)

The same technique applies to active products and customers and other dimensions. Makes the cube smaller and only used dimension keys are diplayed.

|||

Perhaps it's good to know some background...

I need this information ("today") because I need to build up reports which show "todays" Orders, Revenue or whatever. The users should do nothing but open the report, so I need something like a set to use instead of a fixed day or something what a user has to select.

Using the approach to "just offer what you have" is OK in many cases, however what do you do if you not only have actual sales but also your planned sales? Then you have to be able to show all days (or months) of the current year or also the next year... So this will not help you much...

|||

Quote:"what do you do if you not only have actual sales but also your planned sales". Do you have a version dimension like Actual, Budget, Planned, Forecast ? Or is this in the measures like, ActualSales, BudgetSales, Planned Sales, ForecastSales?

/Thomas

|||

Have a look here: http://support.dspanel.com/help43/Web_Part/Examples/MDX_Examples.htm

Regards

Thomas Ivarsson

|||

Thomas,

that's basically what Chris posted before... I used that for my solution as well because I'll have daily reprocesses of the cube... But it's a nice page with quite some useful stuff...

#Error when printing report

I have a field on a Group Footer which is a Sum field based of an IIF statement.

Here it is.

=Sum(IIF(Fields!EXPR1.Value=2 and Fields!Type.Value=1,Fields!Quantity.Value,0))

If I do not use the AND, by by only checking on one field it works fine and it returns a result, however if I use the AND Operator I get the #Error when previewing the report.

Does anyone have any idea why this is happening. Any suggestion would help/

THanks

Can you try to get more information about the #Error? The output window should have something listed about your error.

Jarret

|||

I figured this one out.

I used the cddl to convert the values to double values.

Thanks

### I think its a bug ### - Export to excel

Hi:
I have a report with 2 tables in it, both have drillthroughs. I need to
toggle the visibility between the two tables based on the report parameters,
and hence have an expression in the visibility of the tables properties.
1) Loss of data when exported to excel (sublevels not exported)
The problem caused with this is that when I export to excel the
drillthroughs whihc usually get exported as levels in excel are not being
exported. I remove the visibility expression in the table it works fine.
2) When the visibilty function is present even though only one table is
visible in the report, when i export it i see both the tables. This is a
little inconvenient but fine because it was excess data. But definetly 1 is
an issue that needs to be dealt with.
For now I'll just create two separate reports, or have the second table in a
subreport.
ThanksDo you really have toggles in your report or you just conditinally hide
items based on report parameters?
Could you send us the report, please?
--
Nico Cristache [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"NI" <NI@.discussions.microsoft.com> wrote in message
news:3DE5068D-E7F9-41A3-8CC1-1405F20B6277@.microsoft.com...
> Hi:
> I have a report with 2 tables in it, both have drillthroughs. I need to
> toggle the visibility between the two tables based on the report
parameters,
> and hence have an expression in the visibility of the tables properties.
> 1) Loss of data when exported to excel (sublevels not exported)
> The problem caused with this is that when I export to excel the
> drillthroughs whihc usually get exported as levels in excel are not being
> exported. I remove the visibility expression in the table it works fine.
> 2) When the visibilty function is present even though only one table is
> visible in the report, when i export it i see both the tables. This is a
> little inconvenient but fine because it was excess data. But definetly 1
is
> an issue that needs to be dealt with.
> For now I'll just create two separate reports, or have the second table in
a
> subreport.
> Thanks
>
>|||Hi Nico:
I conditionally hide the 2 tables, based on a parameter. This puts in this
section of code under
<Table>
<Visibility>
<Hidden>=IIF(Parameters!RP_SELECT.Value=2,false,true)</Hidden>
</Visibility>
</Table>
If I remove that visibility section they export fine to excel. If it remains
the sub levels do not export.
Thanks
PS: Couldnt find an option to attach here, and hence I inlined only a part
of the report. Hope this helps.
--
<Table Name="table1">
<Height>1.19669in</Height>
<ZIndex>2</ZIndex>
<Style>
<BackgroundColor>White</BackgroundColor>
<BorderStyle>
<Top>None</Top>
</BorderStyle>
</Style>
<Header>
<TableRows>
<TableRow>
<Height>0.19669in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox7">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>LightGrey</BackgroundColor>
<BorderWidth>
<Bottom>2pt</Bottom>
<Top>2pt</Top>
</BorderWidth>
<BorderColor>
<Bottom>DarkRed</Bottom>
<Top>Black</Top>
</BorderColor>
<BorderStyle>
<Bottom>Solid</Bottom>
<Top>Solid</Top>
</BorderStyle>
<TextAlign>Left</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>19</ZIndex>
<CanGrow>true</CanGrow>
<Value>PROJECT</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox8">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>LightGrey</BackgroundColor>
<BorderWidth>
<Bottom>2pt</Bottom>
<Top>2pt</Top>
</BorderWidth>
<BorderColor>
<Bottom>DarkRed</Bottom>
<Top>Black</Top>
</BorderColor>
<BorderStyle>
<Bottom>Solid</Bottom>
<Top>Solid</Top>
</BorderStyle>
<TextAlign>Left</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>18</ZIndex>
<CanGrow>true</CanGrow>
<Value>ACTIVITY HOURS</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox10">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>LightGrey</BackgroundColor>
<BorderWidth>
<Bottom>2pt</Bottom>
<Top>2pt</Top>
</BorderWidth>
<BorderColor>
<Bottom>DarkRed</Bottom>
<Top>Black</Top>
</BorderColor>
<BorderStyle>
<Bottom>Solid</Bottom>
<Top>Solid</Top>
</BorderStyle>
<TextAlign>Left</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>17</ZIndex>
<CanGrow>true</CanGrow>
<Value>GROUP</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox12">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>LightGrey</BackgroundColor>
<BorderWidth>
<Bottom>2pt</Bottom>
<Top>2pt</Top>
</BorderWidth>
<BorderColor>
<Bottom>DarkRed</Bottom>
<Top>Black</Top>
</BorderColor>
<BorderStyle>
<Bottom>Solid</Bottom>
<Top>Solid</Top>
</BorderStyle>
<TextAlign>Center</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>900</FontWeight>
</Style>
<ZIndex>16</ZIndex>
<CanGrow>true</CanGrow>
<Value>TOTAL HOURS</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox18">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>LightCyan</BackgroundColor>
<BorderWidth>
<Bottom>2pt</Bottom>
<Top>2pt</Top>
</BorderWidth>
<BorderColor>
<Bottom>DarkRed</Bottom>
<Top>Black</Top>
</BorderColor>
<BorderStyle>
<Bottom>Solid</Bottom>
<Top>Solid</Top>
</BorderStyle>
<TextAlign>Left</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>15</ZIndex>
<CanGrow>true</CanGrow>
<Value>GRANDTOTAL</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox23">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>LightCyan</BackgroundColor>
<BorderWidth>
<Bottom>2pt</Bottom>
<Top>2pt</Top>
</BorderWidth>
<BorderColor>
<Bottom>DarkRed</Bottom>
<Top>Black</Top>
</BorderColor>
<BorderStyle>
<Bottom>Solid</Bottom>
<Top>Solid</Top>
</BorderStyle>
<TextAlign>Left</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>14</ZIndex>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox25">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>LightCyan</BackgroundColor>
<BorderWidth>
<Bottom>2pt</Bottom>
<Top>2pt</Top>
</BorderWidth>
<BorderColor>
<Bottom>DarkRed</Bottom>
<Top>Black</Top>
</BorderColor>
<BorderStyle>
<Bottom>Solid</Bottom>
<Top>Solid</Top>
</BorderStyle>
<TextAlign>Left</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>13</ZIndex>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox26">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<Format>N</Format>
<BackgroundColor>LightCyan</BackgroundColor>
<BorderWidth>
<Bottom>2pt</Bottom>
<Top>2pt</Top>
</BorderWidth>
<BorderColor>
<Bottom>DarkRed</Bottom>
<Top>Black</Top>
</BorderColor>
<BorderStyle>
<Bottom>Solid</Bottom>
<Top>Solid</Top>
</BorderStyle>
<TextAlign>Center</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>900</FontWeight>
</Style>
<ZIndex>12</ZIndex>
<CanGrow>true</CanGrow>
<Value>= RunningValue(Fields!GroupHours.Value,Sum,
"GetProjectResourceHours")</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
<RepeatOnNewPage>true</RepeatOnNewPage>
</Header>
<DataSetName>GetProjectResourceHours</DataSetName>
<Top>2.25in</Top>
<TableGroups>
<TableGroup>
<Header>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox28">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>=iif(RunningValue(Fields!ProjectName.Value,CountDistinct,Nothing) Mod 2, "Cornsilk", "White")</BackgroundColor>
<FontSize>8pt</FontSize>
<TextAlign>Left</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>11</ZIndex>
<CanGrow>true</CanGrow>
<Value>= Fields!ProjectName.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox29">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>=iif(RunningValue(Fields!ProjectName.Value,CountDistinct,Nothing) Mod 2, "Cornsilk", "White")</BackgroundColor>
<FontSize>8pt</FontSize>
<TextAlign>Left</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>10</ZIndex>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox30">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>=iif(RunningValue(Fields!ProjectName.Value,CountDistinct,Nothing) Mod 2, "Cornsilk", "White")</BackgroundColor>
<FontSize>8pt</FontSize>
<TextAlign>Left</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>9</ZIndex>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox31">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<Format>N</Format>
<BackgroundColor>=iif(RunningValue(Fields!ProjectName.Value,CountDistinct,Nothing) Mod 2, "Cornsilk", "White")</BackgroundColor>
<TextAlign>Center</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>8</ZIndex>
<CanGrow>true</CanGrow>
<Value>=SUM( Fields!GroupHours.Value)</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Header>
<Grouping Name="table1_Group1">
<GroupExpressions>
<GroupExpression>=Fields!ProjectName.Value</GroupExpression>
</GroupExpressions>
</Grouping>
</TableGroup>
<TableGroup>
<Header>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox32">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>=iif(RunningValue(Fields!ActivityCode.Value,CountDistinct,Nothing) Mod 2, "LavenderBlush", "White")</BackgroundColor>
<BorderStyle>
<Bottom>None</Bottom>
</BorderStyle>
<FontSize>8pt</FontSize>
<TextAlign>Left</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>7</ZIndex>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox33">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>=iif(RunningValue(Fields!ActivityCode.Value,CountDistinct,Nothing) Mod 2, "LavenderBlush", "White")</BackgroundColor>
<BorderStyle>
<Bottom>None</Bottom>
</BorderStyle>
<FontSize>8pt</FontSize>
<TextAlign>Left</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>6</ZIndex>
<CanGrow>true</CanGrow>
<Value>= Fields!ActivityCode.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox34">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>=iif(RunningValue(Fields!ActivityCode.Value,CountDistinct,Nothing) Mod 2, "LavenderBlush", "White")</BackgroundColor>
<BorderStyle>
<Bottom>None</Bottom>
</BorderStyle>
<FontSize>8pt</FontSize>
<TextAlign>Left</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>5</ZIndex>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox35">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<Format>N</Format>
<BackgroundColor>=iif(RunningValue(Fields!ActivityCode.Value,CountDistinct,Nothing) Mod 2, "LavenderBlush", "White")</BackgroundColor>
<BorderStyle>
<Bottom>None</Bottom>
</BorderStyle>
<FontSize>9pt</FontSize>
<TextAlign>Center</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>4</ZIndex>
<CanGrow>true</CanGrow>
<Value>=SUM(Fields!GroupHours.Value)</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Header>
<Grouping Name="table1_Group2">
<GroupExpressions>
<GroupExpression>=Fields!ActivityCode.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<Visibility>
<ToggleItem>textbox28</ToggleItem>
<Hidden>true</Hidden>
</Visibility>
</TableGroup>
<TableGroup>
<Header>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox36">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>=iif(RunningValue(Fields!GroupName.Value,CountDistinct,Nothing) Mod 2, "LightYellow", "White")</BackgroundColor>
<TextAlign>Left</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>3</ZIndex>
<rd:DefaultName>textbox36</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox37">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>=iif(RunningValue(Fields!GroupName.Value,CountDistinct,Nothing) Mod 2, "LightYellow", "White")</BackgroundColor>
<TextAlign>Left</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>2</ZIndex>
<rd:DefaultName>textbox37</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox38">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>=iif(RunningValue(Fields!GroupName.Value,CountDistinct,Nothing) Mod 2, "LightYellow", "White")</BackgroundColor>
<FontSize>8pt</FontSize>
<TextAlign>Left</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>1</ZIndex>
<rd:DefaultName>textbox38</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>= Fields!GroupName.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox39">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<Format>N</Format>
<BackgroundColor>=iif(RunningValue(Fields!GroupName.Value,CountDistinct,Nothing) Mod 2, "LightYellow", "White")</BackgroundColor>
<FontSize>8pt</FontSize>
<TextAlign>Center</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<rd:DefaultName>textbox39</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=(Fields!GroupHours.Value)</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Header>
<Grouping Name="table1_Group4">
<GroupExpressions>
<GroupExpression>=Fields!GroupName.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<Visibility>
<ToggleItem>textbox33</ToggleItem>
<Hidden>true</Hidden>
</Visibility>
</TableGroup>
<TableGroup>
<Grouping Name="table1_Group3">
<GroupExpressions>
<GroupExpression>=Fields!ActivityCode.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<Visibility>
<ToggleItem>textbox33</ToggleItem>
<Hidden>true</Hidden>
</Visibility>
</TableGroup>
</TableGroups>
<Visibility>
<Hidden>=IIF(Parameters!RP_SELECT.Value=2,false,true)</Hidden>
</Visibility>
<TableColumns>
<TableColumn>
<Width>2.625in</Width>
</TableColumn>
<TableColumn>
<Width>1.875in</Width>
</TableColumn>
<TableColumn>
<Width>2.5in</Width>
</TableColumn>
<TableColumn>
<Width>1.75in</Width>
</TableColumn>
</TableColumns>
</Table>
--
"Nico Cristache [MSFT]" wrote:
> Do you really have toggles in your report or you just conditinally hide
> items based on report parameters?
> Could you send us the report, please?
> --
> Nico Cristache [MSFT]
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "NI" <NI@.discussions.microsoft.com> wrote in message
> news:3DE5068D-E7F9-41A3-8CC1-1405F20B6277@.microsoft.com...
> > Hi:
> >
> > I have a report with 2 tables in it, both have drillthroughs. I need to
> > toggle the visibility between the two tables based on the report
> parameters,
> > and hence have an expression in the visibility of the tables properties.
> >
> > 1) Loss of data when exported to excel (sublevels not exported)
> > The problem caused with this is that when I export to excel the
> > drillthroughs whihc usually get exported as levels in excel are not being
> > exported. I remove the visibility expression in the table it works fine.
> >
> > 2) When the visibilty function is present even though only one table is
> > visible in the report, when i export it i see both the tables. This is a
> > little inconvenient but fine because it was excess data. But definetly 1
> is
> > an issue that needs to be dealt with.
> >
> > For now I'll just create two separate reports, or have the second table in
> a
> > subreport.
> >
> > Thanks
> >
> >
> >
>
>|||Will this be fixed in SP2? I have the same problem with toggeling visibility
on 2 tables and exporting to excel. I have tryed to install the latest SP2
beta but it didn't seem to fix the problem.
"NI" wrote:
> Hi Nico:
> I conditionally hide the 2 tables, based on a parameter. This puts in this
> section of code under
> <Table>
> <Visibility>
> <Hidden>=IIF(Parameters!RP_SELECT.Value=2,false,true)</Hidden>
> </Visibility>
> </Table>
> If I remove that visibility section they export fine to excel. If it remains
> the sub levels do not export.
> Thanks
> PS: Couldnt find an option to attach here, and hence I inlined only a part
> of the report. Hope this helps.
> -- Had to remove the report becouse my post was too long... --
>
> "Nico Cristache [MSFT]" wrote:
> > Do you really have toggles in your report or you just conditinally hide
> > items based on report parameters?
> > Could you send us the report, please?
> >
> > --
> > Nico Cristache [MSFT]
> > Microsoft SQL Server Reporting Services
> >
> > This posting is provided "AS IS" with no warranties, and confers no rights.
> >
> >
> > "NI" <NI@.discussions.microsoft.com> wrote in message
> > news:3DE5068D-E7F9-41A3-8CC1-1405F20B6277@.microsoft.com...
> > > Hi:
> > >
> > > I have a report with 2 tables in it, both have drillthroughs. I need to
> > > toggle the visibility between the two tables based on the report
> > parameters,
> > > and hence have an expression in the visibility of the tables properties.
> > >
> > > 1) Loss of data when exported to excel (sublevels not exported)
> > > The problem caused with this is that when I export to excel the
> > > drillthroughs whihc usually get exported as levels in excel are not being
> > > exported. I remove the visibility expression in the table it works fine.
> > >
> > > 2) When the visibilty function is present even though only one table is
> > > visible in the report, when i export it i see both the tables. This is a
> > > little inconvenient but fine because it was excess data. But definetly 1
> > is
> > > an issue that needs to be dealt with.
> > >
> > > For now I'll just create two separate reports, or have the second table in
> > a
> > > subreport.
> > >
> > > Thanks
> > >
> > >
> > >
> >
> >
> >