Query for multiple tables

Hi,

I have a query that returns values from three seperate data tables into a dataset:

SELECT [MediaFileData].[FileIdentifier], [AudioPCData].[RecorderID],
[AudioPCData].[Channel], [MediaFileData].[SubmittingUser], [MediaFileData].[DateTimeAsString],
[MetaData].[FieldText]

FROM [MediaFileData] INNER JOIN [AudioPCData] ON MediaFileData.FileIdentifier = AudioPCData.FileIdentifier
INNER JOIN [MetaData] ON MediaFileData.FileIdentifier = MetaData.FileIdentifier
INNER JOIN [SSRData] ON MediaFileData.FileIdentifier = SSRData.FileIdentifier

WHERE ([MediaFileData].[SubmittingUser] = '{0}') AND ([AudioPCData].[RecorderID] = '{0}')
AND ([MediaFileData].[MediaDescription] = '{0}') AND ([SSRData].[ModelUsed] = '{0}')

Each table has only got one 'FileIdentifier' apart from MetaData. This table has three columns 'FileIdentifer', 'FiledName' and 'FieldText'. One file can have more than one field and therefore It will have the same 'FileIdentifier' e.g.

FileIdentifier FieldName FieldText

1 Field 1 Hello

1 Field 2 Goodbye

The first problem is I only want to display the first and second field 'FiledText' in my results grid but still load all the other fields into my dataset.

The second problem is that it creates a new row for every field, whereas I want the fields with the same 'FileIdentifier' to be in the same row!

At the moment mt results gridlooks like this:

FileIdentifier RecorderID Channel SubmittingUser DateTimeAsString Field 1 Field 2

1 MyPC 1 Me 03/05/07 14:24 Hello Hello

1 MyPC 1 Me 03/05/07 14:24 GoodBye Goodbye

I need it to look like this:

FileIdentifier RecorderID Channel SubmittingUser DateTimeAsString Field 1 Field 2

1 MyPC 1 Me 03/05/07 14:24 Hello GoodBye

Thanks,

Guy

Depends on which version of SQL Server you are using. Using SQL 2k5 you should take a look on Cross apply.

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

Hi Guy,

Could you be more clear as to what your goal is, and what the purpose of these fields might be, their scope, the number of overlapping entries, and generally how you view this thing to be structured? On first blush, this seems like a situation where a relational database setup could save you some storage and access time in the long run if you can identify the shared fields and refactor your tables. The problem with setting Field1 and Field2 appropriately is also interesting in that you want Field1 to have "hello" in it and field2 to have "goodbye" in it whereas other than that string nothing differs between the records. This behaviour seems highly specific to your problem, and so knowing more about what your goals are for this database might help us to identify how best to solve your immediate problems in such a manner as to have long-term benefits.

Some natural things to weigh might be:

How do these components logically relate, and what are valid values for things in the database? (e.g. identical entries with field1 and field2 values, should this happen?, should field1 and field2 be mirrored?, etc.).

Does this solution need to last, or is it a one-time only operation?

How important is it to retain the database structure?

Is there a rule to how you want this join to work in the long run that requires domain specific knowledge?

How important is the performance of your database in the long run?

How often do you intend to perform this operation, and how much development work is "worth it"?

I think that as we understand the constraints of your task and what you intend to accomplish, we can find a solution that best fits your needs.

Thanks,

John (MSFT)

Query Execution failed for dataset error / the syntax for " " is incorrect

Hi,

Am totally new to MDX Queries.

I got this error while previewing a report that we had made. It goes like this --

Query execution failed for dataset 'some_dataset'.

Parser: the syntax for 'some_character' is incorrect.

What was figured out was this error shows up only for those values in a particular dropdown list which are multiword. Eg : My Company Technology and not for those values which a re single word. Eg : Company

what could be the possible problem and the solution to that.

Try to use [My Company Technology] as parameter value.|||

I dont wish to hard code the values.

It should be dynamic

|||

Hi Madu,

here you can find the rules for the identifiers in MDX:

http://msdn2.microsoft.com/en-us/library/ms145572.aspx

Query Execution Failed for Dataset (Beginner)

I got this Error, Query Execution for Dataset 'Source'.

When i try to run a report that is actually a drillthrough from another report. It runs fine in Report Designer, and when i deploy to my Local server. But when i deploy it to my virtual Report Server, I get this error messege. Why is it doing this? and where can i see errors for this type of stuff, so i can figure this out. This uses the same Datasource as the report linked from it. That report works fine, why wont this one? any ideas?

