install SQL on WinXP: based

Showing posts with label based. Show all posts

Tuesday, March 20, 2012

Can not change filter on model report - UPDATED 7th June

I have a very simple report based on a test model.

The report is filtered via a date field and runs ok.

If I try to change the filter I get the following error message:

Operation Failed

Short time pattern does not include hours and minutes

Does anyone know how I can correct this?

Regards

John

UPDATE

This only happens if I try to filter between two dates, a single date works correctly!

Can you please save report that fails to file and post it?|||

Alexandre

This is the rdl for the failing report.

<?xml version="1.0" encoding="utf-8"?>

<Query>

<DataSourceName>dataSource1</DataSourceName>

</BaseEntity>

</EntityRef>

</Expression>

</AttributeRef>

</Expression>

</AttributeRef>

</Expression>

<Path>

</RolePathItem>

</Path>

</AttributeRef>

</Expression>

</AttributeRef>

</Expression>

</Details>

</Grouping>

</Groupings>

<FunctionName>GreaterThanOrEquals</FunctionName>

</AttributeRef>

</Expression>

<DataType>DateTime</DataType>

</Literal>

</Expression>

</Arguments>

</Function>

</Expression>

<FunctionName>LessThanOrEquals</FunctionName>

</AttributeRef>

</Expression>

<DataType>DateTime</DataType>

</Literal>

</Expression>

</Arguments>

</Function>

</Expression>

</Arguments>

</Function>

</CustomProperty>

</CustomProperty>

</CustomProperties>

</Expression>

</Filter>

</Hierarchy>

</Hierarchies>

<FunctionName>GreaterThanOrEquals</FunctionName>

</AttributeRef>

</Expression>

<DataType>DateTime</DataType>

</Literal>

</Expression>

</Arguments>

</Function>

</Expression>

<FunctionName>LessThanOrEquals</FunctionName>

</AttributeRef>

</Expression>

<DataType>DateTime</DataType>

</Literal>

</Expression>

</Arguments>

</Function>

</Expression>

</Arguments>

</Function>

</CustomProperties>

</Expression>

</CustomProperties>

</Expression>

</Arguments>

</Function>

</CustomProperty>

</CustomProperties>

</Expression>

</CalculatedAttributes>

</SemanticQuery></CommandText>

</Query>

<DataField>Epex Referral</DataField>

</Field>

<DataField>Created</DataField>

</Field>

<DataField>Close Date</DataField>

</Field>

<DataField>Patient</DataField>

</Field>

</Field>

</Fields>

</DataSet>

</DataSets>

<DataSourceReference>/Models/Mars Cutdown</DataSourceReference>

<rd:DataSourceID>4d1edcd6-3c78-4e68-9566-ef952e495be4</rd:DataSourceID>

</DataSource>

</DataSources>

<Body>

<Style>

<FontFamily>Tahoma</FontFamily>

<FontWeight>Normal</FontWeight>

</Style>

<Name>rb:Watermark</Name>

<Value>Click to add title</Value>

</CustomProperty>

</CustomProperties>

</Textbox>

<DataElementOutput>Output</DataElementOutput>

<ReportName>=DataSources!dataSource1.DataSourceReference</ReportName>

</Parameter>

<Value>Detail</Value>

</Parameter>

<Value>Drillthrough</Value>

</Parameter>

<Value>=DataSets!dataSet.RewrittenCommandText</Value>

</Parameter>

<Value>=CreateDrillthroughContext()</Value>

</Parameter>

</Parameters>

</Drillthrough>

</Action>

<Value>=Fields!Created.Value</Value>

<Styl>

<Default>LightGrey</Default>

</BorderColor>

<Default>Solid</Default>

</BorderStyle>

<FontFamily>Tahoma</FontFamily>

<FontWeight>Normal</FontWeight>

<BackgroundColor>White</BackgroundColor>

</Style>

</Textbox>

</ReportItems>

</TableCell>

<DataElementOutput>Output</DataElementOutput>

<Value>=Fields!CloseDate.Value</Value>

<Style>

<Default>LightGrey</Default>

</BorderColor>

<Default>Solid</Default>

</BorderStyle>

<FontFamily>Tahoma</FontFamily>

<FontWeight>Normal</FontWeight>

<BackgroundColor>White</BackgroundColor>

</Style>

</Textbox>

</ReportItems>

</TableCell>

<DataElementOutput>Output</DataElementOutput>

<ReportName>=DataSources!dataSource1.DataSourceReference</ReportName>

</Parameter>

<Value>Detail</Value>

</Parameter>

<Value>Drillthrough</Value>

</Parameter>

<Value>=DataSets!dataSet.RewrittenCommandText</Value>

</Parameter>

<Value>=CreateDrillthroughContext()</Value>

</Parameter>

</Parameters>

</Drillthrough>

</Action>

<Value>=Fields!Patient.Value</Value>

