Hide Columns

Hi,
I have created a report that contains a table. The table returns data w/
multiple statuses. I have created a group based on the status and have a
page break at the end of the group (so each page will display only one status
type). I would like to dynamically show / hide columns based on the status.
Is this possible? I tried setting the visibility of the individual cells,
however, this leaves a gap where the cells were (which is not desirable)â?¦
Any thoughts?
Thanks!Try setting the visibility property on the column instead of the
individual cells.
On Sep 5, 4:18 pm, David <Da...@.discussions.microsoft.com> wrote:
> Hi,
> I have created a report that contains a table. The table returns data w/
> multiple statuses. I have created a group based on the status and have a
> page break at the end of the group (so each page will display only one status
> type). I would like to dynamically show / hide columns based on the status.
> Is this possible? I tried setting the visibility of the individual cells,
> however, this leaves a gap where the cells were (which is not desirable)...
> Any thoughts?
> Thanks!|||Hi Jen,
Can this be done with a Cross-Tab Report? I dont see the Visibility Property
of the column group :( .
--
With Thanks and Regards,
Raja Krishnamurthy
"Jen" wrote:
> Try setting the visibility property on the column instead of the
> individual cells.
> On Sep 5, 4:18 pm, David <Da...@.discussions.microsoft.com> wrote:
> > Hi,
> >
> > I have created a report that contains a table. The table returns data w/
> > multiple statuses. I have created a group based on the status and have a
> > page break at the end of the group (so each page will display only one status
> > type). I would like to dynamically show / hide columns based on the status.
> > Is this possible? I tried setting the visibility of the individual cells,
> > however, this leaves a gap where the cells were (which is not desirable)...
> >
> > Any thoughts?
> >
> > Thanks!
>
>

Hide aspnet_* objects

Hello, I would like to not have to see the aspnet_ tables and stored procedures that are created when using the Membership, roles, and personalization. Currently I have to suffer seeing the handful of tables and 40+ stored procedures in both the Visual Studio and the SQL Management tool. I have found that on a 2000 SQL Server I can execute a command that forces objects to be created as system objects. If I execute this before creating the objects they become system objects and I don't have to see them any longer.

However, this trick doesnot work in SQL Server 2005. So, I would like to know either 1) is there an easy way to hide these objects or 2) is there a way to change the objects to system objects in SQL Server 2005?

CodeGuy

Right click on the tables folder in Management Studio and choose Filter -> Filter settings.

Now create a filter using name NOT CONTAINING aspnet_

Hope it helps

|||

Klaus, thank you for mentioning that. I actually found reference to that as well. Two downsides to that approach, first you have to set the filter upEVERY TIME, because it's not saved. Second, that only helps me a small amount because it only works in the Management Studio.

CodeGuy

|||

Another option would be to use a separate database for the aspnet_* tables... but that has drawbacks aswell. Hope you find a good solution and keep us posted.

|||

Klaus, thank you for the ideas. You suggestion definitely would work, however, I'd prefer to have these objects in the same database for portability reasons.

Anyone else have any good ideas?

hidden views

hi ,
i have created views in database Test, in sqlServer.when i had to export Test to another database Test1, the views become hiddden.
i can't see the views and when i try to create them again, i get this msg:
There is already an object named 'view1' in the database.
so how can i resolve this problem?

thanks,

when u export view from one database to another the view is created as table in the target database. To migrate view , create script of the view and run that script in Target DB... ... do not select view in Export wizards only select tables...

if you are getting View1 already exists then :-

Check in Tables there will be a table called View1 , first drop that and run the script for view1 to create...

Madhu

|||When you choose to Generate script for the database objects, then it should have that value to drop the object before creating it, follow as suggested.|||

Well, thanks!

As u said, the views were created as tables in the target DB. So i droped them and made a new script for the views.

Myra

|||

thanks for ur help!

myra

Hidden Values are not calculated in SSRS 2005?!

Hi,
I just created a Report in SSRS 2005 and wanted to use the "old" trick to
display data values in the header (reference body-elements from the header).
I know this worked fine in SSRS 2000, but in SSRS 2005 it seems, that they
have build in some "optimization" which doesn't render/calculate hidden
Fields anymore.
So, when I reference a Field that isn't hidden it works just fine - but as
soon as I hide the referenced field in the body it never gets a value, thus
the textbox in the header stays empty as well.
Initially I referenced Textboxes embedded in a table, but it neither works
for texboxes which are directly contained in the body-region. As soon as an
element is hidden it seems to be ignored by the rendering engine.
Any help would be appreciated.
Thanks,
FlorianA suitable workaround would be to set the font color to the same color as
the background color.
-Tim
"Florian" <Florian@.discussions.microsoft.com> wrote in message
news:AB31608E-EFF9-4D0B-865C-956519A96859@.microsoft.com...
> Hi,
> I just created a Report in SSRS 2005 and wanted to use the "old" trick to
> display data values in the header (reference body-elements from the
> header).
> I know this worked fine in SSRS 2000, but in SSRS 2005 it seems, that they
> have build in some "optimization" which doesn't render/calculate hidden
> Fields anymore.
> So, when I reference a Field that isn't hidden it works just fine - but as
> soon as I hide the referenced field in the body it never gets a value,
> thus
> the textbox in the header stays empty as well.
> Initially I referenced Textboxes embedded in a table, but it neither works
> for texboxes which are directly contained in the body-region. As soon as
> an
> element is hidden it seems to be ignored by the rendering engine.
> Any help would be appreciated.
> Thanks,
> Florian

