Report accessing wrong data table

[former_member338618]

Running CR Server 2013 BO4.1...

I had an occurrence today where a report that has been 'working OK' for weeks that, all of a sudden, a dynamic parameter decided it wanted to access a completely different table (it was a selection for supplier code & started returning scrap analysis codes).

I opened the master copy in Crystal Developer & it seemed fine, re-deployed to the server & it was still broken, Opened the report in Dev. again, ran the 'Verify Database' which told me it needed to update the Supplier table, which broke it in Dev. I started from scratch with the original master copy & now it works again.

Has anyone experienced this & is there a known cause?

[abhilash_kumar · Accepted solution]

Hi Andrew,

Did the structure of the Supplier table change in DEV?

When you save a CR report with Dynamic prompts to the repository, the LOVs are managed via the Business View Manager. Are the LOVs 'scheduled' to run at a particular time?

-Abhilash

[former_member338618 · Accepted solution]

Hi Abhilash,

After running the Verify Database the list of fields stayed the same in the field explorer yet when I chose 'Browse Data' it returned the scrap codes data.

We don't schedule anything (I don't think) everything is 'View on demand'...

p.s. appreciate the prompt response.

[abhilash_kumar · Accepted solution]

If you run a simple 'select distinct' query on the code field, do you see those scrap codes?

-Abhilash

[former_member338618 · Accepted solution]

No, I get what I would expect, however, I now cannot replicate the problem.

So confusing.

[DellSC · Accepted solution]

When you create a dynamic parameter for a Crystal report that is published to BO, the parameter is actually defined in the Business View Manager.  It sounds like you're using the Supplier table for the dynamic parameter and there were changes to that table.  In the future if this happens, I would go to the Business View Manager and verify the table in the Data Foundation for the parameter instead of recreating the report with a whole new parameter.

-Dell

[former_member338618 · Accepted solution]

Thanks Dell,

BVM is new to me, is there any way I can link the Data Foundation back to the specific report (I have more than 1 entry)...

[DellSC · Accepted solution]

What you need to do is look at the parameter in the report to determine which ReportParameter from the BVM it's using.  You would then work backward from the ReportParameter, to the LOV.  By default, the data foundation is the name of the LOV + "_DF".

When you're just creating the parameters in your reports, you get a proliferation of objects in the BVM - each prompt will have a corresponding Data Connection ("_DC"), Data Foundation ("_DF"), Business View("_BV"), Business Element ("_BE"), LOV (no suffix), and possibly Report Parameter.  Best practice is to create the LOV for your dynamic parameters in the BVM in the first place - that way you can create a single Data Connection for all of your params that use the same database.  You'll also create the SQL in the Data Foundation to just pull the lookup values instead of using the full SQL for your report to get them.  You can then create a single set of objects for each parameter that can be reused in your reports as needed.

-Dell

[former_member338618 · Accepted solution]

Hi Dell,

Am i looking in the right place? This is what I find in the parameter itself:

Is this what I should be looking for in BVM?

[DellSC · Accepted solution]

Yes.  I used the wrong terminology (that will teach me to do this from memory!).  You want to look for the Prompt Group in the BVM.  When you open the Prompt Group, it will tell you which LOV it uses so that you can find the Data Foundation.

-Dell

[former_member338618 · Accepted solution]

I Think you've set me on a trip down the rabbit hole with this, It looks like I need quite a bit of training.

I think we must be running Crystal as inefficiently as possible...

Thanks for all the help

[DellSC · Accepted solution]

It's something that it appears that a LOT of people aren't aware of!

-Dell