<Style>

<Default>LightGrey</Default>

</BorderColor>

<Default>Solid</Default>

</BorderStyle>

<FontFamily>Tahoma</FontFamily>

<FontWeight>Normal</FontWeight>

<BackgroundColor>White</BackgroundColor>

</Style>

</Textbox>

</ReportItems>

</TableCell>

</TableCells>

</TableRow>

</TableRows>

<GroupExpression>=Fields!EpexReferral.Value</GroupExpression>

</GroupExpressions>

</Grouping>

<SortExpression>=IIf(True, Fields!expr1.Value, Nothing)</SortExpression>

<Direction>Descending</Direction>

</SortBy>

<SortExpression>=IIf(True, Fields!Created.Value, Nothing)</SortExpression>

<Direction>Descending</Direction>

</SortBy>

</Sorting>

</Details>

<DataSetName>dataSet</DataSetName>

<Hidden>=Fields!Created.IsMissing</Hidden>

</Visibility>

</TableColumn>

<Hidden>=Fields!CloseDate.IsMissing</Hidden>

</Visibility>

</TableColumn>

<Hidden>=Fields!Patient.IsMissing</Hidden>

</Visibility>

</TableColumn>

</TableColumns>

<SortExpression>=Fields!Created.Value</SortExpression>

<SortExpressionScope>table_EpexReferral</SortExpressionScope>

</UserSort>

<Value>Created</Value>

<Style>

<Default>LightGrey</Default>

</BorderColor>

<Default>Solid</Default>

</BorderStyle>

<FontFamily>Tahoma</FontFamily>

<Color>White</Color>

</Style>

</Textbox>

</ReportItems>

</TableCell>

<SortExpression>=Fields!CloseDate.Value</SortExpression>

<SortExpressionScope>table_EpexReferral</SortExpressionScope>

</UserSort>

<Value>Close Date</Value>

<Style

<Default>LightGrey</Default>

</BorderColor>

<Default>Solid</Default>

</BorderStyle>

<FontFamily>Tahoma</FontFamily>

<Color>White</Color>

</Style>

</Textbox>

</ReportItems>

</TableCell>

<SortExpression>=Fields!Patient.Value</SortExpression>

<SortExpressionScope>table_EpexReferral</SortExpressionScope>

</UserSort>

<Value>Patient</Value>

<Style>

<Default>LightGrey</Default>

</BorderColor>

<Default>Solid</Default>

</BorderStyle>

<FontFamily>Tahoma</FontFamily>

<Color>White</Color>

</Style>

</Textbox>

</ReportItems>

</TableCell>

</TableCells>

</TableRow>

</TableRows>

</Header>

</Table>

<Value>=String.Format("Total Epex Referrals" & Chr(58) & " {0}", COUNTROWS("dataSet"))</Value>

<Style>

<FontWeight>Normal</FontWeight>

</Style>

<Name>rb:SpecialContent</Name>

<Value>TotalRows</Value>

</CustomProperty>

</CustomProperties>

</Textbox>

<Value>Filter: Epex Referrals with: Created from 01/01/2005 to 01/01/2006</Value>

<Style>

<FontWeight>Normal</FontWeight>

</Style>

<Name>rb:SpecialContent</Name>

Value>FilterDescription</Value>

</CustomProperty>

</CustomProperties>

</Textbox>

</ReportItems>

</Body>

</Report>

|||