hidden parameters are not hidden

Hello,
I've created a report with a parameter with no prompt ( hidden ) and a
second one with a prompt. When I switch to preview in Visual Studio only the
second parameter appears. This is OK.
When I launch the report in IE :
http://servername/ReportServer?/ReportFolder/Myreport&Param1=1&Param2=2
both parameters appear in the parameter section !?!From the SP1 readme:
4.3.1 Hiding Parameters in a Published Report
In Report Manager, you can now set parameter properties in a way that
allows you to achieve two objectives simultaneously:
· Hide the parameter fields in a published report.
· Specify a parameter value at run time (for example, through a
subscription that is used to trigger report execution and delivery).
Previously, the only way to hide a parameter value was to clear the Prompt
User check box in the Parameters properties page. However, a side effect of
clearing the check box was that you could no longer specify a parameter
value for the report at run time. This limitation has been removed. In SP1,
you can clear the Prompt User check box to hide the parameter fields and
values in the report. Doing so does not introduce restrictions on how you
subsequently set the parameter value externally at run time.
--
| Thread-Topic: hidden parameters are not hidden
| thread-index: AcTjhM6KynxAWJglRZyZ76tv0LGvGQ==| X-WBNR-Posting-Host: 198.240.212.30
| From: "=?Utf-8?B?UGFzY2FsIFNpbWxlcg==?=" <Pascal
Simler@.discussions.microsoft.com>
| Subject: hidden parameters are not hidden
| Date: Thu, 16 Dec 2004 07:35:01 -0800
| Lines: 9
| Message-ID: <7E509939-5E13-4BBD-987D-F73CA24397CE@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
| Path: cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.reportingsvcs:37567
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| Hello,
|
| I've created a report with a parameter with no prompt ( hidden ) and a
| second one with a prompt. When I switch to preview in Visual Studio only
the
| second parameter appears. This is OK.
| When I launch the report in IE :
| http://servername/ReportServer?/ReportFolder/Myreport&Param1=1&Param2=2
| both parameters appear in the parameter section !?!
|
||||Brad, are you aware of any probelms when using hidden parameters along with
prompted credentials? The result I encounter when attempting this is that RS
continually prompts for credentials rather than proceeding to view the
report...
""Brad Syputa - MS"" wrote:
> From the SP1 readme:
> 4.3.1 Hiding Parameters in a Published Report
> In Report Manager, you can now set parameter properties in a way that
> allows you to achieve two objectives simultaneously:
> · Hide the parameter fields in a published report.
> · Specify a parameter value at run time (for example, through a
> subscription that is used to trigger report execution and delivery).
> Previously, the only way to hide a parameter value was to clear the Prompt
> User check box in the Parameters properties page. However, a side effect of
> clearing the check box was that you could no longer specify a parameter
> value for the report at run time. This limitation has been removed. In SP1,
> you can clear the Prompt User check box to hide the parameter fields and
> values in the report. Doing so does not introduce restrictions on how you
> subsequently set the parameter value externally at run time.
> --
> | Thread-Topic: hidden parameters are not hidden
> | thread-index: AcTjhM6KynxAWJglRZyZ76tv0LGvGQ==> | X-WBNR-Posting-Host: 198.240.212.30
> | From: "=?Utf-8?B?UGFzY2FsIFNpbWxlcg==?=" <Pascal
> Simler@.discussions.microsoft.com>
> | Subject: hidden parameters are not hidden
> | Date: Thu, 16 Dec 2004 07:35:01 -0800
> | Lines: 9
> | Message-ID: <7E509939-5E13-4BBD-987D-F73CA24397CE@.microsoft.com>
> | MIME-Version: 1.0
> | Content-Type: text/plain;
> | charset="Utf-8"
> | Content-Transfer-Encoding: 7bit
> | X-Newsreader: Microsoft CDO for Windows 2000
> | Content-Class: urn:content-classes:message
> | Importance: normal
> | Priority: normal
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> | Newsgroups: microsoft.public.sqlserver.reportingsvcs
> | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
> | Path: cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl
> | Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.reportingsvcs:37567
> | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
> |
> | Hello,
> |
> | I've created a report with a parameter with no prompt ( hidden ) and a
> | second one with a prompt. When I switch to preview in Visual Studio only
> the
> | second parameter appears. This is OK.
> | When I launch the report in IE :
> | http://servername/ReportServer?/ReportFolder/Myreport&Param1=1&Param2=2
> | both parameters appear in the parameter section !?!
> |
> |
>