Look for the report server log at this location C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\LogFiles if installed to the default location.|||A collegue of mine helped me figure this one out. My stored procedure was not set to public in the properties section. Thats why it worked on my local computer and not on the report server.

query execution failed for dataset

have all my reports on a server, and so far have had no problems giving
users access. They are all located in the Cg Role, i created. Here's the
delima, I added a new user and to this role. The user can view report
manager, view the folders, and view the reports..but when they try to run a
report, they get this error.
query execution failed for dataset "CrashStatisticalYear" (rsError
executingcommand)
She has access to everything, why is she the only one getting this error'
Please Help!It seems like she doesnt have access to a specific table inside that
dataset.
"Tenchy" <Tenchy@.discussions.microsoft.com> wrote in message
news:4088044D-2DD6-4FB1-A9BC-59BED72B1C24@.microsoft.com...
> have all my reports on a server, and so far have had no problems giving
> users access. They are all located in the Cg Role, i created. Here's the
> delima, I added a new user and to this role. The user can view report
> manager, view the folders, and view the reports..but when they try to run
> a
> report, they get this error.
>
> query execution failed for dataset "CrashStatisticalYear" (rsError
> executingcommand)
>
> She has access to everything, why is she the only one getting this error'
>
> Please Help!
>

query defining parameter dropdowns is run twice!

I built a very simple report which uses a query to define the options in the parameter’s dropdown. I used that same dataset to define the default for that parameter (meaning that it will just pick the first row from the dataset and use that as the default). When I run the report watching a Profiler trace on the SQL database, it runs that query twice. (Presumably, that’s once to fill the dropdown list and once to figure out the default.) That seems silly to me since it is the same query that is the same dataset in Reporting Services. Is there any way around this? My parameter bar is rendering twice as slowly as it should be.

I've tested against SSRS 2005 SP1 and the CTP of SP2.

Can you confirm your test by removing the default and re-testing to make sure it gets run once. Then adding the default again to confirm it goes back to 2 executions?|||Yes, it only runs it once if I don't specify that the parameter should have a default from a query.

Query dataset locally from code

Hi all,
I need to get 20 rows of data from the SQL Server DB to put in 20 Textboxes
always one item of one row.
It's about Text in various languages, so each language needs 20 textstrings.
I don't want to call the Stored Procedure 20 times.
So I thought in calling it one time, get the data out once of the DB and
refer to it in queries local to the report 20 times.
I tried it with an Assembly. This worked, but I needed to configure the
assembly's trust etc.
This is speaking against a copy/paste deployment to the client's Reporting
Server.
Is it possible to create a standard data set in the Report and refer in some
form to it locally?
E.g.: Dataset dsData takes one time the 20 rows.
In a textbox I call a function like =code.getValue("item17")
and in the code section I query the dsData with something like
function getValue (item as string) as string
return (select col2 from dsData where col1 = item)
end function
Thanks in advance, HenryHello Henry,
I have suggested the following article to you.
http://msdn2.microsoft.com/en-gb/library/aa179521(SQL.80).aspx
You do not need to configure the trust level if it not required.
I hope this will be some help.
The Assembly is more functional than the Embeded Code in Reporting Services.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Wei Lu,
thank you for your answer.
But, as written above, I've already programmed an Assembly that works, but
the client DENIES the use of an Assembly.
I know also, that the <Code> part of the RDL-File is compiled implicitely
into an Assembly by ReportServer on load, so I cant access the outside world
(like the filesystem or the SQL-Server) from within the custom Code without
elevating its trustlevel.
I know also, that it is possible to handover e.g. Parameters to the code to
manipulate it programatically.
Like:
Function GetValue(reportParameters as Parameters) As Object
do sth with the Parameters
return sthelse
end function
The question was:
Since via a Data Source and a DataSet I've already the data available.
CAN I do anything like above with the Parameters, but here with the dataset?
E.g. giving a whole Dataset to a Function (best byRef) to do a select from?
Function GetValue(dsData as Dataset) As Object
return (select sth from dsData where ...)
end function
Thanks in advance, Henry|||Hello Henry,
Unfortunately, you could not refer the dataset in the code.
I would like to know the concern from your client of using the assembly.
You could only grant some proper trust for runing the report.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Dear Wei Lu,
it might be possible to access the file system, as in your
XML-Assembly-Example that you sent me, but getting data out from an
SQL-Server DB is a big trouble.
The info under your hint:
http://msdn2.microsoft.com/en-gb/library/aa179521(SQL.80).aspx
is far from sufficient.
Only after lots of hours and the following resources, I got it to work.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/dngrfCodeAccessSecurityInSQLServer2000ReportingServices.asp
http://forums.microsoft.com/msdn/showpost.aspx?postid=139787&siteid=1
Microsoft Official Course 2840A, Implementing Security for Applications
So, in the very end I needed to give:
- fulltrust via UrlMembershipCondition
Error message: "Cannot generate Hash"
- Give the assembly a strong name
Error message:
Request for the permission of type
'System.Security.Permissions.SecurityPermission, mscorlib, Version=2.0.0.0,
... failed. (rsRuntimeErrorI-nExpression)
- Adjust CAS
- Assert
System.Data.SqlClient.SqlClientPermission(Security.Permissions.PermissionState.Unrestricted)
It didn't work until now!!!
So, worse, I had to install the Assembly in the GAC.
and with this give: AllowPartiallyTrustedCallers()
With our client was agreed a copy/paste-deployment.
All this procedure is FAR from that!
Any ideas?
Thank you, Henry|||Hello Henry,
Yes, the render order is from the upper-left to bottom-right.
I would like to suggest you use a hidden textbox in the report to
initialize.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||If I understood you correctly you are passing a predefined number of
parameters to this function. This can also be done dynamically using a
little hack described in this page:
http://blogs.msdn.com/bwelcker/archive/2005/05/10/416306.aspx
I took the code at this page and used to build a dynamic localization
function that reads the text id numbers, language codes and corresponging
texts from a dataset and stores them into a Hashtable object using text id +
language code as the key and text as the value.
Basic idea is that if an aggregate function like Sum is called for a certain
scope, it will go through all of the data in that scope and it can take a
function call that returns a number as a parameter.
Like this:
Sum(Code.LoadDictionary(Fields!TEXT_ID.Value , Fields!LANGUAGE_CODE.Value,
Fields!TEXT_TXT.Value), "DICTIONARY")
Where DICTIONARY is the dataset for dictionary. The LoadDictionary function
is called once for each row in the scope.
You can the have this 'loader' in a hidden field as Mr. Lu suggested.
Then for the localized field you just have for example
=Code.GetLocalizedText("TEXT_0001", Parameters!LanguagePar.Value)
I know that your solution works great. I just think tahat this way the
function calls are simpler. I hope this helps.
Juho Salo