Is this error happening when you are editing report or when you are running this report? Can you save this report to report server and run it from Report Manager (http://rshost/reports)?

Thanks!

|||

The report executes with no problems, I only get the error if I try to change the filter.

What is strange, I can get around the error by apply to separate date filters, one using Greater Than Or Equal and the other Less Than or Equal.

This gets around the problem but I would like to offer my end users the option of From and To as this is what the use in other parts of SSRS.

Regards

John

|||

We have the same problem even after installing SP1 and the hotfixes. Has anybody a solution/answer?

Regards

Werner

|||This is a known issue that will be fixed in SP2. Unfortunately the only way to avoid this error for now is to change your Windows regional settings to use a 12-hour clock.|||

Bob,

thanks for the immediate answer, when can we approx. expect SP2?

Regards

Werner

|||No committed dates yet, but I believe it's on track for later this year.|||

Bob,

i tried to change the format in the regional settings like you said but no changes. We use a german W2003 server... When i select a date field in the report builder filter, select a date from the datetime picker (22.06.2006) and try to save it with the ok button i get a message box saying 'no valid date'...?!?

Can i change anything in the model to solve the problem? Is this filter problem caused by SP1 (in this case we could think about setting up the SQL Server again)?

Regards

Werner

Can not change filter on model report - UPDATED

I have a very simple report based on a test model.

The report is filtered via a date field and runs ok.

If I try to change the filter I get the following error message:

Operation Failed

Short time pattern does not include hours and minutes

Does anyone know how I can correct this?

Regards

John

UPDATE

This only happens if I try to filter between two dates, a single date works correctly!

Can you please save report that fails to file and post it?|||

Alexandre

This is the rdl for the failing report.

<?xml version="1.0" encoding="utf-8"?>

<Query>

<DataSourceName>dataSource1</DataSourceName>

</BaseEntity>

</EntityRef>

</Expression>

</AttributeRef>

</Expression>

</AttributeRef>

</Expression>

<Path>

</RolePathItem>

</Path>

</AttributeRef>

</Expression>

</AttributeRef>

</Expression>

</Details>

</Grouping>

</Groupings>

<FunctionName>GreaterThanOrEquals</FunctionName>

</AttributeRef>

</Expression>

<DataType>DateTime</DataType>

</Literal>

</Expression>

</Arguments>

</Function>

</Expression>

<FunctionName>LessThanOrEquals</FunctionName>

</AttributeRef>

</Expression>

<DataType>DateTime</DataType>

</Literal>

</Expression>

</Arguments>

</Function>

</Expression>

</Arguments>

</Function>

</CustomProperty>

</CustomProperty>

</CustomProperties>

</Expression>

</Filter>

</Hierarchy>

</Hierarchies>

<FunctionName>GreaterThanOrEquals</FunctionName>

</AttributeRef>

</Expression>

<DataType>DateTime</DataType>

</Literal>

</Expression>

</Arguments>

</Function>

</Expression>

<FunctionName>LessThanOrEquals</FunctionName>

</AttributeRef>

</Expression>

<DataType>DateTime</DataType>

</Literal>

</Expression>

</Arguments>

</Function>

</Expression>

</Arguments>

</Function>

</CustomProperties>

</Expression>

</CustomProperties>

</Expression>

</Arguments>

</Function>

</CustomProperty>

</CustomProperties>

</Expression>

</CalculatedAttributes>

</SemanticQuery></CommandText>

</Query>

<DataField>Epex Referral</DataField>

</Field>

<DataField>Created</DataField>

</Field>

<DataField>Close Date</DataField>

</Field>

<DataField>Patient</DataField>

</Field>

</Field>

</Fields>

</DataSet>

</DataSets>

<DataSourceReference>/Models/Mars Cutdown</DataSourceReference>

<rd:DataSourceID>4d1edcd6-3c78-4e68-9566-ef952e495be4</rd:DataSourceID>

</DataSource>

</DataSources>

<Body>

<Style>

<FontFamily>Tahoma</FontFamily>

<FontWeight>Normal</FontWeight>

</Style>

<Name>rb:Watermark</Name>

<Value>Click to add title</Value>

</CustomProperty>

</CustomProperties>

</Textbox>

<DataElementOutput>Output</DataElementOutput>

<ReportName>=DataSources!dataSource1.DataSourceReference</ReportName>

</Parameter>

<Value>Detail</Value>

</Parameter>

<Value>Drillthrough</Value>

</Parameter>

<Value>=DataSets!dataSet.RewrittenCommandText</Value>

</Parameter>

<Value>=CreateDrillthroughContext()</Value>

</Parameter>

</Parameters>

</Drillthrough>

</Action>

<Value>=Fields!Created.Value</Value>

<Styl>

<Default>LightGrey</Default>

</BorderColor>

<Default>Solid</Default>

</BorderStyle>

<FontFamily>Tahoma</FontFamily>

<FontWeight>Normal</FontWeight>

<BackgroundColor>White</BackgroundColor>

</Style>

</Textbox>

</ReportItems>

</TableCell>

<DataElementOutput>Output</DataElementOutput>

<Value>=Fields!CloseDate.Value</Value>

<Style>

<Default>LightGrey</Default>

</BorderColor>

<Default>Solid</Default>

</BorderStyle>

<FontFamily>Tahoma</FontFamily>

<FontWeight>Normal</FontWeight>

<BackgroundColor>White</BackgroundColor>

</Style>

</Textbox>

</ReportItems>

</TableCell>

<DataElementOutput>Output</DataElementOutput>

<ReportName>=DataSources!dataSource1.DataSourceReference</ReportName>

</Parameter>

<Value>Detail</Value>

</Parameter>

<Value>Drillthrough</Value>

</Parameter>

<Value>=DataSets!dataSet.RewrittenCommandText</Value>

</Parameter>

<Value>=CreateDrillthroughContext()</Value>

</Parameter>

</Parameters>

</Drillthrough>

</Action>

<Value>=Fields!Patient.Value</Value>

<Style>

<Default>LightGrey</Default>

</BorderColor>

<Default>Solid</Default>

</BorderStyle>

<FontFamily>Tahoma</FontFamily>

<FontWeight>Normal</FontWeight>

<BackgroundColor>White</BackgroundColor>

</Style>

</Textbox>

</ReportItems>

</TableCell>

</TableCells>

</TableRow>

</TableRows>

<GroupExpression>=Fields!EpexReferral.Value</GroupExpression>

</GroupExpressions>

</Grouping>

<SortExpression>=IIf(True, Fields!expr1.Value, Nothing)</SortExpression>

<Direction>Descending</Direction>

</SortBy>

<SortExpression>=IIf(True, Fields!Created.Value, Nothing)</SortExpression>

<Direction>Descending</Direction>

</SortBy>

</Sorting>

</Details>

<DataSetName>dataSet</DataSetName>

<Hidden>=Fields!Created.IsMissing</Hidden>

</Visibility>

</TableColumn>

<Hidden>=Fields!CloseDate.IsMissing</Hidden>

</Visibility>

</TableColumn>

<Hidden>=Fields!Patient.IsMissing</Hidden>

</Visibility>

</TableColumn>

</TableColumns>

<SortExpression>=Fields!Created.Value</SortExpression>

<SortExpressionScope>table_EpexReferral</SortExpressionScope>

</UserSort>

<Value>Created</Value>

<Style>

<Default>LightGrey</Default>

</BorderColor>

<Default>Solid</Default>

</BorderStyle>

<FontFamily>Tahoma</FontFamily>

<Color>White</Color>

</Style>

</Textbox>

</ReportItems>

</TableCell>

<SortExpression>=Fields!CloseDate.Value</SortExpression>

<SortExpressionScope>table_EpexReferral</SortExpressionScope>

</UserSort>

<Value>Close Date</Value>

<Style

<Default>LightGrey</Default>

</BorderColor>

<Default>Solid</Default>

</BorderStyle>

<FontFamily>Tahoma</FontFamily>

<Color>White</Color>

</Style>

</Textbox>

</ReportItems>

</TableCell>

<SortExpression>=Fields!Patient.Value</SortExpression>

<SortExpressionScope>table_EpexReferral</SortExpressionScope>

</UserSort>

<Value>Patient</Value>

<Style>

<Default>LightGrey</Default>

</BorderColor>

<Default>Solid</Default>

</BorderStyle>

<FontFamily>Tahoma</FontFamily>

<Color>White</Color>

</Style>

</Textbox>

</ReportItems>

</TableCell>

</TableCells>

</TableRow>

</TableRows>

</Header>

</Table>

<Value>=String.Format("Total Epex Referrals" & Chr(58) & " {0}", COUNTROWS("dataSet"))</Value>

<Style>

<FontWeight>Normal</FontWeight>

</Style>

<Name>rb:SpecialContent</Name>

<Value>TotalRows</Value>

</CustomProperty>

</CustomProperties>

</Textbox>

<Value>Filter: Epex Referrals with: Created from 01/01/2005 to 01/01/2006</Value>

<Style>

<FontWeight>Normal</FontWeight>

</Style>

<Name>rb:SpecialContent</Name>

Value>FilterDescription</Value>

</CustomProperty>

</CustomProperties>

</Textbox>

</ReportItems>

</Body>

</Report>

|||

Is this error happening when you are editing report or when you are running this report? Can you save this report to report server and run it from Report Manager (http://rshost/reports)?

Thanks!

|||

The report executes with no problems, I only get the error if I try to change the filter.

What is strange, I can get around the error by apply to separate date filters, one using Greater Than Or Equal and the other Less Than or Equal.

This gets around the problem but I would like to offer my end users the option of From and To as this is what the use in other parts of SSRS.

Regards

John

|||

We have the same problem even after installing SP1 and the hotfixes. Has anybody a solution/answer?

Regards

Werner

|||This is a known issue that will be fixed in SP2. Unfortunately the only way to avoid this error for now is to change your Windows regional settings to use a 12-hour clock.|||

Bob,

thanks for the immediate answer, when can we approx. expect SP2?

Regards

Werner

|||No committed dates yet, but I believe it's on track for later this year.|||

Bob,

Can i change anything in the model to solve the problem? Is this filter problem caused by SP1 (in this case we could think about setting up the SQL Server again)?

Regards

Werner

sql

Sunday, March 11, 2012

Can MDX or latch a value based on a signal?

Dear All,

Persume that I have a cube with the following measures and dimension:

DimTime, measures.LatchSignal, measures.Voltage

0,1,4

1,0,5

2,0,6

3,0,5

4,1,3

5,0,7

6,0,5

7,1,6

8,0,7

Is there anyway I can repeat the value of voltage on and after the time when latch signal = 1, so that the result is rendered as follows:

DimTime, measures.LatchSignal, measures.Voltage, measure.latchedVoltage

0,1,4,4

1,0,5,4

2,0,6,4

3,0,5,4

4,1,3,3

5,0,7,3

6,0,5,3

7,1,6,6

8,0,7,6

Thanks and regards,

Tony Chun Tung Siu

Hi Tony!

Why do you try to make it in MDX. What the problem do you solve?

I would make it in DSV in oder to get

0,1,4,4

1,0,5,NULL

2,0,6,NULL

3,0,5,NULL

4,1,3,3

5,0,7,NULL

6,0,5,NULL

7,1,6,6

8,0,7,NULL

then apply lastnonempty aggregation function for the measure latchedVoltage

|||

I just wanna plot a graph for such signal implementation. This works!.

Thanks

Tony

Wednesday, March 7, 2012

Can I use Reporting Services 2005 against a Analysis Services 2000 OLAP cube?

Is it possible to create reports in SQL Server 2005 Reporting Services
against OLAP cubes based on Analysis Services 2000? Any limitations?
Any things I should know about?
I've tried to upload a report made in the SQL Server 2005 Business
Intelligence Management Studio to a 2000 edition Reporting Services,
and that didn't work out. (No surprise there, I just wanted to try it
out to see what happened. ) But would there be any problems using a
2000 OLAP cube as datasource?
All comments appreciated.
Kaisa M. Lindahl> Is it possible to create reports in SQL Server 2005 Reporting Services
> against OLAP cubes based on Analysis Services 2000? Any limitations?
> Any things I should know about?
Yes and there are known limitations. See
http://msdn.microsoft.com/sql/default.aspx?pull=/library/en-us/dnsql2k/html/olapasandrs.asp.
HTH,
---
Teo Lachev, MVP, MCSD, MCT
"Microsoft Reporting Services in Action"
"Applied Microsoft Analysis Services 2005"
Home page and blog: http://www.prologika.com/
"Kaisa M. Lindahl" <kaisaml@.hotmail.com> wrote in message
news:1133378489.639395.34470@.g44g2000cwa.googlegroups.com...
> Is it possible to create reports in SQL Server 2005 Reporting Services
> against OLAP cubes based on Analysis Services 2000? Any limitations?
> Any things I should know about?
> I've tried to upload a report made in the SQL Server 2005 Business
> Intelligence Management Studio to a 2000 edition Reporting Services,
> and that didn't work out. (No surprise there, I just wanted to try it
> out to see what happened. ) But would there be any problems using a
> 2000 OLAP cube as datasource?
> All comments appreciated.
> Kaisa M. Lindahl
>|||Are you thinking about the last bit ("Moving Towards SQL Server 2005"),
or using Reporting Services against OLAP cubes in general? I've already
made a few set of reports in RS 2000 based on AS 2000, so I've figured
out the limitations there. I just want to know if there are any
problems developing reports in the 2005 designer against a 200
datasource.
Sincerely,
Kaisa M. Lindahl|||What I was eluding to is:
RS 2005 & AS 2000 = RS 2000 & AS 2000
I am not aware of any aditional limitations.
--
HTH,
---
Teo Lachev, MVP, MCSD, MCT
"Microsoft Reporting Services in Action"
"Applied Microsoft Analysis Services 2005"
Home page and blog: http://www.prologika.com/
---
"Kaisa M. Lindahl" <kaisaml@.hotmail.com> wrote in message
news:1133470523.084460.320180@.g47g2000cwa.googlegroups.com...
> Are you thinking about the last bit ("Moving Towards SQL Server 2005"),
> or using Reporting Services against OLAP cubes in general? I've already
> made a few set of reports in RS 2000 based on AS 2000, so I've figured
> out the limitations there. I just want to know if there are any
> problems developing reports in the 2005 designer against a 200
> datasource.
> Sincerely,
> Kaisa M. Lindahl
>

