Obtain a record from a table via a formula

[blaatblaat]

In the report that I'm working on has some localization in the reports. I have a seperate table in my database which contains the necessary resources for localization. This table has two fields where I can identity which resource I want to use. These fields are the ResourceId and the LanguageId (For example: ResourceId = 1 & LanguageId = "en-US").

Now what I want is to obtain that particular resource via a formula. The following formula is what I have now currently.

// Get the field section from the xml
stringvar fieldSection := extractstring({Object.AddressXml},"<Fields>","</Fields>");
// Get all the fields
stringvar array fields := split(fieldSection,"</Field>");
// Get the resource id
numbervar resourceId := tonumber(extractstring(fields[1],"<ResourceId>","</ResourceId>"));

// Set some criteria?
{?ResourceId} = resourceId;
{?LanguageId} = "en-US"; // This is only for the example

// The resource text
stringvar resourceText := {Resource.Value};

resourceText;

I thought if I set my parameters and say Resource.Value (to obtain my localized resource) I would get my string, but it doesn't work. Is this even possible what I'm trying to achieve?

Thank you in advance.

 

[lbass]

Isn't there a way to link the table directly to your report table?

-LB

 

[blaatblaat]

Thank you for your reply lbass, but I'm not quite sure what you mean. You mean that I need to set the table into my database expert?

 

[alvieklaud]

Maybe you can try using the command at database expert. that might help

 

[blaatblaat]

Well, that was also a thought of me to use the Add Command in the database expert. Only there is only one slight of a problem. This will select only one time the resource instead of dynamically. In the example what I have described in the first post I select a ID from the XML and with that ID I select my resource (at least that is what I'm trying to do ).

 

[lbass]

I don't know how one goes about setting the language in a report dynamically, so probably shouldn't have responded regarding that. If you want help troubleshooting your formula, can you please show how the fields from the resource table display if you place them in the detail section?

-LB

 

[blaatblaat]

Alright my table of resources looks like this:

Id (int) - LanguageId(varchar) - Category (varchar) - Value (varchar)

And some example data

5555 - en-US - System.UI - Some english value
5555 - nl-NL - System.UI - Some dutch value
5556 - en-US - System.UI - Some english value
5556 - nl-NL - System.UI - Some dutch value

I add parameters via the codebehind of a webpage. I've created an usercontrol in ASP.NET and my crystalreportsviewer is placed on that usercontrol. In the codebehind of the usercontrol I add dynamically parameters to the report so it can render the report based on some parameters.

So that how the report gets the paramters where it needs to work with, only that xml address field (which I've described in the first post) needs to parse the resource id and must get the localized resources for that particular field. I thought by settings the values so that it can get the associated value, but that didn't work.

I hope this makes it more clear? Thank you in advance again...

 

[lbass]

I think you should also show how {Object.AddressXml} displays for the above related sample data--but I'm not sure I can help anyway. I wonder whether you would get a more helpful response in forum768.

-LB

 

[blaatblaat]

I'm sorry I forgot indeed the XML data. Here is a sample of the XML data

<Service.Address xmlns:xsi="

http://www.w3.org/2001/XMLSchema-instance"

xmlns:xsd="

http://www.w3.org/2001/XMLSchema">

<Fields>
<Field Type="Rack">
<Alt>Rack</Alt>
<Value>12</Value>
<ResourceId>0</ResourceId>
</Field>
<Field Type="Row">
<Alt>Row</Alt>
<Value>1</Value>
<ResourceId>0</ResourceId>
</Field>
<Field Type="Group">
<Alt>Group</Alt>
<Value>7</Value>
<ResourceId>1120</ResourceId>
</Field>
</Fields>
</Service.Address>

 

[lbass]

So in this case, the Resource ID = 0. Is that what you were expecting?

-LB

 

[blaatblaat]

It can be 0, 1120, etc... it needs to iterate through the records

 

[lbass]

You realize your formula is only grabbing the first value, right? I'm still unclear whether you think the formula is the issue or the overall method (which I can't help you with).

-LB

 

[blaatblaat]

Yeah, well I thought that the a formula can work differently by passing by new values, but what didn't work. I have already realized that it only grabs the first value. I have now an another solution for my problem and personally I think it is a better approach for heavy reports with lots of data parsing etc.

Thank you for your time lbass and your thinking.

 

[lbass]

Can you share your solution so others could benefit?

-LB

 

[blaatblaat]

It is quite easy, I had even two options to solve this problem.

1) Solve this in SQL (xml parsing)
2) Use an ADO.NET Dataset (which I have chosen)

With that dataset I can fill it in the codebehind of a page and do SetDataSource on the crystal report. In this way I've all the data parsed which I needed and solves my problem.