Hidden parameter using userid

I have created a hidden parameter with a default value of user!userid so I can pass this to a UDF which returns a table with data dependent on the logged on windows user. This works fine until I view the report using Report Manager when any alteration to any of the other parameters requires me to click twice on the View Report button before the report is displayed. The problem does not occur if I have a hidden parameter which either has a default value obtained from a query or is a constant. Any ideas on how I can overcome this? Thanks.I have since discovered the problem is not as stated but is due to the fact that I have hidden parameters dependent on the input of an earlier parameter. If the input parameter is a drop down then all is OK but if you have to interactively enter a value then you have to click the View Report button twice. Removing the userid parameter meant that the dependent parameter was last in the list and again all was then OK; however, I still have some reports where I have more than one hidden parameter dependent on an earlier value so the problem still occurs.
"JohnH" wrote:
> I have created a hidden parameter with a default value of user!userid so I can pass this to a UDF which returns a table with data dependent on the logged on windows user. This works fine until I view the report using Report Manager when any alteration to any of the other parameters requires me to click twice on the View Report button before the report is displayed. The problem does not occur if I have a hidden parameter which either has a default value obtained from a query or is a constant. Any ideas on how I can overcome this? Thanks.

HexToInt and HexToSmallInt

Inspired/challenged by Hans Lindgren's stored procedures of these same names
posted on SQLServerCentral, I created these. Note that they produce strange
results on non-hexadecimal strings, and may have issues with byte-ordering
in some architectures (but Itanium is little-endian like x86 and x64,
right?).

How do they work? well, the distance between one after '9' (':') and 'A' is
7 in ASCII. Also, if I subtract 48 from an upper-cased hex-digit, digit & 16
will always be equal to 16. So I can mask that bit out, shift it down 4 bits
(/16), multiply by 7, subtract from the original value, and come up with a
value from 0 to 15. This can be done on all 8 digits in parallel, as you can
see below.

I use CAST(CAST('1234ABCD' AS BINARY(8))AS BIGINT) to put the hex value
1234ABCD into a number I can manipulate, then subtract the value '00000000'
(CAST(0x3030303030303030 AS BIGINT)), then mask out the hex overflow bits,
shift right, multiply by 7, subtract to make the values 0x010203040A0B0C0D,
then I shift the bits into the proper places and add.

It's probably easier in assembly language than SQL, but oh well.

It's only about 20% faster than Hans's series of CHARINDEX calls.

CREATE FUNCTION dbo.HexToINT
(
@.Value VARCHAR(8)
)
RETURNS INT
AS
BEGIN
DECLARE @.I BIGINT
SET @.I = CAST(CAST(RIGHT( UPPER( '00000000' + @.Value ) , 8 )
AS BINARY(8)) AS BIGINT) - 3472328296227680304
SET @.I=@.I-((@.I/16)&CAST(72340172838076673 AS BIGINT))*7
RETURN (
(@.I&15)
+((@.I/16)&240)
+((@.I/256)&3840)
+((@.I/4096)&61440)
+((@.I/65536)&983040)
+((@.I/1048576)&15728640)
+((@.I/16777216)&251658240)
+((@.I/72057594037927936)*268435456) -- cause an OF if > 0x80000000
)

END
GO

CREATE FUNCTION dbo.HexToSMALLINT
(
@.Value VARCHAR(4)
)
RETURNS SMALLINT
AS
BEGIN
DECLARE @.I INT
SET @.I = CAST(CAST(RIGHT( UPPER( '0000' + @.Value ) , 4 )
AS BINARY(4)) AS INT) - 808464432
SET @.I=@.I-(@.I&269488144)*7/16
RETURN (
@.I&255
+(@.I&65280)/16
+(@.I&16711680)/256
+(@.I&2130706432)/4096
)
END
GOThe revised versions below will allow negative numbers, eg.
HexToINT('80000000')

--

Inspired/challenged by Hans Lindgren's stored procedures of these same names
posted on SQLServerCentral, I created these. Note that they produce strange
results on non-hexadecimal strings, and may have issues with byte-ordering
in some architectures (but Itanium is little-endian like x86 and x64,
right?).