Friday, February 24, 2012

can I update based on a join result with a calculation

My table design is
CREATE TABLE [HashKeyTotals] (
[hKey] [bigint] NOT NULL ,
[Total] [int] NULL ,
CONSTRAINT [HashKeyTotals] PRIMARY KEY CLUSTERED
(
[hKey]
) ON [PRIMARY]
) ON [PRIMARY]
GO
and I also have data files of the same structure. My goal is to import the
data files in order to update the Total table where the hKey matches.
I could
A. Read each record (4.5 million) and send to a stored procedure which does
a look up and add to the total field.
or
B. Import the data file into a temp table, join the HashKeyTotals table with
some sort of calc'd field summing the two totals and put that into a temp
table to be loaded back into the HashKeyTotals table.
I'm not even sure if B is possible but just a crazy idea. Will it work and
will it be that much faster than plan A? The HashKeyTotals table can have up
to 25 million records and I'd be loading a data file with 5 million records
in it each day.
Any thoughts?
ThanksSo, you're incrementing the Total by what you get in the import table for
the corresponding key? If so, try:
CREATE TABLE [HashKeyTotals] (
[hKey] [bigint] NOT NULL ,
[Total] [int] NULL ,
CONSTRAINT [PK_HashKeyTotals] PRIMARY KEY CLUSTERED
(
[hKey]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [HashKeyTotals2] (
[hKey] [bigint] NOT NULL ,
[Total] [int] NULL ,
CONSTRAINT [PK_HashKeyTotals2] PRIMARY KEY CLUSTERED
(
[hKey]
) ON [PRIMARY]
) ON [PRIMARY]
GO
insert HashKeyTotals values (1, 10)
insert HashKeyTotals values (2, 20)
insert HashKeyTotals2 values (1, 5)
insert HashKeyTotals2 values (2, 15)
go
update h
set
Total = h.Total + h2.Total
from
HashKeyTotals h
join HashKeyTotals2 h2 on h2.hKey = h.hKey
go
select * from HashKeyTotals
go
drop table HashKeyTotals, HashKeyTotals2
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Mike" <Mike@.mike.com> wrote in message
news:uuIuedUcGHA.3936@.TK2MSFTNGP05.phx.gbl...
My table design is
CREATE TABLE [HashKeyTotals] (
[hKey] [bigint] NOT NULL ,
[Total] [int] NULL ,
CONSTRAINT [HashKeyTotals] PRIMARY KEY CLUSTERED
(
[hKey]
) ON [PRIMARY]
) ON [PRIMARY]
GO
and I also have data files of the same structure. My goal is to import the
data files in order to update the Total table where the hKey matches.
I could
A. Read each record (4.5 million) and send to a stored procedure which does
a look up and add to the total field.
or
B. Import the data file into a temp table, join the HashKeyTotals table with
some sort of calc'd field summing the two totals and put that into a temp
table to be loaded back into the HashKeyTotals table.
I'm not even sure if B is possible but just a crazy idea. Will it work and
will it be that much faster than plan A? The HashKeyTotals table can have up
to 25 million records and I'd be loading a data file with 5 million records
in it each day.
Any thoughts?
Thanks|||Yes thats exactly what I need. Thank you very much.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23LBk8iVcGHA.1276@.TK2MSFTNGP03.phx.gbl...
> So, you're incrementing the Total by what you get in the import table for
> the corresponding key? If so, try:
> CREATE TABLE [HashKeyTotals] (
> [hKey] [bigint] NOT NULL ,
> [Total] [int] NULL ,
> CONSTRAINT [PK_HashKeyTotals] PRIMARY KEY CLUSTERED
> (
> [hKey]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
> CREATE TABLE [HashKeyTotals2] (
> [hKey] [bigint] NOT NULL ,
> [Total] [int] NULL ,
> CONSTRAINT [PK_HashKeyTotals2] PRIMARY KEY CLUSTERED
> (
> [hKey]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
> insert HashKeyTotals values (1, 10)
> insert HashKeyTotals values (2, 20)
> insert HashKeyTotals2 values (1, 5)
> insert HashKeyTotals2 values (2, 15)
> go
> update h
> set
> Total = h.Total + h2.Total
> from
> HashKeyTotals h
> join HashKeyTotals2 h2 on h2.hKey = h.hKey
> go
> select * from HashKeyTotals
> go
> drop table HashKeyTotals, HashKeyTotals2
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Mike" <Mike@.mike.com> wrote in message
> news:uuIuedUcGHA.3936@.TK2MSFTNGP05.phx.gbl...
> My table design is
> CREATE TABLE [HashKeyTotals] (
> [hKey] [bigint] NOT NULL ,
> [Total] [int] NULL ,
> CONSTRAINT [HashKeyTotals] PRIMARY KEY CLUSTERED
> (
> [hKey]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
> and I also have data files of the same structure. My goal is to import the
> data files in order to update the Total table where the hKey matches.
> I could
> A. Read each record (4.5 million) and send to a stored procedure which
> does
> a look up and add to the total field.
> or
> B. Import the data file into a temp table, join the HashKeyTotals table
> with
> some sort of calc'd field summing the two totals and put that into a temp
> table to be loaded back into the HashKeyTotals table.
> I'm not even sure if B is possible but just a crazy idea. Will it work and
> will it be that much faster than plan A? The HashKeyTotals table can have
> up
> to 25 million records and I'd be loading a data file with 5 million
> records
> in it each day.
> Any thoughts?
> Thanks
>

Can I split a field based on a character?

Here's a question for the SQL gurus out there:
I have a varchar(20) field DIAGNOSISCODE in a table that can either be null, or contain up to 3 comma-separated codes, each of which relates to a description in another table. For example, some sample rows might be
8060
8060,4450
8060,4123,3245
Now I need to structure a query to return these values from this single field as three fields CODE1, CODE2, CODE3, with NULL as appropriate for example
CODE1=8060, CODE2=4450, CODE3=NULL.
I have been using CASE along with CHARINDEX and PATINDEX but it it becoming extremely messy. Can anyone think of a "neater" way to return three fields from this one field?
Any help very greatly appreciated.
Thanks, Simon.Here's a question for the SQL gurus out there:
I have a varchar(20) field DIAGNOSISCODE in a table that can either be null, or contain up to 3 comma-separated codes, each of which relates to a description in another table. For example, some sample rows might be
8060
8060,4450
8060,4123,3245
Now I need to structure a query to return these values from this single field as three fields CODE1, CODE2, CODE3, with NULL as appropriate for example
CODE1=8060, CODE2=4450, CODE3=NULL.
I have been using CASE along with CHARINDEX and PATINDEX but it it becoming extremely messy. Can anyone think of a "neater" way to return three fields from this one field?
Any help very greatly appreciated.
Thanks, Simon.

Don't let blindman see this post; he'll pull out the complete works of E.F. Codd!!! :D

Seriously, do a google on fn_Split(). I think you will find it will suit your purposes.

Regards,

hmscott|||check that site,I think that is the best and simple way to do.
http://www.sqlteam.com/item.asp?ItemID=2652|||The requirements are very close to the ones of generating a remittance advice when running Claims Reimbursement...It's been awhile, but I had to deal with the same scenario. First, I stored Dx codes separately from Claim Details. I did have a reference from ClaimDxCodes back to Claim Details table to retain dependency of CPT codes on diagnosis codes (Dx). When printing the Remittance Advice I transformed the data to present it in the format that you're trying to accomplish, using Crystal (the original app was written in VB4 with SQL 6.5). Later, when I recoded the reporting piece for ActiveReports control, I created a sub-report to display the Dx codes as a comma-separated list. But the concept remained the same, - ClaimHeader-->>ClaimDetail-->>ClaimDxCodes. Do not store Dx codes in the same table as your CPT codes, or your claim header info! Doing so violates the fundamentals of relational database principles, and B. Lindman WILL pull out the complete works of Codd at you...Let the massacre begin!..|||That's got me out of a tight spot, thanks very much.
Cheers, Simon.|||I'm really a nice guy. Really.

Thursday, February 16, 2012

Can I Restrict Values in a Parameter Dropdown based on the User?

I've got a puzzler here. I have a report with 4 different dropdown lists for
parameters. One of them is Department Id. For some users, I want them to be
able to only run the report against their own department id but still be able
to select any of the values for the other parameters. Other users will be
able to select any value for department id and the other parameters.
I can figure out how to write the query to populate the Department ID
dropdown but cannot figure how to access the current userid programatically.
Is there a way to access the user ID in order to restrict the values in a
parameter dropdown?
I'm using RS2003 with SQL Server 2000. Any ideas are welcome!
Thanks,
LizThis is really quite easy. There is a global parameter called User!UserID it
returns the domain and user ID of the user running the report. To see the
format of this create a report with a single textbox (no datasets). Set the
text box to an expression and set it to this (case sensitive):
=User!UserID
You might want to strip the domain off.
OK, the second part of this is that you need to realize that query parameter
and report parameter are two different things. You do not have to have a
query parameter mapped to a report parameter. It can be mapped to an
expression instead.
So, let's say you have this query
Select department from mydepartmentlist where userID = @.UserID
RS will create a report parameter for this called UserID. You will not use
it. You will delete it. Instead in the dataset tab click on the ... go to
the parameters tab, click on the dropdown box where the query parameter is
being mapped to report parameter and pick expression instead. Then set it to
the expression above. If you don't want the domain then use something like
this:
=mid(User!UserID, LengthToCutOffTheFront)
Of course for all of this to work you need a table somewhere that maps the
userid to the departments.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Liz" <Liz@.discussions.microsoft.com> wrote in message
news:5A54A060-B916-4BB4-AE03-9AF8302601A7@.microsoft.com...
> I've got a puzzler here. I have a report with 4 different dropdown lists
> for
> parameters. One of them is Department Id. For some users, I want them to
> be
> able to only run the report against their own department id but still be
> able
> to select any of the values for the other parameters. Other users will be
> able to select any value for department id and the other parameters.
> I can figure out how to write the query to populate the Department ID
> dropdown but cannot figure how to access the current userid
> programatically.
> Is there a way to access the user ID in order to restrict the values in a
> parameter dropdown?
> I'm using RS2003 with SQL Server 2000. Any ideas are welcome!
> Thanks,
> Liz
>|||Thanks! That's very helpful and I've already succeeded at stripping off the
domain name.
I know you can pass parameter values through the URL and query string to the
report. If I wanted to pass the department ID from the calling application
to populate the dropdown, is there a way to pass that one parameter value and
let the user select the other three?
Thanks for your help,
Liz
"Bruce L-C [MVP]" wrote:
> This is really quite easy. There is a global parameter called User!UserID it
> returns the domain and user ID of the user running the report. To see the
> format of this create a report with a single textbox (no datasets). Set the
> text box to an expression and set it to this (case sensitive):
> =User!UserID
> You might want to strip the domain off.
> OK, the second part of this is that you need to realize that query parameter
> and report parameter are two different things. You do not have to have a
> query parameter mapped to a report parameter. It can be mapped to an
> expression instead.
> So, let's say you have this query
> Select department from mydepartmentlist where userID = @.UserID
> RS will create a report parameter for this called UserID. You will not use
> it. You will delete it. Instead in the dataset tab click on the ... go to
> the parameters tab, click on the dropdown box where the query parameter is
> being mapped to report parameter and pick expression instead. Then set it to
> the expression above. If you don't want the domain then use something like
> this:
> =mid(User!UserID, LengthToCutOffTheFront)
> Of course for all of this to work you need a table somewhere that maps the
> userid to the departments.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Liz" <Liz@.discussions.microsoft.com> wrote in message
> news:5A54A060-B916-4BB4-AE03-9AF8302601A7@.microsoft.com...
> > I've got a puzzler here. I have a report with 4 different dropdown lists
> > for
> > parameters. One of them is Department Id. For some users, I want them to
> > be
> > able to only run the report against their own department id but still be
> > able
> > to select any of the values for the other parameters. Other users will be
> > able to select any value for department id and the other parameters.
> >
> > I can figure out how to write the query to populate the Department ID
> > dropdown but cannot figure how to access the current userid
> > programatically.
> > Is there a way to access the user ID in order to restrict the values in a
> > parameter dropdown?
> >
> > I'm using RS2003 with SQL Server 2000. Any ideas are welcome!
> >
> > Thanks,
> > Liz
> >
> >
>
>|||Sure. You have have a hidden parameter that gets filled in by the calling
application. The user only selects from the list you provide. Look at hidden
parameters in the books online.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Liz" <Liz@.discussions.microsoft.com> wrote in message
news:D0F372BF-DA85-4BD6-A01C-ED2708F14BAC@.microsoft.com...
> Thanks! That's very helpful and I've already succeeded at stripping off
> the
> domain name.
> I know you can pass parameter values through the URL and query string to
> the
> report. If I wanted to pass the department ID from the calling
> application
> to populate the dropdown, is there a way to pass that one parameter value
> and
> let the user select the other three?
> Thanks for your help,
> Liz
>
> "Bruce L-C [MVP]" wrote:
>> This is really quite easy. There is a global parameter called User!UserID
>> it
>> returns the domain and user ID of the user running the report. To see the
>> format of this create a report with a single textbox (no datasets). Set
>> the
>> text box to an expression and set it to this (case sensitive):
>> =User!UserID
>> You might want to strip the domain off.
>> OK, the second part of this is that you need to realize that query
>> parameter
>> and report parameter are two different things. You do not have to have a
>> query parameter mapped to a report parameter. It can be mapped to an
>> expression instead.
>> So, let's say you have this query
>> Select department from mydepartmentlist where userID = @.UserID
>> RS will create a report parameter for this called UserID. You will not
>> use
>> it. You will delete it. Instead in the dataset tab click on the ... go to
>> the parameters tab, click on the dropdown box where the query parameter
>> is
>> being mapped to report parameter and pick expression instead. Then set it
>> to
>> the expression above. If you don't want the domain then use something
>> like
>> this:
>> =mid(User!UserID, LengthToCutOffTheFront)
>> Of course for all of this to work you need a table somewhere that maps
>> the
>> userid to the departments.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Liz" <Liz@.discussions.microsoft.com> wrote in message
>> news:5A54A060-B916-4BB4-AE03-9AF8302601A7@.microsoft.com...
>> > I've got a puzzler here. I have a report with 4 different dropdown
>> > lists
>> > for
>> > parameters. One of them is Department Id. For some users, I want them
>> > to
>> > be
>> > able to only run the report against their own department id but still
>> > be
>> > able
>> > to select any of the values for the other parameters. Other users will
>> > be
>> > able to select any value for department id and the other parameters.
>> >
>> > I can figure out how to write the query to populate the Department ID
>> > dropdown but cannot figure how to access the current userid
>> > programatically.
>> > Is there a way to access the user ID in order to restrict the values in
>> > a
>> > parameter dropdown?
>> >
>> > I'm using RS2003 with SQL Server 2000. Any ideas are welcome!
>> >
>> > Thanks,
>> > Liz
>> >
>> >
>>

Tuesday, March 20, 2012

Can not change filter on model report - UPDATED 7th June

Can not change filter on model report - UPDATED

Sunday, March 11, 2012

Can MDX or latch a value based on a signal?

Wednesday, March 7, 2012

Can I use Reporting Services 2005 against a Analysis Services 2000 OLAP cube?

Friday, February 24, 2012

can I update based on a join result with a calculation

Can I split a field based on a character?

Thursday, February 16, 2012

Can I Restrict Values in a Parameter Dropdown based on the User?

install SQL on WinXP

Blog Archive

About Me