Reporting Services 2005 semantic query error

I recently changed my datasource in our Reporting Service Model. We changed to a quicker SQL server with disk capacity. I am able to run new reports but when I try to run old ones that were on the old datasource, I get an error. The datasource has been changed to point to the new server (datasource).

The error is: An error has occurred during report processing. (rsProcessingAborted)
Semantic query compilation failed: e EmptySemanticQuery The SemanticQuery does not contain any Groupings or MeasureGroups. SemanticQuery must contain at least one of these elements. (SemanticQuery ''). (rsSemanticQueryEngineError)

I will appreciate any help.

Nick

Is your data source an SSAS 2005 cube? Or did you misconfigure the model data source? Use the Report Manager to verify/reset the model data source.

|||My datasource is one of our SQL server databases. I know that the datasource is not misconfigured in the model because I am able to run new reports.sql

Reporting Services - Variables/Parameters - Is this a bug?

When creating a Reporting Services report and declaring local variables as part of your query in a dataset there is sometimes a problem. When you hit run in the Data section and the “Define Query Parameters” box pops up, all the variables are not there. Sometimes when you go to properties (…) of that dataset the parameters are gone. Is this a bug? This is happening both in RS2000 and 2005.

Thanks

?

|||Anyone?|||

Not an expert in this area of the product, but i tried it briefly on my computer - local variables are recognized when you use the generic query designer (the default). They do not appear in the parameters (as they shouldn't). Parameters that are not local variables are automatically detected and shown in the parameters.

I couldn't emulate the behavior you describe. No matter how many times I opened/closed the report the parameters were correctly recognized and the local variables were preserved.

If you're using the graphical query designer, you may be running into some problems - that component rewrites queries and sometimes makes mistakes with more complicated query structures. Unfortunately, it is a standard component that we do not control. Recommendation is to try the generic query designer and see if the problem persists. There is a button on the data tab that lets you switch from using the graphical to using the generic query designer.

Hope that helps,

-Lukasz

|||

this is possibly related, and everytime i encounter it i get closer to having a nervous break down.. its very very annoying to say the least..

here's what i'm getting:

i have a report that gets data from a WebService.. webservice takes 6 parameters, all are primative datatypes.

i have a set of report parameters, and a set of dataset parameters that come from the report params.

i hit the execute button, fill in my values, it chugs along and give's me my expected results in the results grid.. perfect..

i edit my dataset query and put in an <ElementPath> so i can specify a particular table, hit execute and again i get my expected results..

if i do anything ie, edit the query, change to the layout window, then go back to the data window and hit refresh, the query blows up.. i edit my dataset and on the parameters tab, there's nothing.. all gone! wow.. i put all the parameters back in again, viewed the report source, copy and paste the block of code contained in the <DataSets> node to the notepad so i dont have to continually waste time re-entering the parameters..

this seems to happen whenever i click the refresh button.. i've tried to put it in generic query designer, and do it, but the UI seems to revert back to the graphical designer..

i've been dealing with it because most of my reports only took a parameter or two..

any ideas?

EDIT:

actually, i think it might be a problem if there is an error when you type in the parameters.. ie if i have a string param called strGuid, and it craps out the webservice when i cast the string to a guid (missed a char).. it seems to wipe out the dataset parameters..

|||

I've seen this many times, codemare & ruckazz

It will happen for one of the following reasons:

1. Your Query does not get parsed correctly i.e type error and you hit the properties button to go to parameters

2. You use a parameter that is not defined or you spelled incorrectly and go to parameters in properties

Use the Undo button (Ctrl-Z or Edit>Undo) and your parameters WILL be restored, do not save, find your flaw, fix it, save it, run it.

Reporting Services - Variables/Parameters - Is this a bug?

When creating a Reporting Services report and declaring local variables as part of your query in a dataset there is sometimes a problem. When you hit run in the Data section and the “Define Query Parameters” box pops up, all the variables are not there. Sometimes when you go to properties (…) of that dataset the parameters are gone. Is this a bug? This is happening both in RS2000 and 2005.

Thanks

?

|||Anyone?|||

Not an expert in this area of the product, but i tried it briefly on my computer - local variables are recognized when you use the generic query designer (the default). They do not appear in the parameters (as they shouldn't). Parameters that are not local variables are automatically detected and shown in the parameters.

I couldn't emulate the behavior you describe. No matter how many times I opened/closed the report the parameters were correctly recognized and the local variables were preserved.

If you're using the graphical query designer, you may be running into some problems - that component rewrites queries and sometimes makes mistakes with more complicated query structures. Unfortunately, it is a standard component that we do not control. Recommendation is to try the generic query designer and see if the problem persists. There is a button on the data tab that lets you switch from using the graphical to using the generic query designer.

Hope that helps,

-Lukasz

|||

this is possibly related, and everytime i encounter it i get closer to having a nervous break down.. its very very annoying to say the least..

here's what i'm getting:

i have a report that gets data from a WebService.. webservice takes 6 parameters, all are primative datatypes.

i have a set of report parameters, and a set of dataset parameters that come from the report params.

i hit the execute button, fill in my values, it chugs along and give's me my expected results in the results grid.. perfect..

i edit my dataset query and put in an <ElementPath> so i can specify a particular table, hit execute and again i get my expected results..

if i do anything ie, edit the query, change to the layout window, then go back to the data window and hit refresh, the query blows up.. i edit my dataset and on the parameters tab, there's nothing.. all gone! wow.. i put all the parameters back in again, viewed the report source, copy and paste the block of code contained in the <DataSets> node to the notepad so i dont have to continually waste time re-entering the parameters..

this seems to happen whenever i click the refresh button.. i've tried to put it in generic query designer, and do it, but the UI seems to revert back to the graphical designer..

i've been dealing with it because most of my reports only took a parameter or two..

any ideas?

EDIT:

actually, i think it might be a problem if there is an error when you type in the parameters.. ie if i have a string param called strGuid, and it craps out the webservice when i cast the string to a guid (missed a char).. it seems to wipe out the dataset parameters..

|||

I've seen this many times, codemare & ruckazz

It will happen for one of the following reasons:

1. Your Query does not get parsed correctly i.e type error and you hit the properties button to go to parameters

2. You use a parameter that is not defined or you spelled incorrectly and go to parameters in properties

Use the Undo button (Ctrl-Z or Edit>Undo) and your parameters WILL be restored, do not save, find your flaw, fix it, save it, run it.

Reporting Services - Variables/Parameters - Is this a bug?

When creating a Reporting Services report and declaring local variables as part of your query in a dataset there is sometimes a problem. When you hit run in the Data section and the “Define Query Parameters” box pops up, all the variables are not there. Sometimes when you go to properties (…) of that dataset the parameters are gone. Is this a bug? This is happening both in RS2000 and 2005.

Thanks

?

|||Anyone?|||

Not an expert in this area of the product, but i tried it briefly on my computer - local variables are recognized when you use the generic query designer (the default). They do not appear in the parameters (as they shouldn't). Parameters that are not local variables are automatically detected and shown in the parameters.

I couldn't emulate the behavior you describe. No matter how many times I opened/closed the report the parameters were correctly recognized and the local variables were preserved.

If you're using the graphical query designer, you may be running into some problems - that component rewrites queries and sometimes makes mistakes with more complicated query structures. Unfortunately, it is a standard component that we do not control. Recommendation is to try the generic query designer and see if the problem persists. There is a button on the data tab that lets you switch from using the graphical to using the generic query designer.

Hope that helps,

-Lukasz

|||

this is possibly related, and everytime i encounter it i get closer to having a nervous break down.. its very very annoying to say the least..

here's what i'm getting:

i have a report that gets data from a WebService.. webservice takes 6 parameters, all are primative datatypes.

i have a set of report parameters, and a set of dataset parameters that come from the report params.

i hit the execute button, fill in my values, it chugs along and give's me my expected results in the results grid.. perfect..

i edit my dataset query and put in an <ElementPath> so i can specify a particular table, hit execute and again i get my expected results..

if i do anything ie, edit the query, change to the layout window, then go back to the data window and hit refresh, the query blows up.. i edit my dataset and on the parameters tab, there's nothing.. all gone! wow.. i put all the parameters back in again, viewed the report source, copy and paste the block of code contained in the <DataSets> node to the notepad so i dont have to continually waste time re-entering the parameters..

this seems to happen whenever i click the refresh button.. i've tried to put it in generic query designer, and do it, but the UI seems to revert back to the graphical designer..

i've been dealing with it because most of my reports only took a parameter or two..

any ideas?

EDIT:

actually, i think it might be a problem if there is an error when you type in the parameters.. ie if i have a string param called strGuid, and it craps out the webservice when i cast the string to a guid (missed a char).. it seems to wipe out the dataset parameters..

|||

I've seen this many times, codemare & ruckazz

It will happen for one of the following reasons:

1. Your Query does not get parsed correctly i.e type error and you hit the properties button to go to parameters

2. You use a parameter that is not defined or you spelled incorrectly and go to parameters in properties

Use the Undo button (Ctrl-Z or Edit>Undo) and your parameters WILL be restored, do not save, find your flaw, fix it, save it, run it.

Reporting Services - Only Measures on Column axis

Hi,

I'm currently using AS 2005 and Reporting Services.

I have some results of a query split into age buckets (0-90 days, 91-180 days, etc). I would like to display the buckets, whether there are results or not as I am using them in the column . If I do not include "Non Empty", it returns a huge number of records because there is a cross join with another dimension which does not need to include all records.
For example, here is my query:

SELECT NON EMPTY { [Measures].[To Bill Amount] } ON COLUMNS,

NON EMPTY { (

[Product].[Product Name].[Product Name].ALLMEMBERS*

[Transaction Date].[Age Buckets].[Age Buckets].ALLMEMBERS ) }

ON ROWS

FROM ( SELECT ( { [Client].[Client Key].&[2] } ) ON COLUMNS

FROM [Cube])

WHERE ( [Client].[Client Key].&[2])

So, I would like to include all [Transaction Date].[Age Buckets].[Age Buckets] but only the Products that fall under the Client Key. Another thing to note is that Reporting Services seems to only allow measures on the Column axis.

Any help would be great.

Thanks.

Hi Dear

In SSRS try to make MDX in Such a way that result should look like Report output. By doing that U will save the design time as well as Report level calculation, I hope by doing this your report will be little bit faster. And see the functions like STRTOMEMBER, STRTOSET etc. You can use here Peramaters also. Using peramaters you will add a lot values in your reports. In your example I donnot know excatly how data U R storing in Age bukets. If there is only Age then make Calculated Members Or if there is age bucket in the desired format like [0-90 Days] etc. then use only U needed Buckets. Hope U will get some help.

WITH

MEMBER [Transaction Date].[Age Buckets].[0-90 days] AS

CASE WHEN

[Transaction Date].[Age Buckets].[Age Buckets] <= 90

THEN [Transaction Date].[Age Buckets].[Age Buckets]

ELSE NULL END

MEMBER [Transaction Date].[Age Buckets].[91-180 days] AS

CASE WHEN

[Transaction Date].[Age Buckets].[Age Buckets] > 90

AND [Transaction Date].[Age Buckets].[Age Buckets] <= 180

THEN [Transaction Date].[Age Buckets].[Age Buckets]

ELSE NULL END

SELECT

NON EMPTY

{[Measures].[To Bill Amount]} ON COLUMNS,

{

(

{[Product].[Product Name].[Product Name]}

*

{

[Transaction Date].[Age Buckets].[0-90 days],

[Transaction Date].[Age Buckets].[91-180 days]

}

)

}

ON ROWS

FROM (SELECT({[Client].[Client Key].&[2]}) ON COLUMNS

FROM [Cube])

WHERE ( [Client].[Client Key].&[2])

sql

Reporting Services - How to create additional details section

Hi All,

I'm using VS 2005 to build a SQL Server reporting Services report. The wizard works great and prompts me to build my results query and then select fields to group the report by.

I'm using the 3 groups as follows:

Page: Product / Model name
Group: Serial No
Details: Orders

Here's the problem. I also want to show my order lines as there is another linked table connected to my order table which holds all order line items. Classic order - line example.

How can I modify the details section to show my line items, as well as not show empty rows when no line items exist?

Thanks!

Hi.

I myself have only just started using Reporting Services, but from what I have read, I believe that if you switch to ReportDesigner in VS2005, you may be able to add a Sub Report to your Orders group. Then you could filter the data shown in the Sub Report by the ID value of the current Orders row.

Add a Report Parameter to your Orders group and set its value to the Order's ID (that links Orders and Order Lines). Add another Report Parameter to your Sub Report that get it's value from the Orders Report Parameter you just set up. Then set the Sub Report to get your Order Lines data and use a WHERE clause set to the Sub Report Report Parameter value so that the Sub Report displays just the Order Lines that match the current Orders value. The sub report will not show any data if no rows from the Order Lines table match the current Orders ID value.

The SQL Server 2005 BOL has info about using Reporting Services.

I am sure there may be other (better) ways of doing what you ask, but I hope this gives you an idea of what can be done.

HTH.

Best regards.

sql

Reporting Services - Analysis Services - Displaying Dimension Members as Columns

I think I've seen a similar post on a blog or on the forums - but it seems like this should be possible -

I have an MDX query - that works fine in SQL Enterprise Manager, and has my dimension members on columns, and my measures on the rows. When I try the same query in Reporting Services, I get the error:

"The query cannot be prepared: The query must have at least one axis. The first axis of the query should not have multiple hierarchies, nor should it reference any dimension other than the Measures dimension..
Parameter name: mdx (MDXQueryGenerator)"

Although it works when you pivot the view, I really need my data presented with the members on the columns and the measures on the rows. Another forum post mentioned using the SQL 9.0 driver, but I can't see this listed anywhere (the only one I see is the .NET framework Data Provider for Microsoft Analysis Services).

Here's what my query looks like -

SELECT
{ [Time].[Month].&[2006-09-01T00:00:00] ,
[Time].[Month].&[2006-10-01T00:00:00],
[Time].[Month].&[2006-11-01T00:00:00],
[Time].[Month].&[2006-12-01T00:00:00]
} on COLUMNS,
{
[Measures].[Unique Users],
[Measures].[UU Pct 1],
[Measures].[UU Pct 2],
} ON ROWS
FROM [Cube]

Any ideas?

Thanks,
Arjun

As the error message says, the SSRS Analysis Services provider expects dimension on rows. If you want to use this query, you need to bypass the SSRS Analysis Services provider and use directly the OLE DB Provider for Analysis Services 9.0. The limitation of this approach is that the OLE DB driver doesn't support parameters. If you need to parameterize your report, you need to use an expression-based query that concatenates the parameter values, a-la RS 2000.

|||Thanks Teo, that makes sense. I found the OLE DB driver and I'll use this method. Really strange limitation in SSRS, hope they get rid of it in the next release.|||

you need to bypass the SSRS Analysis Services provider and use directly the OLE DB Provider for Analysis Services 9.0

How do you do this, Please?

|||When setting up your data source, choose the Microsoft OLE DB Provider for Analysis Services 9.0.|||thanks, but that choice does not show up when creating a datasource in reporting services 2005. OLE DB provider for analysis service 9.0 is definitely installed on that machine. I do see that choice from Excel when creating a pivot table based on a cube. Thanks for your help.|||Chose OLE DB as Type, then click on the Edit button.|||Thanks, it works .

Reporting Services - Analysis Services - Displaying Dimension Members as Columns

I think I've seen a similar post on a blog or on the forums - but it seems like this should be possible -

I have an MDX query - that works fine in SQL Enterprise Manager, and has my dimension members on columns, and my measures on the rows. When I try the same query in Reporting Services, I get the error:

"The query cannot be prepared: The query must have at least one axis. The first axis of the query should not have multiple hierarchies, nor should it reference any dimension other than the Measures dimension..
Parameter name: mdx (MDXQueryGenerator)"

Although it works when you pivot the view, I really need my data presented with the members on the columns and the measures on the rows. Another forum post mentioned using the SQL 9.0 driver, but I can't see this listed anywhere (the only one I see is the .NET framework Data Provider for Microsoft Analysis Services).

Here's what my query looks like -

SELECT
{ [Time].[Month].&[2006-09-01T00:00:00] ,
[Time].[Month].&[2006-10-01T00:00:00],
[Time].[Month].&[2006-11-01T00:00:00],
[Time].[Month].&[2006-12-01T00:00:00]
} on COLUMNS,
{
[Measures].[Unique Users],
[Measures].[UU Pct 1],
[Measures].[UU Pct 2],
} ON ROWS
FROM [Cube]

Any ideas?

Thanks,
Arjun

As the error message says, the SSRS Analysis Services provider expects dimension on rows. If you want to use this query, you need to bypass the SSRS Analysis Services provider and use directly the OLE DB Provider for Analysis Services 9.0. The limitation of this approach is that the OLE DB driver doesn't support parameters. If you need to parameterize your report, you need to use an expression-based query that concatenates the parameter values, a-la RS 2000.

|||Thanks Teo, that makes sense. I found the OLE DB driver and I'll use this method. Really strange limitation in SSRS, hope they get rid of it in the next release.|||

you need to bypass the SSRS Analysis Services provider and use directly the OLE DB Provider for Analysis Services 9.0

How do you do this, Please?

|||When setting up your data source, choose the Microsoft OLE DB Provider for Analysis Services 9.0.|||thanks, but that choice does not show up when creating a datasource in reporting services 2005. OLE DB provider for analysis service 9.0 is definitely installed on that machine. I do see that choice from Excel when creating a pivot table based on a cube. Thanks for your help.|||Chose OLE DB as Type, then click on the Edit button.|||Thanks, it works .

Reporting Services

CAn you do a cross tab query in RS. I don't think you can. If not, I can do
it in access. just thought I'd askYou don't need to do a cross tab in your query. You can just use the matrix
object in the report.
--
Brian Welcker
Group Program Manager
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Randy" <Randy@.discussions.microsoft.com> wrote in message
news:F885EB88-121F-4DAF-8866-BC26E8A0F811@.microsoft.com...
> CAn you do a cross tab query in RS. I don't think you can. If not, I can
> do
> it in access. just thought I'd ask

reporting services

hi,

i have to do some reports of a database sql server 2000 with visual studio 20003 reporting services,

The question is:

as the possible Query are a lot, what is the criterion to adopt to choose the Query for populate the Reports?

Hi,

could you explain that abit in detail ?

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Dear ,

What is ur question btw.Pls get in detail?

regards

sufian

|||my boss told my to do some reports, but don't told what to put in reports, so what usually is reported in a report: eg. the most selled items, the less .... what|||You are kidding right ? Ask you boss What does he need the reports for ? ASking him will help you to meet his expectations.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Reporting On Sproc Update Dates?

Does anyone know the system tables I need to query to produce a report of stored procedures (SQL 2005) that had any changes made to them in a user-specified date range?

In SQL 2005, I saw the canned database reports, but this one didn't exist. Any help would be greatly appreciated.

Thanks!

Wow, already off the first page so quickly?

Sorry for the bump, I just wanted to be sure there was no quick answer to this. I would have thought it would be an easy thing to do, I may be mistaken.

Any takers?

Thanks again.

|||

You can get any object now using the sys.objects view. For sprocs, you'd want something like this:

Code Snippet

select * from sys.objects where type='P' and modify_date between getdate() - 1000 and getdate()

-Jessica

|||

Perfect! I knew it must be something simple I was missing.

Thanks, Jessica!

Reporting On Sproc Update Dates?

Does anyone know the system tables I need to query to produce a report of stored procedures (SQL 2005) that had any changes made to them in a user-specified date range?

In SQL 2005, I saw the canned database reports, but this one didn't exist. Any help would be greatly appreciated.

Thanks!

Wow, already off the first page so quickly?

Sorry for the bump, I just wanted to be sure there was no quick answer to this. I would have thought it would be an easy thing to do, I may be mistaken.

Any takers?

Thanks again.

|||

You can get any object now using the sys.objects view. For sprocs, you'd want something like this:

Code Snippet

select * from sys.objects where type='P' and modify_date between getdate() - 1000 and getdate()

-Jessica

|||

Perfect! I knew it must be something simple I was missing.

Thanks, Jessica!

Reporting on Exchange Emails

Is it possible tpo query exchange directly from reporting services or ... how do i extract data out of exchange into a sql database so i can report on it with RS.

Any ideas anyone..

CheersThere is an OLE DB provider for Exchange that is built into Exchnage and one for HTTP DAV somewhere (I think it is called 'OLE DB for Internet Publishing') but I can't remember where to find it right now.

There are also some third party products that extract statistical information from Exchange (Microsoft Operations Manager http://www.microsoft.com/mom/default.mspx and Exchange Reporter at http://www.ssw.com.au/ssw/ExchangeReporter/ are two that come to mind).

Reporting on an Analysis Services data cube

Hello,

The problem I am having is I am trying to report on an Analysis Services data cube.

I connect fine but for some reason the "MDX query designer" does not appear as an option for creating the query for this data source. Is there a component of SQL 2000 or visual studio I am missing

Any help Would be great

Thank you

One good starting point might be this link:

http://www.microsoft.com/downloads/details.aspx?FamilyID=f9b6e945-1f4c-4b7c-9c83-c6801f0576ff&DisplayLang=en

It is a rs 2000 sample that uses cubes. I hope this helps

|||Just to confirm - are you using RS 2005 with the Analysis Services Provider - otherwise you won't get the MDX Query Designer?|||

Thank you for your help. I unfortunately am only using RS2003.

SO i need to upgrade to RS 2005 and install Analysis Services Provider before ill get the designer ?

|||

You would need to upgrade RS 2005 - the Analysis Services Provider should get added as part of the upgrade:

http://msdn2.microsoft.com/en-us/library/ms170417(SQL.90).aspx

>>

What's New in SQL Server 2005 > Reporting Services Enhancements

Reporting Services Design-Time Enhancements

New Analysis Services Query Designer

Report Designer includes a new query designer for creating MDX queries. You can use the integrated query designer for Analysis Services to build queries by dragging and dropping server metadata onto a report layout and previewing the results.

>>