How do they work? well, the distance between one after '9' (':') and 'A' is
7 in ASCII. Also, if I subtract 48 from an upper-cased hex-digit, digit & 16
will always be equal to 16. So I can mask that bit out, shift it down 4 bits
(/16), multiply by 7, subtract from the original value, and come up with a
value from 0 to 15. This can be done on all 8 digits in parallel, as you can
see below.

I use CAST(CAST('1234ABCD' AS BINARY(8)) AS BIGINT) to put the
string of hexadecimal digit characters 1234ABCD into a number I can
manipulate,
then subtract the value '00000000' (CAST(0x3030303030303030 AS BIGINT)),
then mask out the hex overflow bits, shift right 4 places (/16), multiply by
7,
subtract to make the values 0x010203040A0B0C0D,
then I shift the bits into the proper places and add. to result in
0x1234ABCD, CAST AS INT.

alter FUNCTION dbo.HexToSMALLINT
(
@.Value VARCHAR(4)
)
RETURNS SMALLINT
AS
BEGIN
DECLARE @.I INT
SET @.I = CAST(CAST(RIGHT( UPPER( '0000' + @.Value ) , 4 )
AS BINARY(4)) AS INT) - 808464432
SET @.I=@.I-(@.I&269488144)*7/16
RETURN CAST(CAST(
(@.I&15)
+((@.I/16)&240)
+((@.I/256)&3840)
+((@.I/4096)&61440)
AS BINARY(2))AS SMALLINT)
END
GO

alter FUNCTION dbo.HexToINT
(
@.Value VARCHAR(8)
)
RETURNS INT
AS
BEGIN
DECLARE @.I BIGINT
SET @.I = CAST(CAST(RIGHT( UPPER( '00000000' + @.Value ) , 8 )
AS BINARY(8)) AS BIGINT) - 3472328296227680304
SET @.I=@.I-((@.I/16)&CAST(72340172838076673 AS BIGINT))*7
RETURN CAST(CAST(
(@.I&15)
+((@.I/16)&240)
+((@.I/256)&3840)
+((@.I/4096)&61440)
+((@.I/65536)&983040)
+((@.I/1048576)&15728640)
+((@.I/16777216)&251658240)
+(CAST(@.I/72057594037927936 AS BIGINT)*268435456)
AS BINARY(4))AS INT)
END
GO

Hewlp needed creating a stored proceedure

Can someone please help me...I have created a DNN module that works onthe test site but when I upload the module zip to a new site I get anerror on creating my stored proceedure as follows:

StartJobBegin Sql executionInfoExecuting 01.00.00.SqlDataProviderStartJobStart Sql execution: 01.00.00.SqlDataProvider fileFailureSQL Execution resulted infollowing Exceptions: System.Data.SqlClient.SqlException: Line 25: Incorrect syntax near '@.Str_Title'. Line 51: Incorrect syntax near '@.Str_Title'. at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteNonQuery(SqlConnection connection, CommandType commandType, String commandText, SqlParameter[] commandParameters) at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteNonQuery(String connectionString, CommandType commandType, String commandText, SqlParameter[] commandParameters) at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteNonQuery(String connectionString, CommandType commandType, String commandText) at DotNetNuke.Data.SqlDataProvider.ExecuteScript(String Script, Boolean UseTransactions) CREATE PROCEDURE dbo. ListTAS_Journal @.PortalID int, @.SortOrder tinyint = NULL, @.Str_Title varchar(100) = '', @.Str_Text varchar(100) = '' AS IF ISNULL(@.Str_Title, '') = '' or ISNULL(@.Str_Text, '') = '' SELECT [EntryID], [PortalID], [ModuleID], [Title], [Text], [DateAdded], [DateMod], [Owner], [Access] FROM TAS_Journal WHERE PortalID = @.PortalID AND (Title like COALESCE('%' @.Str_Title '%' ,Title , '') AND Text like COALESCE('%' @.Str_Text '%' ,Text, '')) ORDER BY (CASE WHEN @.SortOrder = 1 THEN DateAdded WHEN @.SortOrder = 0 THEN DateMod END) DESC, EntryID DESC else /***Select from either field ***/ SELECT [EntryID], [PortalID], [ModuleID], [Title], [Text], [DateAdded], [DateMod], [Owner], [Access] FROM TAS_Journal WHERE PortalID = @.PortalID AND (Title like COALESCE('%' @.Str_Title '%' ,Title , '') OR Text like COALESCE('%' @.Str_Text '%' ,Text, '')) ORDER BY (CASE WHEN @.SortOrder = 1 THEN DateAdded WHEN @.SortOrder = 0 THEN DateMod END) DESC, EntryID DESCEndJobEnd Sql execution: 01.00.00.SqlDataProvider file

The SP looks like this:

/* --------------------------
/ ListTAS_Journal
/ -------------------------- */
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier} ListTAS_Journal
@.PortalID int,
@.SortOrder tinyint = NULL,
@.Str_Title varchar(100) = '',
@.Str_Text varchar(100) = ''
AS