Query based Textbox

Hello Everyone,

How do you display a dataset query result in a textbox?

Thanks for the easy answer...

drag and drop the textbox

set the 'datasetname' on the textbox to whatever your dataset is called

then in the expression editor you can access the result

boink

actually thinking about it, you probably don't need to do the 'datasetname' bit

just right click on the text box and edit expression

then in the panes at the bottom (left hand side) you can see all the cols in your dataset|||

Thank you for the quick reply...I will try this and see if it works...

I have a Parameter that uses a ClientID as the selected value and I tried to have a textbox display the User Parameter Choice but it only diplayed the ID and I want the corresponding text (Client Name Value). But I can not find the correct expression.

I will post a follow-up to this and mark you as answered or not...

Thanks!

|||

Not working...I can not select a Dataset for the textbox

Any other ideas?

|||=Parameters!ClientID.Label|||

Got the following error:

The Value expression used in textbox ‘Client’ returned a data type that is not valid.

|||Is it a multi-value parameter?|||

yes

|||Try this: =Join(Parameters!ClientID.Label, ", ")|||

Great!

That was the ticket!

Thank You!

Query based Textbox

Hello Everyone,

How do you display a dataset query result in a textbox?

Thanks for the easy answer...

drag and drop the textbox

set the 'datasetname' on the textbox to whatever your dataset is called

then in the expression editor you can access the result

boink

actually thinking about it, you probably don't need to do the 'datasetname' bit

just right click on the text box and edit expression

then in the panes at the bottom (left hand side) you can see all the cols in your dataset|||

Thank you for the quick reply...I will try this and see if it works...

I have a Parameter that uses a ClientID as the selected value and I tried to have a textbox display the User Parameter Choice but it only diplayed the ID and I want the corresponding text (Client Name Value). But I can not find the correct expression.

I will post a follow-up to this and mark you as answered or not...

Thanks!

|||

Not working...I can not select a Dataset for the textbox

Any other ideas?

|||=Parameters!ClientID.Label|||

Got the following error:

The Value expression used in textbox ‘Client’ returned a data type that is not valid.

|||Is it a multi-value parameter?|||

yes

|||Try this: =Join(Parameters!ClientID.Label, ", ")|||

Great!

That was the ticket!

Thank You!