IF ISNULL(@.Str_Title, '') = '' or ISNULL(@.Str_Text, '') = ''

SELECT
[EntryID],
[PortalID],
[ModuleID],
[Title],
[Text],
[DateAdded],
[DateMod],
[Owner],
[Access]
FROM
TAS_Journal
WHERE
PortalID = @.PortalID AND
(Title like COALESCE('%' + @.Str_Title + '%' ,Title , '') AND
Text like COALESCE('%' + @.Str_Text + '%' ,Text, ''))

ORDER BY
(CASE
WHEN @.SortOrder = 1 THEN DateAdded
WHEN @.SortOrder = 0 THEN DateMod
END) DESC, EntryID DESC
else
/***Select from either field
***/
SELECT
[EntryID],
[PortalID],
[ModuleID],
[Title],
[Text],
[DateAdded],
[DateMod],
[Owner],
[Access]
FROM
TAS_Journal
WHERE
PortalID = @.PortalID AND
(Title like COALESCE('%' + @.Str_Title + '%' ,Title , '') OR
Text like COALESCE('%' + @.Str_Text + '%' ,Text, ''))

ORDER BY
(CASE
WHEN @.SortOrder = 1 THEN DateAdded
WHEN @.SortOrder = 0 THEN DateMod
END) DESC, EntryID DESC
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

This SP works on the test site.
Any help would be creatly apreciated

Mark

CREATE PROCEDURE dbo. ListTAS_Journal @.PortalID int, @.SortOrder tinyint = NULL, @.Str_Title varchar(100) = '', @.Str_Text varchar(100) = '' AS IF ISNULL(@.Str_Title, '') = '' or ISNULL(@.Str_Text, '') = '' SELECT [EntryID], [PortalID], [ModuleID], [Title], [Text], [DateAdded], [DateMod], [Owner], [Access] FROM TAS_Journal WHERE PortalID = @.PortalID AND (Title like COALESCE('%' @.Str_Title '%' ,Title , '') AND Text like COALESCE('%' @.Str_Text '%' ,Text, '')) ORDER BY (CASE WHEN @.SortOrder = 1 THEN DateAdded WHEN @.SortOrder = 0 THEN DateMod END) DESC, EntryID DESC else /***Select from either field ***/ SELECT [EntryID], [PortalID], [ModuleID], [Title], [Text], [DateAdded], [DateMod], [Owner], [Access] FROM TAS_Journal WHERE PortalID = @.PortalID AND (Title like COALESCE('%' @.Str_Title '%' ,Title , '') OR Text like COALESCE('%' @.Str_Text '%' ,Text, '')) ORDER BY (CASE WHEN @.SortOrder = 1 THEN DateAdded WHEN @.SortOrder = 0 THEN DateMod END) DESC, EntryID DESC

Notice, your pluses are missing.

|||Hi Thanks for the reply,

Yes the Pluses are in the sql file if you look at my first post but theerror is reporting they are not there so DNN must be stripping them outfor some reason!

Mark

Help-Forecasting Model Viewer

Hi,
I am working in Share point service now.

I created a website using share point.
I worked in mining algorithms in sqlserevr 2005.
I am able to view scorecard, BIP in my website.

My Question is:
I am having a forecasting model developed using Time series algorithm in Sqlserver 2005.
I need viewer to view my forecasting model, I created.
Plz suggest me any viewer like DataAnalyzer, to view my forecasting model.
Tx in Advance!

Was your question answered in the newsgorup, or do you still need more information?

Realistically you can use any charting control and simply query the model for the data if the redistributable viewers don't work for you.

|||

Hai,

I used pivot table OWC to show my data.

Unfortunately, it doenot work with DMX query.

But spreadsheet OWC accepts DMX query. why not pivot table?

I need some solution to run my DMX query and give as input to my chart/pivot control.

plz help me.

its urgent

Tx in advance.

|||You will have to write some custom Excel code (you can find samples on sqlserverdatamining.com) to execute the DMX query and put it in a worksheet. Then you can put the result into an Excel pivot table

Help-Forecasting Model Viewer

Hi,
I am working in Share point service now.

I created a website using share point.
I worked in mining algorithms in sqlserevr 2005.
I am able to view scorecard, BIP in my website.

My Question is:
I am having a forecasting model developed using Time series algorithm in Sqlserver 2005.
I need viewer to view my forecasting model, I created.
Plz suggest me any viewer like DataAnalyzer, to view my forecasting model.
Tx in Advance!

Was your question answered in the newsgorup, or do you still need more information?

Realistically you can use any charting control and simply query the model for the data if the redistributable viewers don't work for you.

|||

Hai,

I used pivot table OWC to show my data.

Unfortunately, it doenot work with DMX query.

But spreadsheet OWC accepts DMX query. why not pivot table?

I need some solution to run my DMX query and give as input to my chart/pivot control.

plz help me.

its urgent

Tx in advance.

|||You will have to write some custom Excel code (you can find samples on sqlserverdatamining.com) to execute the DMX query and put it in a worksheet. Then you can put the result into an Excel pivot table

Help: Question to the Group on Report Grouping

Has anyone successfully created a report that has a main Table and 2 Group
Levels and correctly shows aggregates (SUM) in both Group footers and the
Table footer?
Like this:
_______________________________________________________________
Table
Group #1
Group #2
Detail Row #1 (this shows fields that relate to Group #1. HideDuplicates is
set to True.
Detail Row #2 (this shows fields that relate to Group #2)
Group #2 Footer
Group #1 Footer
Table Footer
_______________________________________________________________
The data I am selecting contains, per row, all the data to populate the
detail rows. (It's basically a flat row that selects invoice and invoice
line item information. So it looks like this:
CustID, InvoiceID, Invoice Number, Invoice Amt, Invoice Line Item ID,
Invoice Line Item Amount...
1,001,123ABC,100.00,1,50
1,001,123ABC,100,2,50
... and so on...
The Invoice fields go in the first detail row. The invoice line item fields
go in the 2nd detail row. I'm just showing line items by invoice by
customer.
I have SUMs in the Group #2 footer. Works fine.
I have SUMs in the Group #1 footer. Problem: Since each detail row
contains the value, it adds them all. For example, if I put invoice amount
in Detail Row #1 and SUM(invoiceamount) in the Group #1 footer it adds
invoice amount in every detail row so, of course, the value isn't correct.
Now, it only shows on the report once because of HideDuplicates is true.
The same applies to what goes in the Table footer.
Is this report design wrong? Any ideas on how to handle this'
--
Adrian M.
MCPHi Adrian,
I'm not 100% sure I understand how you are designing your report, how's
this?: You have many invoices, and each invoice has many line items,
right? So for each invoice you want a sum of the invoice amount?
Without knowing what your groups are, here is what I would suggest, I
hope this helps: I would add another group - this group would be where
you put your invoice fields. There would then only be 1 detail row for
your line items.
Group #1 ?
Group #2 ?
Group #3 invoice info
Detail Row line items
Take care,
Michelle|||I think my question is similar. I'm trying to total items that are grouped
but I don't want all the items totaled, just the grouping. For example, I
have 1 - 1, 2-2's and 3-3's, which display as, 1-1, 2-2, 3-3 (in a table
format). I want to sum those 'groups', meaning the sum would be '6', not '14'.
How can I do that?
Bill
"Adrian M." wrote:
> Has anyone successfully created a report that has a main Table and 2 Group
> Levels and correctly shows aggregates (SUM) in both Group footers and the
> Table footer?
> Like this:
> _______________________________________________________________
> Table
> Group #1
> Group #2
> Detail Row #1 (this shows fields that relate to Group #1. HideDuplicates is
> set to True.
> Detail Row #2 (this shows fields that relate to Group #2)
> Group #2 Footer
> Group #1 Footer
> Table Footer
> _______________________________________________________________
> The data I am selecting contains, per row, all the data to populate the
> detail rows. (It's basically a flat row that selects invoice and invoice
> line item information. So it looks like this:
> CustID, InvoiceID, Invoice Number, Invoice Amt, Invoice Line Item ID,
> Invoice Line Item Amount...
> 1,001,123ABC,100.00,1,50
> 1,001,123ABC,100,2,50
> ... and so on...
> The Invoice fields go in the first detail row. The invoice line item fields
> go in the 2nd detail row. I'm just showing line items by invoice by
> customer.
>
> I have SUMs in the Group #2 footer. Works fine.
> I have SUMs in the Group #1 footer. Problem: Since each detail row
> contains the value, it adds them all. For example, if I put invoice amount
> in Detail Row #1 and SUM(invoiceamount) in the Group #1 footer it adds
> invoice amount in every detail row so, of course, the value isn't correct.
> Now, it only shows on the report once because of HideDuplicates is true.
> The same applies to what goes in the Table footer.
> Is this report design wrong? Any ideas on how to handle this'
> --
> Adrian M.
> MCP
>
>|||So are you trying to COUNT and not SUM?|||No, I'm trying to sum. I'm trying to su the values that are grouped, not all
the values of data set.
"Vivienne" wrote:
> So are you trying to COUNT and not SUM?
>|||I have a similar problem and I know there has to be a way to do it. My
dataset query returns results like this:
Company Value CaseNumber CaseDate
---
Microsoft 134.40 00462 June 25
Microsoft 134.40 01568 May 6
Microsoft 134.40 00224 Sept 12
Sony 212.00 00986 Mar 22
Sony 212.00 02944 Dec 1
I want the company and value shown at the group level, and each case listed
in the details for each company. I can get everything to work except for a
grand total of the company's values.
Here is how I want the report to look:
Microsoft 134.40
00462 June 25
01568 May 6
00224 Sept 12
Sony 212.00
00986 Mar 22
02944 Dec 1
--
Total 346.40
Everything I've tried gives me an error, or returns a Total of 827.20
instead of 346.40.
Thanks in advance for any help.

Help: Odd Error Mess :The table terms has been created but its max rowsize(8850)

Hi, I've come across a error message and I'm not sure what to do.

Warning: The table 'terms' has been created but its maximum row size (8850) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.

I've tried playing with odd and ends, but I'm still getting the message does anyone know hwo you expand the maxium number of bytes per row.

Thanks For any help.You obviously have too much information to fit in one row.

This error message is caused by either a table with too many columns or a few very wide columns.

The best solution is to break up your table in to 2, 3 smaller tables and have a one-to-one relationship. The combination of the matching rows will meet your larger rowsize requirement.|||SQL Server has a 8K block size, which means that a single row cannot exceed this size (8060 bytes). When all column sizes are added up, SQL is telling you that that number potentially exceeds that size (this is because of varchars). You only have 2 options, 1 - reduce the size of your columns until the total is below 8060, or 2 - split the table up - as the previous post suggests.|||You cannot "force" sql to overcome the limit of 8060 bytes per row.

Anyway you'll get an error ONLY IF your actual data is larger that 8060 bytes.
For example if your table is made of two columns of VARCHAR(5000) you'll get the warning you already know.
Than you can insert values in your table with no problem, expect for that rows that are bigger that the near 8Kb limit.

If you really need to have not virtual limits you can use the TEXT or IMAGE data types.

HELP: Failed to create the task. (Microsoft Visual Studio)

I created my first SSIS project in BI Dev Studio but get the following error when trying to add a data flow task to the Control container:

==================================

Failed to create the task. (Microsoft Visual Studio)

===================================

Value does not fall within the expected range. (Microsoft.SqlServer.ManagedDTS)

Program Location:

at Microsoft.SqlServer.Dts.Runtime.Executables.Add(String moniker)
at Microsoft.DataTransformationServices.Design.DtsBasePackageDesigner.CreateExecutable(String moniker, IDTSSequence container, String name)

Any advice greatly appreciated..

cheers,
jordan

What build are you on? June CTP by chance?|||I just installed the June CPT and I am gettting this same error. Any ideas?

Thanks in advance.|||

Here's the entire error message:

===================================

Value does not fall within the expected range. (Microsoft.SqlServer.ManagedDTS)

Program Location:

at Microsoft.SqlServer.Dts.Runtime.Executables.Add(String moniker)
at Microsoft.DataTransformationServices.Design.DtsBasePackageDesigner.CreateExecutable(String moniker, IDTSSequence container, String name)

|||Yes it is June CTP Developer Edition.|||I also couldn't use the SQL Server Import and Export Wizard. The error that I get:

The SSIS data flow task could not be created. Verify that DTSPipeline.dll is available and registered. The wizard cannot continue and it will terminate.

Value does not fall within the expected range. (Microsoft.SQLServer.DTSRuntimeWrap)|||Hmmm, very odd. It sounds as though you have a corrupted installation. Is this the first time you've installed SQL Server on that machine, IOW is it a clean install? You might try to regsvr32 DTSPipeline.dll. Then try again. It appears that it's just not registered.|||

I have tried uninstalling and reinstalling the CPT 4 times. The last time I did a regclean, deleted left over directories and anything else I could thing of the make the install clean.

One thing I should mention is that I had the VSS.Net 2005 Beta 2 on my machine before I installed the SQL Server 2005 CPT and I had to remove it in order to go forward with the SQL install. Not sure if that left something’s behind. I did use the SQL removal tools to get things off my machine the last couple of times, but not the first. The first time I used the Add/Remove Programs applet, and those uninstalls failed, but the option to remove through the applet disappeared afterward.

Very frustrating. I have installed this on another machine without the problems; it's just that that machine is not my main workstation (and not portable).

I will try the regsvr32 suggestion and regasm on some of the other libraries if that doesn't help.

Thanks!

|||Tried that, no luck.

Any other suggestions?

Thanks!|||I did a search on my local hard drives and couldn't even find DTSPipeline.dll. The CTP version that we have were downloaded from MSDN subscribers download web site.

cheers,
j.|||Has there been any resolve to this error at all? I am close to giving up on the CTP entirely.

Any help would be greatly appreciated.|||Where are you getting the install from? What SKU are you installing? The DtsPipeline.dll should be installed on all SKUs. Are you installing Integration Services? Is there a Microsoft SQL Server\90\DTS folder on your machine? What's in there?|||yeah there is indeed a C:\Program Files\Microsoft SQL Server\90\DTS\Binn directory with DTSPipeline.dll in there. But still re-registering the DLL did not help.|||What control container are you trying to add the dataflow task to?|||Control Flow. But as I mentioned in my earlier post I couldn't even get Import/Export wizard to work within Management Studio.

HELP: Exporting to excel

Hi,
I have been working on this problem for some time now and I am no closer to
a solution/answer.
The trouble is that I have created two reports which are basically tables
which display database data, with the input of several search parameters, but
when they get to a certain size (it appears to be like this) and I try to
export to excel I get this error when trying to open the file:
************
Microsoft Office Excel File Repair Log
Errors were detected in file 'ViewRequests.xls'
The following is a list of repairs:
Damage to the file was so extensive that repairs were not possible. Excel
attempted to recover your formulas and values, but some data may have been
lost or corrupted.
************
From this error, it seems like an excel problem, but we have tried different
versions and we do have a possible workaround we got from this site:
http://support.microsoft.com/default.aspx?scid=kb en-us 820712
but it is very time consuming and not many users have two versions on their
machine.
A couple of people have told me that it must be something in the report, so
I am wondering if formulas or something else would cause this, or if anyone
as encountered this before, or if anyone can confirm is this is a report or
excel error. It appers to be happening only for larger files, but I am not
sure.
ThanksThis command reaks havick on my emf files.
=System.Math.Floor((RowNumber(table4_Group1)-1)/10)
It makes all of multiple page reports act differently and look
differently.
Trint|||Specer23,
You made no mention of clearing out your user/system temp folders or creating a
new Excel template or being sure that there are no Addins or similar templates
loaded in your Excel.
Nor did you mention the scope of affected users.
These are all cursory steps to solving any MSO product issue and if not done is
a grand waste of time if not done.
You certainly could have a problem w/your report, however once you tip Excel,
you must do a little clean up.
I also would through trial & error, get to the magic number between a rendered
report and Excel or not.
Then I'd look at the raw data near that point and look for things like coma's in
numeric fields, invalid dates etc...
HTH
JeffP....
"Spencer23" <Spencer23@.discussions.microsoft.com> wrote in message
news:76DEC6BB-B20C-41BE-9CD3-60A1D638120C@.microsoft.com...
> Hi,
> I have been working on this problem for some time now and I am no closer to
> a solution/answer.
> The trouble is that I have created two reports which are basically tables
> which display database data, with the input of several search parameters, but
> when they get to a certain size (it appears to be like this) and I try to
> export to excel I get this error when trying to open the file:
> ************
> Microsoft Office Excel File Repair Log
> Errors were detected in file 'ViewRequests.xls'
> The following is a list of repairs:
> Damage to the file was so extensive that repairs were not possible. Excel
> attempted to recover your formulas and values, but some data may have been
> lost or corrupted.
> ************
> From this error, it seems like an excel problem, but we have tried different
> versions and we do have a possible workaround we got from this site:
> http://support.microsoft.com/default.aspx?scid=kb en-us 820712
> but it is very time consuming and not many users have two versions on their
> machine.
> A couple of people have told me that it must be something in the report, so
> I am wondering if formulas or something else would cause this, or if anyone
> as encountered this before, or if anyone can confirm is this is a report or
> excel error. It appers to be happening only for larger files, but I am not
> sure.
>
> Thanks

help: indexed view question

I have created a unique clustered index on a view.
The view does a GROUP BY on 3 of the columns and
uses the COUNT_BIG aggregate function.
I used the following SET commands before creating the view and the index:

SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET NUMERIC_ROUNDABORT OFF

I can insert and delete rows from the base table, and the indexed view is updated fine.

However, when a scheduled job does effectively the same thing (delete some rows, and insert some new rows) I get the following error:

Executed as user: NT AUTHORITY\SYSTEM. DELETE failed because the following SET options have incorrect settings: 'ARITHABORT'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or query notifications and/or xml data type methods. [SQLSTATE 42000] (Error 1934). The step failed.

Why am I getting this error?

The same SET commands above are in the Transact-SQL code for the job before the delete and before the insert statements.

Thanks,
TomYES!!!!! I found a simple solution!!!

I simply put a GO after the SETs.
Although the code was not exactly in a stored procedure I suppose it acted like it was (the code was in a T-SQL script as one of the steps of a SQL Server agent job in Management Studio).
It may not sound like much, but this will help save 1 hour a month for an end user ... for probably 3-4 years!

Thanks Dan Guzman (SQL Server MVP) and Alex Kuznetsov!!!
:beer: